Hello Instructors,
Hope you are well.
Using Power Query, I am trying to replicate the functionality of SUMIFS formula by merging two queries based on specific conditions such as project code, and then performing a calculation, such as a deduction, on the matching rows for my company’s forecast from Jan – Apr.
I have been using the usual Excel formula below to calculate the difference between the forecasts for List A and List B to arrive at reconciling and non-reconciling project codes and their amounts per month. Please refer to the reconciliation with the formula I am currently using which I would like to replace with Power Query process.
Instead of using these Excel formulas/functions, I want to use PQ such that whenever someone updates lists A or B, the reconciliation between the two automatically happens by refreshing only.
Please advise how this can be done using Power Query.
Best,
Mohamed
Hi Mohamed,
The first rule of data is that it should be in a tabular layout, so the very first step you need to take is to unpivot Lists A and B.
In the attached file you'll see I've created a new 'Rec' query that is a merge of the two lists. I've then loaded the data to a PivotTable to generate the summary on the 'Rec' sheet in the file.
Note: your reconciliation only brings in Project Codes from the Area_Data table, but there are Project Codes on the Country_Data table that aren't on the Area_Data table. My query includes all Project Codes.
Mynda
Hi Mynda,
Thank you for taking the time to review my case and offer your guidance. I appreciate that you provided a comprehensive list that combines variance arising from both lists; this will be particularly helpful for my situation.
Appreciate your timely turnaround and sharing the tool with clear steps to follow.
Mohamed
My pleasure, Mohamed
