Remove & add certain data in load

[Nico_Beckers]

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

[j_ocean · Accepted solution]

1. Ingest all files using the combine function, but make sure some relevant file-level date stamp remains (it auto-generates a "remove other columns" step before the expand, you can edit this).
2. Reference this table into another table. Keep only the file-level date stamp and the by-row date stamp.
3. Group-by the by-row-level date, aggregate max file-level date. This gives you a list: for every data date X, which file Y contains the most most recent version.
4. Inner merge this resulting list on both dates. This will drop anything that's not in your list of selected rows.

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.

[BA_Pete]

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