Forum Discussion

zsechler's avatar
zsechler
Frequent Visitor
6 years ago
Solved

IF Statement Help - EoF Error

Hello All

 

I have a dataset i am trying to simplfy. I have 3 values in one column that i am trying to create a custom column with an "IF" statement to just say billabe or non-billable. I keep getting a Eof Error though. Column type is text only. Values are text only but have special characters in there as you can see.

 

Things I tried

IF [#"Task - Phase/Task name"] = "Fixed Fee (Billable)" || [#"Task - Phase/Task name"] || "T&M (Billable)" || [#"Task - Phase/Task name"] = "Milestone (Billable)" then "Billable" else "Non-Billable"

 

IF ([#"Task - Phase/Task name"] = "Fixed Fee (Billable)",[#"Task - Phase/Task name"],"T&M (Billable)",[#"Task - Phase/Task name"] = "Milestone (Billable)") then "Billable" else "Non-Billable"

 

Thank you!!

Need Help
  • fhill's avatar
    fhill
    6 years ago

     

    Power Query 'Custom Columns' are different than DAX 'New Column'.  Try this...

     

    = if [#"Task - Phase/Task name"] = "Fixed Fee (Billable)" or [#"Task - Phase/Task name"] = "T&M (Billable)" or [#"Task - Phase/Task name"] = "Milestone (Billable)" then "Billable" else "Non-Billable"

     

     

  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi zsechler ,

     

    As fhill's suggestion, the formula in Power Query will works well. If the result returns the false value of non-billable, it means that the IF condition don't meet the actual values. Please pay attention to the case sensitive(lower case) and the accurate value within the "" in IF function, check if there may be spaces in "".

     

    You can refer to the link:https://www.myonlinetraininghub.com/power-query-if-statements.

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • fhill's avatar
    fhill
    Resident Rockstar
    6 years ago

     

    Power Query 'Custom Columns' are different than DAX 'New Column'.  Try this...

     

    = if [#"Task - Phase/Task name"] = "Fixed Fee (Billable)" or [#"Task - Phase/Task name"] = "T&M (Billable)" or [#"Task - Phase/Task name"] = "Milestone (Billable)" then "Billable" else "Non-Billable"

     

     

    • fhill's avatar
      fhill
      Resident Rockstar
      6 years ago

      P.S. the 'if' 'then' and 'else' have to be lower case!

  • zsechler's avatar
    zsechler
    Frequent Visitor
    6 years ago

    Thank you, fhill. It now prints out. That said it only prints the false value of non-billable. Wouldn't "or" work as i am using it?

     

    Available values. 

    Milestone (Billable)

    Fixed Fee (Billable)

    T&M (Billable)

    Admin

    Non-Project Work

    PTO

     

    Thank you for the help!!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      The Query Editor is case sensitive.  Please ensure that the case of what you type within "" in your If formula matches with the case of entries in that column.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi zsechler ,

     

    As fhill's suggestion, the formula in Power Query will works well. If the result returns the false value of non-billable, it means that the IF condition don't meet the actual values. Please pay attention to the case sensitive(lower case) and the accurate value within the "" in IF function, check if there may be spaces in "".

     

    You can refer to the link:https://www.myonlinetraininghub.com/power-query-if-statements.

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.