mssqltips logo

Power BI Workbooks

By:   |   Updated: 2019-10-22   |   Comments (2)   |   Related: More > Power BI

Problem

I've created a couple of charts and PivotTables using Excel. Can I share these with my coworkers in the Power BI service? Or do I have to create everything in Power BI Desktop? What is the difference between a dataset and a workbook?

Solution

In part 1 of this tip series, we gave an introduction to Power BI datasets, which are created in Power BI Desktop and uploaded to the Power BI service, the online platform where you can share your reports and dashboards within your organization. In this part, we'll take a closer look at the workbooks feature of Power BI.

Workbooks

In short, workbooks are Excel files which are published to the Power BI service. There are two options for the data inside the Excel file:

  • You leave everything as-is and you can browse the workbook using Excel Online
  • Or you import the data into Power BI. In this case, the data will be converted to a dataset, so you can use it to create Power BI reports on top of it.

Let's take a look at both options.

Upload an Excel File

Suppose we have an Excel file with some data and a line chart (called SimpleExample.xlsx):

excel sample data

We can then upload the workbook to the Power BI service by choosing Get Data:

service get data

In the new screen, choose Files in the Create new content section.

get files

In the next screen, there are a couple of options for the file location. In our case, we're going to choose the local file:

local file

The OneDrive for Business option is a useful option if you want to automatically sync your workbook to Power BI. More information can be found in the documentation.

As mentioned, when using a workbook, you can choose between two options: uploading or importing. Let's choose uploading.

upload or import

Once the file is uploaded, you can find it under the worksheets section of your workspace:

uploaded workbook

Clicking on the object will open up Excel Online inside the Power BI service, allowing you to browse the Excel workbook like you normally would:

update excel workbook online

It's also possible to manipulate data, and the chart will change accordingly:

update data

However, changes are not persisted.

Import an Excel File

The other option is to import the workbook and convert it to Power BI objects. To do this, select Get Data, click on Get in the Files section and choose Local File again. This time, choose the Import option.

import workbook

However, you have to make sure the data you want to import is formatted as a table, otherwise you'll get this error:

format data as table

The data will then be imported into a Power BI dataset (more info on those in part 1 of this tip):

importing data

The result can be found in the datasets section of your workspace:

imported dataset from workbook

When you click on the dataset, you can explore it and create a new report on top of it:

create new report

Other sources of Data

We've taken a look on both the import and upload option for an Excel workbook where the data itself is stored in a table. But there are other possible data sources as well, such as a Power Pivot model or a PivotTable with a connection to an Analysis Services instance. In the following sections, we'll explore those options.

Power Pivot Model

The Power Pivot model – which is now known as the Excel Data Model – has been around for quite some time now. It's basically the same model as a Power BI dataset, but in Excel. For an introduction, please read the tip Introduction to PowerPivot for Excel and SQL Server. Suppose we have the following model created in Power Pivot (the Excel file containing the model can be downloaded from here):

power pivot model

Keep in mind you can create a model without actually importing all of the data inside Excel worksheets by using the following options:

only create connection

Using the model, the following PivotTable was created:

pivot table sample

When we upload the workbook to the Power BI service, the PivotTable is kept as-is. The data itself from the Power Pivot model is also present, albeit you can't see it.

uploaded power pivot model

The PivotTable can be modified and you can browse the model like you want. You might want to click on All in the PivotTable field list to show all possible fields, otherwise only the tables used in the PivotTable are shown.

change pivottable

On the other hand, when we import the workbook, the Power Pivot model will be converted to a Power BI dataset, including all the relationships, measures etc. you defined. There you can create reports using the full Power BI experience.

imported power pivot model

Live Connection to SSAS

Our last workbook has a live connection to a SSAS Tabular instance (hosting a model containing data from the Wide World Importers sample database):

ssas uploaded

We can upload the workbook to the Power BI service, but modifying the PivotTable might not be possible since we're using an external connection to an on-premises data source:

modify pivottable ssas

Depending on the type of external data source used or gateways configured, this refresh might fail:

refresh failed

A better option would be to create the live connection in a Power BI Desktop file.

Workbooks and Datasets: A Compromise?

Most users have little trouble working with Excel; in fact, some users might even prefer Excel over other tools for reporting. But Power BI datasets have much more power and flexibility over a traditional Excel file. They are even better than Power Pivot models, since Power BI Desktop is updated more frequently and has the latest updates for the DAX language, relationships and other features. Is it possible to combine the best of both worlds? Luckily, we can.

With the Analyze in Excel feature, we can use Excel as a front-end client – which most people know how to work with – while at the back-end we have a Power BI dataset providing us with full flexibility and scale. How does it work? For any published dataset, you can simply right-click it and choose Analyze in Excel.

analyze in Excel

This will make you download a .odc file (Office Data Connection) to your machine. When you double-click it, Excel will open (the first you might be asked to install some additional software). You will end up with a PivotTable, directly connected to the dataset in the Power BI service:

analyze in excel pivottable

Now you can use Excel to browse the data. This is similar to using Power BI desktop to connect to a Power BI dataset, as demonstrated in part 1.

Conclusion

In part 2 of this tip we gave an introduction to the role Excel workbooks can play in the Power BI ecosystem. Basically, there are two choices: either you upload the workbook so you can view it in Excel Online, or you import the workbook so it is converted into a Power BI dataset. Depending on the type of data or the storage location, it's possible to refresh the data.

As a hybrid combination, you can use the Analyze in Excel feature to create a PivotTable in Excel directly connected to a Power BI dataset.

Which option would be the best for your scenario?

  • If you already have reporting in Excel and you want to share it with colleagues with minimum effort, importing the workbook as-is is your choice.
  • In all other cases, using Power BI Desktop to create a dataset is the preferred choice. Even if you have a dataset in Excel, importing it in Power BI Desktop gives you maximum flexibility. You can for example combine that dataset with other data sources to enrich the data. You also have the latest features of the M and DAX language.
Next Steps


Last Updated: 2019-10-22


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, October 25, 2019 - 10:10:01 AM - Hieu Ho Trung Back To Top

Excellent subject.


Wednesday, October 23, 2019 - 2:59:07 AM - Addend Analytics Back To Top

Fantastic Blog! This post was really helpful for my research as I was searching for an article related to the Power BI Workbook. Thanks and keep sharing such informative articles.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools