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.
Hi,
For the syntax below, I want to set Staged column to Yes when status >15 else No . However,I also want to set to No if status is null or blank, but don't know the syntax. Can you assist ?
Staged = if(Number.FromText([#"Warehousing [Status]"]))>15 then "Yes" else "No"
Solved! Go to Solution.
Hi @hongho2 , this should work:
if Number.FromText([Status]) <= 15 or List.Contains({null, ""}, [Status]) then "No" else "Yes"
it merged with other table, but the filter is in the same table.
Hi @hongho2 , this should work:
if Number.FromText([Status]) <= 15 or List.Contains({null, ""}, [Status]) then "No" else "Yes"
Thank you for your reply, but it did not work. I filtered for blanks, It should show "No." from the status instead of nothing.
Hi @hongho2 ,
Is this PowerQuery or DAX? If it didn't work in PowerQuery it should have given you an error message. Can you share me the error message?
Perhaps you can share a bit more information on how you're getting blanks? You're connecting to another table via merge, append, or a relationship?
it was merged with other table, but the filter is in the same table.
Hi @hongho2
Since it's merged with another table there could be a value or evaluation that doesn't match causing what could be a RI violation and then giving you a "blank" value.
I don't know your model well enough to tell you where is your blank issue but if the data is not sensitive and you want me to investigate your issue for you, share your pbix for me to diagnose it.
Also seems you may need to clean your data a bit further.
Here's my full sample query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRVitUBUgYQyghMIQhDMwhlDqYcdY10/UpzcpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"A-2-Null",null,Replacer.ReplaceValue,{"Status"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Staged", each if Number.FromText([Status]) <= 15 or List.Contains({null, ""}, [Status]) then "No" else "Yes")
in
#"Added Custom"
And the sample expected results:
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.