Text.AfterDelimiter tricky

[jmdlb]

Hi!

I have to extract some text from a field after N commas (","), so I use Text.AfterDelimiter with optional index to specify hoy many of those characters commas I want to jump. The tricky part is that number is specified in another field of the table.

Example
Text = "BOT/test1/test2,BOT/test3,hello world"
Number of comma to jump = 2 (given in another field of the table)
What I want= "hello world"
 Table.TransformColumns(#"Columna duplicada", {{"Results", each Text.AfterDelimiter(_,",",2) , type text}})

I want to replace "2" with the value of the field of the table which specify how many comas I have to jump, but I get an error.

I would like something like this:
= Table.TransformColumns(#"Columna duplicada", {{"Results", each Text.AfterDelimiter(_, ",", [CommaJump] ) , type text}})

The error I got is "Expression.Error: We cannot apply field access to the type Text."

Thankss a lot!

[Vijay_A_Verma · Accepted solution]

You will need to use Table.ReplaceValue for this. 

= Table.ReplaceValue(#"Columna duplicada",each [Results],each Text.AfterDelimiter([Results], ",", [CommaJump] ),Replacer.ReplaceValue,{"Results"})

[jmdlb]

Genius! Thanks 🙂

[HotChilli]

I don't think you can use Table.TransformColumns for this.  Probably have to add a custom column