Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Importing excel file with multiple sheets from Sharepoing

 

Hello everyone,

 

Senario: There is an excel file in sharepoint contanting some test results. Everyday test data gets changed and a new file is saved in the sharepoint folder with the date. (File structure is same only data would be changed)

 

I am having two challenges.

1) How to import the new file in the Power BI keeping all the sheets in tact so that I can see all the tables in the data modeling view.

2) How to automate this new file load process so that every the user will get the latest visuals.

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , Anonymous 

    I know it's stupid but it works (I'm actually not good at this issue)

    Here is the method I try if I understand your question correctly:

    step1. copy the query first

     

    step2. click the buttom "combine  files" in query 1

     

    （If there is an error, please ignore it,  click "close & apply" to close "Edit Queries" and enter again.)

     

    It will show as below:

     

    step3.Do the similar steps as the step2 ( click the buttom "combine  files" in query 2)

     

    it will show as below:

     

     

     

    Hope others can come up with better suggestions.

     

    Best Regards,
    Community Support Team _ Eason

9 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago
    Hi Anonymous,

    If your files are saved on a sharepoint folder you can use the sharepoint folder option to get all the files.

    This will create a custom function that allow the replication of the treatment of the information in all files in the same way.
    You need to readjust the treatment for getting also the different sheets but it's doable.

    Regarding the last question when you publish the file then you can turn on the refresh options to get the data from every day's and hours you need.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Migel,

       

      I was able to upload the file using sharefolder connection but when I click the combine action button then it shows two sheets but lets me selece only one. If I select the parameter folder then it gives me the metadata only.

       

      Are you suggesting me to use incremental refresh option ?

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you for such a wonderful idea. I wonder why Microsoft does not provide with an option to do so 

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi , Anonymous 

      Could you please tell me whether your problem has been solved?
      If it is,  please mark the helpful replies or add your reply as Answered to close this thread.

       

      Best Regards,
      Community Support Team _ Eason
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.