Forum Discussion

olander's avatar
olander
Frequent Visitor
3 years ago
Solved

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...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    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
  • AlienSx's avatar
    AlienSx
    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. 

olander's avatar
olander
Frequent Visitor
3 years ago

Wow wdx223_Daniel what a truly elegant solution! I love learning something new, and that is Expression.Evaluate for me. It's just the kind of function needed to quickly turn the text "lists" into actual Lists. That should get its own kudos. 😄

 

This question is answered, but I will probably post another about solving this problem with either Table.TransformColumns() or Table.SplitColumn(). Neither of these seems to play well with Splitters on dynamic values.

E.g., I can’t substitute your Custom1 step with either

Custom1 = Table.TransformColumns(Source,{{"Names",each Splitter.SplitTextByPositions(List.Transform(Expression.Evaluate([Positions]),each if _=0 then 0 else _-1))([Names])}})

or

Custom1 = Table.SplitColumn(Source,"Names",each Splitter.SplitTextByPositions(List.Transform(Expression.Evaluate([Positions]),each if _=0 then 0 else _-1))([Names]))

and I don’t understand why.

 

Anyway, tysm!

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

Table.TransformColumns() can not get the value in the other column, it only handle the data in each column repectively. So does Table.SplitColumn()

if you do not want to add a custom column, you can try the function of Table.TransformRows()

Custom1 = Table.FromRecords(Table.TransformRows(Source,each _&[Names=Splitter.SplitTextByPositions(List.Transform(Expression.Evaluate([Positions]),each if _=0 then 0 else _-1))([Names])]))