How to Import Excel Data in Power BI Report Builder

Connecting an Excel spreadsheet to Power BI Report Builder is a straightforward way to create pixel-perfect, printable reports like invoices, statements, or inventory lists. This guide walks you through the exact steps, from preparing your Excel file to troubleshooting common connection errors.

Why Use Excel with Power BI Report Builder?

While Power BI Desktop is designed for interactive dashboards, Power BI Report Builder specializes in creating paginated reports. These are static, multi-page documents optimized for being printed or exported as PDFs. Think of catalogs, financial statements, or formal letters where precise formatting and layout are essential.

Since so much business data starts or ends up in Excel, knowing how to use it as a data source for these structured reports is a foundational skill. You can leverage the simplicity of a spreadsheet while gaining the powerful formatting and distribution capabilities of Report Builder.

First, Prepare Your Excel File for Success

Before you even open Report Builder, a few minutes spent preparing your Excel file will save you a lot of trouble later. A clean, well-structured data source is the key to a smooth import process.

1. Format Your Data as a Table

The single most important step you can take is to format your data range as an official Excel Table. This gives your data set a defined name and structure that Report Builder can easily reference.

How to do it:

• Click anywhere inside your data range.
• Go to the Insert tab on the ribbon and click Table.
• Ensure the correct range is selected and check the box for "My table has headers."
• Click OK.
• Optional but recommended: Go to the "Table Design" tab that appears and give your table a descriptive name (e.g., "SalesData" instead of "Table1").

2. Clean Your Column Headers

Simple headers work best. Make sure your column headers are in the very first row of your table and follow these rules:

• No merged cells: Each column needs its own single-cell header.
• No special characters: Avoid characters like (, ), %, /. Use letters, numbers, and underscores if needed.
• Keep them unique: Don't use the same header for multiple columns.

3. Check for Data Consistency

A quick scan of your data will prevent common issues. Look for:

• Empty Rows and Columns: Delete any completely blank rows or columns within your table.
• Mixed Data Types: Ensure a column for dates only contains dates and a column for sales figures only contains numbers. Inconsistent data can cause import errors or incorrect data types in your report.

Once your Excel file is clean and saved, you're ready to connect it in Power BI Report Builder.

Step-by-Step Guide to Importing Excel Data

With your prepared file ready, launch Power BI Report Builder. The process involves creating a Data Source (the connection to your file) and then a Dataset (the specific data you want to use from that file).

Step 1: Create a New Data Source

The Data Source tells your report where to find the Excel file. In the Report Data pane on the left side (if you don't see it, go to View > Report Data), get started:

1. Right-click the Data Sources folder and select Add Data Source.
2. Give your data source a descriptive name, like Excel_Sales_DataSource.
3. For the connection type, select OLE DB. This is the most reliable method for connecting to Excel files.
4. Click the Build... button to configure the connection string.

This will open the "Connection Properties" window.

Configuring the OLE DB Connection

This is the most technical part, but it's simple once you know which settings to choose.

1. On the Provider tab, select Microsoft Office 12.0 Access Database Engine OLE DB Provider.
2. Click on the All tab to enter the detailed properties.
3. Double-click on Data Source and enter the full file path to your saved Excel file (e.g., C:\Users\YourName\Documents\SalesData.xlsx).
4. Double-click on Extended Properties. This tells the driver you're connecting to an Excel file. Use the following value:

Excel 12.0 Xml,HDR=YES

• Excel 12.0 Xml specifies the modern Excel format (.xlsx).
• HDR=YES tells the driver that your first row contains headers.

1. Click OK. You can use the Test Connection button to verify everything is working. If successful, click OK to close the data source properties.

Step 2: Create a New Dataset

Now that Report Builder knows where the file is, you need to tell it what data to pull from it. This is done with a Dataset.

1. In the Report Data pane, right-click the Datasets folder and select Add Dataset.
2. Give your dataset a clear name, like SalesData.
3. Ensure the "Data source" dropdown points to the Excel_Sales_DataSource you just created.
4. Under "Query type," leave Text selected. You will now write a simple query to select your data.

Writing Your Query

You use a variation of SQL to pull data from an Excel workbook.

• To pull data from a worksheet: Use the sheet name followed by a dollar sign $ and surrounded by square brackets. Example: SELECT * FROM [Sheet1$]
• To pull data from a named table (Best Practice): Use the table name surrounded by square brackets. This is why we named our table in the preparation step. Example: SELECT * FROM [SalesData]

In the query box, enter the command for your named table:

SELECT * FROM [SalesData]

You can go to the Fields tab on the left to confirm that Report Builder has successfully read the columns from your Excel table. If they appear there, you're all set! Click OK.

You will now see your dataset and all its fields available in the Report Data pane, ready to be used in your report.

Using Your Excel Data in the Report

With the connection complete, building the report is easy.

1. From the Insert tab on the ribbon, choose Table, then click Table Wizard.
2. Select your new SalesData dataset and click next.
3. Drag the fields you want to display from the "Available fields" box into the "Values" box.
4. Follow the remaining steps to choose layouts and styles.

Alternatively, you can insert a table and drag fields directly from the Report Data pane into the columns of the table on your report canvas.

Troubleshooting Common Connection Issues

Sometimes things don't go perfectly. Here are solutions for a few common roadblocks.

• Error: "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered..." This is the most common error. It means the Access Database Engine driver isn't installed or its bit version (32-bit vs. 64-bit) doesn't match your Report Builder installation. Download and install the correct version to fix it.
• Error: "External table is not in the expected format." This usually points to a mismatch between your Excel file and the connection string. Make sure you are using Excel 12.0 Xml for .xlsx files or Excel 8.0 for older .xls files. It could also indicate a corrupted Excel file.
• Data Appears Incorrectly (e.g., Dates as Numbers) Report Builder can sometimes misinterpret data types. Go to your dataset properties, select the Fields tab, and you can manually change the data type for any field. You can also use TextBox formatting properties within the report itself to control how numbers and dates are displayed.
• File Path "network source not available" issues If you move or rename your Excel file, the connection will break. If using it in a deployed environment, ensure the file is on a shared network drive with a consistent path (UNC paths like \\ServerName\Share\File.xlsx are more reliable than mapped drive letters).

Final Thoughts

Connecting Excel to Power BI Report Builder is a process of preparing your file and then configuring an OLE DB data source to read it. Once you've done it a few times, it becomes a quick and efficient way to produce professionally formatted paginated reports from your most common data source.

While this is perfect for specific reporting tasks, manually pulling data from Excel is just one piece of a much larger analytics puzzle. Building a full picture often means trying to stitch together data from Excel, Google Analytics, Shopify, and your CRM - a time-consuming process that often leads to stale reports. Honestly, this manual reporting drudgery is exactly why we built Graphed. We connect all your marketing and sales data sources automatically, so you can build real-time dashboards and get answers instantly just by talking to our platform in plain English. No more downloading CSVs or building OLE DB connection strings required.