Combine Files from a Folder with Power Query the RIGHT WAY!

[@thatJustinUknow]

Love how you don't mince words, and keep it clear by comparing and contrasting, and telling WHY to do a thing as opposed just explaining WHAT to do, thanks!!

[@MyOnlineTrainingHub]

I appreciate that! 🙏😊

[@mobe7]

Great video, I always overlooked the sample file. Now I know it's worth. I also loved your example with transposing the column headers - I am sure I will be using that sometime in the future!

[@MyOnlineTrainingHub]

Great to hear!

[@StephenZipprich]

Rather than manually refreshing, go to Data Ribbon > Queries > Properties and set it to refresh whenever the file opens. This ensures you dont have to remember to refresh the data, and potentially have an error in your output.

[@MyOnlineTrainingHub]

Yes, great idea. 👍

[@shivankurchahar7368]

Where do i get this option? I am not able to find Queries & Properties option. Can you please elaborate?

[@nazarkamal8831]

Before combining do it in sample / After combining do it in either sample query or in final query !!! Well said this is what i want !! Searched in so many channels regarding this difference finally got from yours channel 👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏 thanks 👍

[@MyOnlineTrainingHub]

Glad I could help. This is a fundamental point many people don’t realise.

[@mcegirl4]

I'm still trying to get a handle on PQ, and you are helping immensely! I had a couple of 'light bulb' moments watching this video - thank you!!

[@MyOnlineTrainingHub]

That’s great to hear!

[@williamarthur4801]

Loved the way of promoting two rows as a header I've always ended up isolating the first two rows , Table to columns, Table column names , zipping and renaming. But thanks for the whole thing, I still find brining in files form a folder with different names and structure very confusing and there's a lot of trial and error.

[@MyOnlineTrainingHub]

Glad you liked it 🙏 different column names is a tricky one because there are many scenarios, so it's difficult to have a one size fits all approach.

[@emmanuelle8032]

Excellent ! Very clear explanations as always. Just used them and 💥... my work has been produced in half the time I'd previously planned. Thanks you very much !

[@MyOnlineTrainingHub]

Wow! That's awesome to hear 👏

[@AchmadHilmanS]

I just wanted to say a huge thank you for all the amazing tutorials you've shared! Your tips and tricks have completely transformed the way I use Excel. Every video is packed with useful information, and your clear, step-by-step instructions make everything so easy to understand. I've learned so much from you and now feel much more confident with my Excel skills. Keep up the fantastic work!

[@MyOnlineTrainingHub]

Thank you so much! 🥰 I'm so pleased I can help. Keep learning and practicing 💪

[@ivanbork4175]

Hi Mynda You truly are a genius, I get a lot out of watching your uploads. Use PQ almost every day, and never stumble over this, but instead use filtering out and renaming columns. So grateful, thanks

[@MyOnlineTrainingHub]

So wonderful to hear 🙏

[@dmytrofryashchikov4847]

Hi Mynda, Your channel is the best Excel-related resource I've ever seen in my life. Keep up!

[@MyOnlineTrainingHub]

Wow! Thanks so much for your kind words. Please share it with your friends and co-workers.

[@Lone_Wolf_2000]

Words cannot BEGIN to describe how grateful I am for this! 😍😍🙏🙏

[@MyOnlineTrainingHub]

So glad I could help!

[@josephdelvecchio235]

Thank you. Yours is the only video I was able to find that explained the need to apply transformations to the sample file to cause them to be applied to all files in the specified folder before combining the data. My issue is that my weekly CSV source data is formatted is such a way that there are data in multiple discontiguous tables arranged vertically throughout the file which need to be collected and then combined into one table. The only way I've been able to accomplish this is creating multiple duplicates of the original sample file, each of which take different data from different areas of the source file. I use the original sample file to get the first set of necessary data, the first duplicate to get the next set, and so forth. The resulting data sets from each query do not have matching columns but do need to be put together to create a complete table. So I insert an index column in each query's data set and then merge them using the original sample file. However, the final query table only shows the first set of data from the original sample file for each respective source file. All other data for each of the duplicate sample files is just repeated down the column for every respective source file. I imagine this has something to do with the fact that only the original sample file is respected in this manner, but I don't know if/how I can fix this or if I'm taking the wrong approach to this altogether. Any help would be appreciated.

[@MyOnlineTrainingHub]

