Sort one by one column then by another then remove duplicates

[Anonymous]

I have a table in Excel like the one below. I can sort it by the left column and then by the right column. When I then remove duplicates in the left column, it leaves me with a single entry for that column and keeps the highest number from the right

column. I've pasted the Excel results here (I spaced the second column to show that the highest revision was definitely kept for the corresponding item in the first 2 columns). Is there a way to do this in PBI like I can Excel? Ultimately, I need to be able to find the highest revision number of each work order and then calculate the time it took for a work order to go from start to finish. I can't simply add the time between each revision because our system doesn't capture it that way. 

With Duplicates		Duplicates Removed
WO #	Revision #		WO #	Revision #
11356	4		11356	4
11356	3
11356	2
11356	1
11356	0
11364	1		11364	1
11364	0
11366	3		11366	3
11366	2
11366	1
11366	0
11392	1		11392	1
11392	0
11398	3		11398	3
11398	2
11398	1
11398	0
11407	3		11407	3
11407	2
11407	1		11422	1
11407	0
11422	1
11422	0
11433	2		11433	2
11433	1
11433	0
11528	6		11528	6
11528	5
11528	4
11528	3
11528	2
11528	1
11528	0

I appreciate any help. I also want to point out that while I can obviously do this in Excel, the point is to do as little manipulation in Excel as possible. Currently, every last report I have created works that way. It makes it easier for any other person to run the report rather than trying to understand how I did it in Excel and then transformed it to PBI. 

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO #", Int64.Type}, {"Revision #", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WO #"}, {{"Max", each List.Max([#"Revision #"]), type nullable number}, {"All", each _, type table [#"WO #"=nullable number, #"Revision #"=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Revision #"}, {"Revision #"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each [Max]=[#"Revision #"]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max", "Custom"})
in
    #"Removed Columns"

Hope this helps.

[FrankAT]

Hi @Anonymous ,

with DAX you can achieve this as follows:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

For whatever reason, I can't get this to work. ...doh...it (my known column to verify it was returning properly)was set as sum and not do not summarize. Works fine. 

[Anonymous]

Ok, I figured how to edit the query to sort by WO then by Revision. But, when I then remove duplicates, it keeps the lowest revision number vice the highest. Maybe another line in the query editor? 

#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"PO", Order.Ascending},{"Revision", Order.Descending}})

[Anonymous]

For whatever reason, the cut and paste didn't hold. Just know that the WO in the 2 instance is the highest remaining by revision after dupes are removed.