Query Editor If Statement

[Anonymous]

I have tried both the Conditional Column and the Custom column but have not been able to get this to work. 

I need a field called Workmanship, IF the value in the column English is Scratches, I want Workmanship to = No. For all other values, I want it to = Yes. 

Please help. 

[v-yulgu-msft]

Hi @Anonymous,

The expression you wrote for custom column works fine in my test. There might existing some non-printable characters in [English] field. Please try smoupre's suggestion to remove those non-printable characters and try again.

Regards,

Yuliana Gu

[RohithGn]

You could also create a custom column using a search function within IF to get what you want

Workmanship = If(SEARCH("Scratches",Table1[English],1,0)>0,"No","Yes")

But this will include all the rows where ever the word Scratches available to No

Hope this helps

[Greg_Deckler]

I suspect that you need to add a Trim step and maybe a clean step for your English column, See my example here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYuxDsIwDAV/5Skz38EEE2PUwTFWYxE5UexW9O9B6gjj3elyTlcqUxnbqL15yDygjl19o9YOWA8Qs4yg0gTd4EJfZU8U4ldaLjk9eFJwFT8pSM3/hFvvLvCYautp7uIV8mZx112w0vj5lg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Engrish = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Engrish", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Engrish", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Engrish", Text.Clean, type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Cleaned Text", "Custom", each if [Engrish] = "Scratches" then "No" else "Yes")
in
    #"Added Conditional Column"

[Anonymous]

Thank you for the response. This looks a little scary to me. 🙂

Would I add this in the actual formula of the added column? 

[Greg_Deckler · Accepted solution]

You don't have to write the code. In Query Editor, right-click the column in question and then go to Transform | Trim. Then repeat for Transform | Clean. These two steps should be BEFORE your conditional column.

[Anonymous]

Thank you so much! This worked perfectly!