Any transformation steps applied in the sample query are applied to ALL files before appending them. However, if your sample file has different column names to the other files, then the other files may not receive all transformations because Power Query will be looking for column names that don't exist in those files. If you have different column names, you might find this tutorial helpful: www.myonlinetraininghub.com/combine-files-with-different-column-names-in-power-query

[@davidm2419]

This is very good explained and important. In the real world you almost NEVER get clean data, no matter the source. Transforming the sample file is such an important step. I had to learn it the hard way.

[@MyOnlineTrainingHub]

Thank you! Glad it was helpful.

[@ersofrescht4661]

Thanks! This was useful. Your tip on naming the sheets exactly the same helped me resolve the error "[Expression.Error] The key didn't match any rows in the table." I kept getting in loading excel files.

[@MyOnlineTrainingHub]

Glad it helped! 😊

[@joncue0304]

I have always used databases instead of spreadsheets. Unfortunately I don't have access to a database server, so I have to keep it on my local machine, which does no one but me any good. I'm looking into putting some of the data I have into Excel instead (even though I personally consider this going backwards) so that other people have access to the dashboard and so the files are properly backed up. I greatly appreciate your videos, they are really helpful. I am very fortunate in that queries already make sense to me since I use them all the time in the applications I've created using a database, but I have found that there are actually some things that Excel handles better. I was surprised, shocked actually, at just how functional Excel can be. It can come close to the abilities of a real database. Thank you for your videos, they are appreciated.

[@MyOnlineTrainingHub]

Awesome to hear! I guess you could always use Access if you really wanted/needed a database. You can then connect to Access via Power Query to get the data into Excel for your reports.

[@joncue0304]

@@MyOnlineTrainingHub Have done that some, I'll probably start doing it again.

[@azmatmalikLTU]

The best thing i notice besides the video is that you responded each and every comment. ❤

[@MyOnlineTrainingHub]

Always! 😊Thanks for watching.

[@elizabethantony9236]

Excellent and to the point. thank you!

[@MyOnlineTrainingHub]

Thanks for watching!

[@saumilparadkar8636]

I use PQ at my workplace and end up doing many things manually... This video was extremely insightful... Thanks Mynda

[@MyOnlineTrainingHub]

Great to hear!

[@Fxingenieria]

Excelent video, can we get the files to practice? I didn't found them in the article. Thanks.

[@MyOnlineTrainingHub]

It's there now: www.myonlinetraininghub.com/power-query-get-files-from-a-folder

[@reginaldarbruthnot1766]

absolutely outstanding video - superbly presented - detailed yet simple and clear and easy to follow - outstanding! Thank you so much.

[@MyOnlineTrainingHub]

Glad it was helpful! 🙏😊

[@reginaldarbruthnot1766]

Outstanding - solved a real problem I was facing in terms of pulling data into excel and added immense value to me - cannot thank enough!@@MyOnlineTrainingHub

[@peterburke5720]

A great video... would be good if you could add the step to externalise the folder location.. ie have the location in a worksheet cell rather than coded within the power query.

[@MyOnlineTrainingHub]

Thanks! I cover that in my Power Query course: www.myonlinetraininghub.com/excel-power-query-course

[@IvanCortinas_ES]

Great video Mynda. As always a clear explanation!

[@MyOnlineTrainingHub]

Thank you!

[@KathleenChartier]

Another great video on how to make use of the great functionality in Excel!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@yuhooh]

Great for share me the tips , edit Transform sample files query if we want to clean and transform before combine.

[@MyOnlineTrainingHub]

Awesome to hear!

[@notesfromleisa-land]

Great video. I would add that you can combine pdfs as well from folder. I did this for point of sale reports in order to automate a journal entry go uploading. Dropped all weekly sales for each store into a discrete weekly folder. Grabbed the data from the folder. Repoint each week to current weekly folder. Power query has allowed me to save my clients time and money while improving accuracy.

[@MyOnlineTrainingHub]

Nice!

[@talkinghat88]

The transpose trick is very useful to say the least. Thank you!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@amanzurusaviour6021]

Awesome, your are the best teacher and I can't wait to enrolled for your paid sessions.

[@MyOnlineTrainingHub]

Awesome, thank you! I look forward to teaching you more of the amazing things Power Query can do 😊

[@k0023382]

Thanks for this, very helpful. To add a new scenario to the mix, each workbook containes 12 tabs (one each month) and there are 4 years (ie 4 workbooks), the end result, the same, one big table with everything on it. How do you go about this? Thanks in advance.

