CODEWITHSUNDEEP
excelpowerbipowerquery
Siroros Roongdonsai
Siroros Roongdonsai

Reputation: 31

Power Query Remove latest row of data did not work

I have the table from excel file which have the structure like below;

Key     Report_Month      Amount
001     31/01/2017        0
001     28/02/2017        1000
002     31/05/2017        0
002     30/06/2017        60000

I need the latest row of each ID. The final result should be like this;

Key     Report_Month       Amount
001     28/02/2017        1000
002     30/06/2017        60000

So I started by sorting data by ID and ordering them by ascending and descending respectively. Then I removed the duplicated row by using ID in removing. but It show the first record of each ID.

Here is my code 

#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Key", Order.Ascending}, {"Report_Month", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Key"})

I have tried Group by ,but it showed only related columns (ID, and Date). I lost other columns. I don't think we can use Group By for solving this case. Please suggest me.

Upvotes: 1

Views: 169

Answers (1)

Siroros Roongdonsai
Siroros Roongdonsai

Reputation: 31

I have already found solution. I have to duplicate my table and Group the Data to get the latest date of each key. Then apply inner join in for selecting the records I needed. I follow the instruction from this website https://community.powerbi.com/t5/Desktop/How-to-remove-duplicates-based-on-sort-order/td-p/130860

Upvotes: 1

Related Questions