Latest extract per article

[joshua1990]

Hi community!

To ensure every changing dimension for each week is captured we have a weekly extract for all our articles.

The result is structued like this:

Year-Week Extract	Article	Dim 1	Dim 2
2023-01	A	1	1
2023-02	A	1	1
2023-03	A	2	1
2023-01	B	1	1

Now I would like to get for every article the latest available row.

How would you do that in PQ?

[ppm1 · Accepted solution]

Here's one way to do it, with a Group following by a Transform step using Table.Max. Paste the below into a blank query (Advanced Editor) to see how it works.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1MFTSUXIEYkMwjtWBiRvhEDeGihuhiYPUOSHUxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year-Week Extract" = _t, Article = _t, #"Dim 1" = _t, #"Dim 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year-Week Extract", type date}, {"Article", type text}, {"Dim 1", Int64.Type}, {"Dim 2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Article"}, {{"AllRows", each _, type table [#"Year-Week Extract"=nullable date, Article=nullable text, Dim 1=nullable number, Dim 2=nullable number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Max(_, "Year-Week Extract")}}),
    #"Expanded AllRows" = Table.ExpandRecordColumn(Custom1, "AllRows", {"Year-Week Extract", "Dim 1", "Dim 2"}, {"Year-Week Extract", "Dim 1", "Dim 2"})
in
    #"Expanded AllRows"

Pat