Forum Discussion
Solved
Parent Split for two columns
Hello guys, I have a very simple problem, but I cannot seem to find a solution. I have a very simple table containing 3 columns: Product, Serial & Heritage as below image. Unfortunately for the...
Didn't help, but your idea manage to point me in the right direction. Got to work with a duplicate of the same table:
1st Table splits the Serial, 2nd Table splits the Heritage.
Create Index in both of them, and then Merge them together using the Indexes and comum column.
Thanks!
Hi jomaor1 ,
You can use Text.Split and List.Zip for a very clean process:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQJc1bSUXLUcdIB0YY6RjrGSrE60Uohjn7eQAEXHVcdNyBtomOqY6YUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Serie = _t, Patri = _t]),
addZippedLists = Table.AddColumn(
Source,
"ZippedLists",
each List.Zip(
{
Text.Split([Serie], ","),
Text.Split([Patri], ",")
}
)
),
expandZippedLists = Table.ExpandListColumn(addZippedLists, "ZippedLists"),
extractZippedLists = Table.TransformColumns(expandZippedLists, {"ZippedLists", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
remOthCols = Table.SelectColumns(extractZippedLists,{"Product", "ZippedLists"}),
splitByDelimiter = Table.SplitColumn(remOthCols, "ZippedLists", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Serie", "Patri"})
in
splitByDelimiter
Pete