[@MyOnlineTrainingHub]

Here is a tutorial on getting multiple files containing multiple sheets with Power Query: www.myonlinetraininghub.com/import-multiple-files-containing-multiple-sheets-with-power-query

[@ITSzkolenia]

Great stuff. It would be great if you could also show how to manage data from folders which are availiable through sharepoint. There might be some authorization errors occurring.

[@MyOnlineTrainingHub]

This video covers getting data from SharePoint: kzbin.info/www/bejne/qJS8g5apec-Dg6s

[@computerdaddymultimedia281]

Love your teaching style. When you are merging sources to update main sheet, is there a way to prevent "incoming" duplicates rows from overwriting what I already have? My goal is to be able to update an excel sheet with sources of data that sometimes have additional headers or not in the same order.

[@MyOnlineTrainingHub]

It’s not ideal to modify the table output of a query for this reason. It would depend on what you’re changing as to the best approach. You’re welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@Mahesh717]

What in case if we have different no of columns & data in different files !

[@MyOnlineTrainingHub]

Ideally the files should have the same structure. However, as long as the first file/sample file has all possible columns, it will work.

[@Lyle-In-NO]

Absolutely awesome! Thank you for sharing this.

[@MyOnlineTrainingHub]

Our pleasure!

[@TVSCDN]

Thanks a lot Madam 🎉🎉🎉...could you also do an example of combine or merge pdf bank statements??? please... thanks 😊

[@MyOnlineTrainingHub]

Glad it was helpful. Every bank statement is different. If they're in CSV format, you can use these techniques. If they're in PDF format, check out this video: kzbin.info/www/bejne/jpzIqGV9p72ob8U

[@GaryMillerUK]

Very useful video, thanks. But, I need to import from files in different subfolders (one spreadsheet in each), and when I do this, I don't get the Source.Name option. Is there a way I can get the filenames in the resultant data table, so I know which rows came from which file?

[@NicolasBORDE]

I had no idea we can make transformations on the sample file.... Will check the improveness on load time on a huge report.

[@MyOnlineTrainingHub]

Hope it helps.

[@mdukaruppu]

Thanks for your input. I have pasted close to 400 lst files in a folder and used the power query. The power query is only showing the file name and not the data into it. However If I extract one indvidual file. I am able extract.

[@MyOnlineTrainingHub]

Not sure what a lst file is, but you're welcome to post your question on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@Beemerbmr]

Thank you for this awesome video. If I wanted to add columns in the middle of the data set for each of the source files, how would I reflect this update in the query?

[@MyOnlineTrainingHub]

You can add the columns and then rearrange them to the middle as required.

[@Beemerbmr]

@@MyOnlineTrainingHub when I refresh the data source, the new columns aren’t available to be added though

[@MyOnlineTrainingHub]

Hard to say without seeing your query and understanding the data source. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@ksperder4035]

Thank you very much for this content. I wonder if there is some kind of limit in how many files can Power BI can fetch and consolidate from a folder. I may have a scenario where the client uploads like 40 different files a day to be used in a report.

[@MyOnlineTrainingHub]

I'm not aware of a specific limit, but you might find the queries become very slow.

[@ksperder4035]

@@MyOnlineTrainingHub Thanks! Happy new Year!

[@aikimark1955]

This might be an example I use when promoting ETLT in the "ETL vs ELT" debate. Thanks

[@MyOnlineTrainingHub]

😊ETLT! Yes.

[@brunomelki4512]

Your new look is a winning one and the content as usual outstanding!

[@MyOnlineTrainingHub]

Thanks so much 😊

[@enrique7estrada]

Great info thanks for sharing! What if I have a new file being added to the folder but each new file includes the older info? is there a way to just add the new information? without having to manually remove the older file from the folder? Otherwise, my data keeps accumulating

[@MyOnlineTrainingHub]

You could add a filter in the query to ignore data today's date minus n days old.

[@enrique7estrada]

@@MyOnlineTrainingHub Thanks so much for the suggestion! Any resource where I can see an example?

[@MyOnlineTrainingHub]

No, but if you get stuck you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@enrique7estrada]

@@MyOnlineTrainingHub I tried signing up but i'm getting an error saying my username is not registered

[@MyOnlineTrainingHub]

Sounds like you tried to login, because upon signing up you choose your own username. Please try again, and reach out via email if you still have trouble: website at MyOnlineTrainingHub.com

