Forum Discussion
Excel Workbook refresh Setup
From my understanding of what you are saying, you are trying to have Power BI refresh your data in Excel, then refresh data in PoerBI. It isn't designed to do that. The gateway will take your data in the Workbook "as-is" and refresh the elements in Power BI. It won't refresh your data in the source first...
I thought part of PowerBI was to do refresh the workbook itself, especially when PowerQuery was used to pull the data.
I suppose I've been chasing rabbits, then.
The tables explain that differently - I totally interpreted it as a workbook using Power Query "to connect to and query data from any listed online data source and load data into the Excel data model."
(Under "Excel workbook on OneDrive")
https://support.powerbi.com/knowledgebase/articles/474669-data-refresh-in-power-bi
As long as power query is connected to a supported data source and the data is loaded into the model and not into excel it should work. Now your "in-house" data is a lil different. I would take those lookup tables and try to store them directly in the same power query file as excel tables.
I will try putting them into the Workbook as tables - but what about when I have to update those tables?
OK, hoping to put this one to rest.
Short answer is YES, you can do what you want to do in Power BI. Not sure of the problems you are having with refresh but here is what I did, hopefully this will help.
1. Created new Excel workbook
2. Used Power Query to connect to CRM Online (sorry I don't have a Salesforce instance to test with so best I could do)
3. Imported OpportunitySet into my data model, imported 142 opportunities
4. Saved Excel workbook to OneDrive for Business
5. Went into Power BI Service and Get Data | Files | One Drive for Business | [my file] | Connect | Connect, Manage and View Excel in Power BI
6. File showed up under Reports
7. Clicked ellipses | Schedule Refresh
8. Entered credentials (I used OAuth2)
9. Set schedule
10. Went into CRM Online and entered a new opportunity
11. Back to Power BI Service, didn't feel like waiting so clicked ellipses next to my file under Reports and did a "Refresh Now"
11. Nothing
12. Clicked ellipses and it said something to the effect that refresh failed because file was open
13. Closed file
14. Refresh now once again
15. Clicked ellipses, success this time
16. Opened file, looked at data model and it had 143 opportunities
17. Stood up and did an awkward dance
18. Wrote this reply
Should work the same if you choose the other option when importing your Excel file except that it will show up under Datasets and that is where you will get the Schedule Refresh and Refesh Now options with the ellipses.
Yes, I"ve tried bringing it in as a report as well as a dataset, but it never refreshed my data. Hmmmm....I'm glad it worked for you, though. Did it save a new Excel file for you? or was it just new data?
Couple things.
1) The PBI Personal Gateway will not help you if you are trying to perform a refresh of an Excel file in One Drive without any tie to Power BI.
2) If you were loading your Excel file into Power BI then I would direct you here. And it will outline what is possible.
3) The page you listed relates to how the Personal Gateway relates to a bunch of different scenerio's of how data sources are compiled. The table still defines the Excel file and Power Query scenerio as a "datasource" - which, as highighted earlier in the page, outlines that the context of the entire page fall under this statement:
"When you refresh data in Power BI, you're updating data in the dataset with new and updated data from data sources."
You are in charge of updating your data source
Power BI will take that data from the data source and update the dataset.
Right, I understood the differentiation between dataset and data sources - I guess it just lost me the requirement that it had to be loaded into PowerBI.
So there's no automatic (scheduled) way of refreshing data sources, then? (Power Update is out there, but pricey)
