Load multiple sheets from multiple excel files

[leonghio]

Hi,

I am new to M programming.

I am trying to load multiple sheets from multiple excel files into PowerBI but I couldn't get it to work.

I want to use a parameter to pass in a folder location.

In that folder location, there are mutiple excel workbooks. Each workbook is an entity, eg company A.

In each workbook, there are multiple sheets, eg revenue, expense, updates sheets etc. All the workbooks (entity) have the same no of sheets. Each sheet has different no of headers.

I would like to load all revenue into a single table, all the expense into a single table, all updates into single table.

How can I do this? Can't seem to figure this out.

Thanks in advance.

[Anonymous]

Hi

I know this thread is old, but i had the same issue: multiple Excel files with multiple sheets needed to be imported in PBI.

I found the solution via this article: https://www.howtoexcel.org/power-query/how-to-import-multiple-files-with-multiple-sheets-in-power-qu...

Cheers. G

[Anonymous]

Hey @Anonymous , I've attempted the solution from your link.  And it will only M code-work one file in the folder.  Leaving all the other as blank entries.

Have you encountered this issue? Think you could lend me a hand?

Particularly I have about 15 files, each with 3 tabs each.  I want to merge the files by tab (they are all named the same).

Thanks!

[Anonymous]

Hi @Anonymous 

Sorry for my late reply.

No. I did not encounter such difficulty.

But perhaps if you share your M code the community and I could help you to find a solution.

Best regards. G.

[v-yuta-msft]

Hi leonghio,

To achieve your requirement, please refer to steps below:

1.Put all your workbooks in a folder, then click Get Data->Folder-> Edit.

2.After step 1, click context column then click Combine Files like below:

3.Choose which type of sheet you want to combine to a single table, here I choose Sheet 1, it means sheet1 in all the workbooks will be combined to a single table:

4.The result is like below:

 

Reference:

https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries

Regards,

Jimmy Tao

[leonghio]

Hi Jimmy,

How do you set the folder location as a parameter?

ie, Under Manage Parameters, I create a dir parameter.

I want to pass this dir parameter in the query generated by Power BI.

But i will get the error: 

Formula.Firewall: Query 'OperationKPI' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

But if I hard-code the directory location in the generated query, it works.

How do I get folder location parameter to work?

Thanks.