Daily or weekly reports constantly need data appended to a source folder in non-database situations. As the files pile up folder it can be a little difficult to differentiate between them in Power BI, when one really only needs the latest for a particular visualization or report page.
Another story on Medium, linked below, does a great job of detailing how to get only the latest file data. The solution points to creating a separate, identical query on the same pile of data and then, singling out the most recent report in the new query.
A few standard conventions are required to do this. Naming files to include a YYYYMMDD date prefix makes it a little easier to perform the sort needed but inserts an extra “hands on” step in the process. [Date Modified] or [Date Created] from the file attributes usually prove more useful.
While all of this is effective enough, it can make things a little clunky; especially true if seeking to have the queries always mirror one another. There are quite a few elegant solutions to do just that too, if one digs around enough.
There is a simpler solution. It still requires sorting the data in Power Query. It does not require another query.
In Power BI, a slicer leveraging whatever field used to do that sort pulls off the same stunt. Place a “Top N” filter on that slicer to show only the “Top 1” based on whatever seems most appropriate. In my use case, it is a date field and works perfect every time, with every “Scheduled Refresh,” and appears a little more flexible too.
There are a few limitations on editing the text that appears in the slicer. A few modifications may be needed to get the right look. However, it enables the report to host as many visualizations as needed, based on one data file, without an extra query and the need to maintain that query. I suspect it probably performs better too.
. . .
further reading
No comments:
Post a Comment