Power BI Service and Importing and Uploading Excel Spreadsheets

Here we will look at how using Excel spreadsheets as datasources works with Power BI.

Let’s start with a spreadsheet we have stored locally on our server. The file is called ExcelTransactions.xlsx and is stored locally. It contains rows of data (that have been typed into the spreadsheet and are not external data sources):

In PowerBI.com, select Get Data->Files->Get:

Select Local File:

Select our ExcelTransactions.xlsx file. We will now see this page:

Select “Import Excel data into Power BI”:

At this point the service has not imported the data due to the above message. To format as a table, let’s open the spreadsheet and select our data, then press CTRL + T:

We have 2 tabs of data, so we will repeat the step above on the other tab.

Now, let’s repeat the process of uploading the Excel spreadsheet to powerbi.com.

The data is imported into PowerBI.com as a datasource. There are no reports of dashboards created for this datasource at this time:

We can create a basic report for this data. Note there are no relationships defined between Orders and Order Lines:

Save the report:

This report will now appear under Reports, and we can pin the visuals to dashboards:

Let’s add another row to our excel data:

Refresh Power BI. At this point, data is not refreshed in Power BI. The new row we added to Excel does not appear in Power BI. If we go to Scheduled Refresh settings in Power BI, we see this message:

Let’s remove this data source from Power BI and start again. Reupload the data and select “Upload your Excel file to Power BI”:

We see this Excel workbook is now in Power BI as a Report. There are no datasets created:

We can pin data to dashboards as well:

Note data refresh is not supported:

The sheets are actually editable in Power BI – we can see here we can type in a new row:

However the data is not saved either in Power BI or the original Excel spreadsheet.

Now let’s use OneDrive for Business. Let’s upload the spreadsheet we used in the first example, without the data model:

In Power BI, Get Data and select OneDrive for Business:

Connect:

First connect by selecting “Import Excel data into Power BI”:

This creates a data set but no reports or dashboards:

Create a simple report showing customers:

Now add a row to the Excel spreadsheet:

Data in the report is refreshed.

Next, connect to the data from Power BI using “Upload your excel file to Power BI”. This creates a report, and not a dataset:

We can see for this report, data refresh is not supported. However, if we add a row to our spreadsheet in OneDrive, this is updated in Power BI. This is effectively a live read-only view of the data in the OneDrive spreadsheet.

Summary

In order to refresh spreadsheet information from Power BI, we can do this using the gateway, which we will talk about in a future post and also use the data model.

Note using the data model for spreadsheets without a Power Query data source does not support refresh.

For more information on OneDrive and Power BI click here.

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin