Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

If Statement Returns Incorrect Result in New Column

I am attempting to add a new column which looks through the specification column and returns the details in the rating column.  I have tried this two different ways but neither seems to work as all that is returned is *.

 

Desired Outcome

SpecificationRating
53/80 Name * *
46/90 Name ****
46/90 Name ******
53/90 Name  
53/80 Name ******

 

Current Outcome

SpecificationRating
53/80 Name * *
46/90 Name ***
46/90 Name ****
53/90 Name  
53/80 Name ****

 

Method 1 Attempted in Query Editor:

 

if Text.Contains([Specification],"*") then "*" else if Text.Contains([Specification],"**") then "**"
else if Text.Contains([Specification],"***") then "***" else ""

 

Method 2 Attempted by using Add Custom Column to the table:

 

Rating= if(SEARCH("*",Table1[Specification]),"*", if(SEARCH("**",Table1[Specification]),"**",if(SEARCH("***",Table1[Specification]),"***", "")))
Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    You will want to reverse your checks. Check for *** first, then for ** , then for * .

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    You will want to reverse your checks. Check for *** first, then for ** , then for * .

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much for that. Can you please explain why this worked? 

       

      Is it because in the way I had it set up originally the function would find the first * in the string return the defined result for that and then stop iterating through the rest of the if statements?