Consolidate Excel Sheets with Power Query - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Peace
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
reply
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
reply
robwin007
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.
reply
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.
reply
gary
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?
reply
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?
reply
Ian
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.
reply
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.
reply
SANKET
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.
reply
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.
reply
Susan
Thanks so much Mynda. Such a simple video to follow along to. I have a scenario that's a little more complex Im seeking advice on. I have 200+ workbooks with data but the data isn't formatted into a table. Plus certain sheets have hidden colums and in a few instances, a column heading may differ here and there (which Im not so concerned about). I applied steps from this video but I keep getting errors during close and load. Any advice?
reply
Thanks so much Mynda. Such a simple video to follow along to. I have a scenario that's a little more complex Im seeking advice on. I have 200+ workbooks with data but the data isn't formatted into a table. Plus certain sheets have hidden colums and in a few instances, a column heading may differ here and there (which Im not so concerned about). I applied steps from this video but I keep getting errors during close and load. Any advice?
reply
Tadz
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 !!
reply
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 !!
reply
fati
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
reply
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
reply
Pranav
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
reply
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
reply
Peace
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
reply
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
reply
Add a review, comment
Other channel videos
