Thank you for your effective teaching style, which has greatly improved my understanding of banking reconciliation using Power Query. I kindly request you to create another tutorial featuring real bank statements and cash book data. Real statements are often less organized, requiring advanced skills for effective cleaning and organization with Power Query. In essence, a tutorial incorporating real bank statements and ledger accounts would be highly beneficial.
By far the most complete bank rec (lists comparision) i found on entire youtube so far! i cant lie i had to watch the video for 2 days and on the 3rd day i was able to do the whole rec from start to end by myself with no mistakes. Excel and Power query is an amazing tool and Jeff is a great teacher! Kudos!!
Thanks for the suggestion about sharing the sample file. I have added the download here: www.excel-university.com/fxm8ay8sudu/ I also made a note in the video description. Hope it helps! Thanks Jeff
This process helped me with a credit card clearing account that has thousands of transactions a month. I had to create a funky index column because the amounts are the only things that repeat in each of the comparative systems (and the amounts can be repeated dozens of times), but it worked! Thanks!
Hi Jeff big fan of your work and I am actually a student from Excel Campus and nice to see you here. Just wondering if the worksheet and data shared so that we can work along with you. Thanks a lot
Thanks for your kind note :) I'm working on the written narrative w/file that accompanies this video, and once I post it to my website, I'll add a link here. It should be ready soon.
Hii jeff hope you are good, Actually we do store deposit reconciliation and takes us alot of time, Like we have a bank statement with store name and deposit statement with a store name too, Now we have to match the values as well as write the date in which amount is deposited in bank, Is there any simple shortcut for that?
Very easy to follow steps, great presentation. Thanks for sharing, I am trying to combine multiple worksheets, each work sheets having different header columns. For eg, Sheet 1 has A,B,C has columns. Sheet 2 has B,D,E has columns, Sheet 3 has A,CF has columns, Sheet 4 has E,G,H has columns, I wanted to merge all 4 sheets together A,B,C,D,E,F,G,H is this possible in Power Query? Can you please share a technique if there is any?
how to reconcile if there is no common reference in both sheets or you cannot have any clue about the similarity in both sheet. For Example, your check sheet only have check numbers and your bank book have check numbers with something with Text also and you have separated checks from text and further you match them in query. suppose if both the sheets have different reference number but they must reconcile with value, then how to achieve that?
@@craftyboy2919 what if on the GL (books) its 3 or 4 separate amounts that make up a batch and that ties to the total amount that comes out of the back on one day with a different date because on the books it's posting with the Transaction date and the Bank Has a date of the Settlment date.
Hi Jeff..thanks for the wonderful content. However, every time I try to download the sample I am blocked by Excel Universiy! Please help! Thanks Taiyab
Hi there..Thank you so much for your content. So for odd reason everytime I try to download the excel workbook from the given link above I am blocked! Please remove this block. Thanks
You can even reference other files as source data so you do not need to copy paste data in the next month just replace the source data files and hit refresh and all new data is loaded and transformed
Really great video and I have a question please? what if two different people created a sheet include names and i want to compare names but everyone writes the names by his own spelling for example jo and joe or johnn and john ..etc here the same data but different spelling and i have more than 50 names and all names not repeated again in the same sheet so i can't make it manually by replace so how to solve that because different spelling will affect the comparison and match formula
A really good video; good moderate pace, good clear speech and reference naming (eg. transformations). I am now being exposed to Power Query in this vid and have a question - When do you know to use a "reference" to create "a connection only query" - What does "connection only query" really mean for transformation purposes? And when to double click an existing query to "modify it". Overall, what's the difference between creating a reference to connection only query versus modifying a query? (Is it a connection to source data versus modifying source data?) Also, when are you to use "Close & load" versus "Close & Load to"?
Hi Samantha, I’ve used Power Query some. A connection only query means that you have only loaded it to a connection within Excel, as opposed to loading it into a sheet as a table. There are a couple other ways to load the data, but I have only used “connection only” and tables. The connection only option is useful if you are going to reference it with it another query or if you are not finished and plan to finish and load the query to a table. Close and load is going to default to loading all the queries as tables within your sheets. “Close and load to” is going to give you loading options such as a table, pivot table (if I recall right), and connection only.
I downloaded your Excel spreadsheet (check register with reconciliation) I must say I very much impressed. However, I do a question I can't seem to find solution to. Is there any to display my values in two digit numbers or dollars and cents? I have looked at the Queries. They show to convert text to an Integer. If i change int 64 to number of course get an error.
i haven't tried it yet but I think I'm gonna cry, this will make reconciliation efficient. just have to be patient at first. just need to identify common data
Love this! My merged query keeps adding the total from one of my tables as an additional line item in the query result which is essentially doubling the total (except it adds another instance every time I refresh so the difference keeps getting larger) any ideas why this might be happening?
This happens because every time you refresh, power query will add together all the data from the tables in that workbook (including your merged query table). As a result, your merged query table will always be duplicated by itself anytime you refresh. To avoid this, copy your merged query table name, then using filters do a "DOES NOT INCLUDE" type filter in the applied steps to exclude that table from your merged query source data.
