Split a column by positions in another column

[olander]

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	{0,5,8,15}
EchoFoxGemma	{0,5,8}
HenriIlsaJeff	{0,6,10}
KarlLisa	{0,5}
MaryNettleOrt	{0,5,11}
Penny	{0}
QualiahRoseSennaTodd	{0,8,12,17}
UrsulaVictorWilla	{0,7,13}
XiaYaniZohan	{0,4,8}

I’ve tried using the Table.SplitColumn function with Splitter.SplitTextByPositions, but I get this error: [Expression.Error] We cannot apply field access to the type Text.

Here’s my code

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}}),
  #"Added custom" = Table.AddColumn(#"Extracted text between delimiters", "Lists", each Text.Split([Positions], ",")),
  #"Removed columns" = Table.RemoveColumns(#"Added custom", {"Positions"}),
  #"Split column by positions" = Table.SplitColumn(#"Removed columns", "Names", each Splitter.SplitTextByPositions([Lists]))
in
  #"Split column by positions"

Please assist. Thanks.

[wdx223_Daniel · Accepted solution]

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

[wdx223_Daniel · Accepted solution]

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

[olander]

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]

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])]))

[AlienSx]

Hi, try to use this code. "spl" column contains a list you are looking for. You are free to modify fx_split function to return a table and expand it later if you wish.

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(Text.Split([Positions], ","), Number.From)),
  fx_split = (txt as text, pos as list) as list =>
    let 
      tbl = #table({"name"}, {{txt}})
    in Table.ToRows(Table.SplitColumn(tbl, "name", Splitter.SplitTextByPositions(pos))){0},
  d = Table.AddColumn(c, "spl", each fx_split([Names], [Lists]))
in d

[KeyurPatel14]

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.

[AlienSx · Accepted solution]

@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]

@AlienSx thanks so much for your work on this. You and @wdx223_Daniel should both get the honors.

[Mahesh0016]

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.

[Mahesh0016]

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]

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

[KeyurPatel14]

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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}, {"Positions", type text}}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Positions", each Text.BetweenDelimiters(_, "{", "}"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text Between Delimiters", "Positions", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Positions.1", "Positions.2", "Positions.3", "Positions.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Positions.1", Int64.Type}, {"Positions.2", Int64.Type}, {"Positions.3", Int64.Type}, {"Positions.4", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Positions.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Positions.3"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,0,Replacer.ReplaceValue,{"Positions.4"}),
    Custom2 = Table.AddColumn(#"Replaced Value2", "Custom2", each if [Positions.2] = 0
then 
[Names]
else
let 
a = [Positions.2]-1
in
Text.Insert(Text.From([Names]),a,":")),
    #"Replaced Errors" = Table.ReplaceErrorValues(Custom2, {{"Custom2", null}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Errors", "Custom2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom2.1", "Custom2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom2.1", type text}, {"Custom2.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each if [Positions.3] = 0
then 
[Custom2.2]
else
let 
a = [Positions.3]-[Positions.2]
in
Text.Insert(Text.From([Custom2.2]),a,":")),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type3", "Custom", each if [Positions.4] = 0
then 
[Custom.2]
else
let 
a = [Positions.4]-[Positions.3]
in
Text.Insert(Text.From([Custom.2]),a,":")),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Added Custom1", {{"Custom", null}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Errors1", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1.1", "Custom.2.1"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Custom.1.1", type text}, {"Custom.2.1", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Positions.1", "Positions.2", "Positions.3", "Positions.4", "Custom2.2", "Custom.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom2.1", "Name 1"}, {"Custom.1", "Name 2"}, {"Custom.1.1", "Name 3"}, {"Custom.2.1", "Name 4"}})
in
    #"Renamed Columns"

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

And Special thanks to my friend @Anonymous for the help.

[olander]

Thanks for such a thorough answer! I respect your workaround involving the repeated conditional functions. However, the number of names in a given row can be large. The data above is just for illustration. 

Is there a way to dynamically split a string of names of arbitrary size?