Automate reconciliations with Power Query | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Get the example file ★
exceloffthegrid.com/automate-...
★ 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 ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

[@adhamm5503]

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

[@StopWhining491]

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

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@sakhilengwenya594]

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.

[@ExcelOffTheGrid]

You may be the first person ever to binge watch a Power Query video 🤣 But, Thank You, I really hope it can save you a lot of time.

[@IvanCortinas_ES]

Excellent explanation Mark. Thank you!!!

[@ExcelOffTheGrid]

Thanks Ivan. Glad it was helpful!

[@olufemiolawale3848]

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

[@bishowjitbanik6342]

Thanks for making it easy to understand. 🤘🤘

[@kebincui]

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

[@ExcelOffTheGrid]

Thanks Kebin. I’ve got some good stuff planned for 2024, so I hopefully I won’t disappoint. 😁

[@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.

[@ExcelOffTheGrid]

Agreed 100%. Bank statements don't have a unique reference for transactions, and there is no unique reference in the cash book either. So it can only be based upon human knowledge (I wouldn't trust AI to get it right either).

[@viralvaishnav451]

You can do the bank reconciliation in Excel via power query...I have done it...

[@OZbMG8jsJTX14AWYne4omBw]

Good job! Thx!

[@ExcelOffTheGrid]

Thank you.

[@TAR2711]

Thank you for you time! Its usefull.

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@mcwahaab]

Big thanks from Somalia

[@ExcelOffTheGrid]

You’re welcome 😁

[@Bhavik_Khatri]

Nice process

[@ExcelOffTheGrid]

Thank you! Cheers! 😁

[@Sumanth1601]

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.

[@ExcelOffTheGrid]

There are lots of ways to solve this, so your existing way may be better. I’ve not done any performance testing to compare. Provided you get the right answer, with minimal effort and in reasonable time, then that’s what matters. 👍

[@rajeshmajumdar4999]

Superb Thank you so much!

[@ExcelOffTheGrid]

You're welcome!

[@ramruttunaubeeluck9235]

Awesome

[@ExcelOffTheGrid]

Thanks. 😁

[@marwanattya8067]

Great 👍

[@ExcelOffTheGrid]

Thank you! Cheers!

[@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.

[@ExcelOffTheGrid]

I'm only trying to teach one thing in each video, otherwise it (a) is too confusing for people to follow or (b) turns into a 1 hour video for something simple. All your points are valid. Regarding Names from last file modified. Yes, that is a valid option where there are multiple files covering different time periods. Which we doesn't always happen with reconciliations. If you are building a data model, then I agree with your point about normalizing the Tables. But, if you're not building a data model, then there is no "dictionary" to refer to. In terms of using List.Sum / aggregation to prevent duplication that is mentioned in the video towards the end. Yes, I could have done avoided the change data type step, but didn't want to get into manually applying data types inside the Table.AddColumn function. I've covered that in other videos previously.

[@StopWhining491]

No idea what all of that means in the context of the video.🤥

[@dougmphilly]

nice

[@ExcelOffTheGrid]

Thanks 😁

[@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?

[@waheedakhtar9242]

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.

[@ExcelOffTheGrid]

A partial payment is not reconciled. It remains I reconciled until either: A) the full payment is made B) an adjustment is made to write off the outstanding amount.

[@kudifilipe9875]

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

[@ExcelOffTheGrid]

For this to work, you need to have a shared reference. You could add a reference using this method: kzbin.info/www/bejne/bpTQpnime5iihck But you can’t match on amount as you will end up to too many items matching which shouldn’t be.

[@aggelospapaemmanouil5666]

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

[@ExcelOffTheGrid]

In many circumstances, we would have to open both files, copy and paste the data into the main workbook. Then we would need to VSTACK the data, adjusting the data ranges to ensure they include all the data. Then we can continue with the UNIQUE… So, depending on scenario, it requires more manual steps (increasing risk of error) and takes longer than a Power Query refresh option.

[@tariqaziz783]

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

[@willianrom]

Hello from Curitiba city in Brazil 🟩🟨

[@ExcelOffTheGrid]

Hello. I thought Brazilians spelt it as Brasil, rather than Brazil.

[@danielgoncalveslima9350]

​@@ExcelOffTheGridsim, in Brazil is Brasil 😊

[@willianrom]

​@@ExcelOffTheGrid In English it is Brazil, if you write texts in Brazilian Portuguese, then you write Brasil. I don't know why it's Brazil in English, maybe because of the pronunciation.

[@nuqwestr]

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

[@ExcelOffTheGrid]

It depends on what you're reconciling. In the example, we are using payroll data. So there may not be a concept of debit or credit as the values do not exist on a ledger. Equally, we could have been reconciling Health & Saftey Lost Time Injuries data and therefore the concept of debit/credit is non-existent. Debit, Credit or Adjustment, are accounting concepts tied to double-entry bookkeeping, and not to the underlying mathematics behind them. - Debit: A positive number - Credit: A negative number - Adjustment: A positive or negative change to a number Either way, they are all numbers, just in different directions or different categorizations. From a practical perspective, I would expect you would reconcile the total value of the item represented by the reference, irrespective of whether it is made up of Debits/Credits or Adjustments (which is why the video covers the section about what to do a reference exists multiple times in the data set). So, you may need to use Power Query to clean your accounting format into a normalized data format before undertaking a reconciliation.

[@scottjezard6447]

Get well soon 😂

[@ExcelOffTheGrid]

Thanks 🤣 I feel terrible, but the show must go on. Hopefully by next week I’ll be on full power again 😁

[@petelukz342]

inefficient way to do it but you tried

[@ExcelOffTheGrid]

How would you do it?

[@Softwaretrain]

Great video, Mark! Your explanation on automating reconciliations with Power Query is clear and very helpful. It's impressive how you simplified the reconciliation process, especially when there is a shared reference. I've also explored a related scenario in one of my video, focusing on reconciliations where we lack a reference column. In accounting, as you know, we often rely on amounts and dates for comparison, and these can often be duplicated in statements. My video, although in Persian, visually guides through a method to reconcile and detect discrepancies on both sides in such cases. It complements your approach by addressing a different yet common scenario in accounting. You can check it out here: kzbin.info/www/bejne/nHfJfGCAqMSLj5o. I believe it could add value to those who follow your channel for Power Query insights.