Excel and Power BI

In an old cartoon was promoted a saying: “Cats and mice can be friends”. Consequently, Excel and Power BI can also be friends! Power BI allows you to import Excel reports, whether traditional, Pivot or Power Pivot reports. This is not a whole new topic, but I think it would be nice to remember it in a new light, with an emphasis on what you can do for free and when you definitely have to buy licenses.

We will see how this works by using an Excel document we created by dragging three tables into the Data Model, linking them, and creating a Power Pivot report. Several sliders have been added, as well as a Pivot chart illustrating the data, which is a list of the five top-selling items. We saved the Excel document to the OneDrive for Business repository.

Retrieving data from a file is done from the Get Data menu. After you run this option, Power BI will offer you several options: import from your PC (Local File), import from the OneDrive or OneDrive for Business repository, as well as import from the SharePoint team site, which will not be covered in this text.

If you are importing a file as a dataset which is the source, more or less, it doesn’t matter. However, if you want to retrieve a finished report that will be viewed by more users, One Drive for Business is a better option. First of all, because OneDrive won’t allow you to import a Workbook along with its structure, data model, sliders, charts … It allows you to download the file solely as a Dataset. On the other hand, OneDrive for Business works far faster and more reliably so that when it comes to business deployment, there is no dilemma which service to choose.

After choosing the One Drive for Business option, you need to find and select the document you are importing, and then click Connect.

You will be offered two options: Importing data as a dataset (Import) or Importing whole reports within a workbook (Connect). If you choose the first option, Power BI will try to identify your tables and create a dataset from which you can later create reports. Another choice is to retrieve the finished Excel reports that you want to manage within your Power BI account.

Importing a document will show it within Workbooks section and there you can see it through the Excel Online service. At the same time, data was also imported as Dataset. If you select an area and then click on the icon that resembles a pin, you will add a report to the chosen Dashboard.

If there is any change to a report hosted on the OneDrive for Business repository, it will automatically be updated after a while in the Power BI service.