Power BI Intermediate Level: 55 - Using Power BI Functionality in Excel

Table of Contents | Excel Report File | Sample Input

Short summary: You can use much but not all of the Power BI Functionality in Excel at no extra cost. The data extraction, transformation, and loading (ETL) can be done using Power Query in Excel, found under the data ribbon. Data modelling, including the definition of measures and setting up table relationships, can be done with the free add-in Power Pivot.

Microsoft Excel is reportedly being used by over a billion people worldwide, especially in businesses and organizations. Power BI on the other hand likely has a user base of "only" a few tens of millions of people. In my years of consulting experience, I had the experience that many clients still heavily rely on Excel. In case you have to or want to use Excel rather than Power BI, it is very good to know that you can use much but not all of Power BI's functionality in Excel, without installation and at no extra cost. Refer to my articles on using Power Query in Excel to cover your ETL needs [1], [2], [3].

In this article, I will show you how to do data modelling in Excel, such as defining measures and setting up table relationships. The underlying mechanism and engine are mostly the same, it's just that the menus are different and that some features are not supported in Excel. More on that later.

Enabling the Power Pivot Add-In

The data modelling functionality of Power BI is realized in Excel through Microsoft's Power Pivot addin. It is preinstalled but needs to be enabled, so let's enable it. First go to the File ribbon, then to Options.

Then go to Add-ins and under Manage click COM Add-ins, then click go.

Then enable Microsoft Power Pivot for Excel and click ok. Then you will see the Power Pivot ribbon in the top right.

Loading our Data into the Excel Data Model

Let's quickly import our samples sales data into Excel.

The most important step is that when loading the data or later on, you need to go to the Load To settings, e.g. by right-clicking the query in the query pane and then selecting Load To.

Then check "Add this data to the Data Model". The Data Model is like a little database which resides inside the Excel file and enables the data modelling features of Power BI.

Defining Measures and Table Relationships

Now let's go to the Power Pivot ribbon and click Manage.

What you will see then is the Power Pivot window, which has similar functionality like the Power BI Data View. You can preview data, change formatting, and define your measures. To define a measure, click into one of the cells in the bottom part of the screen and type the DAX code.

Important: Here you will need to specify a colon ":" before the equal sign.

Let's define a measure for the average sales amount:

Average Sales Amount:=AVERAGE([Sales Amount Before Discount])        

Now let's set the table relationships. Go to the Diagram View in the top right. This will get you the equivalent of the Relationship View in Power BI. Connect the tables via their IDs.

Creating Visualizations

Close the Power Pivot window. Now back in Excel, there are essentially three different ways for outputting data:

• Output a table using the Load To setting of the query pane we saw earlier
• Insert a Pivot Table, which is the equivalent of a Matrix visual in Power BI
• Insert a Pivot Chart, which let's you plot different kinds of charts.

Let's use the third method. First insert a Pivot Chart.

It is very important that you use the data model as source, which allows you to use many of the Power BI features.

Let's demonstrate this by plotting the average sales over the stores. Let's put our Average Sales Amount measure under Values, the Date of the Transactions table under Axis, and the Store Name of the Stores table under Legend.

Limitations

There are some limitations of Power Query and Power Pivot in Excel compared to Power BI. The most important ones include:

• There are fewer data connectors for Power Query, for example the connector for Snowflake is missing
• Only one-to-many relationships are supported. One-to-one and many-to-many relationships are missing
• Only unidirectional filtering is supported, bi-directional filtering is not supported
• Some (comfort) functions are missing, such as SELECTEDVALUE
• If you use the 32-bit version of Excel, Power Query and Power Pivot may run into memory issues rather quickly and stop working. In my experience, the 64-bit version is prevalent nowadays. You can check your Excel version under File, Account, About Excel.

Conclusion

You now know that you can use many of the Power BI features in Excel for free and without additional installation. Given the widespread adoption of Excel in the business world, there are cases where you may want to or have to use Excel instead of Power BI. In those cases, you can save massive amounts of time by essentially using Power BI in Excel. It is especially useful for quick analyses or when your colleagues or clients primarily work with Excel.

Congratulations for making it through this long but important article. This marks the end of the intermediate-level articles. In the next article, I will give a summary of all the important topics we have learned.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: Intermediate Level Summary