Direct Query for Excel Data

[Anonymous]

I know that various data sources support a Direct Query (https://docs.microsoft.com/en-us/power-bi/desktop-directquery-data-sources) but is it possible to connect an Excel spreadsheet held on a Shareport site in the same way?

I want a user to update the Excel spreadsheet so that another user can simply refresh a web-based Power BI report and pull that data through, rather than having to refresh the Power BI report in the desktop version and then publish it again.

Many thanks.

[jgrote]

This is very recently now supported, in terms of the semantic model and data refresh from Sharepoint

Get data from Excel workbook files - Power BI | Microsoft Learn

[Anonymous]

If you mean Sharepoint, this link might help you out. Even though it's explaining how to do it in One Drive for Business, the proccess is the same in SharePoint.

I had the same problem and this helped me a lot. I didn't test the update functionality yet, but I guess it works.

[Anonymous]

Thanks guivecchi for a quick response.

Interesting link. The web based Data Source did pull data from the Excel file on Sharepoint into Power BI desktop.

Sadly, when I published the report to the web version of Power BI and then made changes to the Excel file, it still acts like the connector is an import query i.e. the report did not refresh.

[Anonymous]

I tested it here. Try refreshing the data source by clicking in those three little dots by the name of the data source. 

After that go to the report section and click update above the report. This second part is important for it to update. 

I guess this is made automatically over time but to test it instantly, those steps have to be followed.

[Anonymous]

I'm not sure I am following you when you say "After that go to the report section and click update above the report. This second part is important for it to update."

Can you expand upon this? Is it PowerBI desktop? The Excel file on Sharepoint?

Thanks for the continual insights.

[Anonymous]

Sorry for not being very clear. I'm talking about Power BI Service, where the report is after published. I guess that's where you want your report to update. 

When I talk about "After that go to the report section and click update above the report. This second part is important for it to update." I mean that you should click on the report name in the left-side tab which shows you the content of your Workspace (Reports, Dashboard, Datasources etc). That should open your report. When the report is open there's a settings bar above it, just like that: (for some reason I can't attach a picture here so I'll try my best to represent it haha)

File v   View v  Edit Report |  Refresh ... and it goes on like that.

You should click on the Refresh button. If you had refreshed the data source, that should update the visualization with the new data.

[AryanParekh]

• Very interesting thread, i got lost here, i have : file✓ export✓ share chat in teams get insights...... And eventually a refresh circle arrow symbol whose alt text reads "refresh visuals......refreshing will update visuals with latest data"... But clicking it is not refreshing the data. To clarify the desktop app gets updated when i edit the sharepoint excel database but the report via link does not! Thanks for your time.

[Anonymous]

I see the 'scheduled refresh' and the 'refresh now' menu items for the Dataset in the web version of Power BI. It seems that these are the only two valid options for Power BI reports that link to Excel data.

Thanks for your assistance!

[Anonymous · Accepted solution]

Yep, those are the only possibilities. I'd use refresh now to check out if it's working. Then you should set up some refresh times to refresh the report periodically everyday. I think there is no way to have automatic updates that refresh the report as soon as you change your file.

[karaoan]

@Anonymous One other option that you can try is: Acterys Power BI Sync This solution enables you to automatically synchronize a Power BI model with any source supported in Power BI to Azure SQL Server and also adds very extensive planning and editing options with 8 Power BI visuals: Business Apps – Microsoft AppSource

[Anonymous]

Agreed.