[@traciesmobile683]

Power query is game changing I use it for document control and working out what is held on relevant company systems so it can actually be found. I do like how if data is not in a table in the source due to being in a form with merged cells, you can run a few queries with results next to each other which create a bigger table that all works together.

[@MyOnlineTrainingHub]

So pleased to hear you're making use of Power Query 😊

[@shoppersdream]

Very Nice, thanks! On MacBook Pro, I don't get the option to choose "From Folder". Thanks

[@MyOnlineTrainingHub]

Thank you! Power Query on Mac is still under development.

[@shoppersdream]

@@MyOnlineTrainingHub Thank You!

[@chrism9037]

Another great video, thanks Mynda!

[@MyOnlineTrainingHub]

Cheers, Chris 🙏

[@kapil4757]

Very informative and practical. Kudos to you. Would you mind sharing the data files, thanks in advance.

[@MyOnlineTrainingHub]

The file download link is in the video description 😉

[@kapil4757]

I am afraid,but file download link is not there in the description. Please have a look again, thanks

[@ericnovotny7381]

Great video! What if the datasheets within the folder are cumulative rather than containing exclusive data? In other words, the datasheets in the folder each have the same data but the newest one has added the latest rows. Is there a different process for this?

[@MyOnlineTrainingHub]

In that case, I would only get that one file, rather than all of them. You can simplify the process and use the Get data > From Excel File connector.

[@arifakanwal6928]

Dear Mynda, Thank you for all the great work..I have been following you on LinkedIn as well But I am in trouble..I want to develop a Training and Development Dashboard without Power BI etc..how can I? I am unable to find something easy and relevant

[@arifakanwal6928]

Even I face problem in updating a dashboard over and over again with Pivot tables

[@MyOnlineTrainingHub]

I don't have an example of a training and development dashboard, however in my Excel Dashboard course (www.myonlinetraininghub.com/excel-dashboard-course) I teach you the skills to enable you to build any dashboard. In terms of PivotTables updating, as long as they share the same source data/query data, when you refresh all (via the data tab) they will all update.

[@jorgelara3116]

Thanks for this video !! Help me a lot .

[@MyOnlineTrainingHub]

Glad to hear that!

[@rezaamini8580]

Hi and thanks for your useful video. after transformation I've got the error "The column '....' of the table wasn't found. and I don't know how to fix it?

[@MyOnlineTrainingHub]

That means in one of the steps it's looking for a column in your table with a specific name. You need to either remove the action that references the name, or edit your source file to rename the column that's incorrectly named.

[@raghwendrapandey6664]

It's really useful !

[@MyOnlineTrainingHub]

Glad to hear that!

[@cathrach9576]

In PowerBI, when combining from a Folder, is there a way to work with different tables stored in different sheets in an Excel Workbook. I seem to be able to do so when I load one Workbook, but I don't see the tables when I combine from the Folder. Thank you!

[@al3xj]

First question - how to get bank files sent from the bank periodically!? That would be so good as in Australia this data is never consistent from banks, but this is great Mynda thanks solves my need to collate all csv's in 1 automated place!

[@MyOnlineTrainingHub]

You’d have to ask your bank how you can get the data in a csv file.

[@RamKumar-vb4et]

Excellent video. A basic question: What could be examples of transformations before the files are combined as opposed to after the files are combined? I am not clear about this difference. Thanks much.

[@MyOnlineTrainingHub]

Thanks! I give an example in the video of transformations that need to be done in the sample file.

[@marjoriefrancis4560]

Thank you Mynda! I've made a few of those mistakes and this comes in handy for me. I really appreciate you.

[@MyOnlineTrainingHub]

Glad it was helpful 🙏

[@kwanhangcheung691]

Thx a lot. This is indeed a very video n explaination how its work.😊

[@MyOnlineTrainingHub]

Glad you liked it 🙏

[@damorgman]

Very nice! If they decide to add a new column to source system and all my new CSVs have a new column I want to include, do I just set that in the sample file? For some reason, just dropping them in, the new column wasn't identified. Hopefully there's another video you can point me too. :)

[@MyOnlineTrainingHub]

Go to the 'Transform Sample' query and edit the Source step in the formula bar, removing the argument for 'Columns =n,". Removing the hard keyed number of columns will allow the query to get all columns. Also, make sure the file with the new/extra columns is the sample file.

[@damorgman]

@@MyOnlineTrainingHub Your better than chatGPT!

