"if" function in Power Query not working properly

Copper Contributor

Good day, I have a power query set up that is giving me the number of days between task completions during a large project. 

However, I am missing the time from the last completed task until "today" for any Job sites that are not fully completed yet. 


=if [#"Final Walkdown"] = [#"Completed"]
  then [#"0"]
  else ( if [#"Final Walkdown"] <> ""
           then [#"Completed"]-[#"24-Hour Run Test"]
           else ( if [#"24-Hour Run Test"] <> ""
                     then [#"Completed"]-[#"Rotor Install"]
                     else ( if [#"Rotor Install"] <> ""
                              then [#"Completed"]-[#"Rotor Removal"]
                              else ( if [#"Rotor Removal"] <> ""
                                       then [#"Completed"]-[#"Prework Walkdown"]
                                        else "0"))))


I get a valid result up until then [#"Completed"]-[#"24-Hour Run Test"]. 

Any fields beyond that just return "null".


Any tips?

8 Replies




= Table.AddColumn(
    each if [#"Final Walkdown"] = [#"Completed"]
        then 0
        else if [#"Final Walkdown"] <> null and [#"Final Walkdown"] <> ""
            then Duration.Days(DateTime.Date([#"Completed"]) - DateTime.Date([#"24-Hour Run Test"]))
            else if [#"24-Hour Run Test"] <> null and [#"24-Hour Run Test"] <> ""
                then Duration.Days(DateTime.Date([#"Completed"]) - DateTime.Date([#"Rotor Install"]))
                else if [#"Rotor Install"] <> null and [#"Rotor Install"] <> ""
                    then Duration.Days(DateTime.Date([#"Completed"]) - DateTime.Date([#"Rotor Removal"]))
                    else if [#"Rotor Removal"] <> null and [#"Rotor Removal"] <> ""
                        then Duration.Days(DateTime.Date([#"Completed"]) - DateTime.Date([#"Prework Walkdown"]))
                        else 0


NOTE: My knowledge with Power BI is limited, maybe this revised version will help you with your project, if not, just ignore it :smile:.


Nevertheless, I hope that I could help with this.

@Pascal_Werner I would add a column first that returns todays date when the completion date is blank/empty or the completion date when it exists. Then use that column in stead of the [#"Completed"] column to calculate duration.


The step to add such a column, that you could call 'CutOffDate', would be:


Table.AddColumn(PreviousStep, "CutOffDate", each if [#"Completed"] = null then DateTime.LocalNow() else [#"Completed"])


Format this column as 'Date'.


You'll find a very small working example that brings you through the stage of adding the CutOffDate and calculate a duration, in the attached workbook. See if you can get it to work with this approach. Come back here if you don't. And then, please clarify the issue by attaching a file with some example date (non confidential) or a link that gives access to the file, stored on OneDrive or some similar file-sharing site, and mention the expected outcome.



If the "if" function in Power Query is not working properly, ensure your syntax is correct. The basic structure is:

Copy code
if [condition] then [result1] else [result2]
Common issues include missing "else" statements or incorrect comparisons (e.g., using "=" instead of "=="). Double-check your syntax and conditions.


As a comment, "doesn't work properly" means the statement returns some result, but not one which we expect. If syntax is wrong, statement doesn't work at all.

@mickhence Sorry, but I don't know what you mean. I know how 'if' works in PQ.


Not sure I understood the business logic. If that's something like


the query could be

  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Duration = Table.AddColumn(
      if [#"Final Walkdown"] = [#"Completed"]
      then #duration(0,0,0,0)
        [Completed] - (
          _[#"24-Hour Run Test"]
         ??[#"Rotor Install"]
         ??[#"Rotor Removal"]
         ??[#"Prework Walkdown"]
    , Duration.Type )

@Sergei Baklan 


Thank you very much. You did understand the business application correctly. 


I ended up thinking of a different method last night that I was able to implicate. 


=if [Prework Walkdown] = null
then null
else (if [Rotor Removal] = null
then [Completed]-[Prework Walkdown]
else (if [Rotor Install] = null
then [Completed]-[Rotor Removal]
else (if [#"24-Hour Run Test"] = null
then [Completed]-[Rotor Install]
else (if [Final Walkdown] = null 
then [Completed]-[#"24-Hour Run Test"]
else "0"))))


The Graph is working as I needed it now! 



Thank you for your help.

@Pascal_Werner , you are welcome.

Now formula is absolutely correct and graph looks nice.

Some cosmetic, let me explain what was used in previous post.

Your formula is based on statement

if [A] = null then [B] else [A]

nested in the reverse order.

In Power Query there is shorter notation for that


which means we have field A if it is not null, otherwise B. Above also could be "nested".


Here is one more sample

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Initial = Table.AddColumn( Source, "Initial", each
    if [Prework Walkdown] = null
    then null
    else (if [Rotor Removal] = null
    then [Completed]-[Prework Walkdown]
    else (if [Rotor Install] = null
    then [Completed]-[Rotor Removal]
    else (if [#"24-Hour Run Test"] = null
    then [Completed]-[Rotor Install]
    else (if [Final Walkdown] = null 
    then [Completed]-[#"24-Hour Run Test"]
    else "0"))))),

    Modified = Table.AddColumn(
            [Completed] - (
            (0*[Final Walkdown]+[Completed])
            ??[#"24-Hour Run Test"]
            ??[Rotor Install]
            ??[Rotor Removal]
            ??[Prework Walkdown]