AWESOME Excel trick to combine data from files with DIFFERENT headers
Vložit
- čas přidán 30. 07. 2024
- 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 - Věda a technologie
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_
Excellent and easy to understand👌.
Thanks you Chandoo for this Mcode..
Thanks Chandoo !!! This is a very common problem to many!! Thanks again!! :) :)
As always awesome :) Thank you Chandoo
Every time I watch your video I learn something new. Keep it up.
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 ⚡
Wow!..... new learning for the day!.... Thanks again, Sir.... I'm an avid fan...
Thank very much for sharing that great knowledge
Thanks for teaching us.. chandu sir
Excellent video, thank you!
this is exactly what I am looking for ! Thank you so much !
Awesome!. Thanks a lot for that!
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
Chandoo is "Awesome".. Thanks man!
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.
Great videos as usual!
Exactly what I want to learn! Thank You!!!
You are welcome!
Extremely helpful, thank you !
Glad it helped!
Nice time saver.
Very well explained SIR
Excellent buddy😍
Thanks Sir...🙂
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.
Very useful tutorial. Thanks Chandoo
love this video and great information I can use right away
Glad it was helpful!
great bro
Excellent!
Awesome💐
Whoaaaaa great 👍👍👍👍
Thank you.
Thanks chandoo
So so helpful
Great 👍👍
Excellent
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
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.
awesome , thanks
You're welcome!
Chandoo 👌 Excel dooo
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?
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 - czcams.com/video/SGzegma9bdY/video.html
Combine all sheets - czcams.com/video/k_ugshJ4wIw/video.html
could you make some tutorials on how to analyze stock, share, and companies on excel?
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??
Please make video on Small, ROW Combined in excel ...for filtering data
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..
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_ 😎
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.
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.
A+
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
PLSQL video on this topic please sir 🙏🙏🙏
Hi, CHANDOO ji Very complicated topic Part-1 is done still have doubts about Part-2 and need more help.....
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.
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????
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..!!!
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.
How can non IT person can learn automation.. Any videos on automation.. Pls advise .. Thanks
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. czcams.com/video/k_ugshJ4wIw/video.html
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? 🤯
excel 2016 has differentinterface. i am unable to perform this task
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
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 - czcams.com/video/5u7bpysO3FQ/video.html
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.
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 😉
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.
Expression.Error: The column 'Column1' of the table wasn't found.
Details:
Column1
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.
How to remove unwanted spaces from multiple columns in power querry
DO YOU HAVE VIDEOS FROM BASICS TO ADVANCED ..?
How to combine data from multiple excel with multiple sheets
😁
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....
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