Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group by date and keep all columns

Hi everyone,    I am rather new to Power Bi and i have a question someone might be able to answer. I have a set of entries for the same ID. I only want to keep the tuple with the most recent Chang...
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi jama1234

    You could try below M query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIw1zOw1DMyMLQEcoJSE1MqgbRzUWpiSWqKUqxOtJIhkG9ogKTIM08hPL8oG8TKLchJzU3NgykFmWdoQpRSkKkGFsRYbYZsHpIpOkou+XmpcHuBihDqYIYgXIDVJ1iUxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Changed Date" = _t, State = _t, Create = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Changed Date", type text}, {"State", type text}, {"Create", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"max", each List.Max([Changed Date]), type text}, {"all", each _, type table [ID=number, Changed Date=text, State=text, Create=text]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Changed Date", "State", "Create"}, {"all.Changed Date", "all.State", "all.Create"}),
    #"selectrow"=Table.SelectRows(#"Expanded all",each [max]=[all.Changed Date]),
    #"Removed Columns" = Table.RemoveColumns(selectrow,{"all.Changed Date"})
in
    #"Removed Columns"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

The Microsoft documentation on the Group By operation shows an alternative approach that works well too. See How to GROUP BY or summarize rows - Power Query | Microsoft Learn

 

My query looks like this:

let
    Source = #"MySource",
    #"Sorted Rows" = Table.Sort(Source,{{"Updated Time", Order.Descending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"ActivityID", type text}, {"Updated Time", type datetime}}),
    #"Group Rows" = Table.Group(#"Changed Type", {"ActivityID", "Status"}, {{"Updated Time", each List.Max([Updated Time]), type nullable datetime}, {"AllGroupedRows", each _, type table [ActivityID=nullable text, Activity Type=nullable text, Old Status=nullable text, Status=nullable text, Updated By=nullable text, Updated Time=nullable datetime, Duration=nullable number]}}),
    #"Sorted Rows1" = Table.Sort(#"Group Rows",{{"ActivityID", Order.Ascending}, {"Updated Time", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each Table.Max([AllGroupedRows],"Updated Time")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Updated By"}, {"Updated By"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllGroupedRows"})
in
    #"Removed Columns"