Forum Discussion

Ollie98's avatar
Ollie98
New Member
3 years ago

Missing Row data from pdf to Excel

Good morning,

 

I'm having some issues with importing data from PDF to Excel using PowerQuery as the error message displays the error message: 'data may be missing' when recognising the tables in the PDF and have caused the data to be missed when transformed.

 

I would just like to know if there is a way around this?

 

Thank you for your help,

Ollie

General Comment
Need Help
Show and Tell

1 Reply

  • jennratten's avatar
    jennratten
    Super User
    3 years ago

    Hello - there are many reasons why Power Query may not locate all of your intended data.  This can include things like the quality of the PDF, tables spanning multiple pages, whether or not the tables have borders, etc.  I recommend you try using some of the optional arguments in the Pdf.Tables connection. 

    Pdf.Tables(pdf as binary, optional options as nullable record) as table

    Returns any tables found in pdf. An optional record parameter, options, may be provided to specify additional properties. The record can contain the following fields:

    • Implementation: The version of the algorithm to use when identifying tables. Old versions are available only for backwards compatibility, to prevent old queries from being broken by algorithm updates. The newest version should always give the best results. Valid values are "1.3", "1.2", "1.1", or null.
    • StartPage: Specifies the first page in the range of pages to examine. Default: 1.
    • EndPage: Specifies the last page in the range of pages to examine. Default: the last page of the document.
    • MultiPageTables: Controls whether similar tables on consecutive pages will be automatically combined into a single table. Default: true.
    • EnforceBorderLines: Controls whether border lines are always enforced as cell boundaries (when true), or simply used as one hint among many for determining cell boundaries (when false). Default: false.

    You can include the options like this:

    Pdf.Tables(File.Contents("c:\sample.pdf"),[Implementation=1.3, StartPage=5, EndPage=20, MultiPageTables=true, EnforceBorderLines=false])