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. 

Mahesh0016's avatar
Mahesh0016
Super User
3 years ago

Hope you are doing well.
Please paste the following code to the Advanced Editor:

 

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]),
#"Extracted text between delimiters" = Table.TransformColumns(Source, {{"Positions", each Text.BetweenDelimiters(_, "{", "}", 0, 0), type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Extracted text between delimiters", "Names", "Names - Copy"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Names - Copy", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Names - Copy.1", "Names - Copy.2", "Names - Copy.3", "Names - Copy.4"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Positions"})
in
#"Removed Columns"

 

 

Hope this will solve your problem.
If this helps then please give it a kudos and mark it as a solution.
Thank you.

olander's avatar
olander
Frequent Visitor
3 years ago

Unfortunately, the names in my data are not always capitalized. The positions data must be used in the solution.