Forum Discussion
"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"...
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
[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