Combine Data From Multiple Excel Files into a Single Excel File - With Dynamic Columns and Sheets
ฝัง
- เผยแพร่เมื่อ 31 ม.ค. 2020
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
2 Similar Videos that I made earlier
- Combine Data From Multiple Excel Files - Same Columns - • Combine Data from Mult...
- Combine Data From Multiple Excel Files - Different Columns - • Combine Data from Mult...
More tips and tricks on Power Query | Excel | Power BI - www.goodly.co.in
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - - Blog - - - -
www.goodly.co.in/blog - วิทยาศาสตร์และเทคโนโลยี
![เกิดใหม่ทั้งทีก็เป็นสไลม์ไปซะแล้ว ซีซั่น 3 - ตอนที่ 62 [ซับไทย]](http://i.ytimg.com/vi/fsIY2fKn38Y/mqdefault.jpg)
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
This is exactly what I was searching
This is what I am looking for. Thank you so much for sharing! It’s awesome!!!
Thanks a ton!! I liked the way you explained with crisp words
This is what i am look for the Past one week . Now i find out. Thank you so much.
Excelllent video! You tuly deserve many more subscribers (11K) and views (23K) than I can see at the time of watching it. I 'm glad that I found your channel and videos. Keep them coming and know that they are much appreciated!
The best Goodly video in my opinion. Loved it! Thanks a ton!!
Thanks Smit!
Thanks a million Brother, I was struggling since last two days to understand the error from where it was coming now after watching your video it's cleared. Keep it up. 👍
Thank you for this super helpful vid! So great to stumble upon someone who knows their stuff. Sub'd and can't wait to see more from you!!
Brilliant as always.thank you.
Excellent!! Thank you very much!
Excellent video !!!Helps me a lot
This was very helpful, and your style was so easy to follow along. It was exactly what I needed to do with some files I received (100+). Thank you so much! And I'm a total noob with Power Query, but still got it done. Thanks!
Sorry happy to hear this Michael.. Kudos to you!
Such an under-rated video. Thanks so much!
Thanks Bro!
Tusi great ho, I have never seen any tutorial like this. Amazing & to the point
Pure awsomeness!!!
Thank you for sharing your knowledge. I subscribed your channel.
Very good informative video. Thank you a lot. You explain the solution in the way I can easily understand and grab the logic although I just start using power query. And yet not in a simplistic way. Perfect) I subrscribed. Can't wait to go though you other videos.
I LOVE IT ♥ Man, you're my savior! I have been looking for such solution for more than 6 months and I couldn't find it anywhere! I have a report coming every week with rolling 52 weeks meaning that the next week report will have one week less at the beginning but a new one at the end. The only way I could "solve it" was altering the Excel files before uploading them. This will save me a lot of time and headaches when explaining to people how to update their own reports! (Using it in Power BI but still, solved it the same!)
I will share this solution everywhere to give you the credit!
Hey thanks Alan.
The good part is that Power Query in Excel works the same as Power Query in Power BI.
Cheers
@@GoodlyChandeep :) thanks to you mate!
I do have one more "issue" with my report that I can work around again directly in Excel however I am sure there is a way to do it straight in Query (although obviously I don't know): how can I use this solution you have very well explained mixed with "hard coding" the name for some other columns that are changing dynamically from report to report? I would like to keep the solution for the 52 rolling weeks I have but I also receive in the same reports the last 4 weeks of sales (changing the name according to the first week in the 52 rolling one). I would like those 4 columns to always keep a dedicated name (for instance, SALES WK1, SALES WK2, SALES WK3 and SALES WK4) ignoring the dynamic name they will come with.
In another words, my headers will be partially fixed (from dynamic names) and partially dynamic
I hope you can help me or refer me to some possible solution out there :)
Cheers!
@@Alan.DL7 See if this helps th-cam.com/video/xamU5QLNiew/w-d-xo.html
@@GoodlyChandeep ey :)
I have checked that video before and it does solve some other things I have been working with but in this case I do not want to remove those columns, I want them to adopt an specific name. The thing is that those 4 columns are dynamic, which each report they are rolling one week. :(
@@Alan.DL7 Try dynamic renaming -
www.goodly.co.in/dynamic-column-names-power-query/
I am not sure if I have understood your problem thoroughly. If this doesn't work, dont hesitate to write to me on goodly.wordpress@gmail.com
Thanks a lot Sir !!!
Mind Blown
Your video deserves much more attention. I have a folder with more than 100 excel workbooks, it will take forever if not for your awesome video.
Keep it up man
If you can do vids about fuzzy lookup for match, even better!
Glad you found it helpful! I'd try to put together a video on FUZZY LOOKUP
Genius. Thank you!
Welcome!
You've explained very well. Very helpful.
Glad you found it helpful
Thanks a lot
Thank you! :)
Wow! Thank you so much!
You're welcome!
you are expert. Thanks for sharing the knowladge.
Glad to help
Best power query video i have ever watched. It is highly appreciated that you help me a lot. Although i am using power bi for this application, it still works. Cheers man you save my week.
Glad it helped :)
Thank you a lot. You are my EXCEL MASTER
Glad I could help!
Thank you. This will be very usefull! Is gonna save me a lot of time
Glad you found this helpful!
Thank you very much. this was really helpful.
Glad it was helpful!
supereb!!!!!!!!!!!!!!!!!!!!! you are a Gem
Hi. Thanks for the detailed explanation on your video. I have two questions - 1) I need to combine files from multiple excel sheets into one file. However the source files have a version control with incremental data in each file that is added to the folder. Can I do a refresh wherein only the incremental data is taken from the newer files added ? 2) Once my power query is added, can I edit the table to add columns, edit cells and save the file? Next time the file refreshes, will it overwrite my edited information ?
This is very helpful. When I combine data from multiple files (all files have same column headers), the rows of column headers from each file are showing up in the combined worksheet. How to remove those rows of column headers. If I promote the first record as column header, the record from the first file only is promoted, still the first rows (column headers) from other files are still showing up. I am still trying to fix this. Can you please help? Thanks
Thanks you so much for this video, it helped me, can you can make a video, how to combine specific data from multiples files, e.g. i want to combine only state specific sate data.
Thanks. I have one question. I have some 90 files from plant database which contains more than 10 K Tags in which some parameter are incorrect in each files that can only found by analyzing in database generated by power query. It is very difficult to go to every file location one by one and correct the mistakes. Can you help me by adding hyperlink for data points so that files that have mistake can be open by one click rather going each folder. Thanks
Sir , Please help me if we File saved as .html type then which M code need to used
Will it work if we have one common column in both excel files, and merge according to the common column?
Nice video as usually, but do you have Workbook to follow?
Thank you for the lesson. I got an error message while applying the formula shown in this video at 11:34 minutes saying: "Expression.Error: A cyclic reference was encountered during evaluation." Please let me know how to solve this. Thanks!
Excelent explanation, but what if one to combine 2 o 3 excel files (each of them has 3 or more sheets). How i can i join these 3 files into one. What am i looking for is each excel file will be given to an specific person, so i want to consolidate those files into one file keeping the sheets separated. Thanks!!
Very good video
Thank you 💚
can just tell how to export the merged two data frame data in to excel file
How Can we compile .xlsb (binary) files by power query.....thanks in advance...
Thank you for your tutorial. In my case, I The import from folder doesn't appear. Just from Access, From Web, From text, And in other sources, appears other sources but not from folder.
How can I import multiple xlsx files to one combined sheet? with some specific columns?
You might get the "From Folder" option in the "New Query" drop down in the Data Tab
It's a really good information. But I can't because of security system at work.
Can we make it into a VBA?
How to do the same thing but the location of files is on sharepoint various folders?
if have any chance downloading this link for practice
What if we want data from column no. 8 of every workbook and combine?
Great video! It works perfectly!! I have one issue I only have one problem and is that one column "Plant" can be called sometimes "Plnt" is it possible to modify before we expand Custom1? I tried with replacing on the list name but I still get empty rows. Thanks in advance!
You'll have to merge the columns (Plant and Plnt) before you load the data. Please see this video.
th-cam.com/video/kS312KyXtSA/w-d-xo.html
Beatifull😀
Thank you 💚
Can you pls help in this case me - having binary file with contain multiple table having inconsistent header. We have map them with the required column header name and then consolidate the data. How we could achieve this. I tried but i got an error while giving the updated column name the duplicate names not allowing (e.g old multiple table column have another name and we have to single column only
Sir how to do same task in power bi, because whole process shown accurate in query window,
When we close & load whole data shown null even header also shown null
This was extremely helpful, thank you! I have one question though! There are many hidden rows and columns in my Excel files. How can I prevent them from appearing in the combined list? I'm a total beginner with this method and would greatly appreciate your help 🙈
If there is a way to identify the rows and columns which are hidden, It can done with some more powerquery code. I'm afraid, I can't explain it unless I see some example!
@@GoodlyChandeep Sir do you provide training
Very good video
Sir I have perform your steps and all goes well
How can activate Intellisence in Excel power query (I am using Excel 2010)
how to address error relating to filterdatabase while fetching data from current excel workbook
The two errors mean
- A column named "Name" is already there in your data, for the moment you can rename the Name column in the back-end excel file.
- Intellisense can be activated by updating your excel.
Hope this helps.
Thanks you for the knowledge sharing.
If you don,t mind, would you please share method on how to combine selected sheet from our workbooks.
In my case, I just want to combine first sheet of the 29 excel workbooks.
Thanks
watch this video - th-cam.com/video/_jegiQkyC3s/w-d-xo.html
Great explanation 👍.. is there a way to do the same thing in VBA programming for dynamic headers
I am sure there would be a way to achieve that using VBA too... but I haven't tried it.
@@GoodlyChandeep if you want will share the code with you can you please help me
Again I thought this would help but it still keep on adding none existing columns like column1, column2 etc uptill 100 and something, after the steps of "extracting new rows" please how do I fix it thanks
GOOD ONE SIR
VERY VERY IMPRESSIVE VIDEO SIR😈😈😈
Glad you liked it Brother!
This is amazing! How I can contact you directly with a question on a master file I’m trying to build?
goodly.wordpress@gmail.com
Hello Chandeep,
Your training videos are awesome. I have problem in power query while combining multiple excel files. I have 3 types of files here.
1. Files that have 1 row heading for each column
2. Files with 2 rows for headings in which one of the rows is with merged columns and below that row second heading, 1 heading per column. Having some trouble.
I am grateful to you for the help here.
Regards,
George
You'll first need to unpivot data for the files with 2 headers and then combine them
See this - th-cam.com/video/GIBaVc01GH4/w-d-xo.html
And this - th-cam.com/video/O2d5Ec10E1E/w-d-xo.html
Hello Chandeep
Let me try this
Thanks for your quick response.
Hi how can I promote all the rows of headers if I have 3-4 rows as headers ?
See this please - www.goodly.co.in/unpivot-data-with-2-headers/
What can I do to combine CSV/xls files by column and not by row? I want more than 300 files combined side by side. I would appreciate it if you could answer. Thanks!
extract the headers of all the files using Table.ColumnNames()
and combine those!
The issue that I found, the headers are getting repeated.... for every table I extract from individual file.
In My Excel power query formulas don't pop up. (Like a preview) please help
I have 14 excel files with multiple sheets, what will be the recommended option to combine and create a pivot table for a summary of all sheets..? 2)following the step of this video, while copy the path of the folder,I don't get the option transform.Rathet I have option :combine,edit and load..where to see the transform option in excel 2016?
Click on Edit (i.e Transform)
What about password protected files? Can it be merged with an existing masterfile using this? It saya corrupted data when I merge files or combibe files.
Please help! Thanks!
I am sorry it won't work with Password protected files
i want to merge multiple excel containing multiple sheets with data like sheet1, sheet2 etc to once existing excel with same sheets accordingly. is it possible
you;ll have to load all the data into power query and then append them
Seems no one has tried to do it practically except playitright member, one who has done facing the issue at last step "Name already exist in the record ", which you'vent addressed
Hi, I have multiple files with single sheet in it. File name would be A, B, C, D, E, F, G, H, I.
A, B and C should combine in sheet 1, D, E and F in sheet 2 and G, H and I should combine in sheet 3. Please guide me on this.
Awaiting for your reply
How to upload all excel files in one file (all files in sheet)??
How to finish this guide with CSV data? I have changed Excel.workbook with CSV.document (03:30), then I have used as same steps as You. The final step is to expand the table (05:20), but in case of CSV source, the data are not in columns, but in one column, delimited with semicolon. The expand button (two arrows in column header) says then "No columns found". I guess, there must be the step of import wizard by first example (the helper queries) in case of CSV, isn' it?
Did you use Csv.Document to extract the data from the CSV?
Ideally it should automatically parse the CSV apart
@@GoodlyChandeep I have just replaced excel.workbook with csv.document in the "create custom column" step at 03:30. After that, no helper queries with parsing CSV were created.
let
Source = Folder.Files("O:\MyFolder"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "strety") and [Extension] = ".txt"),
Custom = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),
#"Custom2" = Table.AddColumn(Custom, "Data", each Table.PromoteHeaders([Custom])),
#"Removed other columns" = Table.SelectColumns(#"Custom2",{"Name", "Data"})
in
#"Removed other columns"
Maybe a tip for your next video - combine multiple CSV files with different structure ;-)
Am looking for a solution: my files have huge data
1. I have 1 excel file with multiple sheets using a English headers + a 2nd file having an Italian header..inconsistent details
2. How can I compile both data sheets keeping a common header (preferable using the English one)?
3.In total 8 sheets..data to apend
While working on csv file, when i did custom column and click on table to see the sheets in that workbook, i cant see sheet name instead it shows the complete file.
Plz help me out with this.
Plz reply ASAP.
I am sorry I haven't understood your question.
Can you send me some screenshots and describe your problem - goodly.wordpress@gmail.com
Thanks
Tanuja, A CSV file will only have a single sheet. In your case just Expand the CSV and you'll have your data combined
Hope this helps
Thanks a lot, when I try to add custom column I get this error "Expression.Error: We cannot apply field access to the type Function.
Details:
Value=[Function]
Key=Content" Can you please advise? I am following the same steps I have worksheets with different names
Send me your query
@@GoodlyChandeep I get the same error is there a solution ?
@ahmed did you get it sorted ?
@@leighbrett well it was my fault , rather than typing "Excel.Workbook([Content])" I pulled from the "Available columns" on the right , choosing to insert "Content"... i think making sure to type it manually will resolve it
You are an awesome teacher, thank you for this. I got it all figured, except, I just realized my file 1 has 10 "disclaimer" (useless data/random text) rows before the "header row" that needs to be promoted, and my file 2 has 2 disclaimer rows before the row that I can use as header.
Is it possible to tell Power Query "File1.Sheet1.Remove Top 10 rows, then Table.PromoteHeaders([Data])", "File1.Sheet2.Remote top 10 rows, Then Table.PromoteHeaders([Data])", "File2.Sheet1.Remove top 2 rows, then Promote Headers" and so on ..
I have total 5 excel files. Only File 1 has 3 sheets that I need to do this, other 4 files have only 1 sheet that I will use.
What kind of code can I write in advance editor that does this for me right before 5:20?
*correction typo
Write this formula instead - Table.PromoteHeaders(Table.Skip([Data],10))
The number of rows that you skip/ remove should be the same. Else we'll have to use more of M Code to get this resolved.
@@GoodlyChandeep Ah, so I can't specify the worksheet to select and then apply PromoteHeaders(Table.Skip([Data],10)? I know I'm mixing things up, but can't we do Select Table1.Sheet1, then Table.PromoteHeaders(Table.Skip([Data],10)), and repeat this for each Worksheet with different Skip numbers? Or is there a way to write something maybe combine it with VB script and strip the first 10 rows from File 1 and first 2 rows from File 2, then begin the Power Query? I hope I am making sense. :(
Everything worked perfect following your instructions when I manually removed rows from source files before started working with Power Query, but the file generated tomorrow will have those 10 and 2 useless rows again. :(
Hi Chandeep, any suggestions?
How can I add items to the rows from one file to the other. Example. if two files have the data on the same date but different values or/and columns. Can you PM me. it would greatly help
Hi Peter. Power Query won't be able to make a change to your underlying dataset.
Although you can pick up both data sets and can combine them in which ever way you'd like using Power Query.
I'll be able to give you more direction if I see your data. You can write to me at goodly.wordpress@gmail.com
how can we remove sheets which are blank? please help
The easiest way is to filter them out.
At the last stepI got error The Field 'name' already exist in the record, anyone relates? and how did you solve it? sos. Thanks!
Hi there, I've tried too & facing same issue, why ain't you address this issue..
I have two row headers.. any fix?
You'll first have to unpivot the data and then combine them - th-cam.com/video/GIBaVc01GH4/w-d-xo.html
th-cam.com/video/O2d5Ec10E1E/w-d-xo.html
Thanks for the reply... I have already gone through with that video... Infact most of my queries have resolved by looking at your data.. but in this case I want to have the same feel(consolidation file to be) as we have for multiple files.. as soon as employee enters data manger can have same feel and same view accordingly he can filter the data to view in each resource data. I appreciate your efforts and time. Thank You in Advance.
Hi Chandeep, I found some errors while coding
What errors precisely ?
Amazing! Thank you very much!!
Glad you like it!