Forum Discussion

Ekaterina's avatar
Ekaterina
Regular Visitor
9 years ago

IF condition

Hello,

 

could anyone tell me what is wrong with the following statement:

 

Column = IF Query[CR] = NaN then 0 else Query1[CR]

 

Thank you,

Ekaterina

Need Help

5 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    = NaN won't work either in Power Query.

     

    Working example query:

     

    let
    Source = Table.FromList({1,#nan,2}, Splitter.SplitByNothing(), {"CR"}, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Number.IsNaN([CR]) then 0 else [CR])
in
    #"Added Custom"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Ekaterina

     

    Please replace "IF" with "if" (Power Query is case sensitive)

    and Query[CR] with [CR].

     

     

    • Ekaterina's avatar
      Ekaterina
      Regular Visitor
      9 years ago

      Anonymous well I select Query1[CR] as Power BI advices me in the bar so it knows from which query am I selecting CR. I have tried to replaced IF for if and tried both without Query1 and with it, but it returns me an error anyway. It says that ''the syntax for ´then´ is incorrect. (DAX(IF (Query1[CR] = NaN then 0 else Query1[CR]))).''

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Your Query seems to be a DAX Query..

         

        MarcelBeugand I posted you Power Query solutions (I have to admit, that only MarcelBeug solution works, as I've overlooked the NaN issue....)..