BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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.
- Working with Excel in Power BI Service
- Analysing Data with Excel
- Getting Data from Excel or CSV files in Power BI Service (this blog)
- Publishing Excel workbooks, or tiling parts of a workbook
This blog is part of a 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:

Click on this tool to load data.
Choose to bring in data from a file:

Choose from where you want to get data.
You can now say where this file is stored:

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:

You'll see this message when your dataset is imported.
I now have a new dataset listed:

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:

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:

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:

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:

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:

Here I'm pinning the first two columns of data.
The results can look strange ...

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:

The button to import data from our workbook.
If you haven't got any tables in your workbook, you may then see this 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:

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:

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:

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:

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:

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.
- Working with Excel in Power BI Service
- Analysing Data with Excel
- Getting Data from Excel or CSV files in Power BI Service (this blog)
- Publishing Excel workbooks, or tiling parts of a workbook