May 2, 2021
Hi Beautiful People,
I hope everyone is okay. I am really happy that I was able to find a forum of power query as I am in transition to learning this type of automation. Given that its my first time, I only have few tricks that I know based from youtube and other forum or websites.
I am indesperate need on how can I integrate a nested IF with Workday calculation in my query. To be honest, I don't know what does this correspond in M language. And on how can I call the table from the other excel sheet or workbook, specifically listed the parameter that I need to obtain and calculate.
Majority, I do this in excel using the excel formula below. Is there a way that I can integrate this in my query?
=IF(TODAY()>WORKDAY($A2,IF($C2="Apac",5,7),Table2[Date]),"Case is due!!","Due on "&TEXT(WORKDAY($A2,IF($C2="Apac",4,2),Table2[Date]),"DD/MM/YYYY"))
Attached is a sample file, what I wanted to achieve is for the Data sheet to show the result in column B based on the formula.
I do this in a regular excel formula using or creating a holiday list worksheet.
What I wanted to achieve is the merge this data and have the "holiday list" removed in my report and instead create only connection in my power query.
I hope that I was able to explain myself well. 🙁
Regular Excel sheet - report that I usually do in excel. The result in column B is based on the formula.
I am using microsoft office 365.
Thank you so much! 🙂
November 8, 2013
PQ does not have such a function, as you already noticed.
However, the problem is not new, there are several attempts to solve it, here is one: