Pivot multiple values of one id

[bharukc]

I have a table that I need to pivot. Pivoting should create multiple rows per id

parent.   Child.   Level

C              A.         1

C             B.          2

A.             D.          1

C.             E.           1

end result

parent.  Level 1.     Level 2

C.            A.             B

C.            E.              Null

A.            D

[v-yiruan-msft · Accepted solution]

Hi @bharukc ,

I created a sample pbix file(see the attachment), please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclbSUXLUAxKGSrE6EK4TiGsE5oJlXJCkQUxXKD8WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"parent." = _t, #"Child." = _t, Level = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"parent.", type text}, {"Child.", type text}, {"Level", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Level", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Level", type text}}, "en-US")[Level]), "Level", "Child."),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Level 1"},{"2", "Level 2"}})
in
    #"Renamed Columns"

Best Regards