Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
I have a source column called Defect1Code which can contain any one of dozens of codes that follow this structure: G5.4.1 – Some Text Here. The length of the alphanumeric at the beginning varies, and in some cases the column for a given row may be blank altogether. All are acceptable possibilities.
I want to extract the alphanumeric out, so I am using this DAX:
Column = LEFT ([Defect1Code], (SEARCH (" ", [Defect1Code],, BLANK()))
This seems to work perfectly, but with one itty-bitty hitch: from what I can tell it also seems to be bringing over the first space after the last alphanumeric (i.e. “G5.4.1 “ – but without the quotes of course).
I thought adding a simple “-1” after the SEARCH would solve it, and it does except (and here’s the big problem) it treats BLANK returns as a ZERO, so in rows with a blank Defect1Code the SEARCH returns -1, which hoses up the LEFT function!
Am I missing a better solution?
Solved! Go to Solution.
Hi @Daryl_K
Column =
VAR pos_ =
SEARCH ( " ", [Defect1Code],, BLANK () )
RETURN
IF ( NOT ISBLANK ( pos_ ), LEFT ( [Defect1Code], pos_ - 1 ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Daryl_K
Column =
VAR pos_ =
SEARCH ( " ", [Defect1Code],, BLANK () )
RETURN
IF ( NOT ISBLANK ( pos_ ), LEFT ( [Defect1Code], pos_ - 1 ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Awesome - THANKS!
I dint think DAX is the best option here. Power Query is built to do these types of things. You should consider cleaning the columns in PQ before you load the data so it works to your needs without using functions like LEFT and SEARCH
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!