Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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],"-","")))))
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))))
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
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
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],"-","")))))
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
Personally I bought:
Power Pivot and Power BI Book.
For Power Query: M is for (Data) Monkey.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |