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 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.

 

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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'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.