[DAX] isolate text from text

[PascalT]

Dear PBI Experts

I guess that this is a very noby question... but i've a hard time to solve it.

In a column, I've several line structured like that:

/products/coral-dental-33600002-17

/products/coral-neck-3300028-256

Where I have two numbers at the end, divided by a -

The number of numbers can be slightly different as i show here in these two lines.

I would like to create a column with the two numbers only

ProductNumber

33600002-17

3300028-256

I've succeded to do it by pliting two time the right part with a deliminter "-" but i'm sure that there is an easier way to do it in power query / DAX.

Do you know how to do it ?

Thank you very much.

Pascal

[MarcelBeug]

In Power Query it can be done with:

= Table.AddColumn(Source, "ProductNumber", each Text.Range([String],1+List.First(List.LastN(Text.PositionOf([String],"-",Occurrence.All),2))))

[PascalT]

Hi Marcel

Thank you very much. As i'm not yet very confortable with the PowerQuery, is it possible to do it in DAX. I'll create a new column for it.

In all the case, thank you very much, your help is really appreaciated.

Pascal

[Sean]

@PascalT

If the ProductNumber is always 11 characters you are in business

Hope this helps!

[PascalT]

Hi and thank you too.

Unfortunatly, i can't do like that as the numbers are not always with the same length.

Most of the time, there are 8 + 3 numbers but it can happend that we have 7 + 3 or 8 + 2

Thank you for your help

Pascal

[MarcelBeug · Accepted solution]

Well, I'm not a DAX expert, but from my Excel background I constructed the following DAX formula.
Prerequisite is that your strings don't contain any ^ character.

=right([String],len([String])-find("^",substitute([String],"-","^",-1+len([String])-len(SUBSTITUTE([String],"-","")))))

[PascalT]

Hi again Marcel

Thank you.

I'll sort this out based on your formula. I'll have to change the [String] by the name of my colum containing the products. If I just replace this, it doesnt work but i'll try on my side and see if I succeed 😉

By the way, do you have a good tutorial to recommend about DAX and PowerBI for "newbies" ?

Thank you all

Pascal

[MarcelBeug]

Personally I bought:

Power Pivot and Power BI Book.

For Power Query: M is for (Data) Monkey.