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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Query editor transformation

Hi, 

 

I have a very interesting transformation that I want to do with the least calculations.

 

leharkapil_0-1604692302482.png

 

 

I have a few tag names in my Tag Names column

 

I'm trying to do 2 things. First, extract all the tags that start from R(R stands for release). Ultimately in 1 row if i have 2 tags that start from R, I'm then trying to get the minimum of 2 tags.

 

E.g. For R1;R2 row, I expect R1 as output

For Global;L1;R5;R6 row I expect output to be R5.

 

I want to have this solution in the query editor only(using M code)

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @Anonymous 

To catch that last R5 you'll need to do a case insensitive match and then transform the resulting text to Upper Case.  The line to do this when you add the custom column is 

 

 

= Text.Upper(List.First(List.Select(Text.Split([Tag Names], ";"), each Text.Contains(_,"R",Comparer.OrdinalIgnoreCase)))))

 

 

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Also, If I add a new string

 

leharkapil_0-1604728398238.png

 

 

This is not working anymore

For e.g. in the last row I should have got R4 as output

 

Hi @Anonymous 

Change List.First to List.Min

 

Text.Upper(List.Min(List.Select(Text.Split([Tag Names], ";"), each Text.Contains(_,"R",Comparer.OrdinalIgnoreCase))))

 

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

Hi @Anonymous 

To catch that last R5 you'll need to do a case insensitive match and then transform the resulting text to Upper Case.  The line to do this when you add the custom column is 

 

 

= Text.Upper(List.First(List.Select(Text.Split([Tag Names], ";"), each Text.Contains(_,"R",Comparer.OrdinalIgnoreCase)))))

 

 

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

Hi Philip,

 

Thank You. Your calculation works.

Please also do a favour of explaining the solution in parts

mahoneypat
Microsoft Employee
Microsoft Employee

You can add a custom column in the query editor to get your desired result with this formula

= List.First(List.Select(Text.Split([Tag Names], ";"), each Text.Contains(_,"R")))

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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