Forum Discussion
Collapsing 2 rows in to 1
Hi everyone,
I have a query with 2 rows per ID; one row with a value in the Pillar column and one row with a value in the Deliverable column. I would like to collapse both rows in to one but have not been able to figure out how to do it with grouping.
Kudos to anyone who can assist.
Hi Anonymous,
This is the standard GroupBy functionality in PBI:
Regards,
John
4 Replies
Hi Anonymous,
In the most simplistic way (assuming you do not mind hardcoding the column names):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQIiQwt9A0t9IwMjI6VYHYiokRFcCKgEJOoEUmiATRSk3BTFBGeQkIE+UDmyWmeoWjO4BEjUBWSuMbq5LhC1FgilsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [USER_ID = _t, CREATION_DATE = _t, ADMINISTERED_DATE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"USER_ID", type text}, {"CREATION_DATE", type date}, {"ADMINISTERED_DATE", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"USER_ID"}, {{"CREATION_DATE", each List.Max(_[CREATION_DATE])}, {"ADMINISTERED_DATE", each List.Max(_[ADMINISTERED_DATE])}}) in #"Grouped Rows"Cheers,
John