Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Max date per unique id

Hi 

 

I have a facts table with two columns  Unit ref, Completed Data. The unit ref can appear on more than one line , but with a different  completed date.  

 

I need a measure column that displays the Max Dates for each of the Unit refs

 

Unit refCompleted DateDAX Max Date
43734711/08/201911/08/2019
8767830/05/201016/04/2015
8767823/08/201116/04/2015
8767816/04/201516/04/2015
1804/04/202102/02/2022
1802/02/202202/02/2022
765430/05/201001/03/2022
765405/05/201101/03/2022
765409/04/201201/03/2022
765415/03/201301/03/2022
765418/02/201401/03/2022
67801/03/202201/03/2022
6869623/05/201802/04/2020
6869628/04/201902/04/2020
6869602/04/202002/04/2020

 

I would also be interested in how this could be done in power query.

 

thank you

 

RIchard

 

Need Help
  • PC2790's avatar
    PC2790
    4 years ago

    Hey cottrera ,

     

    It can be done using "Group By" in Power Query.

    See the screenshot of the parameters arrangement in Group By

    And this is the advanced editor code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc/LDcQwCATQXnyOxB/jWqL030YCa8lCe30ChrnvoTJF57gGEQQw0hrPdY+YPuNTQUBLxsYsgDVNjckBNdmKKU0hhekIA6dwyXTT/5ytBnZCtq26R22bDFBSpWkAZhJp6X4R5IR7+PJdp5Kic+w6q/Ov0ffp8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit ref" = _t, #"Completed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit ref", Int64.Type}, {"Completed Date", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit ref"}, {{"Max", each List.Max([Completed Date]), type nullable text}, {"All rows", each _, type table [Unit ref=nullable number, Completed Date=nullable text]}}),
    #"Expanded All rows" = Table.ExpandTableColumn(#"Grouped Rows", "All rows", {"Completed Date"}, {"Completed Date"})
in
    #"Expanded All rows"

    The outcome will be:

     

     

2 Replies

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hey cottrera ,

     

    It can be done using "Group By" in Power Query.

    See the screenshot of the parameters arrangement in Group By

    And this is the advanced editor code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc/LDcQwCATQXnyOxB/jWqL030YCa8lCe30ChrnvoTJF57gGEQQw0hrPdY+YPuNTQUBLxsYsgDVNjckBNdmKKU0hhekIA6dwyXTT/5ytBnZCtq26R22bDFBSpWkAZhJp6X4R5IR7+PJdp5Kic+w6q/Ov0ffp8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit ref" = _t, #"Completed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit ref", Int64.Type}, {"Completed Date", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit ref"}, {{"Max", each List.Max([Completed Date]), type nullable text}, {"All rows", each _, type table [Unit ref=nullable number, Completed Date=nullable text]}}),
    #"Expanded All rows" = Table.ExpandTableColumn(#"Grouped Rows", "All rows", {"Completed Date"}, {"Completed Date"})
in
    #"Expanded All rows"

    The outcome will be: