Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Search for specific word and extract it into new colomn in Power BI DAX

Atm my data is like a list of sentence like following:

FPTS is our (Case ID)

Column

  • FPTS-0009: needed help for software update
  • Helped and customer doing frontend FPTS-0012
  • This time the customer FPTS-0020 needed refresh data

I want to find the word "FPTS" and extract it's ID and have it inside a new colomn. As you can see the word I need to extract doesn't have the same position all the time! sometimes it shifts What I try to do is extract FPTS-xxxx from above which would be:

  • FPTS-0009
  • FPTS-0012
  • FPTS-0029

would help a lot to find a solution!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi!

 

So, use this expression to concatenate the value!

"FPTS-" &Text.Start(Text.AfterDelimiter([<yout original text column here>], "FPTS-"),4)

 

Cheers

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi, @Anonymous!

 

We can solve it in a bunch of ways. a Simpler one is to extract the desired data into a new Column using M language in your Query editor.

So, let's see how to do it!

First of all, in Query Editor, click on "Add Column" and select "Custom Column".

If you need to extract just the numbers, this code can deal with it:

2019-11-04 11_21_09-.png

If you want to have the original value, you can concatenate the text with this code in M language:

 

"FPTS-" &Text.Start(Text.AfterDelimiter([<yout original text column here>], "FPTS-"),4)

 

That's it

Ps.: If you do not use the original column in your report, be sure to delete it before finishing your data preparation, just to get a great performance with your final model.

If I helped you to reach your goals, please, mark this as solution!

 

Take care!

 

 

Anonymous
Not applicable

It helped a lot thank you so much 🙂 only last step were to still have the "FPTS-" part or would that be to complicated to add in the custom colomn: So instead of only have the numerical value also have the "FPTS-" the reason for this is it well be used for a reference point to another colomn 🙂 

Anonymous
Not applicable

Hi!

 

So, use this expression to concatenate the value!

"FPTS-" &Text.Start(Text.AfterDelimiter([<yout original text column here>], "FPTS-"),4)

 

Cheers

Anonymous
Not applicable

It's solved thank you so much 🙂 

Anonymous
Not applicable

These photo I just attached is the solution I really want "atm this were done manually" but really don't it to do auto, because have over 4000 rows so manual process on this is not a option :S 

Anonymous
Not applicable

case.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors