Forum Discussion
Rolling Data Capture
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!
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).
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.
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?