[@mogarrett3045]

always love your tutorials....excellent

[@MyOnlineTrainingHub]

Thanks so much!

[@carolynneccles823]

Great tutorial. If I am using this to combine bank transaction files, is there a way to clean up the description column? The description includeds receipt numbers, dates and purchase type that aren't required and make the column too wide. I am currently using Text to Columns and delimiter it using the dash -, then I delete the additional columns

[@MyOnlineTrainingHub]

Thank you! Yes, you can use Power Query to split the text by delimiters and delete the columns, that way you only need to set this up once and then Power Query will apply it upon refresh to each new file you add to the folder.

[@shaunhoward3977]

Hi, I run Excel from Mac and it does not show the from Folder option. Is there a work around for Mac users? Thank you.

[@MyOnlineTrainingHub]

Not really. The workaround for Mac users is to install Parallels and then install Excel for Windows.

[@et1733]

Hi Mynda, thanks for sharing. My question is : If there's a change in the raw data columns, like additional column in between the existing column or additional columns added after the existing column, how to make the PQ continue to work?

[@MyOnlineTrainingHub]

You can edit the query to allow for the new columns. If it's an ongoing thing, then it's more complicated to automate this and not something I can cover in the comments here, sorry.

[@pedja2518]

Great content!

[@MyOnlineTrainingHub]

Thank you! 😊

[@jacka2210]

Hi Mynda, Very helpful video. Is it possible to do this if not all files are the same template? For example, I have an additional column of data in some sheets but not all sheets I am trying to run a query on. Thanks!

[@MyOnlineTrainingHub]

Yes. Make sure the file you choose as the sample contains all possible columns.

[@jacka2210]

⁠@@MyOnlineTrainingHub I suppose I phrased that incorrectly. I actually have an extra row in some tables in some sheets, but not all of them. Even when modifying the sample, I cannot get it to work with those sheets that do not have that additional row of data. Thank you so much for your prompt response

[@gabz1989a]

what happens if your excels are invoices and the data is in not a pretty pivot table spreadsheet but the information is in different columns and rows? how do you get it to pull the data in specific cells and label them with a name, is that possible?

[@MyOnlineTrainingHub]

Yes, it'll be possible. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@PeterB96]

Hi, can I do this, but each file has its own worksheet instead of combing all of the data on one worksheet?

[@MyOnlineTrainingHub]

You have to create a separate query for each sheet you want as an output. There's no automated way to create separate queries.

[@minetoobin9762]

Love this but on a mac. can't find "folder" option to select data. ugh. also no combine option probably only because it allows me to select one worksheet at a time.

[@MyOnlineTrainingHub]

Yeah, unfortunately the Mac version doesn’t have the full functionality.

[@ClaudeBalleux]

Very good one. Thank you.

[@MyOnlineTrainingHub]

Glad you liked it 🙏

[@Secure-pb6dn]

I am wondering if I am missing something. It only imports the first 255 rows of each file. Files with less than 255 rows, it pads with blanks lines to make up 255. How would I make it import every row and not add blank rows to make up 255 rows per file?

[@MyOnlineTrainingHub]

Power Query imports all data, but in the Power Query editor you only see a preview of the data so that the editor can remain agile as you perform transformations. Once you 'close & load' it will get all data from the source files according to the cleaning steps you perform i.e. if you filter out data in the query, then you won't get that data when you close & load.

[@Secure-pb6dn]

@@MyOnlineTrainingHub Thanks for responding. The issue appears to be with the xls files. I only get the behavior when I import them directly, but when I open, save and immediate close the files then whatever glitch they had is fixed and they import fine. I am now wondering if there is a workaround other than having to resave them, but this forum is probably not the place to work out issues like that.

[@MyOnlineTrainingHub]

It's best to convert the files to .xlsx

[@Secure-pb6dn]

@@MyOnlineTrainingHub Thanks. I will see about doing that.

[@kleinboertjie]

Thanks, that will definitely help

[@MyOnlineTrainingHub]

Great to hear!

[@sachinpatel2583]

Hi, I have transformed the data in transform file and now it is not getting refresh in main query. I have also checked the function sample query and it is linked to transform sample file. I have also observed that whenver I am updating steps in transform sample file, steps are not getting reflected file in function file.

[@Arachagel]

could the power query combine the protected password workbooks?

[@MyOnlineTrainingHub]

No. It can't get data from password protected workbooks.

