unpivot column to N number of columns based on IDs

[ofir_n1]

hi

i have a column that contain transaction ID , and another attribute column that contain user type,

the transaction id reapeat multiple times according to the user type

i wish to create the following table from the data 

to:

transaction id	target1	target2	target3

[mah_priya94]

Hi @ofir_n1 ,
Please try the following code.
Won't require you to manually set the Target number.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijOMiorT8svNi3MNCkyMUg0qTBX0lFyLCjIzCsuScxLTlWK1YlWKs3LzssvzwPKhBanFrkX5ZcWgMUj3YoKTc3LksvdPNINkwIsPX0y3MzSHR2dAk1IVItuI9xhVaV++UZVRh5wh4FMxaeEkDwum4rMMiwKMivDUnDbhKaEkDyKTbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction ID" = _t, Targets = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction ID", type text}, {"Targets", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Transaction ID"}, {{"Data", each _, type table [Transaction ID=nullable text, Targets=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "ID", 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Targets", "ID"}, {"Targets", "ID"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each "Target "&[ID]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Data", "ID"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Targets")
in
#"Pivoted Column"

[v-yingjl · Accepted solution]

Hi @ofir_n1 ,

You can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijOMiorT8svNi3MNCkyMUg0qTBX0lFyLCjIzCsuScxLTlWK1YlWKs3LzssvzwPKhBanFrkX5ZcWgMUj3YoKTc3LksvdPNINkwIsPX0y3MzSHR2dAk1IVItuI9xhVaV++UZVRh5wh4FMxaeEkDwum4rMMiwKMivDUnDbhKaEkDyKTbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction ID" = _t, Targets = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction ID", type text}, {"Targets", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Transaction ID"}, {{"Data", each _, type table [Transaction ID=nullable text, Targets=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Data],"Targets")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Target 1", "Target 2", "Target 3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Target 1", type text}, {"Target 2", type text}, {"Target 3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Data"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnkitKukreja]

Hi @ofir_n1 

Please have a look at the link below.

Pivot columns (Power Query) - Excel (microsoft.com)

Please mark it as a solution, if it answers your question.

Thanks and Regards,
Ankit
www.linkedin.com/in/ankit-kukreja1904