Forum Discussion

leonghio's avatar
leonghio
Frequent Visitor
8 years ago

Load multiple sheets from multiple excel files

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 locatio...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

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's avatar
    leonghio
    Frequent Visitor
    8 years ago

    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.