Forum Discussion

Pascal_Werner's avatar
Pascal_Werner
Copper Contributor
Jun 04, 2024

"if" function in Power Query not working properly

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"...
BI & Data Analysis
Excel
Power BI
SergeiBaklan's avatar
SergeiBaklan
MVP
Jun 05, 2024

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
  • Pascal_Werner's avatar
    Pascal_Werner
    Copper Contributor
    Jun 05, 2024

    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.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jun 05, 2024

      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

       

Resources