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.
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
Hello, I agree with you. I am a fixed asset manager and I get different sets of data each month. I know how to do the process shown in Excel and get the same end result, in about the same amount of time with advanced formulas and shortcuts. An advantage for me would be if I can cut out the prep work and, like you said, only need to update or add the new items to the query each month. Overall, still a great video but I would like to know how we follow up with the next month of data to see automation in action.
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.
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).
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.
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. 👍
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.
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.
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.
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.
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
For this to work, you need to have a shared reference. You could add a reference using this method: th-cam.com/video/8cmuEpF3oOg/w-d-xo.html But you can’t match on amount as you will end up to too many items matching which shouldn’t be.
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?
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.
@@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.
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: th-cam.com/video/fFfK-Js_XY8/w-d-xo.html. I believe it could add value to those who follow your channel for Power Query insights.
Useful having a real-life problem example to solve instead of just showing how an app feature works. Thanks!
Glad it was helpful! 👍
amazing lesson
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.
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.
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
Hello, I agree with you. I am a fixed asset manager and I get different sets of data each month. I know how to do the process shown in Excel and get the same end result, in about the same amount of time with advanced formulas and shortcuts. An advantage for me would be if I can cut out the prep work and, like you said, only need to update or add the new items to the query each month. Overall, still a great video but I would like to know how we follow up with the next month of data to see automation in action.
Damn helpful... 7 months later...thanks
After 7 months it's still help people. That's awesome. 😁
love it
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.
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).
You can do the bank reconciliation in Excel via power query...I have done it...
Excellent explanation Mark. Thank you!!!
Thanks Ivan. Glad it was helpful!
Big thanks from Somalia
You’re welcome 😁
Awesome as always, No 1 in the new year ❤👍
Thanks Kebin. I’ve got some good stuff planned for 2024, so I hopefully I won’t disappoint. 😁
Thank you sir, You made power query to become easier to use for me!
Thanks for making it easy to understand. 🤘🤘
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.
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. 👍
Thank you for you time! Its usefull.
Glad it was helpful! 👍
This was awesome, nice work, thanks!
Thank you - I'm glad you enjoyed it.
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.
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.
No idea what all of that means in the context of the video.🤥
Nice video, thanks for sharing!
Good job! Thx!
Thank you.
You could do it with a simple Unique function and a sumifs second column in 2 seconds
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.
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.
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.
Nice process
Thank you! Cheers! 😁
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
For this to work, you need to have a shared reference.
You could add a reference using this method:
th-cam.com/video/8cmuEpF3oOg/w-d-xo.html
But you can’t match on amount as you will end up to too many items matching which shouldn’t be.
Superb Thank you so much!
You're welcome!
Awesome
Thanks. 😁
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?
Great 👍
Thank you! Cheers!
Make downloading file easy, it's very difficult requires first sign up then another step
nice
Thanks 😁
Debits, Credits, Adjustments, don't see those concepts referenced.
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.
Hello from Curitiba city in Brazil 🟩🟨
Hello. I thought Brazilians spelt it as Brasil, rather than Brazil.
@@ExcelOffTheGridsim, in Brazil is Brasil 😊
@@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.
inefficient way to do it but you tried
How would you do it?
Get well soon 😂
Thanks 🤣
I feel terrible, but the show must go on. Hopefully by next week I’ll be on full power again 😁
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: th-cam.com/video/fFfK-Js_XY8/w-d-xo.html. I believe it could add value to those who follow your channel for Power Query insights.