Group by date and keep all columns

[Syndicate_Admin]

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 Changed Date of each ID. I have found the List.Max function but so far all other columns have been removed except for the one I am grouping by but I need to keep all for this specific date.

ID       Changed Date   State     Previous State

2	07.09.2019	Ready	Created
1	10.09.2019	In Work	Implemented
2	14.09.2019	In Work	Implemented
1	08.09.2019	Ready	Created
1	16.09.2019	Implemented	Done
2	09.09.2019	Created	In Work
1	10.09.2019	Created	In Work

The output should then be:

1	16.09.2019	Implemented	Done
2	14.09.2019	In Work	Implemented

Any ideas on how to solve that?

Thanks a lot and kind regards!

[Syndicate_Admin]

Please use Group By in Edit Queries using Advanced Option, Group by column as ID and MAX of Changed Date, and All rows with Column name as "AllRows". Click ok. Now the table will have ID, Date, AllRows columns. Click AllRows column, expand and remove other columns that are not required. Plese try and let know if this works.

[Syndicate_Admin · Accepted solution]

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]

In this way, when expanding the AllRows column, it again brings back all rows and the aggregation gets void.

Instead of doing AllRows, in Advance, add all columns in Grouping except the one that needs to be aggregated. In this way it will keep all columns and aggregate the ones need aggregation.

For this specific query here, ID, State and Previous State should be kept in Grouping and Changed Date in Aggregation.

In case having loads of columns to be grouped, don't bother to add them one after another. Do it by grouping 1 or 2 and later add all the column names in Advanced Editor.

[GB-Andworx]

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"