Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
TG_12
Frequent Visitor

Return value depending on value contained in text

Hi all, 

 

I have a column where some entries contain the values ML1, ML2, ML3, ML4 or ML5 at the start of the text. I am looking to add a column in my table that returns "ML#"  depending on what ML code it contains and return nothing if there is no ML code. The ideal solutiuon would be the following. 

DescriptionValueML Category 
ML3: Big red house10ML3
ML3: Small dog25ML3
Orange headphones50 
ML1: curved ramp1ML1
Blue pen pot75 

 

It would then be great if I could have a visual showing all the entries with ML codes sorted by a hierarchy of ML1 > ML2 > ML3 >ML4 >ML5 and where there are 2 values for one code sort by 'values' high to low. e.g.  

 

DescriptionValueML Category 
ML1: curved ramp1ML1
ML3: Small dog25ML3
ML3: Big red house10ML3

 

Been trying to find a solution to this for a while so any assistance would be appreciated! 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

Is the ML always going to be at the start?

If so, create a column in Power Query using an if statement with Text.StartsWith() to look for "ML" and then retrieve the first 3 chars using Text.Start() (if it doesn't start with ML, make it "")

--

In the table visual, filter out the empty 'ML Category' values from the filter pane.  And sorting by 2 columns can be done using the shift key clicking on the column header.

View solution in original post

2 REPLIES 2
TG_12
Frequent Visitor

Thats worked perfect - Thanks! 

HotChilli
Super User
Super User

Is the ML always going to be at the start?

If so, create a column in Power Query using an if statement with Text.StartsWith() to look for "ML" and then retrieve the first 3 chars using Text.Start() (if it doesn't start with ML, make it "")

--

In the table visual, filter out the empty 'ML Category' values from the filter pane.  And sorting by 2 columns can be done using the shift key clicking on the column header.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.