Forum Discussion
Helper I[DAX] isolate text from text
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
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],"-","")))))
7 Replies
- MarcelBeug
Community Champion
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))))
PascalTHi 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
