In this article we will see how to remove duplicate record and keep the latest record in table using Power Query.
As you can see in below screenshot, here we have a table which contains product sold details by year.
Now the requirement is to remove duplicate product details and keep the latest record of each product in table.
As you can see, each product has yearly sales so we will use Year field to identify latest sales of product.
So, the idea is first we will sort the records by Year in descending order and then apply the remove duplicates to keep the latest record of each product.
Lets sort the records by year in descending order as shown below.
Now you can see, records are sorted by year in descending order.
Also, a step for sorting rows is created in Applied steps, also you can see the sorting formula in formula bar.
= Table.Sort(#"Changed Type",{{"Year", Order.Descending}})
Now, we will store entire table data into memory using Table.Buffer function, it fetches all rows from source table and store them into memory and after that apply the remove duplicate.
Lets store the sorted data into memory using Table.Buffer function, for this we will modify the sorted rows step in Power Query as shown below.
= Table.Buffer(Table.Sort(#"Changed Type",{{"Year", Order.Descending}}))
After that, select Product Id and Prod Name field and apply remove duplicates
Once you click on remove duplicate and you can see, it removes the duplicate and keep the latest record associated with each product.
Also Read..
