Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
On a daily base I get an excel extract from the past week. What I then need to do is remove the past 6 days from my existing data file and paste the 7 days I received in the latest extract. This because the data can change during the week.
So data 11 =>17th of the month gets deleted, and replaced with 11-18th of the month I received in my new file.
Day after, 12 to 18 removed, 12 to 19 added from the newly received file, rince and repeat.
Is there a way to automate this in power Query instead of updating the 'big' excel file daily?
Many thanks in advance!
KR,
Nico
Solved! Go to Solution.
Coding the extract date into the file name and then parsing it out is more reliable, as certain operations can futz with both the created and modified dates.
Coding the extract date into the file name and then parsing it out is more reliable, as certain operations can futz with both the created and modified dates.
Hi @Nico_Beckers ,
I'd just dump every Excel file into a network or SharePoint folder and, in PBI Desktop, use the Folder/SharePoint Folder connector to that folder.
Once connected, choose the 'Combine & Transform' option.
In Power Query, you can then adjust the standard combined query code at the Source step to filter the [Folder path] column to your document storage location, and also adjust the Remove Columns step later in the query to keep the [Date Created] column:
Once you've completed your generic transformations in the Transform Sample File helper query, you'll be able to filter or group the main output query to only keep the row that has the latest [Date Created] value for each unique combination of dimensions.
...or, even simpler, just filter the [Date Created] column at the Source step something like this:
Date.From([Date created]) >= Date.AddDays(Date.From(DateTime.LocalNow()), - 7)
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.