question

AlexS-3449 avatar image
0 Votes"
AlexS-3449 asked RonRosenfeld-3452 edited

Power Query to Extract Dataset

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

power-query-not-supported
example.jpg (58.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

RonRosenfeld-3452 avatar image
0 Votes"
RonRosenfeld-3452 answered RonRosenfeld-3452 edited

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



image.png (17.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.