Table of Contents
I ran into this question when I wanted to import cost analysis data from Azure into Power BI Desktop using Power Query. Azure Cost Management lets you schedule a daily export of week-to-date or month-to-date cost data and store the files in an Azure Storage account.
When you enable this, the export process creates a folder per year and month, named using a [start date]-[end date] pattern. Because the export is to-date, each file contains all cost records from the beginning of the month up to the export date.
That’s where the issue starts. If you ingest all exported files as-is, you’ll repeatedly load the same cost records for every day in the month. On top of that, you end up importing and transforming far more data than necessary, which wastes processing time and consumes unnecessary system memory.
Get data in Power BI Desktop
Let’s start in Power BI. Choose Get Data, select Folder, and browse to the root directory that contains all monthly folders. In my case, that’s C:\Demo. Click OK to proceed.
(Note: if you’re doing this with an Azure Storage account, select Azure Blob Storage, enter the storage account name, and choose the appropriate container.)
You’ll notice that the preview lists all CSV files from every subfolder under the root directory. Click Transform Data, since we need to add a few transformation steps.
Get last file date per folder
Once Power BI has loaded the data into the Power Query Editor, take a moment to review what was imported. You’ll see familiar metadata similar to what you’d find in File Explorer: the file name, folder path, file extension, and file dates. There’s also a Content column, which holds the binary contents of each file.
The first step is to transform the data so you get the most recent file date for each folder. We’ll do this by using grouping.
- In the Power BI ribbon, go to Transform > Group By
- Choose Basic group by
- Select Folder Path as the grouping column
- Set the new column name to LastDateCreated
- Choose Max as the operation
- Select Date created as the column to aggregate
- Click OK
You now have a table that shows the most recent creation date for each folder. Rename this table to LastDatePerFolder.
Merge queries
Next, repeat the process to retrieve all files from the folders and create a new table. Rename this table to LastFilePerFolder.
- Select the LastFilePerFolder table
- In the ribbon, click Merge Queries
- In the current table, select the Date created column
- Choose the LastDatePerFolder table and select the LastDateCreated column
- Set the join kind to Right Outer, so only files with the latest creation date are returned
- Click OK to apply the merge
In the table preview, you’ll now see only the files you actually need: the most recent files in each folder.
Transform files
Since we only need the Content column, right-click it and choose Remove Other Columns.
Next, click Combine Files in the column header to merge the contents of all files. As long as all files share the same column structure, this step will work smoothly, and your Queries pane will end up looking something like this.
