"if" function in Power Query not working properly

[Pascal_Werner]

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?

[#2]

@Pascal_Werner 

= Table.AddColumn(
    PreviousStepName, 
    "DaysBetweenTasks",
    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 .

Nevertheless, I hope that I could help with this.

[#3]

@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.

[#4]

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

powerquery
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.

[#5]

@mickhence 

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.

[#6]

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

[#7]

@Pascal_Werner 

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

the query could be

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

[#8]

@SergeiBaklan 

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.

[#9]

@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

[A]??[B]

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

Here is one more sample

let
    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(
        Initial,
        "Modified",
        each
            [Completed] - (
            (0*[Final Walkdown]+[Completed])
            ??[#"24-Hour Run Test"]
            ??[Rotor Install]
            ??[Rotor Removal]
            ??[Prework Walkdown]
            )
    )
in
    Modified