DSCallards: Analytics

How to import an Excel-based power pivot or power query connection into Microsoft Power BI

If you have already have a Power Pivot or Power Query (Get External Data) connection from Excel to a data source, you can import that connection into Power BI to feed your Power BI dataset.

Note that this approach only works if your connection is exposed as a Model (created by Power Pivot) or a Query (created by Power Query (Get External Data)).

You can check for queries in Excel by selecting Data | Queries & Connections | Queries & Connections, which opens the Queries & Connections pane:

You can check for models by selecting Power Pivot | Data Model | Manage, to open the Power Pivot editor:

Please note that if you need to connect to your data source via Microsoft Query, this (ironically) does not create a query so does not support this approach. For this or other Excel-based connectivity methods you can try another automation using Powershell described in here.

1. Rather than connecting to the spreadsheet as a data source you need to import it, so create a new Power BI report without connecting to data

2. Select File | Import | Power Query, Power Pivot, Power View

3. Note that doing this with a Power Query from Excel, for some reason the query does not appear in Power BI at first:

Power BI for some reasons disables the load of the query to the report. To fix this you need to:

4. Open the Power BI-based Power Query editor via Home|External|/Edit Queries|Edit Queries

Note that the Query is greyed out.

5. Right click on it and select 'Enable Load'.

6. Close and Apply and choose your preferred storage mode, here I'm using Import.

7. Add a visual to your report.

8. At this point, if your workbook contains a Power Query you'll need to refresh to run the data in.

8. Add some data to your data source and click Refresh (again) in Power BI desktop to check you see the new data.

10. Publish the report up to the Power BI service as normal.

11. As normal, Power BI service requires a data gateway data source to connect to on-premise data. Be aware that because you are inheriting the connectivity rather than connecting to the Excel file, you need to configure the connection to the original source rather than the Excel file, in my case SQL Server:

12. Allocate the relevant data gateway to your dataset by clicking the cog icon at the top right of the Power BI portal |Settings|Datasets and selecting your dataset.