Find the many ways to interact directly between Excel and Power BI Service
Part three of a four-part series of blogs

You can pull Excel workbooks (or individual data ranges) into Power BI Service, or push them from Excel using a special add-in. This blog shows how to use both approaches, as well as the underwhelming Analyze in Excel feature.

  1. Working with Excel in Power BI Service
  2. Analysing Data with Excel
  3. Getting Data from Excel or CSV files in Power BI Service (this blog)
  4. Publishing Excel workbooks, or tiling parts of a workbook

This blog is part of a much longer series, which together comprise a full online training course in Power BI Service.  You can see get details of our classsrom Power BI training courses here.

Posted by Andy Brown on 29 June 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Getting Data from Excel or CSV files in Power BI Service

In this blog I'll first show how to import basic CSV files, then consider what happens when you import a workbook.

Steps to follow for basic imports (CSV fiiles)

At the bottom left of the Power BI Service window, choose to get data:

Getting data

Click on this tool to load data.

 

Choose to bring in data from a file:

Choosing a workbook

Choose from where you want to get data.

You can now say where this file is stored:

Local workbook

If you choose OneDrive refreshing data will be easier, but I'm going to choose Local File and get a workbook from my computer.

 

I chose a list of the top 100 films, as measured by Rotten Tomatoes:

Dataset is ready

You'll see this message when your dataset is imported.

I now have a new dataset listed:

Imported dataset

I can click on this dataset to see its contents.

 

I can now select fields in my dataset to create a report in Power BI Service:

Creating report

I can create a report, just as I could in Power BI Desktop.

 

Starting to import Excel workbooks

If you follow the first few steps above to import a file and choose an Excel workbook instead of a CSV filie, you'll see this choice:

Import choices

You can import the Excel workbook's data, or upload the entire Excel workbook.

Uploading a workbook

If you choose the right-hand button (to upload the entire Excel workbook), you'll see it open in Power BI Service:

Excel online

Power BI Service will use Excel online to open and show the contents of your workbook (in this case, the shopping items I've bought recently).

 

This workbook is listed in a separate category in your workspace:

Excel workbooks

You can see the workbook you've uploaded in your workspace.

You can now pin parts of the workbook (be it ranges of data, charts or anything else) to a dashboard:

Pinning data

Here I'm pinning the first two columns of data.

The results can look strange ...

Pinned data

This would have looked better if I'd removed the title and subtitle from the tile.

 

The alternative: importing data

The other button we could have chosen was to import the data, not upload the entire workbook:

Import button

The button to import data from our workbook.

 

If you haven't got any tables in your workbook, you may then see this message:

No tables message

You'll see this message if you haven't created any tables in your workbook.

To get round this, open up your workbook, and choose to create a table for any block of data:

Creating a table

Click on any single cell in a table, and choose the ribbon option shown (or press CTRL + T).

 

Confirm that your table has column headers:

Column headers

Choose OK to create your table.

 

Apart from applying lots of slinky (and unnecessary) formatting, what this does is to create a special range name for your table:

Renaming your table

You can change the table name from the default Table1 on the DESIGN tab of the ribbon. I'm going to call mine Shopping.

 

When you repeat the import steps above, you'll be able to see a dataset for your workbook:

The workbook dataset

My workbook for this example was called Shopping history no tables, although this name became untrue ...

 

When you select the workbook, you'll see fields for each of the tables it contained:

Tables in field well

I created a second table in the workbook called Table2, just to illustrate that a workbook can contain more than one table.

 

We've just looked at how you can bring Excel workbooks into Power BI Service, but it is possible to publish all or part of a workbook from within Excel - the subject of the last part of this blog. 

This blog has 0 threads Add post