You want to set excel services in O365 and put in the file to the Power BI, it’s super easy! Just simply drag and drop, viola! Done! See my previous blog for detail how to make it.
But the data won’t update automatically, because we use power query in excel. The default data will be refresh went excel worksheet opened, but it not so good. In this blog I want to share how to automatically refresh your excel that stored in power BI in O365. Create New Excel 2013
Create new query, See my previous blog, After workbook query show, choose send to data catalog.
Go to PowerPivot and add to Data Model. And Upload it.
Close Excel, and open it again from Power Query, My Data Catalog Queries, Choose table and Click Load To…
In Load to Form, don’t forget check Add this data to Data Model.
Then create View from Power View. And Upload Again to power BI Site. Go to Power BI admin panel.
Make sure you already subscribed Power BI form Office 365.
Add New Gateway, Gateway name can include only characters, numbers, hyphens and underscores. It cannot contain spaces and must start with a character.
Click next, and we are in Connect Instance Form add new Instance by click plus button if you want add more instance.
Add gateway instance name ad click add.
If you want use default instance then click Create. Then download and Install Data Gateway Management Client. And Copy gateway key and paste in Data Gateway Management Client. For best performance and guarantee the refreshment of the data you can install to server environment instead of your PC or laptop, because once device turn off or internet goes down, Data Gateway Management Client will be stopped and disconnected, the impact is when data source refresh with particular time it will be failed.
If you already have gateway change the key with Change gateway key button. And follow the instruction.
You can choose HTTP or HTTPS endpoint as necessary
And you can select certificate or create new certificate.
After few minutes, Data management Gateway is ready, please make sure Service status started.
And make sure Data management gateway cloud service is connected.
Back to Power BI Admin Page in O365 then go to data sources.
Create New by click plus button and choose Power Query. Please Use IE because it will launch App to Set Credential that not compatible with chrome.
Open Excel file that you have been upload earlier.
Then Choose Power Query and click properties.
Go to Definition Tab and Copy Connection string.
Before paste make sure you open it via IE, paste it to Data source Connection Info. Then Next.
Then Fill the detail info and specify the gateway. Then Click Set Credential.
In data source settings, choose credential type, in this example I choose Anonymous. Then click Test Connection.
Test connection was successful. Then click OK.
Back to Data Source Detail click Save after that click test power query connection.
If Test connection was successful. Then click Next. Next and Save.
Then the data source look like this.
Back to the Power BI site. Choose the file that you want to schedule to refresh by click in right bellow triple dot.
If appear error:
In new Excel get query from Catalog. And repeat from create new data resource.
If this error appear:
Don’t forget to Add Data Model.
If Success Refresh schedule panel set to the off, then turn in on.
And set your desire settings then Save and Refresh Report.
Go back to History then see the result.
If success the data is ready and scheduled. Happy Refreshing the data!
Data per 25 Nov 2014 before 7AM
Data per 25 Nov 2014 after 7AM
Make sure Data Management Gateway running and connected, for best result Data Management Gateway install it to server environment.