cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
normality2000
Regular Visitor

Direct Query for Excel Data

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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.

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
Not applicable

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.

 

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
Not applicable

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.

 

 

 

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
Not applicable

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.

View solution in original post

@normality2000 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

Agreed.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.