Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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
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"
Thank you for the response. This looks a little scary to me. 🙂
Would I add this in the actual formula of the added 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.
Thank you so much! This worked perfectly!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |