Splitting Columns

[Anonymous]

I have been looking around for a solution and am unable to find one for this specific question. I have a "parent" list of unique identifiers with their corresponding "child" listing of identifiers. The "parents" are listed multiple times if they have multiple "children." I'd like the "children" to be split into columns and have the "parent" be one row. See example of desired solution below. Appreciate any advice!

Current
Parent	Child
10002	56747
10002	139505
10003	19733
10003	10064
10003	68275
10003	201692
10003	201691
10003	20928
10004	116130
10004	43641
10004	56781

Desired
Parent	Child 1	Child 2	Child 3	Child 4	Child 5	Child 6
10002	56747	139505
10003	19733	10064	68275	201692	201691	20928
10004	116130	43641	56781

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about this:

Note, make sure that your Child column is in text format!

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67DcAgDIThXVxTnB/YeBbE/msEGiIn7Sf9p5uTGIBQo+5hQau9wpod/ZIeylCtArgV8SFRKwF7yp/4Qynjip1tdlYUMnXjIvv32LIe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", Int64.Type}, {"Child", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Parent"}, {"Child", each Text.Combine([Child], ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Child", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Child.1", "Child.2", "Child.3", "Child.4", "Child.5", "Child.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Child.1", Int64.Type}, {"Child.2", Int64.Type}, {"Child.3", Int64.Type}, {"Child.4", Int64.Type}, {"Child.5", Int64.Type}, {"Child.6", Int64.Type}})
in
    #"Changed Type1"

Let me know if this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/