Forum Discussion

jmdlb's avatar
jmdlb
Regular Visitor
4 years ago
Solved

Text.AfterDelimiter tricky

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!

  • You will need to use Table.ReplaceValue for this. 

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

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    You will need to use Table.ReplaceValue for this. 

    = Table.ReplaceValue(#"Columna duplicada",each [Results],each Text.AfterDelimiter([Results], ",", [CommaJump] ),Replacer.ReplaceValue,{"Results"})
    • jmdlb's avatar
      jmdlb
      Regular Visitor

      Genius! Thanks 🙂

  • HotChilli's avatar
    HotChilli
    Community Champion

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