Rolling Data Capture

[RMW13]

Hello Everyone,

I get daily reports with what I consider to be "rolling data".  I've created an example below to show how the data reports are received.  The daily data report is sent out overnight with "yesterday's" data and the 3 days previous.

I have created a simplified example below where my daily data report keeps has only the data for four days (yesterday's plus the 3 previous).  When a data point is more than 4 days old, it is filtered out of the report.  I cannot change this filter behaviour.  I want to be able to build a dashboard with the sales data going back to the start of 2022.  

I've tried building a summation query using a "base data report" and then appended the new "daily data report" using Power Query and the following function,

= Table.SelectRows(Source, each Date.IsInPreviousNDays([lt Cdate], 1))

However, this results in a summation query that has all of the data in my "base data report" and the data in the previous day, which means that it is only accurate for the first day after my "base data report".  After the first day, I get a gap in my data for any days between the last day on the "base data report" and the previous day's data.  

Using my example above, if I set up my "base data report" for up to and including Jan 21, 2023, then on Jan 23, 2023 the summation query will include all data up to Jan 21 and then the new Jan 22 data is appended to it.  But on Jan 24, the summation query will include all data up to Jan 21 and then the data from Jan 23 is appended to it, but I get a data gap of one day because I am not capturing the data for Jan 22.

Any thoughts on how else I could capture each new day's data and add it to my "base data report" without having to do it manually?  

I posted something similar last year, but did not have any luck and I have yet to be able to figure it out.  Any help would be appreciated.

Cheers!

[DataInsights]

@RMW13,

If you place all the data reports (source files) in a folder, you can use the Folder connector. Power Query will automatically append all the data reports in a single table. Remove the column Source.Name and then Remove Duplicates. New data reports will automatically be included when you refresh the Power BI report.

[RMW13]

@DataInsights  Thank you for your response, but all of my data reports are in a single folder.  I save them there using PowerAutomate.  This leads to a problem though because I end up with duplicates for days that are included in more than one report.  I tried removing duplicates, but it removes all instances of the duplicated data, so I end up only showing unique values, which is only the new entries on the previous day's report.... unless there is a trick to remove duplicates I am not aware of.

Below is a snippet where I show one example of duplicated data. 

This example I highlighted with the green marks is the same entry that shows up on the three reports.  If I were to continue the examples for Jan 26 and Jan 27, you would see that the data entries for the highlighted value would drop off of the daily report on Jan 27, and because it is after the "Base Data", I would lose all visibility to these sales.

What I am looking for is a way to automatically add the highlighted data to the "Base Data" report I created manually that includes all data up to, and including, Jan 21.

Cheers!

[DataInsights]

@RMW13,

Are all data reports kept in the folder, or do you keep only the latest x number of data reports? If all data reports are kept in the folder, then logically you should be able to union them all, remove duplicates, and be left with only one instance of each row. In the example, if you union the three data reports, you would get three rows for Jan 22, Salesperson A, 500. If you remove duplicates, two of these rows would be removed, leaving you with one row for Jan 22, Salesperson A, 500. If you then add data reports for Jan 26 and Jan 27, the row for Jan 22, Salesperson A, 500 would still be picked up via the Jan 23, Jan 24, and Jan 25 reports (this would be a union of 5 data reports).

[RMW13]

I keep all reports in the same folder.

Regarding the suggestion to remove duplicates... I've tried this, but when I do it, it removes all instances of the duplicate, so I'm left with only the new entries.  Is there a way the duplicated instances only?  I know in Excel when you remove duplicates, it leaves you with the original instance, but I can't seem to get that to work in PBI/PQ.

[DataInsights]

@RMW13,

What columns are in the table when you apply the Remove Duplicates step? If there is a column like "Source.Name" that is creating uniqueness, then that column needs to be removed. Or, you can use the second argument of Table.Distinct to specify the columns to test for uniqueness. Can you provide before and after screenshots of your Power Query table (before and after you remove duplicates), as well as the M code?