topic Re: Search for specific word and extract it into new colomn in Power BI DAX in DAX Commands and Tips

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

Anonymous — Mon, 04 Nov 2019 13:59:08 GMT

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!

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

Anonymous — Mon, 04 Nov 2019 14:24:46 GMT

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

Anonymous — Mon, 04 Nov 2019 14:26:18 GMT

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

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

Anonymous — Mon, 04 Nov 2019 14:27:10 GMT

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!

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

Anonymous — Mon, 04 Nov 2019 14:49:40 GMT

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 🙂

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

Anonymous — Mon, 04 Nov 2019 14:50:59 GMT

Hi!

So, use this expression to concatenate the value!

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

Cheers

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

Anonymous — Tue, 05 Nov 2019 14:41:06 GMT

It's solved thank you so much 🙂