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. ๐
Legend:
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! ๐
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi,
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:
https://www.thebiccountant.com.....-power-bi/
Answers Post
1 Guest(s)