Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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.
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"
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.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
40 | |
36 | |
28 | |
15 |