Power BI and Excel are tools so close to each other that Power BI was born inside Excel. Power Query, Power Pivot, Power View, they were all Excel plugins before being united and become Power BI.

The ability to build an ETL, design a model and generate reports and dashboards with great storytelling make Power BI a very powerful tool even beyond the self-service tool which initially was its purpose, but getting closer to an enterprise tool.

However, Excel still has a unique level of flexibility for data analysts. Pivot Tables allow the data analyst to have a view on the model and easily mix measures and dimensions to view a result, even easier then Power BI reports.

When Power BI was created, we easily mistake it as a visualization tool (some people still do this mistake). From that time, power bi has evolved to become very close to an enterprise-level tool.

Nowadays, we have the ability to build powerful ETL architectures building many re-usable dataflows, build models using the ETL as a source and make them re-usable as Datasets published in the portal and create many reports and dashboards from these models.

All these abilities still don’t replace the self-service capability in Excel Pivot Tables. Their flexibility for analysis is unique. So, it’s no surprise that on the way to becoming an enterprise tool Power BI offers Excel connections directly to its datasets.

There are two solutions to make this kind of connection, let’s try.

Analyze in Excel

The first option is the use of the instruction Analyze in Excel. You can find this option on the “…” menu item close to each dataset in the portal.

The idea behind this technique is simple: Power BI is built over the same engine than the tabular model in SSAS. Due to that, you can make a connection to Power BI datasets using Analysis Server as a source.

• When you click Analyse in Excel, the portal downloads to your machine an ODC file. The ODC extension means Office Data Connection, a file with information about how to connect to the dataset.
• Once in Excel, click the Data menu and click the button Existing Connections in the toolbox.

• In the Existing Connections window, click Browse for More button and select the ODC file you just downloaded
• In the Import Data window, select how and where you would like to see the data. For this example, I will use a PivotTable

• In the Import Data window, click Ok

The Pivot Table is created inside the worksheet and you will be able to see on the right side the data in your Power BI Dataset. You will also be able to choose the measures and dimensions to build your Pivot Table.

Power BI Publisher for Excel

Another option is the user of the Power BI Publisher for Excel. You can download this tool here: https://www.microsoft.com/en-us/download/details.aspx?id=50729

This tool will add a new menu to Excel called Power BI.

• Click on the Power BI menu in Excel
• Click on the button Connect to Data. It will request you to login to Power BI.

• In the window Connect to data in Power BI select the workspace you would like to connect on the dropdown.
• Select if you would like to connect to a Report or to a Dataset on the checkboxes
• Select the name of the Report or Dataset on the 2nd dropdown

• Click Connect

A new worksheet will be created with the pivot table, exactly as the previous example.

There are one problem and one advantage. The problem is that you are restricted to connect to classic workspaces. It’s not possible to see the upgraded workspaces. This tool is not updated since 2017 and, according to the official Power BI account on twitter, the upgraded workspaces are still in preview.

The advantage, on the other hand, is that you can also publish data back to power bi, use the option to pin your pivot table in a dashboard.

You can read more details about how to use this tool on this link: https://docs.microsoft.com/en-us/power-bi/publisher-for-excel

Conclusion

The ability to allow self-service BI using Excel puts Power BI closer to the status of enterprise tool, going to one day replace Azure Analysis Services