Can Power BI Extract Data from PDF?

Trying to pull data from a PDF can feel like trying to get ketchup out of a glass bottle - frustrating and messy. The good news is that Power BI can actually extract data directly from PDF files. This article will walk you through exactly how to do it, step-by-step, and also cover the common pitfalls and best practices to make the process as painless as possible.

Yes, Power BI Can Extract Data from a PDF... With Some Caveats

The short answer is yes. Power BI has a built-in connector designed specifically for PDF files. This means you don't need any complex workarounds or third-party tools to get started. You can point Power BI at a PDF on your computer, and it will do its best to identify tables and data within the document.

However, there's a big "but" attached to this capability. The success of the extraction heavily depends on the type of PDF you have:

Digitally Created PDFs (The Good): These are documents created directly from applications like Word, Excel, or reporting systems. The text and tables inside are machine-readable, making them ideal candidates for Power BI. Invoices, financial statements, and exported reports often fall into this category.
Scanned PDFs (The Bad): These are essentially images of documents. If someone physically scanned a paper document to create the PDF, Power BI won't be able to read it. The software sees pixels, not text and numbers. You would need to use a separate OCR (Optical Character Recognition) tool to convert the image to text before Power BI could even attempt to analyze it.

This guide focuses on the first type: digitally created, text-based PDFs with structured tables. If that's what you're working with, let's get that data into Power BI.

How to Connect and Extract PDF Data in Power BI: A Step-by-Step Guide

Connecting to a PDF is surprisingly straightforward. The real work comes in cleaning the data afterward. Here’s how to get the data loaded.

Step 1: Open Power BI and Select "Get Data"

Launch Power BI Desktop. In the "Home" tab of the ribbon, click on the Get Data icon. If you don't see the PDF option immediately, click "More..." to open the full list of data source connectors.

Step 2: Choose the PDF Connector

In the Get Data window, you can either select "File" from the list on the left and then pick "PDF," or simply type "PDF" into the search bar. Select "PDF" and click Connect.

Step 3: Locate and Open Your PDF File

A file browser window will pop up. Navigate to the location of your PDF file, select it, and click Open.

Step 4: Use the Navigator to Select Your Data

After a few moments, Power BI will analyze the document and present you with the Navigator window. This is where you see everything Power BI was able to identify within the PDF.

You’ll see a list of tables and pages. Here's how to interpret what you're seeing:

Tables (e.g., Table001, Table002): Power BI automatically identifies structures that look like tables. It displays each one as a separate option. This is almost always the best place to start.
Pages (e.g., Page 1, Page 2): It also gives you the option to import the entire raw text from a page. This tends to be much messier and is generally only useful if the table detection failed.

Click on one of the tables in the list on the left to see a preview on the right. Usually, you can tell right away if it's the data you're looking for. Select the checkbox next to the table(s) you want to import.

Step 5: Load or Transform the Data

At the bottom of the Navigator window, you have two main choices:

Load: This option will load the data directly into your Power BI data model as-is. You almost never want to do this with PDF data. It’s rarely perfect right out of the box.
Transform Data: This is the correct choice 99% of the time. It opens the Power Query Editor, which is Power BI’s powerful data cleaning and preparation tool. This is where you'll make the messy data usable.

Click Transform Data to proceed.

Cleaning PDF Data in the Power Query Editor

Welcome to Power Query, your best friend for wrangling unruly data. The preview you see here is where you’ll shape the information into something clean and ready for analysis. Here are the most common cleaning steps you'll need to perform on data imported from a PDF.

1. Promote Headers

Often, Power BI won't recognize the first row of your table as the column headers. It will instead give them generic names like Column1, Column2, etc. To fix this, go to the "Home" tab in Power Query and click Use First Row as Headers. This will promote that first row to be the proper column names.

2. Remove Unnecessary Rows

PDFs often contain blank rows or summary rows (like "Total" or "Subtotal") that you don't want included in your dataset for calculations. You can easily filter these out.

To remove blank rows, click the filter arrow on a column, and click Remove Empty.
To remove specific summary rows, use the filter to uncheck the box next to "Total" or any other value you want to exclude.

3. Change Data Types

Power Query often misinterprets the data types from a PDF. A column of numbers might be imported as text, and a date column might be seen the same way. This prevents you from doing calculations or creating proper date-based charts.

Look at the icon next to each column header. "ABC" means text, "123" means whole number, and a calendar means date. Click the icon to change the data type to the correct format for each column.

4. Handle Multi-Page Tables

What if you have one large table that spans multiple pages in the PDF? On each new page, the header row likely repeats. You’ll need to filter these out.

Let's say your header is "Product Name." Click the filter on that column and uncheck the value "Product Name." This will remove all the repeated header rows that were pulled into your data.

Once you are happy with your cleaned data in Power Query, click Close & Apply in the top-left corner. Your cleaned, transformed data will now be loaded into Power BI, ready for you to build visuals.

Limitations and Common Challenges

While the connector is powerful, it's not a magic wand. Here are the common issues you will face:

Inconsistent table structures: If the columns or layout change slightly from page to page, Power BI can get confused.
Merged cells: Merged cells in reports are a nightmare for data extraction tools and almost always result in null values and misaligned columns that require manual fixing in Power Query.
No real-time data: This is the biggest drawback. A PDF is a static snapshot. If a new monthly report is generated, you must save the new PDF (often with the exact same file name and location), go into Power BI, and hit "Refresh." It is not a live data connection.
Headers and footers: Page numbers, report titles, and dates in the header or footer can get accidentally pulled into your data and need to be filtered out.

Best Practices for Working with PDF Data

To save yourself some headaches, follow these simple tips:

Ask for the source file first. The most important rule is to avoid using a PDF if you can. If the PDF was generated from Excel, a database, or another business application, always ask for the original source file (like an .XLSX or .CSV). This will give you perfectly clean, structured data and save you hours of cleanup.
Use well-structured PDFs. The cleaner and more "boring" the table in the PDF, the better it will work. Simple rows and columns are ideal.
Start simply. Connect to a single table on a single page first to understand how Power BI is interpreting it before trying to combine data from a 100-page document.
Document your Power Query steps. Power Query records every transformation you make under "Applied Steps." If you get an updated PDF next month, you can just refresh the query, and it will re-apply all your cleaning steps automatically.

Final Thoughts

So, can Power BI extract data from a PDF? Absolutely. For one-off analyses of reports that only exist in PDF format, its built-in connector combined with the Power Query Editor is an incredibly effective tool. It turns a frustrating manual copy-paste job into a repeatable, automated process.

Manually wrangling data from various platforms - whether they’re PDFs or disconnected apps - is often the most painful part of creating useful reports. We built Graphed because we believe getting insights shouldn't require complex data cleaning or a deep understanding of BI tools. We connect directly to your data sources like Google Analytics, Shopify, and Salesforce, allowing you to ask questions in plain English and create real-time dashboards in seconds, skipping the manual data prep entirely.