Close search

close

Why Choose Us

Why Organizations Recognize EPC Group's Consulting Services as the Industry Leader
Named Gartner's Cool Vendor of the Year
Named Microsoft Partner of the Year Finalist,
2017 & 2018
Over 4 million Office 365 users successfully migrated
200+ years combined senior team migration experience
Expertise migrating to Office 365 in every vertical
EPC Group's Chief Architect Errin O'Connor was on the original SharePoint and
Office 365 Beta teams

Excel Integration with Power BI

How to drill down to analyze and visualize your Excel data with Power BI

Centralize your data, simplify it with queries you create and share visual reports and dashboards enabling you to access your company numbers in real-time.

Introduction

Our desire is to show you some ways that Excel and Power BI work together. Each of these modes of integration will give you some features. Analyze in Excel will give you the ability to drill down into the data model of Power BI service easily from Excel through PivotTable and PivotChart.

Power BI Publisher for Excel has one step to Analyze in Excel that pins part of an Excel file into a Power BI dashboard while updating it.
Both of features mentioned should be easily adopted by users who have a good grasp of Excel as a front-end tool.

You can also upload your Excel files into the workbooks section of Power BI service. This feature will enable users to access Excel content from the same portal that Power BI reports live and edit content with Excel Online.

Lastly, you can import an entire Excel Power Pivot model with all tables, relationships, and calculations into a Power BI report instead of re-creating it.

Contact EPC Group today to inquire about:

  • Excel Power BI training
  • Excel Power BI consulting

Analyze in Excel

In most organizations, your armed with users that have a good grasp and experience with Excel. These users can still use Excel to connect to Power BI to access and utilize Excel features such as PivotTable and PivotChart to slice and dice your data.

Sine the connection to the Power BI is a live connection, this means whenever users refresh the Excel file they will get the most up-to-date comprehensive data from the Power BI service.

Sine the connection to the Power BI is a live connection, this means whenever users refresh the Excel file they will get the most up-to-date comprehensive data from the Power BI service.

Let’s take a deeper look at how Analyze in Excel works in action.

Power BI Service to Analyze in Excel

To start using Analyze in Excel from the Power BI Service report you will need to log in to Power BI service and open Power BI reports. In the top right-hand side click on “more options” and you will find Analyze in Excel.

Keep in mind that if this is the first time using this feature you may need to install a plugin for the Office connection add-on. After download follow the instructions for set up. Analysis Services OLE DB provider is needed for Analyze in Excel to work because the Power BI datasets are hosted in Azure.

Power BI Publisher for Excel

Power BI Publisher is the add-on for Power BI integration with Excel. Power BI Publisher for Excel is a free and enables two-way integration of Excel and Power BI.

With Power BI Publisher you can add a range of cells into a Power BI dashboard that can be updated on a scheduled basis. You can also use the ability to connect to a data model in Power BI and drill down into it the same way you do this in Analyze in Excel.

To use the Power BI Publisher for Excel, download the add-in for free from here:
https://powerbi.microsoft.com/en-us/excel-dashboard-publisher

How Power BI Publisher Works

Typically, you would not upload an Excel workbook into a service; However, if you want to have a single portal to share Excel workbooks with Power BI reports this is your best option. An Excel workbook uploaded into the Power BI service can be opened and edited with Excel online or the local version of Excel.

Next select “Files” as your Data Source and then choose an “Excel file”. Then choose to Upload your Excel file into Power BI. The Excel file will be then viewable in the Workbooks tab of Power BI service. This will enable you to edit the files with Excel online. Having the Excel workbook uploaded into the Power BI service gives you the ability to edit and interact with Excel Online.

Import Excel into Power BI Desktop

You can import an Excel model which includes a Power Pivot model into Power BI. This does not mean that you are going to acquire the data from Excel. This simply means the entire model including all the tables, relationships, and calculations will be imported into a Power BI report. This scenario can be a valuable option when you have an existing Power Pivot model in Excel.

When importing an Excel workbook its important to keep in mind that your Excel file should not be password protected.

Contact EPC Group today to inquire about Excel Power BI training and consulting.