Loading excel files with folder

[Ludvig]

Hi everyone! 

I have been searching for a solution to my problem but havent found one and I figured that someone here might help.

Im trying to load several excels files with several sheets into power BI from a folder. When i do that all sheets and files becomes one huge table. I want to split it into more tables, and some of the excel files should be merged togheter but only with some of the sheets from the files. The folder with the excel files will be filled with more new files every month. To clarify the problem a bit more:

The map with the excel files will be updatated with 10 new excel files every month. I want to merge the number 1 file from last month with this month file number 1 and so forth, and I want to combine sheet 1 with sheet 1, sheet 2 with sheet 2 and so forth for the files that is being merged. So, every file will be it's own table i.e., 10 tables that is being added data to each month

I have tried to do this in the Power query but i cannot come around that it creates one big table, maybe their is a better approach to this but i should be possible.

Im new to Power BI and Power Query (=

[v-jingzhang · Accepted solution]

Hi @Ludvig 

When you first connect to the folder, select "Transform data" instead of "Combine". This will bring you to the Source step, which shows all files in the folder. Then filter rows based on the "Name" column. You can use Text filters like Begins with/Ends with/Contains/... to filter by file names. For example, if you want to combine all files whose names start with File1, you can select Begins with "File1". 

After filtering the files to be combined, click the Combine Files icon on Content column. In the Combine Files window, select a sheet to be combined then click "OK". Then Power Query will combine data from the same sheet of all filtered files. 

Then you need to repeat the same operations for other sheets and other files. You can start from connecting to the same folder again then filter files. 

Before combining, you have to ensure that all sheet and file names are following a consistent format that can be recognized easily. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Ludvig]

Thank you for the answer! This really helped!

I managed to solve it also by having different folders for different files catagories, but with your way i can use same folders. Thank you very much! (=