Importing excel file with multiple sheets from Sharepoing

[Anonymous]

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.

[Anonymous]

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

[v-easonf-msft]

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.

[MFelix]

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]

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 ?

[MFelix]

Hi @Anonymous,

If you two sheets have similar formats you can add a parameter of the sheet name to get both sheets for each file instead of only one.

The incremental refresh is also possible but I'm not refering to it, I'm refering to the option on the link below.

https://docs.microsoft.com/en-us/power-bi/report-server/configure-scheduled-refresh

[Anonymous]

Thank you so much. 

I will check it out.

My sheets have totally different formats. So I have to write two different paramerters one for each sheet ?

[Anonymous]

I tried it but it is combining both the sheets in one table. So instead of getting two separate tables I am getting one merged table

[v-easonf-msft · Accepted solution]

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

[MFelix]

Do you need one table or two tables?

Having you sheets diferent formats you may need to have two different functions.