Collapsing 2 rows in to 1

[bchager6]

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.

[jbwtp]

Hi @bchager6,

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

[bchager6]

@jbwtp Thank you. How did you produce that code?

[jbwtp · Accepted solution]

Hi @bchager6,

This is the standard GroupBy functionality in PBI:

Regards,

John

[bchager6]

@jbwtpThank you!