Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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, ex...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Also, If I add a new string

 

 

 

This is not working anymore

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

 

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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.