Get Data from Multiple Excel Files with multiples Sheet and separate each sheet by table

[twister8889]

Hi guys,

I need help to implement the load by multiples excel files from folder/SharePoint. I have a folder that contains a lot of excel file, all the excel files have the same structure (The same sheet quantity: sheet1, sheet2, sheet3, and sheet4; the same struct sheet, the same sheet name, but each sheet is different from each other). So I need to load theses excel files, after that, I need to create the columns by excel file name (because the excel name will be the filter into the dashboard). I would like to create tables into the model for each sheet, for example, one table to sheet1, sheet2, ...

Basically the problem is: How to load theses excel files into power bi and separate, to each sheet be the table and use the excel name as a column for each table?

Here are excel files: https://1drv.ms/u/s!AoDYwrtLrltJn1AmP9R-vWOWRP_w?e=3XqaKQ

[edhans]

You are going to have to do this 4 times. You can get all sheets in all excel files at once by clicking on the "parameters" folder once you click on combine. That will get all 4 sheets. But, your sheets are not in the same format, so any further transformations will have to be custom. Sheet 1 is the same in files 1 and 2, but Sheet 2 is not the same as sheet 1. So each pair needs its own transformation.

To get the sheet name, you need to make sure that the "Remove Other Columns1" step in the Combine operation retains the Source.Name column.

I do not understand your question though "... each sheet be the table and use the excel name as a column for each table." Each sheet as a table is easy in the combine operation. Do Sheet 1 first, then repeat the combine operation for 2, 3, and 4. But what do you mean use the Excel name as a column for each table exactly? If you mean have a column with the file name, then my direction above will do that.

If you mean something else, please clarify.

[Greg_Deckler · Accepted solution]

@twister8889 - I think you will need to have a Folder query for each of your sheets that you want as a table. @ImkeF or @edhans might know better.

[amitchandak]

@twister8889 , you should be able to load like

https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

and get new data

https://medium.com/@wkrzywiec/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4...

Now if need table for each table that would be like load one excel create the model and the get all other data appended.