cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmdlb
Regular Visitor

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!

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

You will need to use Table.ReplaceValue for this. 

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

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

You will need to use Table.ReplaceValue for this. 

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

Genius! Thanks 🙂

HotChilli
Super User
Super User

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors