Power BI Workbooks

In this Microsoft Power BI tutorial, We will discuss what is Workbook in Power BI, What are the different types of Workbooks support in Power BI, What is the rang or a data table in the Power BI Workbook.

Also, We will see the external data sources that connect with the workbooks, How many data type supports in the Power BI Workbooks, the Location of all the saved Workbook files and how we can upload a Workbook in the Power BI with an example.

If you are new to Microsoft Power BI, then using this below article, you can learn the Power BI from the beginning: Getting started with Microsoft Power BI Tool

What is Power BI Workbook?

Power BI Workbooks are some of the data models with multiple tables of data loaded into it. This data can load by using Power Query or Power Pivot. All data model properties like measures, hierarchies, KPIs, relationships, etc. are supported by Power BI.

Types of Workbooks support in Power BI

Power BI helps to connect workbooks which are created in Excel 2007 and the latest version of Excel. This Workbooks Excel should be saved with .xlsx or .xlsm extension file type and must be under 1 GB.

Power BI supports four different types of Workbooks such as:

  • Workbook ranges or data table
  • Data models in Workbooks
  • External data sources connections with Workbooks
  • Power BI Workbooks with Charts, PivotTables, and View sheets

Workbook ranges or data table:

You need to sure that your worksheet data range should be in table format. When you will create a report in Power BI, it will help you to view the named tables and columns in the Fields pane. So that you can visualize your data easily.

Data models in Workbooks:

Power BI Workbooks are some of the data models with multiple tables of data loaded into it. This data can load by using Power Query or Power Pivot. All data model properties like measures, hierarchies, KPIs, relationships, etc. are supported by Power BI.

Note:

You can not share the Workbooks with data models across Power BI tenants. This means, If a user login into Power BI using a <tenant1>.com account cannot share an Excel Workbook with a user who login using a Power BI login account from <tenant2>.com.

External data sources connections with Workbooks:

You can use external data sources to use Excel. You can create Reports and Dashboards that connected data sources when your workbook is in Power BI. Here the data sources will be updated automatically based on the data that are present in the Reports and tiles in any visualizations.

Power BI Workbooks with Charts, PivotTables, and View sheets:

In Power BI, Workbooks with the charts, PivotTables and View sheets depends on where your workbook file is saved and how you choose to find it into Power BI.

Data type supports in Power BI Workbooks

There are some data types present which supports in the Power BI as the Whole Number, Decimal Number, Currency, Date, True/False, Text, etc.

Workbook files saved location

In Power BI, you need to know where your Workbook file is saved. This makes a big difference in Power BI. There are four ways where you can save your workbook file such as:

  • Local
  • OneDrive – Business
  • OneDrive – Personal
  • SharePoint Team Sites

Local:

In your Organization, If you want to save your Workbook file to a local drive on your computer or any other location, then you can easily load your file into Power BI. In this Local, a new dataset is created in Power BI. The Data and Data model from the workbook is loaded into the dataset.

In Excel 2016, You can Publish feature which is present under the File menu. Similarly, in the Power BI, you can use Get Data -> Files -> Local File.

OneDrive – Business:

If you want to save your Workbook file in the OneDrive – Business, then you can. You just need to sign into it with the same account which you signed in the Power BI. Hence your all Datasets, Reports, Dashboards will in-sync with the Power BI.

Power BI connects the Workbook file on OneDrive per hour because both Power BI and OneDrive are present in the cloud only. It will automatically update if any changes are made in your Datasets, Reports, and Dashboard.

OneDrive – Personal:

It will better if you will save your file in the OneDrive – Personal account. Always make sure that when you are using this OneDrive, At first, you need to sign in to your file (Using the Get Data -> Files -> OneDrive – Personal) OneDrive with your Microsoft account which is different from what you use to sign in to Power BI.

You must be sure to select the Keep me signed in option when signing in the OneDrive with your Microsoft account. Hence, it will update automatically because of sync with the Power BI.

SharePoint Team Sites:

The Workbook file is saving in the SharePoint Team-Sites is very much similar to save the file in OneDrive for Business. The only difference is how you can connect to the file from the Power BI. For this SharePoint Team-Site, You can provide a URL or connect to the root folder.

Upload Power BI Workbooks

Here, I will show you how you can upload the Workbooks in Power BI with an example.

In the below screenshot, you can see there is nothing available in my current Workbooks in my Power BI Workspace.

upload power bi workbooks
upload power bi workbooks

This below screenshot represents my Workbook Excel sheet name as Sales Record which I want to upload in my Power BI Workbook. This Workbook Excel I have saved in my Local system.

how to upload power bi excel workbook
how to upload power bi excel workbook

To upload the Excel Workbook in the Power BI, At first, click on the Get data option which is present on the bottom left corner as shown below.

upload power bi workbook
upload power bi workbook

In this example, I want to upload an Excel Workbook. For that, I clicked on the Get button of the Files Tile as you can see in the below screenshot.

Power BI upload workbooks
Power BI upload workbooks

Once you will click on the Get button of Files tile, it will open the below window. As in this example, I want to upload the Excel workbook from my local system, So I have selected the Local File option.

Similarly, you can use the OneDrive – Business, OneDrive – Personal, SharePoint – Team Sites to upload the Excel Workbook in Power BI.

Power BI upload workbook
Power BI upload workbook

When you will click on the Local File button, a window will open to select the file from your local system. As for my example, I have selected the Sales Record Excel Workbook from my Desktop as shown below.

Once you will select the workbook, just click on to Open.

how to upload workbook in Power BI
how to upload workbook in Power BI

To upload the Excel Workbook file, Power BI provides two options as:

  • Import Excel data into Power BI: This Import Excel data into Power BI option specify to allow you to design reports using the imported excel file. This option will help to add Dataset to the Workspace.
  • Upload your Excel files to Power BI: This Upload Excel file to Power BI is the same as the Excel Online. You can modify, edit, view with the data like as you normally do in the Excel file. This option will help to add Workbook to the Workspace.

As for my requirement, I want to upload the Excel workbook from my Local system, So I have clicked on the Upload button of Upload your Excel file to Power BI option.

how to upload Power BI workbook
how to upload Power BI workbook

Now it will take a little time to upload the Excel Workbook file. In the below screenshot, you can see the upload successful message as below screenshot.

   upload power bi workbook examples
upload power bi workbook examples

Now to view the available workbooks in Power BI, Go to the My workspace -> Workbooks tab as shown below.

In the below screenshot, you can see the Sales Record workbook which I have uploaded currently in Power BI.

power bi upload excel workbook
power bi upload excel workbook

Once you will click on the Sales Record Excel Workbook file, it will display all the items which are present in it as shown below screenshot.

upload power bi excel workbook
upload power bi excel workbook

Also, you may like the below Power BI articles:

Hence, in this Microsoft Power BI tutorial, We discussed what is Workbook in Power BI, What are the different types of Workbooks support in Power BI, What is the rang or a data table in the Power BI Workbook.

Also, We saw the external data sources that connect with the workbooks, How many data type supports in the Power BI Workbooks, the Location of all the saved Workbook files and how we can upload a Workbook in the Power BI with an example.

>