Waoooo,this is awesome.I have been looking for something like this but all the videos I watched are just too way complex to understand. Thanks so much Chandoo for this simple straight forward approach,you are such a fantastic guy in excel. More power to your elbow.
Thank you for taking your valuable time to share this. I have battled this issue for 2 days now. I have researched, searched everywhere I could to find this answer. Everyone likes to post about combining sheets into one pivot table but their examples always show worksheets where everything matches up perfectly. That isn’t the real world. Kind regards and new subscriber!!
Very complicated topic yet... explained in simplified way.... I was able to get it.... even if am just an average person and has no formal @ educational training in programming... I applied most of your excel techniques in my office work. My Boss was impressed with my presentation... Thank you very much.....
While I admit I couldn't get all of the steps to work (almost certainly user error), this helped get rid of most of the tedium of aggregating data across multiple spreadsheets. Such a relief! Thanks for sharing.
Just a question. If the Receipt column was not in the Sample File, will it still come through? Did it not now come through because by chance we used the file with the Receipt column as the Sample? Or will all columns of all files pull through independent of which was the first sample file? I also see that if you combine, it used Column1, Column2 etc as headings. If I test on my side, it automatically picks up the top row of the files as headers and THAT is causing the problem as it used the first row automatically as the headers and therefore it will only import corresponding column names) What am I doing wrong? I went back and switched off the detect headers and thought that might help and that it would import all columns, but when I select the file with only two columns (and now Excel is NOT using the first row as headers) it still only combines the first two columns and still skips the Column 3 because it is empty in the sample file. I know you can solve this with code, but I suggest creating a dummy file with as part of the folder files with all the heading I might want to detect and use that is the first file in the process. Or how would I solve this?
Good questions Chris. If you examine the "Transform sample" query, you will see the assumptions PQ made. If you have variable number of columns, it can be tricky to handle it, but possible. I suggest using the drill-down approach (data{0}) so that we are not exactly getting any columns but just the entire sheet.
Chandoo, it is so so good tutorial. I have sometimes trouble for this issue and I couln't solve it and it makes my brain confuse. Are there any more examples to combine different methods?
Thanks Emre. See these other tutorials too. Combine all workbooks - th-cam.com/video/SGzegma9bdY/w-d-xo.html Combine all sheets - th-cam.com/video/k_ugshJ4wIw/w-d-xo.html
It awesome, today I was looking for this topic and i got yours as notification. Im happy in learning this. I would like to get one more video on the same topic. Where we have multiple workbook with multiple sheets but all work book has same format and even the sheet name. How to consolidate the workbook as per sheets.
Sir plz reply deos data analyst jobs will disappear in feature in next 4 to 5year whatever the role data analyst has right now that r going to done by the data engineer or data scientists in feature????
Hi, this video example is for files where you've only one tab. How do you deal with multiples tabs named dufferentlybon racha file? on each file? Regards
Hi can u help me with how to convert file in CSV format from binary file or any other file format with just any shortcut or any other 1 click or add ins I need to do it daily on save as then drop down csv format
Hi Chandoo, it is a wonderful video. Am trying to combine files with multiple sheets with different names and different column names. It’s way too complicated.
Hi , thanks for the outsanding session. I am working as information management, but i was dealing large of data sometimes combained 3 sheets with heads rowa, the issue that i cannot solved is when uncheck the null valuse , the recods from master sheet(combained sheet) will disapear or if uncheck null value other data will be null defently
Hey chandoo. Good video but in my Excel in power query option I'm not having combine option. So how to combine all files as you have done it in video.. plzz give me a alternative of this..
Thank you! Can I use the List transform method to combine files with not only different headers but different number of columns with no unique column/column header between them. My clinical trial databases all make headings and number of columns as per their own company. No one column header or internal info matches the other databases (i.e., the trial identifier called NCT ID may be a separate column in one database, but in another database, it maybe in a column called 'Identifier' with each cell having the ID & other text/number bunched in). I just need to throw the duplicate trials and keep unique records as one database may be more up-to-date and have listed more trials and the other might be missing some trials. NOTE: All clinical trials are idetifiable by world-centralized NCT IDs. That's how I know there are duplicates between databases.
@@chandoo_ Thank you! What i will do is clean up the data first by splitting the 'identifier' column into NCT ID and other unnecessary extra text/numbers. That way I will have the NCT ID as a common column among all the databases.
nicely explained Sir, although I am facing an Error: The column 'Column1' of the table wasn't found. can you pls tell me how to troubleshoot this problem?
Very useful video, I have a doubt if any of the row is having a blank cell, for that blank cell, can we fil it with color in PQ, and it should be applied for all the exell once we consolidate using PQ.... any inputs or suggestions
PQ cannot colour cells for you. But you can do this with Conditional Formatting in Excel. Select the data, go to Home > Conditional Formatting Click on "Hightlight cell rules" and then select "More rules". Change the "Format only cells with" option to Blanks and apply the color you want.
Good stuff, Chandoo. It's good to highlight though that the last solution heavily relies on the columns being in the same order, am I right? ...and therefore there's a chance to have mixed data if that is not the case
Yes. You can't combine data unless something is fixed. So either column names stay same or columns are in same order. Otherwise, how would we know which column has what with certainty??
Thanks a lot Chandoo, great content really helpful, could you also guide how do we handle if the data is huge 11L+ rows after merging the files. Thanks in advance
Awesome. I have a small question for you. I extract the data in PQ then afterwards I need one column to add I. That extracted data. So how I can add that previously removed column in PQ? Thanks.
@@chandoo_ it worked. But I deleted many column so it take time to arranged but it worked. Also find new error. Which data I extracted the date column every time I refresh the data sheet the date changes everytime 🤦. How come I fix this? Thanks.
@@chandoo_ I extracted data using PQ every time I refresh the data, The Date column format changes everytime. In source file the Date column formate I already set as Dates. How can I fix this problem? Thanks.
Could be a bug. When I set format to "Date" in form Power Query, Excel shows it as date for me. In any case, the format is for visual use only. The dates are stored as numbers internally anyway. If you do use the table for anything else (pivot, formulas etc.) you will need to format again.
Hi Chandoo ur channels helps me a lot. I just wanted to know do we need any kind of certification to become a data analyst or we can become a good analyst just watching ur videos and implement it on the practical data sets. I hv done my BCA and MCA. Hope for an really response from u.
Hmm.. think about it for a second. If the headers are not same, column order is jumbled, how would anyone know what is a column? You need some rules before you can pre-process the data.
I have a question. I have 100 worksheets and i want to extract B2 cell from every worksheet in new worksheet, in new table. Is there faster way than manually linking cell from each sheet in my new table? Ty
You can use Power Query to do this. See my video on combining data from multiple sheets and customize it to get only B2. th-cam.com/video/k_ugshJ4wIw/w-d-xo.html
That is all nice, but where do you find complete list of commands available in Power Query and that it is not in "help file". Once wen I know all commands available and what they do I have no problem to combine them to do what I need to do. Do you have any tips on that matter?
Great one, Chandoo! I seem to have one more problem on top of "Columns not in same order" - some columns are missing in some source files. How do I "Transform Sample Flie" in order to avoid adding the 3 missing columns into all faulty tables in multiple files?
Works up to removing columns. I'm working on first file in transform sample file. If I remove column 1 and 2, the different order query has an expression error. The column column1 of the table wasn't Found. Ps in the zipped files July is called july-2022-z.xlsx none of the others have the z
You are right. I did not notice the inconsistency in file names before. Must have been a typo. Nevertheless the technique should work fine. For different order, you can't delete columns in "transform sample". Instead, do it after you combine all data.
What if you need to combine two files that have partial information about each customer. How do you combine them but create a single row with all of the customer data listed? For example, I have two sheets, one where the customers' name and total ARR is listed and another sheet with the customers' name and size of them. I want a single sheet with all of these columns for the customer on a single row. Would that be possible? 🤯
Not able to combine huge data with same headers through power query. Each spread sheet has 9lac rows of data and total is 7 spreadsheet. Any suggestions on this?
You "can" combine such large volumes of data, but you can't push it back to Excel as it has a limit of 1mn rows. You can use the datamodel to store the data though. See this video for an idea - th-cam.com/video/5u7bpysO3FQ/w-d-xo.html
Hi Chandoo, thanks for this. This is great, looks a bit simple. But I keep having a problem when I enter the formula =source[Data]{0}. My Data would just get (!) sign. I wonder what I might have done wrong. I tried to follow you again and again, slowed down the motion, but still, it gets the same sign. Pls help, salam dari Indonesia.
Hmm... Can you check what you get if you write just source[Data]? Please note that Power Query is case sensitive. So the step name should be exactly as it appears. I think it would Source[Data]
@@chandoo_ I take care of all those sensitive stuff, but still with the same result. the s in 'Source' is also in capital shape. Also the technique in the first half of the video does not work for me. Or, the original files must be first shaped in certain ways to get better processed by this technique? Terimakasih.
So the list works. What is the first element of the list? You can just select the first item and double click to drill down. This shows the value. If you are getting error at this point, it means, there is something wrong with your list's first item.
Import data from a folder with multiple files .A file header: date (ETD)，blalala.B file header : ETD and Date(means ETA),blala.I want to merge A file date and B file ETD, But Error.
Hi sir, can u give solution for my doubt. It collection no. Of days for each invoice. Invoice 1 rs. 10000 DT. 01.01.23, invoice 2 rs. 5000 dt.20.01.23 invoice 3. Rs. 3000 dt. 01.02.23 invoice no. 4 rs. 8000 dt.09.02.23 and collection 1 rs. 4000 dt. 10.01.23, collection 2 rs. 10000 dt. 29.01.23, collection 3 rs. 4000 st. 06.02.23 and collection 4 rs. 10000 st. 18.02.23. Can u pl invoice wise no. Of days from collection date. In excel formula.. Pl....
Hi Chandu, I am interested in taking class , what’s the procedure, I am in 🇺🇸, have a good knowledge in excel, still like to learn from beginning…. Please contact me or how do I get connected with your…. Thanks 😊
Waoooo,this is awesome.I have been looking for something like this but all the videos I watched are just too way complex to understand.
Thanks so much Chandoo for this simple straight forward approach,you are such a fantastic guy in excel.
More power to your elbow.
I am glad you enjoyed this, Alfread. My elbows are getting their own SIX PACK, thanks to all the file combining I've been doing. 🤣
@@chandoo_ Sir how to get job as a fresher in this field and from where we can get
@@chandoo_ Error Data 😒😒=source{data){0}
@@chandoo_
Thank you for taking your valuable time to share this. I have battled this issue for 2 days now. I have researched, searched everywhere I could to find this answer. Everyone likes to post about combining sheets into one pivot table but their examples always show worksheets where everything matches up perfectly. That isn’t the real world.
Kind regards and new subscriber!!
Very useful tutorial. Thanks Chandoo
Very complicated topic yet... explained in simplified way.... I was able to get it.... even if am just an average person and has no formal @ educational training in programming... I applied most of your excel techniques in my office work. My Boss was impressed with my presentation... Thank you very much.....
Wow. so glad to hear this Ben. Kudos on acing that presentation. More power to you ⚡
Thanks Chandoo !!! This is a very common problem to many!! Thanks again!! :) :)
Every time I watch your video I learn something new. Keep it up.
Thanks for teaching us.. chandu sir
As always awesome :) Thank you Chandoo
Great videos as usual!
this is exactly what I am looking for ! Thank you so much !
Great video.
After watching 10 times, I have understood now.
Thank very much for sharing that great knowledge
love this video and great information I can use right away
Glad it was helpful!
Exactly what I want to learn! Thank You!!!
You are welcome!
Excellent and easy to understand👌.
Thanks you Chandoo for this Mcode..
Awesome!. Thanks a lot for that!
Wow!..... new learning for the day!.... Thanks again, Sir.... I'm an avid fan...
While I admit I couldn't get all of the steps to work (almost certainly user error), this helped get rid of most of the tedium of aggregating data across multiple spreadsheets. Such a relief! Thanks for sharing.
Glad to hear this helped!
Extremely helpful, thank you !
Glad it helped!
Very well explained SIR
Nice time saver.
Excellent buddy😍
Whoaaaaa great 👍👍👍👍
Awesome💐
Great 👍👍
Excellent!
Thanks Sir...🙂
Thanks chandoo
So so helpful
Thanks i got stucked and i saw this
Just a question. If the Receipt column was not in the Sample File, will it still come through? Did it not now come through because by chance we used the file with the Receipt column as the Sample? Or will all columns of all files pull through independent of which was the first sample file? I also see that if you combine, it used Column1, Column2 etc as headings. If I test on my side, it automatically picks up the top row of the files as headers and THAT is causing the problem as it used the first row automatically as the headers and therefore it will only import corresponding column names) What am I doing wrong? I went back and switched off the detect headers and thought that might help and that it would import all columns, but when I select the file with only two columns (and now Excel is NOT using the first row as headers) it still only combines the first two columns and still skips the Column 3 because it is empty in the sample file. I know you can solve this with code, but I suggest creating a dummy file with as part of the folder files with all the heading I might want to detect and use that is the first file in the process. Or how would I solve this?
Good questions Chris. If you examine the "Transform sample" query, you will see the assumptions PQ made. If you have variable number of columns, it can be tricky to handle it, but possible. I suggest using the drill-down approach (data{0}) so that we are not exactly getting any columns but just the entire sheet.
Excellent
Chandoo 👌 Excel dooo
awesome , thanks
You're welcome!
great bro
Hi Chandoo i'm one of your sons friends Nish. and I was in nakshu's class last year, you have great content and tutorials!
Hey, thanks PJ_ 😎
Chandoo, it is so so good tutorial. I have sometimes trouble for this issue and I couln't solve it and it makes my brain confuse.
Are there any more examples to combine different methods?
Thanks Emre. See these other tutorials too.
Combine all workbooks - th-cam.com/video/SGzegma9bdY/w-d-xo.html
Combine all sheets - th-cam.com/video/k_ugshJ4wIw/w-d-xo.html
Thank you.
It awesome, today I was looking for this topic and i got yours as notification. Im happy in learning this.
I would like to get one more video on the same topic. Where we have multiple workbook with multiple sheets but all work book has same format and even the sheet name. How to consolidate the workbook as per sheets.
Sir plz reply deos data analyst jobs will disappear in feature in next 4 to 5year whatever the role data analyst has right now that r going to done by the data engineer or data scientists in feature????
Hi, this video example is for files where you've only one tab. How do you deal with multiples tabs named dufferentlybon racha file? on each file? Regards
Hi can u help me with how to convert file in CSV format from binary file or any other file format with just any shortcut or any other 1 click or add ins I need to do it daily on save as then drop down csv format
Hi Chandoo, it is a wonderful video. Am trying to combine files with multiple sheets with different names and different column names. It’s way too complicated.
Hi , thanks for the outsanding session. I am working as information management, but i was dealing large of data sometimes combained 3 sheets with heads rowa, the issue that i cannot solved is when uncheck the null valuse , the recods from master sheet(combained sheet) will disapear or if uncheck null value other data will be null defently
Hey chandoo. Good video but in my Excel in power query option I'm not having combine option. So how to combine all files as you have done it in video.. plzz give me a alternative of this..
Thank you! Can I use the List transform method to combine files with not only different headers but different number of columns with no unique column/column header between them. My clinical trial databases all make headings and number of columns as per their own company.
No one column header or internal info matches the other databases (i.e., the trial identifier called NCT ID may be a separate column in one database, but in another database, it maybe in a column called 'Identifier' with each cell having the ID & other text/number bunched in).
I just need to throw the duplicate trials and keep unique records as one database may be more up-to-date and have listed more trials and the other might be missing some trials.
NOTE: All clinical trials are idetifiable by world-centralized NCT IDs. That's how I know there are duplicates between databases.
You can try. But you will need a robust method to identify the correct columns. Otherwise, you will end up with a jumbled mess.
@@chandoo_ Thank you! What i will do is clean up the data first by splitting the 'identifier' column into NCT ID and other unnecessary extra text/numbers. That way I will have the NCT ID as a common column among all the databases.
nicely explained Sir, although I am facing an Error: The column 'Column1' of the table wasn't found. can you pls tell me how to troubleshoot this problem?
Very useful video,
I have a doubt if any of the row is having a blank cell, for that blank cell, can we fil it with color in PQ, and it should be applied for all the exell once we consolidate using PQ.... any inputs or suggestions
PQ cannot colour cells for you. But you can do this with Conditional Formatting in Excel.
Select the data, go to Home > Conditional Formatting
Click on "Hightlight cell rules" and then select "More rules".
Change the "Format only cells with" option to Blanks and apply the color you want.
Good stuff, Chandoo.
It's good to highlight though that the last solution heavily relies on the columns being in the same order, am I right? ...and therefore there's a chance to have mixed data if that is not the case
Yes. You can't combine data unless something is fixed. So either column names stay same or columns are in same order. Otherwise, how would we know which column has what with certainty??
Thanks a lot Chandoo, great content really helpful, could you also guide how do we handle if the data is huge 11L+ rows after merging the files. Thanks in advance
could you make some tutorials on how to analyze stock, share, and companies on excel?
But what if a multiple tables are in one sheet and each table headers are in shuffle column so how can we get all header at one column
Awesome. I have a small question for you. I extract the data in PQ then afterwards I need one column to add I. That extracted data. So how I can add that previously removed column in PQ? Thanks.
You are welcome Chintan.
You can select the step where you removed the column, edit the step (using gear icon ⚙) and add it back.
@@chandoo_ it worked. But I deleted many column so it take time to arranged but it worked. Also find new error. Which data I extracted the date column every time I refresh the data sheet the date changes everytime 🤦. How come I fix this? Thanks.
Not sure I understand. What do you mean ?
@@chandoo_ I extracted data using PQ every time I refresh the data, The Date column format changes everytime. In source file the Date column formate I already set as Dates. How can I fix this problem? Thanks.
Could be a bug. When I set format to "Date" in form Power Query, Excel shows it as date for me. In any case, the format is for visual use only. The dates are stored as numbers internally anyway. If you do use the table for anything else (pivot, formulas etc.) you will need to format again.
Hi Chandoo ur channels helps me a lot. I just wanted to know do we need any kind of certification to become a data analyst or we can become a good analyst just watching ur videos and implement it on the practical data sets. I hv done my BCA and MCA. Hope for an really response from u.
Great video. But what if the columns are not in the same order in the different files?
It will still work. That is what I explained in the video.
I mean excel files where both headers are not the same and column orders not the same.
Hmm.. think about it for a second. If the headers are not same, column order is jumbled, how would anyone know what is a column? You need some rules before you can pre-process the data.
Please make video on Small, ROW Combined in excel ...for filtering data
I have a question. I have 100 worksheets and i want to extract B2 cell from every worksheet in new worksheet, in new table. Is there faster way than manually linking cell from each sheet in my new table? Ty
You can use Power Query to do this. See my video on combining data from multiple sheets and customize it to get only B2. th-cam.com/video/k_ugshJ4wIw/w-d-xo.html
I am facing both problems simultaneously and How to achieve this in multiple sheets
nice,but how to approach when only one of the multiple files have headers,rest have data in same column order,but dont have headers..!!!
when I'm trying to do this with company data it is showing column 49 not found
That is all nice, but where do you find complete list of commands available in Power Query and that it is not in "help file". Once wen I know all commands available and what they do I have no problem to combine them to do what I need to do. Do you have any tips on that matter?
You can check the official documentation. - learn.microsoft.com/en-us/powerquery-m/
@@chandoo_ thx I guess I should use Bing search to get this one, since goole gives everything but not this 😉
Hi, CHANDOO ji Very complicated topic Part-1 is done still have doubts about Part-2 and need more help.....
Great one, Chandoo! I seem to have one more problem on top of "Columns not in same order" - some columns are missing in some source files. How do I "Transform Sample Flie" in order to avoid adding the 3 missing columns into all faulty tables in multiple files?
You are welcome Markan. You can add a step in the transform sample to keep only the columns you want.
How can non IT person can learn automation.. Any videos on automation.. Pls advise .. Thanks
Hi Chandoo
When i tried to combine file,still got error. How can i fix it
Works up to removing columns. I'm working on first file in transform sample file. If I remove column 1 and 2, the different order query has an expression error. The column column1 of the table wasn't Found. Ps in the zipped files July is called july-2022-z.xlsx none of the others have the z
You are right. I did not notice the inconsistency in file names before. Must have been a typo. Nevertheless the technique should work fine.
For different order, you can't delete columns in "transform sample". Instead, do it after you combine all data.
When I deleted changetype in different order query it seemed to remove the error
What if you need to combine two files that have partial information about each customer. How do you combine them but create a single row with all of the customer data listed? For example, I have two sheets, one where the customers' name and total ARR is listed and another sheet with the customers' name and size of them. I want a single sheet with all of these columns for the customer on a single row. Would that be possible? 🤯
Not able to combine huge data with same headers through power query. Each spread sheet has 9lac rows of data and total is 7 spreadsheet. Any suggestions on this?
You "can" combine such large volumes of data, but you can't push it back to Excel as it has a limit of 1mn rows. You can use the datamodel to store the data though. See this video for an idea - th-cam.com/video/5u7bpysO3FQ/w-d-xo.html
Get data option not available what to do
excel 2016 has differentinterface. i am unable to perform this task
Hi Chandoo, thanks for this. This is great, looks a bit simple. But I keep having a problem when I enter the formula =source[Data]{0}. My Data would just get (!) sign. I wonder what I might have done wrong. I tried to follow you again and again, slowed down the motion, but still, it gets the same sign. Pls help, salam dari Indonesia.
Hmm... Can you check what you get if you write just source[Data]?
Please note that Power Query is case sensitive. So the step name should be exactly as it appears. I think it would Source[Data]
@@chandoo_ I take care of all those sensitive stuff, but still with the same result. the s in 'Source' is also in capital shape. Also the technique in the first half of the video does not work for me.
Or, the original files must be first shaped in certain ways to get better processed by this technique? Terimakasih.
oh, the next result after Source[Data] is List
So the list works. What is the first element of the list? You can just select the first item and double click to drill down. This shows the value. If you are getting error at this point, it means, there is something wrong with your list's first item.
@@chandoo_ thanks Chandoo. Yes correct. I click it, all the content of Transform Sample File appears, and the data turns getting the ! mark.
What if my header are not available in first row (horizontally) and are in the first colomn (vertically)?
You can use table transpose feature (in the transform ribbon) to turn the table and do the rest.
What if both of problems come together
A+
PLSQL video on this topic please sir 🙏🙏🙏
How to remove unwanted spaces from multiple columns in power querry
Import data from a folder with multiple files .A file header: date (ETD)，blalala.B file header
: ETD and Date(means ETA),blala.I want to merge A file date and B file ETD, But
Error.
DO YOU HAVE VIDEOS FROM BASICS TO ADVANCED ..?
Hi sir, can u give solution for my doubt. It collection no. Of days for each invoice. Invoice 1 rs. 10000 DT. 01.01.23, invoice 2 rs. 5000 dt.20.01.23 invoice 3. Rs. 3000 dt. 01.02.23 invoice no. 4 rs. 8000 dt.09.02.23 and collection 1 rs. 4000 dt. 10.01.23, collection 2 rs. 10000 dt. 29.01.23, collection 3 rs. 4000 st. 06.02.23 and collection 4 rs. 10000 st. 18.02.23. Can u pl invoice wise no. Of days from collection date. In excel formula.. Pl....
Expression.Error: The column 'Column1' of the table wasn't found.
Details:
Column1
How to combine data from multiple excel with multiple sheets
😁
Make videos in Telugu also
Hi Chandu, I am interested in taking class , what’s the procedure, I am in 🇺🇸, have a good knowledge in excel, still like to learn from beginning….
Please contact me or how do I get connected with your….
Thanks 😊
Excellent video, thank you!
Excellent