Power Query to Extract Dataset

AlexS 1 Reputation point
2021-07-31T16:29:45.143+00:00

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:

119487-example.jpg

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

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,525 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Ron Rosenfeld 271 Reputation points
    2021-08-01T00:41:13.81+00:00

    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"  
    

    `
    119612-image.png

    0 comments No comments