There are quite a few common business scenarios where you get data in the form of a file on a regular basis from some source system and you need to process these files into your Power BI reports. There are 3 common patterns that come to mind.
- Each day/month/year you get a new file containing incremental data. All the data in all the files need to be combined and loaded.
- Each day/month/year you get a new file and it replaces the old file.
- You have a workbook with multiple sheets within a single workbook that need to be combined. This could be in addition to pattern 1 or 2 above.
I have previously shared an article explaining pattern 1 (how to combine all files in a folder with Power Query). Over the next few weeks I will share a couple of simple techniques for the other 2 common scenarios starting today with pattern 2 above.
Load the Latest Version of a File
This pattern is common if your new file contains a superset of all the data. It could be a transactional file that grows in length each time or it could be a dimension/lookup table (such as Customers) that can change slowly over time, and you always want to see the latest version. My advice to all my Power Query students is “zero touch the file”. In other words, your objective should always be to have the absolute minimum amount of interaction with the source files possible and push all the work into Power Query. This will minimise the amount of work/rework you have to do in the future. Thinking about the use case here “load the latest version of a file”, the question becomes “how can I make this zero touch”? There are a few issues to consider including naming/renaming of the file and also archiving old copies of the file. This doesn’t sound like zero touch to me.
Load from Folder, not File
My solution to this problem is to load the file from a folder and not directly from the file. The pattern is to
- Have the system extract the file to include a unique name eg myFile-YYYYMMDD.xlsx
- Place the file into a standard folder
- Sort the folder descending based on creation date
- Select the first file from the list of files
Using the above approach you will always load just the latest version of the file while keeping an archive history of all the files in the same folder – zero touch.
Here is a video demo of how to do it. Unfortunately I was away from my home office when recording this video and there is quite some background noise – sorry about that.
Want to Learn Power Query in a Structured Way?
My Power Query Online training course consists of 7.5 hours of online video training material that will teach you everything you need to know about using this fabulous tool for both Power BI and Excel. You can watch some free sample videos and find out more about registration following the link.
And, here is the link to the Power BI Report Themes blog article that I mentioned in the video – https://exceleratorbi.com.au/changing-defaults-in-power-bi/.