[@alfredtomiamubiaya3330]

Many thanks,this is awesome.

[@MyOnlineTrainingHub]

Glad you liked it!

[@Bondoz007]

I'm hoping this works for SharePoint folders too?

[@lemlemwoldemariam8180]

Hi Mynda, I have added a new data to my folder and refreshed my power query in excel 365, but it isn't updating the new record. under "Applied steps" double clicked source & I can see its added to the source. But it isn't getting added to the final output of the power query table. Appreciate your feedback on this? I don't understand why refreshing isn't working. Thank you!

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@NevergiveupOnelife]

hi, I am trying to combine 100 workbooks, but some of the files the concerned sheet is not available, so i m hitting the wall again and again. please help

[@ledow119]

You're a Master!

[@MyOnlineTrainingHub]

Thanks so much!

[@googlegoogle-gg3dp]

I have 1 m record on which I want to apply group by to get max value of each category and then same value should reflect against each category in separate custom column.....I tried it but it will take almost 2 hours and eventually system hanged....any suggestions pls

[@MyOnlineTrainingHub]

Maybe load it to the data model and then do the grouping in a PivotTable instead.

[@googlegoogle-gg3dp]

@@MyOnlineTrainingHub...thanks and i will try and update you ....but is their any solution in power query

[@rameshpathyb4996]

Is that all the files name should be same for this , because I tired with one it correctly loaded when i try to get new file the error showing the file name is not found

[@MyOnlineTrainingHub]

No, the file names can't be the same. The table/sheet names in each file must be the same.

[@teoxengineer]

Mynda, if we have different header names and we have to combine all files with different headers? What will we do?

[@MyOnlineTrainingHub]

Great question. See this video: kzbin.info/www/bejne/qqGukKuhocd3nJI

[@kgadeberg]

I am right that this can only be done with local folders? i.e. not folders on SharePoint? I struggle with this, since the spreadsheet can then only be used by the person who created it. Or, as we do, you need to modify the 'Source'-step in all the queries. Any thoughts on this?

[@kgadeberg]

Oh and btw: Great video as always 🙂

[@MyOnlineTrainingHub]

Here is a tutorial on how to get files from a SharePoint folder: www.myonlinetraininghub.com/get-data-from-onedrive-or-sharepoint-with-power-query#folder

[@sergegagne874]

Just missing a super cape! :) Awesome as always!

[@MyOnlineTrainingHub]

😁 thanks so much!

[@agelee89]

When importing files from a folder, if we don't select "Expand the attribute column", then we totally loose those "attribute" data? The same for content, do we need to delete the "Content" column in PowerQuery if we don't want to see content of files? Or this is automatically removed when saving the steps and not selecting "Combine the Content" column?

[@MyOnlineTrainingHub]

If you want data from the Attribute column, then you need to expand this before expanding the Content column. That said, if you choose 'Combine & Transform', you don't get either of these steps because Power Query automatically expands the Content column for you.

[@tarek.grisha]

Thank you. I love you.

[@MyOnlineTrainingHub]

Glad my video was helpful.

[@learnitinstructor5792]

Excellent!

[@MyOnlineTrainingHub]

Thank you!

[@TheThatmatt254]

I have two files types in my folder and there is one common column that does not have the same name in both file types. How can I force alignment of the varied column headings? Sample file has "Receiving Country ", while the other type has "Receiving Country ​​"

[@MyOnlineTrainingHub]

You have to rename the column before the append of the tables.

[@brkc3153]

what if seeing “ “ symbols with numbers when load the dada. How to get ride of them?

[@MyOnlineTrainingHub]

Do a 'Replace' in Power Query that replaces " with nothing.

[@dispirted8]

I like this video. I have edited the sample file query before, and it is good to see it demonstrated. All that stuff that is generated on the left-hand Queries pane in PQ looks daunting. It is always good to know I can ignore most of it! Thinking about the section of the video at 7:37, if a transformation can be done before or after files are combined, my instinct is to put it after. I doubt it makes a noticeable performance difference, if any at all. But it seems like I am being helpful to PQ 😊, by making it do the transformation only once, rather doing it for each file in turn.

[@MyOnlineTrainingHub]

Glad it was helpful. Good point about the efficiency of performing transformations in the final query vs the sample file. I haven't got a dataset big enough to test it on to see if it has an impact. It would be unnoticeable on small datasets.

[@txreal2]

