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"
hnguy71's avatar
hnguy71
Super User
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
    Super 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
        Super 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: