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
@randomnatural9644
3 ай бұрын
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.
@GrainneDuggan_Excel
9 ай бұрын
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
9 ай бұрын
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
4 ай бұрын
You can do the bank reconciliation in Excel via power query...I have done it...
@StopWhining491
8 ай бұрын
Useful having a real-life problem example to solve instead of just showing how an app feature works. Thanks!
@ExcelOffTheGrid
8 ай бұрын
Glad it was helpful! 👍
@sakhilengwenya594
8 ай бұрын
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
8 ай бұрын
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.
@aggelospapaemmanouil5666
9 ай бұрын
You could do it with a simple Unique function and a sumifs second column in 2 seconds
@ExcelOffTheGrid
9 ай бұрын
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
4 ай бұрын
Make downloading file easy, it's very difficult requires first sign up then another step
@TeresaM-w3n
15 күн бұрын
love it
@ramruttunaubeeluck9235
9 ай бұрын
Awesome
@ExcelOffTheGrid
9 ай бұрын
Thanks. 😁
@Deoreth
9 ай бұрын
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
9 ай бұрын
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
8 ай бұрын
No idea what all of that means in the context of the video.🤥
@waheedakhtar9242
8 ай бұрын
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
8 ай бұрын
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.
@Sumanth1601
8 ай бұрын
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
8 ай бұрын
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. 👍
@nuqwestr
9 ай бұрын
Debits, Credits, Adjustments, don't see those concepts referenced.
@ExcelOffTheGrid
9 ай бұрын
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.
@MaydayAggro
7 ай бұрын
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?
@kebincui
9 ай бұрын
Awesome as always, No 1 in the new year ❤👍
@ExcelOffTheGrid
9 ай бұрын
Thanks Kebin. I’ve got some good stuff planned for 2024, so I hopefully I won’t disappoint. 😁
@k16e
Ай бұрын
Damn helpful... 7 months later...thanks
@ExcelOffTheGrid
Ай бұрын
After 7 months it's still help people. That's awesome. 😁
@power-reconcile
3 ай бұрын
Nice video, thanks for sharing!
@kudifilipe9875
8 ай бұрын
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
8 ай бұрын
For this to work, you need to have a shared reference. You could add a reference using this method: kzitem.info/news/bejne/mZmj23mmf2Wggpw But you can’t match on amount as you will end up to too many items matching which shouldn’t be.
@IvanCortinas_ES
9 ай бұрын
Excellent explanation Mark. Thank you!!!
@ExcelOffTheGrid
9 ай бұрын
Thanks Ivan. Glad it was helpful!
@bbworld6363
4 ай бұрын
Thanks for making it easy to understand. 🤘🤘
@TAR2711
8 ай бұрын
Thank you for you time! Its usefull.
@ExcelOffTheGrid
8 ай бұрын
Glad it was helpful! 👍
@assailhawk
17 күн бұрын
amazing lesson
@olufemiolawale3848
8 ай бұрын
Thank you sir, You made power query to become easier to use for me!
@petelukz342
5 ай бұрын
inefficient way to do it but you tried
@ExcelOffTheGrid
5 ай бұрын
How would you do it?
@willianrom
9 ай бұрын
Hello from Curitiba city in Brazil 🟩🟨
@ExcelOffTheGrid
9 ай бұрын
Hello. I thought Brazilians spelt it as Brasil, rather than Brazil.
@danielgoncalveslima9350
9 ай бұрын
@@ExcelOffTheGridsim, in Brazil is Brasil 😊
@willianrom
9 ай бұрын
@@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.
@mcwahaab
9 ай бұрын
Big thanks from Somalia
@ExcelOffTheGrid
9 ай бұрын
You’re welcome 😁
@Alexx9811
Ай бұрын
This was awesome, nice work, thanks!
@ExcelOffTheGrid
Ай бұрын
Thank you - I'm glad you enjoyed it.
@OZbMG8jsJTX14AWYne4omBw
9 ай бұрын
Good job! Thx!
@ExcelOffTheGrid
9 ай бұрын
Thank you.
@Bhavik_Khatri
9 ай бұрын
Nice process
@ExcelOffTheGrid
9 ай бұрын
Thank you! Cheers! 😁
@dougmphilly
9 ай бұрын
nice
@ExcelOffTheGrid
9 ай бұрын
Thanks 😁
@rajeshmajumdar4999
8 ай бұрын
Superb Thank you so much!
@ExcelOffTheGrid
8 ай бұрын
You're welcome!
@Softwaretrain
8 ай бұрын
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: kzitem.info/news/bejne/x3ycsWGArJGJjG0. I believe it could add value to those who follow your channel for Power Query insights.
@marwanattya8067
8 ай бұрын
Great 👍
@ExcelOffTheGrid
8 ай бұрын
Thank you! Cheers!
@scottjezard6447
9 ай бұрын
Get well soon 😂
@ExcelOffTheGrid
9 ай бұрын
Thanks 🤣 I feel terrible, but the show must go on. Hopefully by next week I’ll be on full power again 😁
Пікірлер: 63