AWESOME Excel trick to combine data from files with DIFFERENT headers

I want to combine data from different files, but the format doesn't match. In this video, let me show you two advanced Excel tricks to solve the problem.
We are going to use Power Query and simple M code to take data in different files and combine it. This works great if you have files with columns out of order or different column headers.
⏱ IN THIS VIDEO:
===============
0:00 - Problem 1: Columns not in same order
4:08 - Problem 2: Column names do not match!
📂DOWNLOAD SAMPLE DATA:
=========================
Sample data - chandoo.org/wp/wp-content/upl...
Use the instructions in the video to combine the data.
👩‍💻 MORE POWER QUERY:
======================
[100k+ views] Quick basics - • Learn Power Query & Au...
[400k+ views] Deep dive - • Power Query Tutorial -...
[200k+ views] Combine files in a folder with Power Query - • Powerful trick to comb...
[500k+ views] Combine all sheets in one file with Power Query - • AWESOME Excel trick to...
⏯ POWER QUERY PLAYLIST - • Power Query Tutorial -...
👩‍💻👩‍💻 EVEN MORE ON POWER QUERY:
================================
If you want to learn even more and see how Power Query can be integrated with other Excel features to make your life simple, join my Excel School program. In this complete course, you will learn how to use Excel & advanced Excel for data analytics job.
Visit chandoo.org/wp/excel-school-p... to sign up.
~
#Excel #combinedata

[@alfredoluwatomi6899]

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.

[@chandoo_]

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. 🤣

[@Unknown-ng8me]

@@chandoo_ Sir how to get job as a fresher in this field and from where we can get

[@kganesh3886]

@@chandoo_ Error Data 😒😒=source{data){0}

[@sehrishkiran1165]

@@chandoo_

[@BenDieselBasicAutomotive]

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

[@chandoo_]

Wow. so glad to hear this Ben. Kudos on acing that presentation. More power to you ⚡

[@arbazahmad7177]

Excellent and easy to understand👌. Thanks you Chandoo for this Mcode..

[@johnborg5419]

Thanks Chandoo !!! This is a very common problem to many!! Thanks again!! :) :)

[@FirestormT]

As always awesome :) Thank you Chandoo

[@karimallahwala7022]

Thank very much for sharing that great knowledge

[@justinwduff]

Excellent video, thank you!

[@pidzyn]

Awesome!. Thanks a lot for that!

[@BenDieselBasicAutomotive]

Wow!..... new learning for the day!.... Thanks again, Sir.... I'm an avid fan...

[@sakeobulesu7130]

Thanks for teaching us.. chandu sir

[@abhishekupadhyay6938]

Every time I watch your video I learn something new. Keep it up.

[@yufanli6]

this is exactly what I am looking for ! Thank you so much !

[@phoenix_ever]

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

[@sandorszilagyi5929]

Extremely helpful, thank you !

[@chandoo_]

Glad it helped!

[@niggszo]

Chandoo is "Awesome".. Thanks man!

[@automation254]

Nice time saver.

[@thealexanders7828]

Great videos as usual!

[@bigt6928]

Exactly what I want to learn! Thank You!!!

[@chandoo_]

You are welcome!

[@ICTNuggets]

Excellent!

[@successmantra4525]

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.

[@calbiju]

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.

[@actedorganization9857]

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

[@taizoondean689]

Thanks Sir...🙂

[@anandl2003]

Thanks chandoo

[@cp2345]

Thank you.

[@autobase16]

Whoaaaaa great 👍👍👍👍

[@zaighamuddinfarooqui1705]

Awesome💐

[@thstudies]

Very well explained SIR

[@martinangelraj3415]

Excellent buddy😍

[@mohammadnaved7079]

great bro

[@mandeepkaur-xl1zw]

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.

[@SEZO101]

So so helpful

[@nazarkamal8831]

Great 👍👍

[@ismaelkourouma5558]

Excellent

[@alexrosen8762]

Very useful tutorial. Thanks Chandoo

[@janinemccown617]

love this video and great information I can use right away

[@chandoo_]

Glad it was helpful!

[@radiali482]

