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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors