Stop Doing Manual Reconciliations in Excel: Use Power Query

[@venividioptavi]

I thought I wasn't going to learn Power Query. Your tutorials are well laid out and you explain it very clearly. Thank you! Your work is appreciated.

[@Thund3rSt0rm123]

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!!

[@MaydayAggro]

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!

[@ivarkronick4335]

Question: how did you make that index column? I have the same problem

[@MaydayAggro]

@@ivarkronick4335 i concatenated date and amount. You might even be able to add credit card type depending on how your transactions are displayed.

[@85MA]

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.

[@ExcelU]

Excellent!

[@abdidekmuhumed4510]

Best of video of what I was searching for❤❤❤❤

[@ExcelU]

Thank you!!

[@rebeccaagno6966]

really amazing! Thank you

[@sherlinejeanbaptiste9443]

Thank you so much for sharing!!!

[@MaryGlorPingoy]

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

[@ExcelU]

Glad it will be helpful :)

[@Gorman-84]

And... maybe I missed you saying it, but import table data from a .pdf file! Magic. works pretty well I think. Thanks!

[@samuel4131]

Thank you for the lesson. you demonstrated how to prepare a bank rec for one bank account. Would you prepare additional bank recs for multiple bank accounts in the same workbook or separate ones?

[@terbasol]

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

[@ExcelU]

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.

[@terbasol]

@@ExcelU Thanks a million, really appreciated

[@نقابپوش-ض2ي]

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?

[@Anonymous-e1n2o]

Can this be achieved even if there are no matching unique identifiers in both the bank statement and GL? Also, where I work it’s common for the amounts posted in our ERP system to be broken into smaller numbers that add up to one larger number on the bank statement or vice versa. Any solutions to that? Thanks in advance.

[@silvestrecamposano6317]

Thank you very much for the discussion, I have been looking for this topics!.. Thanks again....

[@ExcelU]

My pleasure!

[@gayustambunan6250]

This is super... Thanks

[@ExcelU]

Welcome 😊

[@AbuTaiyabAbuBakkar]

Hi Jeff..Thanks for your content. However, every time I try to download the sample file I am blocked. Please help! Thanks Taiyab

[@walidgamal9309]

Thank you so much for this informative video, it's very helpful

[@power-reconcile]

Nice video, thanks for sharing!

[@ExcelU]

Thanks for watching!

[@jodavila16]

Great Video, Thanks!! 😀

[@ExcelU]

Glad you liked it!

[@szabopal85]

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

[@anyandeveryistaken]

Awesome Mr. Jeff. Love it

[@ExcelU]

Thanks :)

[@AbuTaiyabAbuBakkar]

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

[@samanthadaroga4811]

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"?

[@ThePattersonPod]

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.

[@phuphatjo4481]

Thankyou

[@ExcelU]

You’re welcome 😊

[@rksenthilrajan]

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?

[@anishchandran9145]

could you share any other excel tool for doing bank recon. I felt this is complicated

[@mahmoudalshami1557]

Do you have similar vedios for AP/AR

[@simbarashemakwati4260]

I have downloaded a ledger report Now want in insert a column and put each ledger name to each transaction.

[@abutaiyababubakkar3563]

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

[@Hyper9th]

But power query I have restriction on row number. Whenever I load data in the power query editor, it shows that only 1000 rows are loaded. I have excel 2021 version. Can you suggest how i increase the row numbers so that It can load up 1,00,000 rows ?

[@ExcelU]

PQ will process all of your data rows even though the preview is limited for performance purposes.

[@umerao]

please share practice file.

[@ExcelU]

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

[@jitendersharma727]

I am new learner but i find the video fast

[@DENNISCOPENHAVER]

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.

[@warrenanderson412]

Change value format to $

[@abbeygarcia410]

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?

[@nnamdiofodile2507]

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.

[@mayssasleiman7639]

Why I can't download the sample file ?

[@AnandGautam9901]

Great

[@umerao]

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]

try matching on value then, and then check they refer to same transaction. maybe by date.

[@byroninya8]

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

[@Colour.Purple]

Hi there, i am follwing this but I do not understand what do you mean by "checks " is that an American way to say transactions ?

[@ExcelU]

Hi there! Checks is a type of expense transaction, yes :)

[@dayama4]

👍

[@LiveStreamingHD]

These teacher take only simplest possible data to teach stuff. Why dont you get some actual bank statement and try to reconcile it.

[@ShaysRebellion1786]

You jumped through the steps too quickly.

[@divyaalok5538]

जय श्री राम