Forum Discussion
Splitting Columns
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 |
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/
1 Reply
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/