Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago
Solved

Latest extract per article

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 ...
  • ppm1's avatar
    3 years ago

    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