Forum Discussion
Split a column by positions in another column
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 Custom1KeyurPatel14 , 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.
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.
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?