Forum Discussion
Filtering Duplicate Data
I am importing data from SQL Server and noticed that my data is getting bloated due to duplicate data that is being loaded. Is there a way to filter out duplicates and keep only the most recent data? Is this suppose to be done in Power Query at the time of ETL? Below are data: Table 1 = the current data including duplicates / Table 2 = expected output
(Table 1 = the current data including duplicates)
| transaction_id | description | total | transaction_datetime |
| 1 | tacos | 10.00 | 2021-01-26 10:00:26.000 |
| 1 | tacos | 10.00 | 2021-01-26 10:20:52.000 |
| 2 | pizza | 6.00 | 2021-02-03 12:30:43.000 |
(Table 2 = expected output)
| transaction_id | description | total | transaction_datetime |
| 1 | tacos | 10.00 | 2021-01-26 10:20:52.000 |
| 2 | pizza | 6.00 | 2021-02-03 12:30:43.000 |
Any advice will be greatly appreciated.
Anonymous in power query, select all columns and then select remove duplicates.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
3 Replies
Anonymous in power query, select all columns and then select remove duplicates.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k thank you for your support. Unfortunately, I am still getting the same results. My thoughts are that power query is still keeping the duplicate values because the timestamp on the duplicates are unique (please look at my sample table 1 / transaction_id = 1).
Any other thoughts on how you would approach removing duplicates that have unique datetime stamps?
parry2k After reworking your suggestion, it ultimately worked out. The only difference with your solution and mine is that I selected the individual column to remove the duplicates. My initial concern was that it was going to remove stores or other legitimate duplicates.
Thank you so much for your support!