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

Hi @olander ,
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}}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Extracted text between delimiters", "Names", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Names.1", "Names.2", "Names.3", "Names.4"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Character Transition",{{"Names.1", "Column1"}, {"Names.2", "Column2"}, {"Names.3", "Column3"}, {"Names.4", "Column4"}}),
#"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Column1", "Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Added Custom" = Table.AddColumn(#"Merged Columns", "Name", each Text.Split([Merged]," ")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Position", each Text.Split([Positions],",")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Table", each Table.FromColumns({[Name],[Position]})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Merged", "Positions", "Name", "Position"}),
#"Expanded Table" = Table.ExpandTableColumn(#"Removed Columns", "Table", {"Column1", "Column2"}, {"Table.Column1", "Table.Column2"}),
#"Removed Blank Rows" = Table.SelectRows(#"Expanded Table", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"


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