I spent a whole day running circles . I try to import a bunch of datasets from folder for my case study. The error message would not go away till I saw your amazing video! I am grateful for for help. Thanks for the high quality video.
i like the way you present , clear and concise, most importantly easy to follow.. there are a lot of other youtube videos uploaded where the presenter is very confusing and sounds like a total show off.. Truly, people with brilliant minds are those who stay down to earth and just shines through without any effort
Absolutely thankful to you Sumit. The whole series of videos for Power Query was like some treasure discovery - so very helpful. Wonderful and detailed explanation
You sir are a carrier saver. That error kept bugging me for the past week till I watched your video. Thank you! I'm going to check your website immediately.
Hi Sumit, thank you so much for sharing your wonderful content, greatly appreciated. I am also like the few other viewers, spent a lot of time trying to figure out some stuff. However, you have covered it all with clear explanations which were easy to follow. In most cases on other videos, error-proofing is never mentioned and covered, leaving me/us behind from progressing. A huge thank you for this, please keep it up!
Many thanks for your video which has just saved me from Expression.Error misery. I followed your steps and success; I was able to merge all the various worksheets into one. Many thanks
It looks like the included "Power Query Course Download files" are the result files AFTER performing the exercise. Or at least in some cases. It would be helpful if they were the source files that you start the exercise with so we end up with the same results. Some files also appear to be missing in the zip file compared to the exercise.
But for this to properly work, you need to have the same amount of columns and the same names in each file, right? How to circle around this issue if you have one extra column in one file that is not in the other files, and properly include it in the combined file?
Thank you for share the files to work along with the video. Thank you for sharing the video. A couple questions What if spreadsheet C is missing a column that was in Table A. , and you still want to combine all the columns in A, B, C and so forth? Can we do that? Can workbooks have extra tabs for which we do not want to combine the data? Suppose all Tables have wanted data, but the colums are in different order?
Good video on teaching me on using power query. But when I follow ur steps in combining the tables for 12 months of a year. I just got 2 columns : Month, Table. But not a full expanded table. Pls advise how to fix this. thanks
When filtering out .extensions, why do you go to Text filter instead of just "uncheck"?? I saw that by many videos of other people...could you tell if there is a reason or any advantage/disadvantage? usually I am using uncheck...
I think that's becase he wants to filter all the files which have the extensions that begin with .xls, so that even macro files with .xlsm would also be considered. If he used the filter option and checked .xlsx only the xslx files are considered ignoring the rest. Hope I make sense.
Hey Sumit does this append all the data from different files? I noticed we didnt need to use the append function of PQ with this method. Can you plz clarify this for me?
Thanks very, very detailed and easily understood. But I have hyperlinks in my data and I lost them. Can you please tell us how to keep the hyperlinks in the query and load ?
Excellent explanation! Easy to follow and implement on my files. I used the power query to combine excel files from a folder. There are hyperlinks in the excel files that are not transferring as hyperlinks. Is there a way to correct this?
I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).
Hi, great tutorial. However, I would like to load the data into an existing sheet, but the option is greyed out. What could it be? The source is a Folder.
Hi sir, thank you for your videos, which is vary helpfull, i have one challange.. everyday i have to send the cdr (calls count) report every hour, i have tried with power query but the problem is wen ever i replace the data in table the power query piot counting histry(yesturday) count also need help on this plz
I followed these steps and it gave me output… when am updating any rows data it’s giving output accurately however if am updating columns to source data it’s not getting picked in power editor. Source data is in table format only ….. Please help pleaaasseee
What do you mean by CONTECT IS THE NAME OF COLUMN, what column are you talking about, there are more than a few columns in a table, I am confuse can you please explain in detail?
@4:01 - it’s not the Content column of the excel files but the Content column that PQ brings back as a reference to the content of the underlying file. So it should be the same when you run the same process. But it’s referring to the name of the column that holds the content of each of the files being combined.
Can the query only import or update new data from an updated excel file in the folder? I mean, it will not duplicate old data and only import new data that did not already exist in the excel files every time someone refreshes. Example: You created the North-west data file in the video (9:45/10:30), the query imported all the rows in the file, which is a duplicate of North data file. What if the North-data file is updated weekly and the folder has North-data_week01, week02, .....Week04. The North-data_WK04 file has content from week 01, how do you setup power query to only import the newly updated value in week 04 file and not duplicate all of its data?
Hi Sumit! Thanks for this tutorial video, this is very helpful! I need to do something like this, however, I would need to add 2 additional columns to the consolidated tables. I need to add "Country" and "Date Extracted (MMM-YY format)". Is this doable in Power Query? Or do you have an existing tutorial on this? btw, for Country column, each excel file comes from different countries, so for ex. first xls is from Canada, so whole Country column should be Canada. Then next xls is India, so the next combined table will be followed by India. I hope you can give me advise. Thanks!
Hi Jam. I’ve done something similar. If you have a fixed naming convention, then you can transform with additional column. Eg if the file name is Canada Feb 2021.xlsx, you can transform the file name to create those columns
This is not I am looking for. It doesn't sum all tables. It just append and there is more easy way to append the tables. But question is how to consolidate inner table data into one table?
This is not useful at all. You keep vacillating back and forth between the proper method and showing what would generate errors. You need to walk straight through doing it the proper way. Then you should go back and show missteps that would generate common errors. This is not a useful presentation at all.
I spent a whole day running circles . I try to import a bunch of datasets from folder for my case study. The error message would not go away till I saw your amazing video! I am grateful for for help. Thanks for the high quality video.
Same was for me as well😮
i like the way you present , clear and concise, most importantly easy to follow.. there are a lot of other youtube videos uploaded where the presenter is very confusing and sounds like a total show off.. Truly, people with brilliant minds are those who stay down to earth and just shines through without any effort
Thank you so much for the kind words Penny... Glad you're finding the videos useful :)
This is a great example how to load multiple excel files in an automated way. Thanks again for your master class!
This saved me so much time! I had to merge 81 excel worksheets and thank god I found your video. There are a total of 90 rows haha. THANK YOU!
You are my saviour Papa. Keep it up.
Hero bro , so simple and useful , love the way you explain without complications ❤️
you literally just saved me a week's worth of work. GOD BLESS YOU!
Absolutely thankful to you Sumit. The whole series of videos for Power Query was like some treasure discovery - so very helpful. Wonderful and detailed explanation
Thanks for the kind words. Glad you liked it
You sir are a carrier saver. That error kept bugging me for the past week till I watched your video. Thank you! I'm going to check your website immediately.
Hi Sumit, thank you so much for sharing your wonderful content, greatly appreciated. I am also like the few other viewers, spent a lot of time trying to figure out some stuff. However, you have covered it all with clear explanations which were easy to follow. In most cases on other videos, error-proofing is never mentioned and covered, leaving me/us behind from progressing. A huge thank you for this, please keep it up!
Many thanks for your video which has just saved me from Expression.Error misery. I followed your steps and success; I was able to merge all the various worksheets into one. Many thanks
These guys are very good at this. 🙂
This video is that one who help a lot working with multiple files in power query, thanks!!!
You saved my life, thanks for the video.
Very well explained. Thanks for covering the in and around issues as well.. :)
Easy Solution to a very annoying problem! Great video!
thank you after 2 days of searching this worked for me
I found what I need thanks to you. There is a lot of info in youtube and google. It is a good tutorial.
Amazing explanation... Love to watch your playlist...
Fantastic this saved me as I was struggling with this so much
When I am appending the files the headers of each file is coming before data. How can I remove the header of each files from being imported?
Excellent job. Well done!!
Great! Thank you! You saved my time.
I found this method to be the best... thanksssssssss.
Nice explanation. Kudos
Thanks so much for another wonderful lesson.
your tutorials are always easy to follow! thank you!
It looks like the included "Power Query Course Download files" are the result files AFTER performing the exercise. Or at least in some cases. It would be helpful if they were the source files that you start the exercise with so we end up with the same results. Some files also appear to be missing in the zip file compared to the exercise.
Very Well Done!!!!
Gracias por compartir tus conocimientos.
Very useful info, thank you
But for this to properly work, you need to have the same amount of columns and the same names in each file, right? How to circle around this issue if you have one extra column in one file that is not in the other files, and properly include it in the combined file?
Thank you one again, especially for covering why the 'combine' button can give an error if file names inconsistant.
I did it step by step as you, It worked, You were of really great help men. I would give 10 out of 10
Thanks Sanjeev... Glad you found it useful :)
Thank you for share the files to work along with the video.
Thank you for sharing the video.
A couple questions
What if spreadsheet C is missing a column that was in Table A. , and you still want to combine all the columns in A, B, C and so forth?
Can we do that?
Can workbooks have extra tabs for which we do not want to combine the data? Suppose all Tables have wanted data, but the colums are in different order?
This video helps me a lot! Thank you!
Thanks very much.
very well explained & helped me a lot.
Thank you, on point
Very good tutorial, thank you
Thanks sir highly grateful for your guidance
Excellent tutorial, Thank You!
Excellent tutorial video
Hi ur video is very informative... Thank you.... I have some data but getting error in one column only.... Can u pls help....
Good video on teaching me on using power query. But when I follow ur steps in combining the tables for 12 months of a year. I just got 2 columns : Month, Table. But not a full expanded table.
Pls advise how to fix this. thanks
When filtering out .extensions, why do you go to Text filter instead of just "uncheck"?? I saw that by many videos of other people...could you tell if there is a reason or any advantage/disadvantage? usually I am using uncheck...
I think that's becase he wants to filter all the files which have the extensions that begin with .xls, so that even macro files with .xlsm would also be considered. If he used the filter option and checked .xlsx only the xslx files are considered ignoring the rest. Hope I make sense.
Hi bro! While adding the column using the syntax Excel.Workbook(Content) under tables I am getting Error and not Table. Where I am doing wrong?
Hey Sumit does this append all the data from different files? I noticed we didnt need to use the append function of PQ with this method. Can you plz clarify this for me?
If I need to combine Sheet kind and get rid of rows which doesn't have values while combining..Do I need to work on sample file query?
Will it load data if there is changes in headers?
Is it possible to change sheet names in existing power query files.
Hello in minute 4:35 instead of Excel Files if we have .csv files we can´t do with Excel.Workbook(), What function to use in this case? Tks in advance
What is the logic of removing the other columns and keeping two at 6:08 ? If you keep those column does it give some other result than expected?
Great Video!
I like very much the way you explain the subject.
All videos are great
Will merging of file work through power queries if the first column of the 2nd file have duplicates?
Yes, it would still work. You can get rid of the duplicates once the files are combined
Thanks very, very detailed and easily understood. But I have hyperlinks in my data and I lost them. Can you please tell us how to keep the hyperlinks in the query and load ?
Excellent explanation! Easy to follow and implement on my files. I used the power query to combine excel files from a folder. There are hyperlinks in the excel files that are not transferring as hyperlinks. Is there a way to correct this?
Wonderfull
How can i use this method for folder containing PDF files only including tables?
Tell me please. How to track changes in the source data?
In my case excel is only importing data from first 30 rows of a worksheet. It isn't loading all the data. How to fix it? Please help.
I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).
Hi, great tutorial. However, I would like to load the data into an existing sheet, but the option is greyed out. What could it be? The source is a Folder.
Hi sir, thank you for your videos, which is vary helpfull, i have one challange.. everyday i have to send the cdr (calls count) report every hour, i have tried with power query but the problem is wen ever i replace the data in table the power query piot counting histry(yesturday) count also need help on this plz
Hello. I don’t have the “Get Data” option under the “Data” tab. Please help.
I followed these steps and it gave me output… when am updating any rows data it’s giving output accurately however if am updating columns to source data it’s not getting picked in power editor. Source data is in table format only ….. Please help pleaaasseee
I want combine excel as a verically.not upend,is this possible??
Thank you sir
Which version of excel you are using?
What do you mean by CONTECT IS THE NAME OF COLUMN, what column are you talking about, there are more than a few columns in a table, I am confuse can you please explain in detail?
@4:01 - it’s not the Content column of the excel files but the Content column that PQ brings back as a reference to the content of the underlying file. So it should be the same when you run the same process. But it’s referring to the name of the column that holds the content of each of the files being combined.
Can the query only import or update new data from an updated excel file in the folder? I mean, it will not duplicate old data and only import new data that did not already exist in the excel files every time someone refreshes.
Example: You created the North-west data file in the video (9:45/10:30), the query imported all the rows in the file, which is a duplicate of North data file. What if the North-data file is updated weekly and the folder has North-data_week01, week02, .....Week04.
The North-data_WK04 file has content from week 01, how do you setup power query to only import the newly updated value in week 04 file and not duplicate all of its data?
If excel files have different columns then what to do
Hi, can i merge 30 workbook holding 46 lakh row
when i add new files , shows key didnt match any rows in the table
what if we have sheets instead of tables?
Hi Sumit! Thanks for this tutorial video, this is very helpful!
I need to do something like this, however, I would need to add 2 additional columns to the consolidated tables. I need to add "Country" and "Date Extracted (MMM-YY format)". Is this doable in Power Query? Or do you have an existing tutorial on this?
btw, for Country column, each excel file comes from different countries, so for ex. first xls is from Canada, so whole Country column should be Canada. Then next xls is India, so the next combined table will be followed by India.
I hope you can give me advise. Thanks!
Hi Jam. I’ve done something similar. If you have a fixed naming convention, then you can transform with additional column. Eg if the file name is Canada Feb 2021.xlsx, you can transform the file name to create those columns
Hai I want to help
How to split the 720x400x0.010
what do you mean by using LOCALE?
Woow 👍
What to do if folder location is changing everyday
Brilliant. You just cleared a cloud of confusion between combining and editing straight and transform data before the combine.
only Sheet type is showing , table type is not showing while clicking on table.
Did anyone not have the edit option after choosing the folder
This is not I am looking for. It doesn't sum all tables. It just append and there is more easy way to append the tables. But question is how to consolidate inner table data into one table?
यदि सारी एक्सेल के फाइल का डाटा मिला क्या 20 लाख होगा कि तो भी हो जाएगा???
Hmm. I have no Tables in my 70 files, only Sheets. I am okay with Sheets if it still grabs all the data :)
This doesn't show how to merge multiple excel files with multiples tabs... it's so frustrating! Great tutorial nonetheless
I can't emphasize the importance of this video!
***imp
This is not useful at all. You keep vacillating back and forth between the proper method and showing what would generate errors. You need to walk straight through doing it the proper way. Then you should go back and show missteps that would generate common errors.
This is not a useful presentation at all.