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