Get Multiple Files Containing Multiple Sheets with Power Query - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
it_industry
FAQs Answered:
1. Can you get workbooks with a different number of columns? Yes, however the columns will be appended in order from left to right. Therefore, if your columns are not all in the same order you'll end up with a mess!
2. What happens if workbooks have different column headers? The worksheets will be appended in column order from left to right irrespective of the column names. As long as the columns contain the same data and are in the same order, then it doesn't matter what they're called.
3. What happens if workbooks include a blank worksheet or worksheets you don't want to import? These worksheets will be included in the query however, you can add a filter in the query to exclude any sheets you don't want included in the final dataset.
reply
FAQs Answered:
1. Can you get workbooks with a different number of columns? Yes, however the columns will be appended in order from left to right. Therefore, if your columns are not all in the same order you'll end up with a mess!
2. What happens if workbooks have different column headers? The worksheets will be appended in column order from left to right irrespective of the column names. As long as the columns contain the same data and are in the same order, then it doesn't matter what they're called.
3. What happens if workbooks include a blank worksheet or worksheets you don't want to import? These worksheets will be included in the query however, you can add a filter in the query to exclude any sheets you don't want included in the final dataset.
reply
Oscar
Great video as always, really love your work, I hope you can help me with a concern, I want to get several files with around 12000 rows each into 1 query, the question is if the queries are limited by a number of rows? I found that excel is limited to a 1,048,576 rows , so will I have problems if I have so many sheets with 12000 rows and the query exceeds a number of rows?
reply
Great video as always, really love your work, I hope you can help me with a concern, I want to get several files with around 12000 rows each into 1 query, the question is if the queries are limited by a number of rows? I found that excel is limited to a 1,048,576 rows , so will I have problems if I have so many sheets with 12000 rows and the query exceeds a number of rows?
reply
Arturo
If you have an additional blank worksheet will it import a blank row into power query? Is there any issue with having a blank sheet2, sheet3, etc., when importing all sheets into power query? I don't want to filter them out. I want them available for the user to add information. Just not sure what happens when one or more sheets have no data. Thank you
reply
If you have an additional blank worksheet will it import a blank row into power query? Is there any issue with having a blank sheet2, sheet3, etc., when importing all sheets into power query? I don't want to filter them out. I want them available for the user to add information. Just not sure what happens when one or more sheets have no data. Thank you
reply
Guillaume
Wonderful video! The content is crystal clear and -almost- covers what I'm looking for - say, if I have 10 tables with 5 sheets, where would this process differ if I want to combine the data not in one single table, but in five tables? (i.e. one for each sheet) Whenever I try to separate it that way, I end up with a clunky amount of queries.
reply
Wonderful video! The content is crystal clear and -almost- covers what I'm looking for - say, if I have 10 tables with 5 sheets, where would this process differ if I want to combine the data not in one single table, but in five tables? (i.e. one for each sheet) Whenever I try to separate it that way, I end up with a clunky amount of queries.
reply
Marilyn
I receive multiple files every day. All these files have data in identical format but different data. Each file contains 10 tabs. Data on different tabs is different in nature so shouldn-t be consolidated across tabs. Is there a way to use Power query to simply consolidate multiple files into one file but still with multiple tabs?
reply
I receive multiple files every day. All these files have data in identical format but different data. Each file contains 10 tabs. Data on different tabs is different in nature so shouldn-t be consolidated across tabs. Is there a way to use Power query to simply consolidate multiple files into one file but still with multiple tabs?
reply
greeppl
Thanks for the video - really helpful
After having used Google Sheets for the past years, moving to Excel was a terrible experience.
As if Microsoft have never heard about the word Usability or User Experience.
The data integration across Google Sheets is MILLION times SIMPLER vs the convoluted Excel...
reply
Thanks for the video - really helpful
After having used Google Sheets for the past years, moving to Excel was a terrible experience.
As if Microsoft have never heard about the word Usability or User Experience.
The data integration across Google Sheets is MILLION times SIMPLER vs the convoluted Excel...
reply
Francisca
Hi! I loved your video, it was exactly what I needed :)
I was wondering if you could help me with this matter: in my case, I selected numerous tables from different sheets from multiple workbooks. If I add another table to one of the source files, how can I include it later with power query?
reply
Hi! I loved your video, it was exactly what I needed :)
I was wondering if you could help me with this matter: in my case, I selected numerous tables from different sheets from multiple workbooks. If I add another table to one of the source files, how can I include it later with power query?
reply
Ah.
I watched the video -Get Multiple Files Containing Multiple Sheets with Power Query- you were clear and the video very instructive. Can you tell me how can I activate autocomplete in the formula bar of -Power Query-. I have Microsoft office professionnel Plus 2019.
reply
I watched the video -Get Multiple Files Containing Multiple Sheets with Power Query- you were clear and the video very instructive. Can you tell me how can I activate autocomplete in the formula bar of -Power Query-. I have Microsoft office professionnel Plus 2019.
reply
Nanci
I follow the instructions..but I don't have the option -transform-..mine is showing edit and load for excel 2016..pls advise if possible to have 14 excel with 4 sheets containing huge data-this option can be used? Want a summary report for 14 countries data
reply
I follow the instructions..but I don't have the option -transform-..mine is showing edit and load for excel 2016..pls advise if possible to have 14 excel with 4 sheets containing huge data-this option can be used? Want a summary report for 14 countries data
reply
william
Hi, yes it all works lovely but the '# date' does not seem to mentioned on Microsoft ignite DAX functions page or appear on the drop down options list when entering, sorry for the confusion, but it wasn't the explanation of the syntax that was lacking.
reply
Hi, yes it all works lovely but the '# date' does not seem to mentioned on Microsoft ignite DAX functions page or appear on the drop down options list when entering, sorry for the confusion, but it wasn't the explanation of the syntax that was lacking.
reply
Add a review, comment
Other channel videos
