An often-overlooked advantage of using Power BI and Excel together is the ability to connect Excel to Power BI datasets. This technique gives Excel users and Power BI developers the flexibility to provide managers with reports they’re familiar with without having to publish two separate reports.
Have you ever heard something like this?
“This thing looks great, but I need the data! Send me the Excel!”
We have heard it a time or two. Usually what goes unnoticed is that it can take many hours to build out a Microsoft Power BI dashboard.
A large majority of that time is spent setting up a data model.
To connect Microsoft Excel to a Power BI Dataset, publish a dataset to the Power BI Service. Next, open Excel and click Insert > Pivot Table > From Power BI and select the published Power BI Dataset. You must be using the same Microsoft Account in Excel that has access to the Power BI dataset.
By following these instructions you will be able to pull a Power BI dataset into a Pivot Table and fill out an entire workbook with the data.
Table of Contents
Why you should connect Excel to Power BI Datasets
There are a number of benefits to creating a single dataset and using it across multiple publishing platforms. These are just a few that come to mind:
- Ensure that KPIs and other metrics match between systems
- Enable Automatic Data Refreshes using the Power BI Service
- Pivot Tables are more familiar to many business users than Power BI Dashboards
- Avoid establishing multiple connections and leverage the Power BI Gateway to access on premises data
- Easily format data in views that finance and accountants are used to
- Power BI has more modern features than Excel Power Query or Get and Transform
We could go on and on. There are so many benefits to connecting Excel to Power BI that everybody should keep this trick in their back pocket.
While Microsoft invests heavily in Power BI, there are still some types of reports like complex financial statements that are difficult to format correctly and build in Power Bi.
Pre-Requisites for Connecting Excel to a Power BI Dataset
Be aware that there are several pre-requisites for getting this to work.
- You cannot connect Excel to Power BI with older versions of Excel, you must be using Microsoft 365 for Enterprise. Microsoft issues updates all of the time, but home versions of Office may not support this.
- Ensure that you are logged into Microsoft Excel under the same Microsoft Account that you use for logging into PowerBI.com. Excel will only let you access datasets that you have permissions to access in Power BI.
If you follow these instructions and the option is still not available reach out to your corporate Power BI admin to have them enable live connections. Your tenant of Power BI must have live connections enabled for Power BI.
How to Connect Excel to a Power BI Dataset
You can either start by creating a dataset in Power Query Online, use an existing dataset, or create a blank report in Power BI Desktop and publish it to the Power BI Service, PowerBI.com
After the data set is published it will be available for you to import into Excel.
In Excel, Navigate to:
Insert > Pivot Table and click the arrow at the bottom of the pivot table icon.
You should see an option that says “From Power BI”
If this option is not visible for you, make sure that you are logged in to Excel under the correct Microsoft account. If you update it, close Excel and re-open it. The connect to Power BI option should appear.
Once you select From Power BI, you will have Power BI Dataset pane that opens up on the right side of Microsoft Excel.
These are all of the datasets that you have access to within your Power BI workspaces.
Once you find the dataset that you want to use for your pivot table click on + Insert Pivot Table.
This will create a pivot table in the existing worksheet.
You can interact and use the pivot table the same as you would with any other pivot table, including formatting, totals, and calculated fields.
It may take a second or two to update each time you change the Pivot Table. Excel queries the Power BI Dataset for each change.
Using an Excel Pivot Table to Populate an Excel Workbook
Once you have the data coming into a pivot table, it’s pretty easy to setup the rest of the Excel workbook to populate the data into a more traditional reporting format.
By using a combination of SUMIF and VLOOKUP formulas you can bring the values from the Pivot table into another tab that’s been formatted.
It will look something like this:
Of course, this is a very simple example, but bringing the data in should only take a couple of formulas.
A great thing about the pivot table is that you can put the data into a tabular format that makes it easier to use SUMIF, SUMIFS, VLOOKUP or XLOOKUP formulas on.
If you’re not sure which one to use, we recommend looking into SUMIFS. They are highly versatile and can be used for almost anything. They expand on a SUMIF by letting you use multiple criteria in the same formula.
How to Publish Excel to Power BI
You can publish from Microsoft Excel directly to the Power BI Service, PowerBI.com. Do this from Excel by clicking on File > Publish
If you are logged into Excel with the same Microsoft Account that you use to log into powerbi.com you will have a Publish to Power BI option available.
Choose your workspace
Then upload the Excel workbook directly to Power BI for future use in a Power BI data model, or save to a workspace to control who the data is shared with along with other Power BI based reports.
Conclusion
While some people get stuck on whether to use Power BI or Excel, we believe that people should feel free to use both. Each system has its own pros and cons and business users should choose the right tool for the job.
While Power BI is excellent at creating reports, dashboards, charts graphs and giving people up to date information it can be more of a struggle to get reports into the exact format that you need.
At the end of the day, just because you can put a report into Power BI doesn’t mean that you should.
The technical aspect of changing a reporting platform may be the easy part. The organizational change that you are typically asking managers and high level VP’s may not be feasible or realistic. Excel has been around for decades and some people are used to looking at the same report format for a similar length of time.
By connecting Excel to a Power BI dataset, you can bring users into the future on the back-end without raising alarm or forcing unrequested changes on them.