awesome , thanks

[@chandoo_]

You're welcome!

[@umersohail1951]

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?

[@abhishekchahar7917]

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

[@williamthedataprof]

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

[@chandoo_]

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

[@teoxengineer]

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?

[@chandoo_]

Thanks Emre. See these other tutorials too. Combine all workbooks - kzbin.info/www/bejne/iXjdlpqjlp6Vmrs Combine all sheets - kzbin.info/www/bejne/oZDYmKaef5mqf9k

[@RAJESHS-df8io]

Chandoo 👌 Excel dooo

[@madmaagic]

could you make some tutorials on how to analyze stock, share, and companies on excel?

[@kirandaund6295]

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

[@markan6076]

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?

[@chandoo_]

You are welcome Markan. You can add a step in the transform sample to keep only the columns you want.

[@sadiaehsan787]

Please make video on Small, ROW Combined in excel ...for filtering data

[@maheshchagi6349]

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

[@chandoo_]

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.

[@ExcelWithChris]

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?

[@chandoo_]

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.

[@mdshahbaz4766]

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

[@BOOBOY19]

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!

[@chandoo_]

Hey, thanks PJ_ 😎

[@operator8194]

A+

[@chintan219]

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_]

You are welcome Chintan. You can select the step where you removed the column, edit the step (using gear icon ⚙) and add it back.

[@chintan219]

@@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_]

Not sure I understand. What do you mean ?

[@chintan219]

@@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.

[@chandoo_]

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.

[@math-buddy1754]

Hi, CHANDOO ji Very complicated topic Part-1 is done still have doubts about Part-2 and need more help.....

[@igordragicevic0606]

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

[@chandoo_]

You can use Power Query to do this. See my video on combining data from multiple sheets and customize it to get only B2. kzbin.info/www/bejne/oZDYmKaef5mqf9k

[@shubhambarolia2176]

I am facing both problems simultaneously and How to achieve this in multiple sheets

[@user-oe1mb5is8d]

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? 🤯

[@republicmanrajesh1874]

PLSQL video on this topic please sir 🙏🙏🙏

[@justmemyselfandi5532]

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?

[@chandoo_]

You can check the official documentation. - learn.microsoft.com/en-us/powerquery-m/

[@justmemyselfandi5532]

@@chandoo_ thx I guess I should use Bing search to get this one, since goole gives everything but not this 😉

[@divinelyblessed2497]

How can non IT person can learn automation.. Any videos on automation.. Pls advise .. Thanks

[@Dan_De_Man]

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

[@chandoo_]

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.

[@Dan_De_Man]

When I deleted changetype in different order query it seemed to remove the error

[@zubairsiddique5566]

Hi Chandoo When i tried to combine file,still got error. How can i fix it

[@wasimshaikh5794]

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?

[@chandoo_]

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 - kzbin.info/www/bejne/a6aak6OvqLRmfLM

[@mdliyaquathusain1239]

excel 2016 has differentinterface. i am unable to perform this task

[@maverick_anoop]

What if my header are not available in first row (horizontally) and are in the first colomn (vertically)?

[@chandoo_]

You can use table transpose feature (in the transform ribbon) to turn the table and do the rest.

[@Hurmali1]

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.

[@chandoo_]

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]

[@Hurmali1]

@@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.

[@Hurmali1]

oh, the next result after Source[Data] is List

[@chandoo_]

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.

[@Hurmali1]

@@chandoo_ thanks Chandoo. Yes correct. I click it, all the content of Transform Sample File appears, and the data turns getting the ! mark.

[@mentertainment1213]

How to remove unwanted spaces from multiple columns in power querry

[@chankokseng1030]

Expression.Error: The column 'Column1' of the table wasn't found. Details: Column1

[@darthmichau878]

😁

[@Denjixerwin]

DO YOU HAVE VIDEOS FROM BASICS TO ADVANCED ..?

[@revmure452]

How to combine data from multiple excel with multiple sheets

[@mohanbabuj2555]

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

[@mayuronce]

Make videos in Telugu also

[@nt4231]

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 😊

[@dipanjandash4485]

Excellent