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...
Hi Anonymous,
This is the standard GroupBy functionality in PBI:
Regards,
John
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