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. T...
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/
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/