Auto refresh from excel file on Onedrive

[Anonymous]

I currently have Enterprise Gateway downloaded to my computer.  I am on the 60 day free trail plan.  I have multiple Datasets linked into my sql databases and scheduled to auto refresh hourly.  Everything works great.

My problem is I have one dataset that pulls information from an excel sheet.  I saved the excel file to my personal Onedrive and linked to it but I can not schedule auto updates.  Says I need to download a personal gateway.  Can I download both the Personal and Enterprise?  Do I have to download the personal to make this work or will the Enterprise gateway work?  How do I fix this?

I run Excel 2016

[v-qiuyu-msft]

Hi @Anonymous,

In your scenario, if the Excel file stored in OneDrive personal contains typed data without connecting to any external data source, when you get data from this Excel, it doesn't require personal gateway. The OneDrive refresh is enabled by default and updates files hourly.

If the Excel contains data which are retrieved from other on-promise database like SQL Server, it also can use OneDrive refresh. But to enable schedule refresh, we need to configure personal gateway like this:

To install a personal gateway and use it in gateway connection for the dataset, you can refer to this article: Power BI Gateway - Personal.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Anonymous]

Ok, I connected my excel file in my OneDrive account to my power bi website that has all my dashboards,  It now appears in my datasets.  The issue is I have a dataset that I'm trying to schedule refreshes for is titled "Accounts Receivable"

1. On Power BI Desktop, I open Accounts Receivable.pbix

2. I have two "Get Data" connections.

3. One is titled "AR", which is a sql query of my database, which is connected by my Enterprise Gateway.(This works great)

4, The second on is titled "Notes AR", which is linked to the file by the same name on my OneDrive account.

5. I can hit Refresh, and it refreshes.

6. I can hit Publish, and it publishes the new refresh data to the website.

7. I go to the Power BI website, go down to datasets, click the three dots to the right of "Accounts Receivable", click on "Schedule Refresh" and it is asking me to install a data gateway(personal) on machine.

Of course, I have the "Notes AR" in my dataset, but it is just notes only. 

So long story short, I have a Enterprise Gateway that is working perfectly on around 10 of my datasets but "Accounts Receivable" is not working and the only difference between the 10 and "Accounts Receivable" is Accounts Receivable is linked to a file on my OneDrive account.  All the other datesets are 100% sql queries of my datebase.

If I delete the "Get Data" connection "Notes AR" from my "Accounts Receivable" dateset, I can Schedule 8 refreshes a day automatically with no problem at all.

[Anonymous]

Thank you for your response.  Can I run both the Enterprise gateway linked to my 10 of my datasets and the Personal gateway linked to the one dataset at the same time on my computer?

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

We can run both the Enterprise and personal gateway on the same computer. If the dataset contains "Accounts Receivable", after publish to service, you can configure SQL Server dtaa source use personal gateway, and make sure you have entered credential under Settings->Datasets->Data source credentials for OneDrive.

Best Regards,
Qiuyun Yu