Confirming Schedule Refresh SharePoint

[Anonymous]

Good morning all,

Please pardon all the long winded details...

I need to make sure how refresh works with data pulled from an Excel file in SharePoint. I have the Power BI pro license.

I've created a report using desktop and connected to an Excel file in Sharepoint using Get Data/SharePoint Folder. After connecting to the file with no errors, the data is correct. I can refresh the Excel data in desktop no problem. Then I upload the report to our organization workspace and all looks good. There's two data sources in the report, one is to a SQL Server and the other is to the excel file in SharePoint.

I've set up the scheduled refresh including email to let me know if there's any failures. One of the end users said the data from the Excel is not refreshing. In Power BI online and schedule refresh view, the SQL Server data source is in green, but the Excel data source has a red X. The schedule refresh section is grayed out. If it manually refresh the report in Power BI online, the Excel data doesn't refresh. We have a corporate gateway set up which the SQL server is using. The refresh view mentions about installing a personal gateway. For now, i'm refreshing the data in Desktop and then uploading to Power BI online.

Here's my questions...Is a personal gateway needed for the Excel data to refresh? Or do I need to connect to the excel file differently? Any suggestions on why Power BI online isn't refreshing the Excel data?

[Icey · Accepted solution]

Hi @Anonymous ,

Sorry to reply late. Is the below screenshot the same as your scenario?

If it is, try this:

1. Add data source of SQL Server and then maps to it.

2. Apply.

3. Edit credentials of SharePoint.

Then, you can set Scheduled refresh.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

no worries @Icey , it's a busy week all round...

That seems to work..! I missed seeing the screen you show, with the data mapping. I went through the steps you outlined and so far it's been refreshing data on schedule for the last 4 days. Good to go..!

thanks for the help, have a great weekend

[Greg_Deckler]

Did you set you Excel file up as a data source in your corporate gateway?

[Anonymous]

I'm not sure if it was set up in corp gateway. When i look at the settings in the report Datasets/Gateway connection/Gateway, it shows the corp gateway name, status (in green), name of SQL Server it's pointing to, and which database it's mapped to. Then under that row, it shows the Sharepoint (shows the red X) and URL it's pointing to.

That probably means it's not set up in the corp gateway?

[Icey]

Hi @Anonymous ,

If your dataset have cloud and on-premises data sources merged or appended in a single query, you need to select "Allow user's cloud data sources to refresh through this gateway cluster".

For details, please refer to this document: https://docs.microsoft.com/en-us/power-bi/service-gateway-mashup-on-premises-cloud.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Icey , I tried the microsoft steps and didn't see the view it noted to check the allow user's cloud box. Then i realized it was referring to on-premise site and the site I'm connecting to is sharepoint online. We aren't allowed to install our own gateway and are supposed to use only gateways corporate has created..

I tried a test with a new Power BI file. I connected just to the excel in sharepoint and published and I could see the schedule refresh and it seemed to work. But, when i connected to the SQL Server and published, that's when the schedule refresh grayed out. the sql server is green and works, the excel has red X. It's like having the excel and sql server connecting in the same report has a conflict.

I'm wondering if i need to use alternative way to pull in excel data from sharepoint?