Forum Discussion
olander
3 years agoFrequent Visitor
Split a column by positions in another column
Hello, I am trying to split a column of text strings by position where the positions are listed in a second column. Here’s a mock set of data to illustrate: Names Positions AnnaBobCamilleDari...
- 3 years ago
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 Custom1 - 3 years ago
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
3 years agoSuper User
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.
olander
3 years agoFrequent Visitor
AlienSx thanks so much for your work on this. You and wdx223_Daniel should both get the honors.