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

[Anonymous]

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!

[Anonymous]

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:

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]

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 · Accepted solution]

Hi!

So, use this expression to concatenate the value!

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

Cheers

[Anonymous]

It's solved thank you so much 🙂 

[Anonymous]

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]