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.