Automate reconciliations with Power Query | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★

★ About this video ★
Reconciliations are a common and time-consuming activity for finance and data professionals. So, in this video, we look at how to automate reconciliations with Power Query.

A reconciliation is nothing more than comparing two pieces of information and ensuring they contain the same values. Unfortunately, reconciliations can be time-consuming, as a user may need to work down each list and tick off the items that are the same (literally with a pen and paper or on-screen). Then, the remaining items that are not the same need to be investigated so that corrective action can be taken.

Where all inputs contain a shared reference, we can automate the reconciliation process with Power Query. It can't investigate the differences but can certainly find them in a few seconds.

0:00 Introduction
0:29 Method
0:56 Example Files
1:45 Step 1: Create unique list
3:30 Step 2: Merge back to unique list
6:09 Step 3: Calculate variance
7:17 Result
7:50 What if records are not unique?
8:57 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[sunnyday]

The most useful reconciliation video that I've ever found. No complex formula, straight to the point! Thank you so much!

[sakhilengwenya]

This is the most effective training video I have ever watched on the subject. I have binge-watched the video more than 20 times now. thank you very much.

[StopWhining]

Useful having a real-life problem example to solve instead of just showing how an app feature works. Thanks!

[GrainneDuggan_Excel]

Nice solution for a straight forward problem. Reconciling bank statements is much more difficult with so many direct deposit and payments occurring these days. Chances that the bank groups them the way accounting systems do are slim. They often do not have a unique identitifier in common or even matching date of the transaction.

[adhamm]

Very helpful, thank you. The reason for using power query is to automate this reconciliation task. It would be great if you could show what happens when you get different lists next month and how to update the query and get the list of items not matching in a few clicks. Regards

[Sumanth]

Excellent solution..clean, easy to follow and highly effective. 😊 I normally used to merge and use option keep rows from both table. But this looks better.

[Deoreth]

A few moments:
- if there are many Names with one Ref it's more convinient to pick last record by date of file modification or any other date (and one more there is no need to keep Names in main table - use it as dictionary and join it in Power Pivot)
- when you merging some values on a list with 1 normalisation form use List.Sum or other aggregation from the start - it prevents you from getting dups from values you join.
- there is no need in step to configure column type if you calculate values from another - just add it to calculation step.

[olufemiolawale]

Thank you sir, You made power query to become easier to use for me!

[IvanCortinas_ES]

Excellent explanation Mark. Thank you!!!

[kebincui]

Awesome as always, No 1 in the new year ❤👍

[bbworld]

Thanks for making it easy to understand. 🤘🤘

[vinmeg]

Nice video. What about an reconciliation of an only text data file where data can be different in more then one column?

[Alexx]

This was awesome, nice work, thanks!

[waheedakhtar]

If we have more than two references, as in the case of partial payment, how should we handle it? This will help us a lot in automating the process.

[kudifilipe]

Great content, thanks. What about diferent reference, and the only match is the amount? And in the end of the day you want to show both, like same amount with diference reference?
Eg:
Ref Amount
X 2
Y 2
Z 3

Result:
X and Y = 2;
Z = 3.

Thanks

[aggelospapaemmanouil]

You could do it with a simple Unique function and a sumifs second column in 2 seconds

[MaydayAggro]

I have credit card clearing accounts in several different companies that each have thousands of transactions per month. For some reason there are no common columns between any of the tables (credit card company vs. general ledger vs. bank) other than the amount column. And because it's credit cards, the deposits post days later than the credit card charge, so that even the dates do not match. Is there a way to get this data to work in power query?

[ke]

Damn helpful... 7 months later...thanks

[tariqaziz]

Make downloading file easy, it's very difficult requires first sign up then another step

[nuqwestr]

Debits, Credits, Adjustments, don't see those concepts referenced.