Forum Discussion
Query Editor If Statement
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.
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.
6 Replies
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"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.
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
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
