After combining my files how do I update the data when a change happens to a unique identifier?

[nebhatt]

Hello All!

I have 19 txt files that are in the same folder labelled Data. Each of these files need to be its own table in Power BI. Everyday a new set of 19 txt files are dropped in the Data folder with the new date attached to the filename. For example: Account_20230920. 

I have combined the 19 txt files from June till now. 

My issue is that any change that has happened to a unique identifer is showing multiple times. 

For Example: 

File: Account_2023_09_18 has an Account ID: 5678 that was LastModifiedOn: 20230917 by User: A1M. Now when I refreshed the data on 9/19/2023 I see Account ID: 5678 twice... because it was LastModifiedOn: 20230918 by User: MB2. I should ONLY see the latest updates made to the Account ID: 5678 not both records. 

How to get rid of this? 

File: Account_2023_09_18  Account ID: 5678  LastModifiedOn: 20230917 User: A1M.

File: Account_2023_09_18  Account ID: 5678  LastModifiedOn: 20230918 by User: MB2 I should only see this record in the Account table

It is causing my fact table Account to have a Many to Many relationship with other tables. 

[JoeBarry · Accepted solution]

Hi @nebhatt 

You filter on the max date, this will only show the record with the latest date.

Please refer to my post https://community.fabric.microsoft.com/t5/Power-Query/Sorting-by-most-recent-date/m-p/3397167/highli...

Thanks

Joe

If this post helps, then please Accept it as the solution