Forum Discussion
Good practice for splitting long text strings
I have a column [text1] with strings of various lenghts anywhere between 10 to 300+ characters. I need to break this text down to 4 columns - knowing that a single column fits up to 70 characters and line breaking should be done nicely with a space (e.g. not to break up words - this is where my M gets annoyingly extensive). The fourth column can contain whatever is left from the broken down string.
While I can eventually get it done, the reality is that I have 2 columns that need this and my M syntax got really long and I wanted to check if there's a better alternative to Split column at 70 characters + split again at right-most space and do A LOT of replacing and custom columns.
Is there some good practice or M text manipulation functions that are well suited for this task? A minor example below of the expected outcome by splitting up to 20 characters per column. Any relevant blog posts or M functions that might be useful in this case? No good hits so far when googling around aside of basic split-by-delimiter stuff. Feels like someone must have surely tackled this issue before...
Hello,
try this.
I have added paramater NumOfChar so you can input desired number of characters to split.
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.PositionOfAny([Column1],{" "},Occurrence.All )), Custom = #"Added Custom"{0}[Custom], #"Converted to Table" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}), #"SPLIT (parameter)" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] < NumOfCh then 0 else if ([Column1] >= NumOfCh and [Column1] < NumOfCh*2) then 1 else if ([Column1] >= NumOfCh*2 and [Column1] < NumOfCh*3) then 2 else if [Column1] >= NumOfCh*3 then 3 else null), #"Grouped Rows" = Table.Group(#"SPLIT (parameter)", {"Custom"}, {{"Gr", each _, type table [Column1=nullable number, Custom=number]}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([Gr], "Index")), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Position", each [Gr][Column1]{0}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Gr", "Custom.1"}), Positions = List.Combine ({{0}, List.RemoveFirstN(#"Removed Columns"[Position], 1)}), FINAL = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions(Positions) ) in FINAL
4 Replies
Hello,
try this.
I have added paramater NumOfChar so you can input desired number of characters to split.
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.PositionOfAny([Column1],{" "},Occurrence.All )), Custom = #"Added Custom"{0}[Custom], #"Converted to Table" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}), #"SPLIT (parameter)" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] < NumOfCh then 0 else if ([Column1] >= NumOfCh and [Column1] < NumOfCh*2) then 1 else if ([Column1] >= NumOfCh*2 and [Column1] < NumOfCh*3) then 2 else if [Column1] >= NumOfCh*3 then 3 else null), #"Grouped Rows" = Table.Group(#"SPLIT (parameter)", {"Custom"}, {{"Gr", each _, type table [Column1=nullable number, Custom=number]}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([Gr], "Index")), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Position", each [Gr][Column1]{0}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Gr", "Custom.1"}), Positions = List.Combine ({{0}, List.RemoveFirstN(#"Removed Columns"[Position], 1)}), FINAL = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions(Positions) ) in FINALThanks, I wasn't able to implement this directly, but was able to pick up some methods.
here you can find all the functions available to act on the texts
I propose a draft resolution. See if it comes close to what you are looking for.
