Consolidate Excel Sheets with Power Query

Excellent . Thank you.

This is Brilliant.Thanks a lot for all of your work.Keep going and post videos.

What if we add another sheet?

My data gets doubled whenever I click on the refresh button
Please helpp

Hi thank you for this video But I have a problem when i click refresh, all my data are duplicated.

You have the simplest
explanations as always, this is great! THANK YOU

Superb!

awesome

how can I consolidate data from a password-protected Excel file into another, using a power query without opening that other file each time I wanted to run a consolidation query?

I already have the password of the QUERY "Excel source file", just wanted not to open that file each time I run Query refresh.

I really wish I had found this video years ago, it would have saved me time and frustration. So happy now I can do my reports in no time 😍 Thank you so much. Minda YOU ROCK!!!

you have, again, saved my bacon. thank you, thank you, thank you.

Many thanks, Awesome

This was exactly what I needed. I followed your instructions and was able to combine 54 tables, but... when I went to update info on one of the tables and refreshed the query, it did not update the query. Did I miss a step?

Very simple

Thanks

I've been using Excel for years to record my betting results. 4 days ago after watching your video introducing Tables, I quickly turned by sheets into tables, but still couldn't see how I could pull my sheets together. Then I watched this video and suddenly I'm using Power Query, something that I never knew existed. Amazing ! Thanks Mynda for your superb tutorial videos. Now to tackle dashboards and Pivot Charts !!

How do i know someone has made any changes??? Is there query that can highlight any changes?

Happy New Year, Mynda.

When one has three workbooks, each with different columns- how would one put that data on one worksheet?

There are several columns that are the same across three workbook files; how could I use the similar columns to work together as one data source?

I get three weekly reports; some of the data columns contain the same data; only one has the workers name.

I need to create a weekly payroll amount report. And, each week create a new (different) payroll report.

I would like to have a dashboards maintain a running total of what each worker has been paid, year-to-date.

That’s my conundrum.

Many thanks, Mynda. Very clearly explained!

Mynda - when i hit refresh my data set doubles instead of replacing the information, Do you know what is causing this

Is it possible to split the data by using Power Query?

Hi There,



I have developed a report in Power BI desktop using various DAX formulas and calculations, report has two or more pages, the report is in table format only. users are accessing this report online



Now I am looking for a solution (formula / VBA or any other trick) by which I can create an export to excel button which will extract two or more pages from the Power BI desktop & online both services and save them in a single excel workbook.



Please guide me to perform this activity.

Thank you for the great video
I am still learning power query.
I have learned that if I always have a backup file, there is not danger in trying new techniques.
So before watching this video, somewhat blindly jumped in, hoping for the best.
I was able to consolidate separate files in one folder. The spreadsheets were NOT in table form., and were NOT in the same workbook. I renamed the tab on each to "Data"
If it works without tables, is there a reason one should convert data to a table first?
Also, any reason to put all in one workbook?

Just wanted to say thank you for providing this information! Just saved me at least two hours of tedious work every month.

Awesome!

cant thank you enough!!! This is exactly what i needed

Awesome very helpful thanks

Awesome help! Definitely worth the time and subscription (and like!)

I just have a question though - what if I'm adding a new Worksheet for the consolidated table? Is there a pro-tip process or do I need to re-do from the start?

Thanks for the video. My question is, I need to copy and update the data from the main sheet but when deleted from main sheet shouldn't delete the copy. Basically, I use main sheet as temporary where I add, update and delete data, but the copy sheet should have all data that is entered in the main sheet as a database. Any ways to accomplish this. Expecting your help. Thanks

received a 1 error, the power query doesn't match adding 6 mos sales manually. Not sure whats wrong. ( my manual numbers are correct)

Hi Mynda, I've used the Query function in Google Sheets quite a bit and find it very useful too. Not being much of an Excel user I was surprised to find that Excel didn't have that sort of function. Well I guess that Power Query is that sort of function in a different sort of way. I see from your example in this video that you appear to have to refresh the Power Query table to load any changed data from the source tables. In Google Sheets Query updates in the background automatically, can the same be achieved in Power Query? Thanks.

Suppose, we have multiple sheets containing not formated official table. What is the way to convert all the sheets table into official table all at once. And also want to learn to combine multiple workbook containing multiple sheets not havening official table.

I Have a data to Consolidate, it contains shop Numbers, Shop Names and Turnovers. The shop name changes but the Shop number which acts as a unique ID Stays the same throughout, my question is will the power query pick that up and add a new line for the same Shop number and a different tenant this time around? Thank you for this great content

I tried to combined all 12 sheets but when i keyed in the formulae =Excel.CurrentWork(), it called out 4 table only not 12? anyone can tell me why?

Another brilliant video...I salute you!!

In 1st file my data is in sheet1 & in 2nd file my data is in sheet2, how to combine in this scenario. pls help

in your examples there were no titles over each of the tables. how do you handle excluding the title material (not the column title)....for example, there is header above the tables for the company name, and the time periods for data in the respective tables for each personal order tab?

Hi! I want to aggregate data in query editor from single table or worksheet. Is that possible. If needed I can send you example table.

Are there any instructionals on how to consolidate sheets that are in non tabular forms? I have thousands of sheets that in the same format, but non-tabular "printable" format I want to aggregate into a table?

Thank you for this beautiful gift of time saving tool.

Thank you! Quick question, I noticed that you added new data into your existing worksheets/tables. What about if I don't want to add to existing worksheets/tables. I want to add a new worksheet. That didn't work for me. How do I add a new worksheet and not new data into the existing worksheet and I want that my pivot table to update with the new worksheet? Thanks

Thanks.
What if the data in each sheet is not in table format? (I mean range)

Thank you! Nice! My question is a little different so can you please help if you know the answer. Let us say that I have 2 Workbooks. One has 5 worksheets(It has all tables so 5 tables) How do I move all 5 tables/5 worksheets into the second workbook all together rather than moving each tab separately (I can move worksheets but not tables) and the second question is when you move even single tab with (Move or Copy) option, you can move or copy either before or after particular worksheet. I have already named all worksheets in my second workbook and I want to move into those particular worksheets not before or after. Is it possible? Thank you in advance

Hi Mynda, your tutorials are blessings.. thank you so much.. I want to know that can I combine data from two worksheets/workbooks having columns with some similar and some different headers? and I want the data from same headers columns to be consolidated in one/same column and data of different headers column to be added separately in different columns. I hope I am not making it too complicated. please help

Really great explanation of a power and advanced concept, thank you!

Thanks! Does this work only if you have different tabs in one file and you are opening the query in one of those tabs? Not a new file. It definitely does not work if you have different files. Please advise?

Hi there, get data is missing from the data tab. any idea pls?

Thank you very much for sharing!!It's very helpful

Can we put tables side by side?

Brilliant, thank you Mynda. This example is easier than consolidating using the Append feature