Consolidate Excel Sheets with Power Query

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

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

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

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

Thank you for this beautiful gift of time saving tool.

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?

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.

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?

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

Another brilliant video...I salute you!!

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?

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

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.

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.

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

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

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?

Awesome very helpful thanks

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

Awesome!

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

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?

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.

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

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

Many thanks, Mynda. Very clearly explained!

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.

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

Thanks

Very simple

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?

Many thanks, Awesome

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

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

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.

awesome

Superb!

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

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

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

What if we add another sheet?

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

Excellent . Thank you.

🎉

Again, I enjoyed your tutorial. Thank you !

Thanks a lot it saved my lot of time after a day research

Hi there:) I am stuck and please need your valuable input! My order amount has a 2 decimal setting, as in $123.00! The currency in in dollars and I want it to be as in this lesson as in 123 (currency)! 1.Why is this as $123.00 and how do i adjust this to read as currency $123. Thank you:)

Perfect explanation! Thank you so much for this!

So simple! Thank you for the clear and concise explanation

Εκατό χιλιάδες συναρτήσεις είχα φτιάξει για να βγαίνουν όλα αυτόματα και εσύ το απεικόνισες τόσα απλά και όμορφα και γρήγορα, ένα μεγάλο μπράβο.