My Query with appended csv files from a folder have multiple rows of same column names (one from each file) What's the best way to keep only one row? Thanks. Subscriber :)

[@MyOnlineTrainingHub]

In the sample file query you can promote the first row as the header row. This will repeat the process for every file in the folder so they do not appear throughout the dataset once combined. You see this in the video.

[@txreal2]

@@MyOnlineTrainingHub Thanks!

[@Mr97Frog]

Does this method works for 3 different data sources in a single folder?

[@MyOnlineTrainingHub]

Yes, assuming those data sources are the same structure. It wouldn't make sense to append files that contain different types of data.

[@ramviru6112]

Can we use the same method to combine multiple JSON files from a folders? Pls let me know. Thanks in advance

[@MyOnlineTrainingHub]

Hmm, I haven't tried JSON files from a folder, so I'm not sure. It'll only take a few minutes to try it yourself and find out though.

[@ramviru6112]

@@MyOnlineTrainingHub Thank you very much. I tried in the same method and it worked out perfectly. Incase if the columns are not matching in some files within that folder then should we edit those manually?

[@sangeetsom82]

Wonderful !

[@MyOnlineTrainingHub]

Glad you like it! 🙏

[@ahmadalhaddad3081]

thanks a lot !!!!

[@MyOnlineTrainingHub]

You're welcome!

[@renielmulingbayan9989]

Hi! i tried doing this, howevery, I encountered a problem. once i expand the table, some rows have values missing for some of the expanded columns. It seems it detects duplicate rows and automatically removes the data in it.

[@MyOnlineTrainingHub]

The Power Query Editor window only displays a subset of the data. It's only when you 'Close & Load' that you get the complete dataset, so check that before assuming it's missing data. It won't automatically remove duplicates. You'd have to apply that as a step.

[@renielmulingbayan9989]

@@MyOnlineTrainingHub I already loaded it and the table still has values missing. It looks like the error occurs once I expand the tables. I can send you the files I have been working on to check it. Thank you.

[@fashihahahmad2346]

What happen if we combine live data? Example, we want to capture the student's height in 4 clases. Notice that there's new student register during the day which in class 1. Can the teacher update/add new line for the new student? And can the master data capture the new line item?

[@MyOnlineTrainingHub]

Yep. Just add the data to the source file and save it. Then refresh the query to pick it up.

[@waleedbinmajid5718]

DataFormat.Error: The input couldn't be recognized as a valid Excel document. Details: Binary I get this error everytime I try to do it

[@MyOnlineTrainingHub]

Hard to say without further information. Please post your question on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum Be sure to include information like what file type you're connecting to, where the files were generated e.g. 3rd party system etc. Some 3rd party systems do not correctly create Excel files despite them having a .xlsx file extension.

[@wizdeasis9846]

Thank you so much for this tips! SHould the number of rows too the same in all files? Or just the columns? Also, after uploading a new file on the same folder, and clicking all refresh button, the new data from the new file won't show. What could be the problem? Appreciate your response Mam. :)

[@MyOnlineTrainingHub]

Just the columns.

[@wizdeasis9846]

Thank you! Also, after uploading a new file on the same folder, and clicking all refresh button, the new data from the new file won't show. What could be the problem? Appreciate your response Mam. :) @@MyOnlineTrainingHub

[@priyeshsanghvi8424]

Please check the filtering in the first step.....maybe you have filtered out some file and the new file is also filtered out later in that step 🤔

[@wizdeasis9846]

thank you SIR! @@priyeshsanghvi8424

[@shubhabratadey]

Very useful video

[@MyOnlineTrainingHub]

Glad you think so!

[@nadermounir8228]

Thank u for this nice Video 📹

[@MyOnlineTrainingHub]

Glad you liked it 🙏

[@mohammeddapwan6226]

thanks alot

[@MyOnlineTrainingHub]

Happy to help 😊

[@kiasca3489]

I don't understand, is there a benefit in performance if I made the changes in the Transform file?

[@MyOnlineTrainingHub]

If you try to unpivot after you've appended all the files you'll end up with a mess. Try it with the sample files for this video (link in description) and you'll see the problem.

[@kiasca3489]

@@MyOnlineTrainingHub thanks, I always learn something new with your videos, I asked because a work around I do is just filter data "does not equal to" and removed those headers but good to know there's other option error free and I had no idea I could edit transform file🙂

[@JonathanExcels]

Good tip!

[@MyOnlineTrainingHub]

Cheers 🙏