Forum Discussion

hongho2's avatar
hongho2
Frequent Visitor
2 years ago
Solved

syntax

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 ?  ...
Need Help
  • hnguy71's avatar
    hnguy71
    2 years ago

    Hi hongho2 , this should work:

     

    if Number.FromText([Status]) <= 15 or List.Contains({null, ""}, [Status]) then "No" else "Yes"
hongho2's avatar
hongho2
Frequent Visitor
2 years ago

Thank you for your reply, but it did not work. I filtered for blanks,  It should show "No." from the status instead of nothing.

 

 

 

hnguy71's avatar
hnguy71
Icon for Super User rankSuper User
2 years ago

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?

  • hongho2's avatar
    hongho2
    Frequent Visitor
    2 years ago

    it was merged with other table, but the filter is in the same table.

    • hnguy71's avatar
      hnguy71
      Icon for Super User rankSuper User
      2 years ago

      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: