Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 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

207.09.2019ReadyCreated
110.09.2019In WorkImplemented
214.09.2019In WorkImplemented
108.09.2019ReadyCreated
116.09.2019ImplementedDone
209.09.2019CreatedIn Work
110.09.2019CreatedIn Work

 

The output should then be:

 

116.09.2019ImplementedDone
214.09.2019In WorkImplemented

 

Any ideas on how to solve that?

 

Thanks a lot and kind regards!

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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"
dax
Community Support
Community Support

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.

SethuPower
Frequent Visitor

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors