Forum Discussion
Query editor transformation
Hi,
I have a very interesting transformation that I want to do with the least calculations.
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)
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.
5 Replies
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
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.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.
