Power BI: How to Combine Multiple Excel Files from a Folder

Bonus Video part of www.LearnPowerBI.com training program.
In this video I explain how you can use Power BI to combine data from multiple excel files. I also show, for complex scenarios, how you can modify the "Sample File" that Power BI Query automatically creates, in order to update the "Custom Function" that is automatically created.

===Most Popular Playlists===

⚡Power On!⚡
-Avi Singh

[skxnick]

Perfect instructions! That helped a lot!

[alpeshrt]

Amazing video ; so thankful ; replaced my earlier version of VBA codes with this ; Thank you Avi

[EFox]

Just discovered Avi's videos and program...wow!!!
For this one...was wondering if theres a way to easily combine the tabs across multiple files. Meaning if I have 3 files in a folder, each with 5 tabs, do I need to go through the 'Get data, Combine Files' process 5 times, and do the transformations, for every tab?

[michaelgearhardt]

Hey avi, nice video. could you please explain the reasoning behind unpivoting the data?

[richcarling]

Can you do multiple sheets in one go? My excel export has 4 sheets, would want to merge each of the 4 sheets in to a separate table if possible. Ideally, add a custom column based on one of the other sheets first

[zaafirc]

Awesome video.
Is is possible to combine/append new data to old historical data without refreshing the old data?

Normally when combining data from a folder, it will load everything in the folder when refreshing. Is there a way to load only a new file and append it to the old data?

[aleksandrsmeilers]

Avi, I'm new to this and really struggling. I have 50 similar excel files. For example first column is the same in all files and has 15 lines, it's a list of subjects to be cheeked. Second column is the score and result of check - score from 1 to 5. So I need to combine all this files in a way that first column is the same but all others are marks - then I can see the best and worst subjects. What I get is all data is lined up row by row and every time 15 subjects are repeated same amount of times as number of files I combine - is there any way to help me?

[masgitz]

Hi, great content thank you. I'm importing excel workbooks from SharePoint folder. How can I skip any open workbooks during import

[chrisbixler]

Hi Avi, Love your work! I am placing many excel files into a source file and combining them in Power Query Editor. I've been adding additional excel files to that same source file, that follow the same structure as the original excel files. My issue is that after I refresh Power Query Editor, the data from the newly added excel files does not appear. Do you know what I would need to do, in order to do a refresh and have PowerQuery Editor show everything from my source file regardless of when I added it?

[dippratimdan]

can you please share the video regarding many to many table relationship using DAX

[neerajkapoor]

It was of help for unstructured Excel worksheet combining

[yoavlubo]

Thanks - Very helpful!
Any ideas how can I bring the source file modified date to the dataset?

[surajkale]

Heyy.. Thanks for this learning, I have a doubt can we add 2 URL links in a single Excel cell and can we have 2 different links to open in the Power BI table. If you provide me solution for this it will be better for me. Thank you so much

[pingchen]

hi Avi, would you pls. help to guide on how to reactive the link of the sample query in custom function?
i encountered some issue that as i modified the sample query that somehow won't apply to the output table.

thanks
ping

[bandishgupta]

Thanks for the informative video! Though I have a query: While combining Excel files from a folder, I too changed the datatype of a date field from text to Date in the 'Transform Sample File' and removed the 'Changed Type' from 'APPLIED STEPS' of the final combined output query. But the data type change was not applied to the final data model in Power BI after applying. What could be the reason?

[m.j.]

Should the sheet name be different for excel files? I get error for excel files with different sheet names. However, that works with the csv files!

[lilyflowyyy]

hi, does it mean the excel spreadsheet name must be same in order to load into powerbi? what if we have different excel spreedsheet name?

[deesterdee]

What do we do if some of the Excel files have one or more extra columns but the rest if the columns are the same? I created a data model but because some of the columns don't match up, I got errors in Power BI.

[normatecuatl]

Hello, this was useful but I have the scenario where I will receive multiple excel reports every month. I would like to know if there is a way to refresh the data set with new data or do I have to follow the same procedure every month?

[powerbi]

Hi,
i am trying to combine three excel sheet, one sheet contains header and other sheet does not contains header details, I have selected example file as first sheet, i am getting data from only from first sheet not from other sheet.
can you pls guide me on this ?
or is it possible to combine that one sheet with header and other sheet without header ?