Forum Discussion
Split a column by positions in another column
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY9JD4IwEIX/S889UBXx6r7vu4TDCCU0KW3SlkRi/O8SRjzO+96bmReGpK8UDPRzCLmQko/ACELJ26M+7VHmf0hEQzKOMz3RrynPc/hTRDOujJhLCwuepsi6lHkIl2DkStgmg+IaTLnhzkm+Na7ZxhjCHVeqrEWc9wVIAdlBW36sEJx0kmCm+q5FWYC2s7GFhIuInTbXqsfvYkBZGw03AXdQ4qEzUMg6dYPoCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Positions = _t]), Custom1 = Table.AddColumn(Source,"Custom",each Splitter.SplitTextByPositions(List.Transform(Expression.Evaluate([Positions]),each if _=0 then 0 else _-1))([Names])) in Custom1KeyurPatel14 , thank you! Lets make it a little bit shorter
let a = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY9JD4IwEIX/S889UBXx6r7vu4TDCCU0KW3SlkRi/O8SRjzO+96bmReGpK8UDPRzCLmQko/ACELJ26M+7VHmf0hEQzKOMz3RrynPc/hTRDOujJhLCwuepsi6lHkIl2DkStgmg+IaTLnhzkm+Na7ZxhjCHVeqrEWc9wVIAdlBW36sEJx0kmCm+q5FWYC2s7GFhIuInTbXqsfvYkBZGw03AXdQ4qEzUMg6dYPoCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Positions = _t]), b = Table.TransformColumns(a, {{"Positions", each Text.BetweenDelimiters(_, "{", "}", 0, 0), type text}}), c = Table.AddColumn(b, "Lists", (x) => List.Transform(Text.Split(x[Positions], ","), (y) => [a = Number.From(y), b = if a <> 0 then a - 1 else a][b] )), fx = (txt as text, pos as list) as table => let tbl = #table({"Names", "Result"}, {{txt, txt}}) in Table.SplitColumn(tbl, "Result", Splitter.SplitTextByPositions(pos)), d = List.Transform(List.Zip({c[Names], c[Lists]}), (x) => fx(x{0},x{1})) in Table.Combine(d)However, wdx223_Daniel 's code with Expression.Evaluate is much more elegant.
Hi olander ,
Please try the below M code which I have made by changing the code of AlienSx as that code was not giving the expected result you want.
M Code:
let
a = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY9JD4IwEIX/S889UBXx6r7vu4TDCCU0KW3SlkRi/O8SRjzO+96bmReGpK8UDPRzCLmQko/ACELJ26M+7VHmf0hEQzKOMz3RrynPc/hTRDOujJhLCwuepsi6lHkIl2DkStgmg+IaTLnhzkm+Na7ZxhjCHVeqrEWc9wVIAdlBW36sEJx0kmCm+q5FWYC2s7GFhIuInTbXqsfvYkBZGw03AXdQ4qEzUMg6dYPoCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Positions = _t]),
b = Table.TransformColumns(a, {{"Positions", each Text.BetweenDelimiters(_, "{", "}", 0, 0), type text}}),
c = Table.AddColumn(b, "Lists", each List.Transform(List.ReplaceValue(List.Transform(Text.Split([Positions], ","), Number.From),0,1,Replacer.ReplaceValue),each _ -1)),
fx = (txt as text, pos as list) as list =>
let
tbl = #table({"name"}, {{txt}})
in Table.ToRows(Table.SplitColumn(tbl, "name", Splitter.SplitTextByPositions(pos))){0},
Custom1 = Table.AddColumn(c,"Result",each fx([Names],[Lists])),
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Lists", "Positions"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Result", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Result", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Result.1", "Result.2", "Result.3", "Result.4"})
in
#"Split Column by Delimiter"
Please try this and let me know if you have any queries and if this helps then please give it a kudos and mark it as a solution.
Also special thanks to AlienSx as this was his/her logic I just modified it according to your requirement. So please also mark his/her reply as a solution.
Thank you.
KeyurPatel14 , thank you! Lets make it a little bit shorter
let
a = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY9JD4IwEIX/S889UBXx6r7vu4TDCCU0KW3SlkRi/O8SRjzO+96bmReGpK8UDPRzCLmQko/ACELJ26M+7VHmf0hEQzKOMz3RrynPc/hTRDOujJhLCwuepsi6lHkIl2DkStgmg+IaTLnhzkm+Na7ZxhjCHVeqrEWc9wVIAdlBW36sEJx0kmCm+q5FWYC2s7GFhIuInTbXqsfvYkBZGw03AXdQ4qEzUMg6dYPoCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Positions = _t]),
b = Table.TransformColumns(a, {{"Positions", each Text.BetweenDelimiters(_, "{", "}", 0, 0), type text}}),
c = Table.AddColumn(b, "Lists", (x) => List.Transform(Text.Split(x[Positions], ","), (y) => [a = Number.From(y), b = if a <> 0 then a - 1 else a][b] )),
fx = (txt as text, pos as list) as table =>
let tbl = #table({"Names", "Result"}, {{txt, txt}})
in Table.SplitColumn(tbl, "Result", Splitter.SplitTextByPositions(pos)),
d = List.Transform(List.Zip({c[Names], c[Lists]}), (x) => fx(x{0},x{1}))
in
Table.Combine(d)However, wdx223_Daniel 's code with Expression.Evaluate is much more elegant.
AlienSx thanks so much for your work on this. You and wdx223_Daniel should both get the honors.
