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" 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?
= 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.
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:
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.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.
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 DurationThank 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
