Forum Discussion
"if" function in Power Query not working properly
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
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.
- SergeiBaklanJun 05, 2024MVP
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