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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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