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

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

I don't understand the interaction between the Sample query (which loads data one specific file) and the Combined query, which only loads names, file extensions, file paths, etc.??? I can't get my Excel to put the two together. :(

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

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

What if you want to include the name of the file in a column?

Very useful video

Absolutely awesome! Thank you for sharing this.

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?

I keep getting only the first file

Your videos are great and well explained. I have learned to use Power query watching your tutorials. I am stuck at an issue that is as below. Please suggest as what can be the solution - I have a large dataset (around 60 columns and 120k rows) in the source excel file. When I use power query to load data from folder, i am getting this error ---------

Unexpected error: External component has thrown an exception.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: External component has thrown an exception. ---> System.Runtime.InteropServices.SEHException: External component has thrown an exception. ---> System.Runtime.InteropServices.SEHException: External component has thrown an exception.
at System.Data.Common.UnsafeNativeMethods.ICommandText.Execute(IntPtr pUnkOuter, Guid& riid, tagDBPARAMS pDBParams, IntPtr& pcRowsAffected, Object& ppRowset)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Mashup.Engine1.Library.Excel.ExcelReaderAce.AceTableValue.EnsureInitialized()
at Microsoft.Mashup.Engine1.Library.Excel.ExcelReaderAce.AceTableValue.get_MetaValue()
at Microsoft.Mashup.Engine1.Runtime.Value.Microsoft.Mashup.Engine.Interface.IValue.get_MetaValue()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.GetMeta(IValue value)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WriteLimitedValue(IValue value)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass109_0.<WritePreviewFieldValue>b__0()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass100_2.<WritePreviewTable>b__2()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass100_1.<WritePreviewTable>b__1()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass100_0.<WritePreviewTable>b__0()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WritePreviewTable(IValue value)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass63_0.<WritePreviewValue>b__0()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WritePreviewValue(IValue value, Int32 serializeStructuredValueDepth)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.SerializePreviewValue(IEngine engine, IValue value, Action`2 rowCountCallback, Nullable`1 options)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.NotifyingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass0_0.<RunStub>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass17_0.<TryReportException>b__1()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass9_0.<OnBeginGetPreviewValueSource>b__0(EvaluationResult2`1 result)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_1`1.<OnBeginGetResult>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_0`1.<AddHandler>b__0(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_0`1.<AddHandler>b__0(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__1(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.BootstrapAppDomainManager.Execute(String[] argv)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.RemoteEvaluationContainerFactory.Container.OnException(IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_0`1.<AddHandler>b__0(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.NotifyingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.NotifyingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.NotifyingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.NotifyingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.NotifyingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.NotifyingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

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

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

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

It's really useful !

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

could the power query combine the protected password workbooks?

Why am I not seeing "from file" & "from folder" on Macbook?

It seems that this doesn't work on Excel 365 on a Mac. I imagine because the file system is completely different?

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.

Great video Mynda. As always a clear explanation!

COMO PUEDO DESCARGAR EL ARHIVO PARA PRATICAR

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.

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.

Thanks for this video !! Help me a lot .

always love your tutorials....excellent

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.

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!

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

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.

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

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

The fact that the sheets must have the same name is annoying.. some programs export data in sheets named by month ecc, it should be able to choose the first sheet...

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

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?

Great!!!!!!

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

Thanks for sharing.

Very good one. Thank you.

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.

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

How to make the number of files in the folder dynamic, so PQ always only grabs the most recent 5 files? I tried to sort the files by dates and keep top 5. But when I tested by adding the 6th file in the folder, the PQ loaded it. Thanks!

Brilliant! Thx!

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

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

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 :)

Many thanks,this is awesome.

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

👊