Power Query to Extract Dataset

Question

Hi -

I have a query that results in a table that lists a repeating group of like records with varying revision numbers (reflected in a separate field). I’d like to query this table for a list of the Items with there highest revision number. This is what I’m after:

I’ve tried some things with Group By but can’t seem to get what I’m after. I’d sure appreciate anyone’s help!

Cheers,

Alex

Answer 1

You should be able to just Group by the first two columns and aggregate by the Max of the third column:

   let  
       Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],  
       #"Changed Type" = Table.TransformColumnTypes(Source,{<!-- -->{"Item", type text}, {"Data1", type text}, {"RevNum", Int64.Type}}),  
       #"Grouped Rows" = Table.Group(#"Changed Type", {"Item", "Data1"}, {  
           {"RevNum", each List.Max([RevNum]), type nullable number}  
           })  
   in  
       #"Grouped Rows"  

`