Forum Discussion

philthomasp's avatar
philthomasp
New Member
6 years ago
Solved

IF statement issue

Hello, I'm new to Power Query, and I am working on a formula where I will create a Custom Column, and what I need the new column to provide is the following:   subtract 2 date columns and return t...
Need Help
  • Thejeswar's avatar
    Thejeswar
    6 years ago

    Hi philthomasp ,

    The Issue could be because subtracting two dates would return a date and not a number to use with a relational operator <. So alternatively create a measure having DATEDIFF function in it as shown below

     

    Measure = IF(DATEDIFF(MAX(Table3[Drte2]),MAX(Table3[Dte1]),DAY) < 0, 0, DATEDIFF(MAX(Table3[Drte2]),MAX(Table3[Dte1]),DAY))

     

    Rgds, 

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi philthomasp ,
Try "if" in m language which is what Power Query uses. Or try using a conditional column rather than a custom column.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

  • philthomasp's avatar
    philthomasp
    New Member
    6 years ago

    Just for my own knowledge, but to clarify...m language is what we see when creating a custom column, and DAX is the advanced editor?

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Hello philthomasp ,
      m language is what is used in Power Query and is shown in the Advanced Editor. DAX is used in Power BI, Excel and elsewhere. 

       Table.AddColumn(#"Pivoted Column", "Custom", each if [User] = "a" then 1 else 0)

      So, I added the a custom column using the UI and above is what shown in the formula bar. The three pictures below, from the bottom, show the same conditional and custom column, and then it is shown in the Advanced Editor. From an efficiency standpoint, most of the time you can create measures (Using DAX) or calculated columns (Using DAX) however calculated columns, are better written in Power Query.

      Check out M is for (DATA) Monkey by KenPuls 

      Let me know if you have any questions.

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
      Nathaniel