Blog How To

Prepare and import your Excel workbook for Power BI

Before you carry on with the guide, I’d like to mention something to you

This post was written in 2017 and so much has changed since then. While the basics remain the same, there’s so much more you can do with PowerBI and Excel. I’m launching a brand new course in 2020 that will really simplify all there is to know and rocket fuel your knowledge

If you’d like to be part of this 30 minute, one on one course then register and I’ll send you some options of how we can help you

As we have covered many times before, Microsoft Excel remains one of the most widely used business applications in the world. Which makes it even easier for businesses to adopt Microsoft Power BI – as it is also one of the most common ways to get data into Power BI – so no need to learn to use new software or battle with file converters. However, before you import your Excel workbook to Power BI you will need to prepare it. But don’t worry, this is easy!

Here are the steps to prepare your workbook:

Step 1: Make your data flat

In order to use Excel workbooks as a data source for Power BI you will need to configure your data in a ‘flat’ format. This means that it must have no ‘totals’ rows or columns and all the data in each column must be the same type, eg. all dates, text, or currency. If your data is in an outline, matrix format, or pivot table you will also need to ‘flatten’ it in the same way.

Step 2: Format as a table

Once you have made your data flat, you will need to format it as a table. Simply select ‘Format as table’ on the Home tab, and choose from the selection of formats. There is also a shortcut, by pressing Ctrl + t, that will do the same thing. Ensure that the box that says ‘My table has headers’ is checked.

Step 3: Name your table

It is also a good idea to give your table a name, that way when you import it to Power BI you know what to look for. Also note that the name that you pick cannot contain any spaces.

Once you have followed these easy steps you will have successfully prepared your data for import into Power BI. Now it’s time to import your workbook, here’s how:

Two ways to import Excel data into Power BI

You can import your Excel workbook into Power BI from your local drive on a computer or from your OneDrive account. There are great benefits to using OneDrive (or OneDrive for Business), you will just need to sign into it with the same account that you use for Power BI. The benefit of this is that your work in Excel is always kept in sync with your dataset, reports, and dashboards in Power BI. Because both Power BI and OneDrive are cloud-based, Power BI automatically connects to your workbook file on OneDrive about every hour. If any changes have been made, your dataset, reports, and dashboards are automatically updated in Power BI.

Step 1: Open Power BI and click ‘Get Data’ in the navigation pane.

Step 2: In Files, click Get.

Step 3: Locate your file. If your workbook file is on OneDrive or SharePoint – Team Sites, choose Import or Connect.


What happens next is up to you!

Now you are ready to explore your data and reports through new eyes from within Power BI. Perhaps now is a good time to brush up on your Power BI knowledge; find out what Power BI apps could mean for your business>>