Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Description | Value | ML Category |
ML3: Big red house | 10 | ML3 |
ML3: Small dog | 25 | ML3 |
Orange headphones | 50 | |
ML1: curved ramp | 1 | ML1 |
Blue pen pot | 75 |
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.
Description | Value | ML Category |
ML1: curved ramp | 1 | ML1 |
ML3: Small dog | 25 | ML3 |
ML3: Big red house | 10 | ML3 |
Been trying to find a solution to this for a while so any assistance would be appreciated!
Solved! Go to Solution.
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.
Thats worked perfect - Thanks!
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
60 | |
51 | |
36 | |
36 |
User | Count |
---|---|
84 | |
70 | |
58 | |
45 | |
44 |