Forum Discussion
TextAfterDelimiter using another column as the text delimiter
Hello,
Sorry to ask, I had been trying and I was not able to do this... is there a way to split column A using column B as delimiter? I was thinking using TextAfterDelimiter but so far no luck. This is just a short list, but I have like 20 different delimiters in column B and it would help me to have something where I can reference that column.
| Column A | Column B | Column C Result |
| material 1000 is not ok | material | 1000 is not ok |
| batch 1100 not ok | batch | 1100 not ok |
| product 1200 is ok | product | 1200 is ok |
It's not a proper split, as you usually sacrifice the delimiter symbol(s)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk0sSS3KTMxRMDQwMFDILFbIyy9RyM9W0oHLKMXqRCslJZYkZygYAhUhFIDFwLIFRfkppcklCoZGEDPA0lBBpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B " = _t]), #"Added Custom" = Table.AddColumn(Source, "Column C Result", each Text.Split([Column A],[#"Column B "]){1},type text) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
4 Replies
It's not a proper split, as you usually sacrifice the delimiter symbol(s)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk0sSS3KTMxRMDQwMFDILFbIyy9RyM9W0oHLKMXqRCslJZYkZygYAhUhFIDFwLIFRfkppcklCoZGEDPA0lBBpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B " = _t]), #"Added Custom" = Table.AddColumn(Source, "Column C Result", each Text.Split([Column A],[#"Column B "]){1},type text) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
thanks! it worked for me
Another way is using this:
Text.End([Column A], Text.Length([Column A])-Text.Length([Column B])-1), type text)
Hi alannavavaldez, for your sample data it is enough replace text:
Text.Trim(Text.Replace([Column A], [Column B], ""))but if you have delimiter somewhere in the middle - this could help you:
Text.Combine(List.Select(List.Transform(Text.Split([Column A], [Column B]), Text.Trim), (x)=> x <> ""), " ")
