For my data set, I need to do a breakdown of sales by the model size. Each model or tonnage size is represented by a 2 digit number.
09 = 0.75 Ton
12= 1 Ton
18= 1.5 Ton
24= 2 Ton
The 2 digit number is contained within the product nomenclature string
However, it is not always in the same location. Therefore, I cannot just do a simple “LEFT” or "MID function. I need formula that can find the first instance of a number in the product nomenclature string, then return that value, along with the digit to the right of it.
I have figured out a way to do this using excel formula. See attached file:test.csv (28.2 KB)
By the way, I received guidance on how to do this from this website, in case you need to understand the method behind the madness: https://exceljet.net/formula/split-text-and-numbers
I have tried to use this same formula in column H from the Excel spreadsheet in PowerBI as a calculated column and it will not let me. I receive an error message:
Does anyone know how I can do this using DAX? Is a calculated column the way to go? I was thinking if I can successfully create a column that determines the model size, I can easily filter and do a breakdown of sales by each model/tonnage size.