Excel is still commonly used to store some static data, which often needs to be analyzed as part of Power BI reports. There some guidelines about how to connect Excel with Power BI web app. But what if we need to have it in Power BI Desktop?
Also, Power BI is a great tool which allow user to mix different data sources in single report. So, our goal is to configure refresh for the workbook created in Power BI Desktop with mixed data sources: Excel file stored in OneDrive and traditional RDBMS source.
There are similar question in Power BI community forum which gives overall idea of what needs to be done.
In short, we need to install Personal Gateway. Even if your other datasets are configured with Enterprise Gateway. Is ok to install personal gateway on the same host.
To add Excel as a data source i use Microsoft SharePoint Folder with particular file specified:
Second data source in this workbook is traditional SQL Server data source with basic authentication, so i will not show it here.
If you already have configured Personal Gateway, you should be able to see this option on Datasets Settings page:
You need to edit credentials for SharePoint data source, select oAuth2 method and click “Sign In”:
You will be redirected to authorization page, where you need to login with your Power BI user. This means that you grant permissions to Power BI service to access the file on behalf of that particular user.
That’s it. We have just configured data refresh from Excel file stored in OneDrive.