Import text from pdf files in Power BI

While Power BI will soon provide functions to import tables from pdf-files, there might be occasions when you actually need to import text from pdf files (in unstructured form). With a little help from R in Power BI you can do exactly that. (And don’t worry: No need to learn R here: The necessary R-code is already included in my function below. All you need is to have R installed your machine). Please also note that at the time of writing the refresh of these queries in the service is only supported with the personal gateway and not with the enterprise version.

Prerequisites

You can use the function below just like a normal M-function, just pass the (URL- or file-) path to it. All you have to take care of is that a instance of R is running on your machine. If this is new to you, check out Ruth Pozuelo’s video showing all the necessary steps: How to install R for Power BI

There is one package required: pdftools. The video above also shows how to install it.

Function

Import text from PDF files:

You can try calling this function for a pdf-file from the internet like the M formula language specification like this:

ImportPdfText("http://download.microsoft.com/download/8/1/A/81A62C9B-04D5-4B6D-B162-D28E4D848552/Power%20Query%20Formula%20Language%20Specification%20(October%202016).pdf")

If you want to import local files from your computer, just paste the full file-path instead of the URL. You don’t have to care about the direction of the slashes, both versions (forward and backwards) are accepted.

How to use

The script will return a table with one row for each page in the pdf-file by default. But it has an optional 2nd parameter that will return one row per pdf-text-line instead, if you put 1 into it. A page index and a row index will help navigating the result.

The 3rd parameter is an optional owner password for the pdf and the 4th the optional user password. If you’re using them, you have to enter null for the previous optional parameters. The following example shows how to use a user password while leaving the others “empty”:

ImportPdfText("MyPdfPath",null, null, "MyPassword")

Enjoy & stay queryious 🙂

Comments (12) Write a comment

  1. Pingback: Reutilizar funciones en Power Query – Power BI y Business Intelligence

  2. @Imke – Thanks – this would be a big help.
    Can a Password be passed as a parameter if the pdf file is password protected

    Reply

  3. This works really well. Thank you, Imke.
    And the youtube video by @Curbal is a nice icing on the cake.

    Reply

  4. Hi, i tried the function but couldn’t make it run correctly.
    R scripts are enabled and pdftools are installed.
    When the function is invoked, a null reference error is generated.
    Can you please provide guidance?
    Thanks

    Reply

    • Did you use one of the optional parameters and forgot to enter null for the preceeding ones that you didn’t use?
      Otherwise I need more details: Please post your exact syntax and the full text of the error message please.
      Thx, Imke

      Reply

  5. Pingback: PDF Tabellen in Power Query einlesen - PowerBI Pro

  6. Imke: Great solution. I wonder if that query can be used in excel in order to extract a bank statement from a PDF. I tried powerquery but it brings all the information in one column. Anyway you can help me? Thanks

    Reply

  7. That’s a great solution, thank you very much for sharing. Is it possible to remove passwords, as in my case I have to hit “Enter” every time Power BI reads new pdf (and I have tens and hundreds of them).
    And I am really interested if I can run this in Excel.

    Reply

    • Thanks Vlad!
      I cannot think of a way to remove the passwords.
      With regards to Excel: R-scripts don’t run in PQ in Excel (yet). So you have to use them in PBI and export your results to csv (with an R-script) to consume the results in Excel.

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz