A week ago in my last training, I was demonstrating how to get rid of duplicate records when making a dimension table. One of the people in attendance asked is there a way to get rid of duplicates but keep the most recent duplicate record based on a date column. I thought, “Of Course!”. All we need to do is use the Query editor, do a sort on the date column, and then get rid of duplicates. Unbeknownst to me, it didn’t work. It kept the oldest record and not the newest.
The reason being after doing some research is that the Power Query editor will make determinations if it can do some of your steps in a different order and still give you the same result while producing the quickest data load. Normally, that is what an ideal Power BI user wants, but in this case, we need the sort to happen before the remove duplicates step. Thus, after research comes in the function Table.Buffer to modify our sort code.
Take a look at my video for a further explanation and demonstration of how to use this function to your advantage.