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.
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, 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 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-SGzegma9bdY.html Combine all sheets - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-k_ugshJ4wIw.html
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??
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.
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.
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.
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..
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
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.
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?
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????
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?
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
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.
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? 🤯
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.
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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-k_ugshJ4wIw.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.
@@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.
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.
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?
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 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5u7bpysO3FQ.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.
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 😊
