Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
olander
Frequent 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:

NamesPositions
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.

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1679297361958.png

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

View solution in original post

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

View solution in original post

12 REPLIES 12
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1679297361958.png

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

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!

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
Super User
Super User

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

 

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.

@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 thanks so much for your work on this. You and @wdx223_Daniel should both get the honors.

Mahesh0016
Super User
Super User

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
Super User
Super User

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.

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

KeyurPatel14
Resolver IV
Resolver IV

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 @VishalJhaveri for the help.

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? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors