how do i extract data from a pdf file using power query in excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-2494076%22%20slang%3D%22en-US%22%3Ehow%20do%20i%20extract%20data%20from%20a%20pdf%20file%20using%20power%20query%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2494076%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%20team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20i%20would%20like%20to%20find%20out%20if%20there%20is%20a%20way%20to%20%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3Eextract%20data%20from%20a%20pdf%20file%3C%2FSTRONG%3E%20%3C%2FFONT%3Eusing%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%20power%20query%20in%20excel%3F%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%20color%3D%22%23000000%22%3Eregards!!%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2494076%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2494335%22%20slang%3D%22en-US%22%3ERe%3A%20how%20do%20i%20extract%20data%20from%20a%20pdf%20file%20using%20power%20query%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2494335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053787%22%20target%3D%22_blank%22%3E%40samuel_kodjoe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20on%20Windows%2C%20on%20Excel%20365%20and%20not%20on%20semi-annual%20channel%20you%20shall%20have%20this%20connector%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20677px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292117iF6C9D7235CE3563A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPerhaps%20these%20days%20availability%20bit%20wider%2C%20not%20sure.%20But%20in%20any%20case%20that's%20for%20subscription%20model.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2495833%22%20slang%3D%22en-US%22%3ERe%3A%20how%20do%20i%20extract%20data%20from%20a%20pdf%20file%20using%20power%20query%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2495833%22%20slang%3D%22en-US%22%3Ewow.....so%20there%20is%20no%20ad%20ins%20i%20can%20install%20to%20access%20this%20with%202019%20version%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2495952%22%20slang%3D%22en-US%22%3ERe%3A%20how%20do%20i%20extract%20data%20from%20a%20pdf%20file%20using%20power%20query%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2495952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053787%22%20target%3D%22_blank%22%3E%40samuel_kodjoe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECopy%2Fpaste%20the%20following%20code%20in%20a%20new%20blank%20query%20using%20the%20Advanced%20Editor%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Record.ToTable(%0A%20%20%20%20%20%20%20%20Record.RemoveFields(%23shared%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20Record.FieldNames(%23sections%5BSection1%5D)%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%2C%0A%20%20%20%20SelectedTypeFunction%20%3D%20Table.SelectRows(Source%2C%20each%20%5BValue%5D%20is%20function)%2C%0A%20%20%20%20RemovedValue%20%3D%20Table.SelectColumns(SelectedTypeFunction%2C%20%7B%22Name%22%7D)%2C%0A%20%20%20%20SortedByName%20%3D%20Table.Sort(RemovedValue%2C%20%7B%7B%22Name%22%2C%20Order.Ascending%7D%7D)%0Ain%0A%20%20%20%20SortedByName%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20load%20the%20result%20of%20that%20query%20on%20a%20sheet.%26nbsp%3BIf%20you%20find%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowerquery-m%2Fpdf-tables%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EPdf.Tables%3C%2FA%3E%26nbsp%3Bin%20column%20%5BName%5D%20the%20function%20is%20available%20in%20your%20version%20of%20Excel%2FPQ.%20Per%20its%20name%20the%20function%20is%20looking%20for%20%3CU%3Etable(s)%3C%2FU%3E%20in%20the%20PDF...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2496286%22%20slang%3D%22en-US%22%3ERe%3A%20how%20do%20i%20extract%20data%20from%20a%20pdf%20file%20using%20power%20query%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2496286%22%20slang%3D%22en-US%22%3Ehi%20Lz!%3CBR%20%2F%3EI%20saw%20Pdf%20Tables%20in%20column%20name%20after%20loading%20the%20code%2C%20how%20then%20do%20i%20locate%20it.%3CBR%20%2F%3EThanks%20for%20the%20feedback.%3C%2FLINGO-BODY%3E
Occasional Contributor

hi team,

 

Please i would like to find out if there is a way to extract data from a pdf file using power query in excel?

 

regards!!

10 Replies

@samuel_kodjoe 

If you are on Windows, on Excel 365 and not on semi-annual channel you shall have this connector

image.png

Perhaps these days availability bit wider, not sure. But in any case that's for subscription model.

wow.....so there is no ad ins i can install to access this with 2019 version?

@samuel_kodjoe 

Copy/paste the following code in a new blank query using the Advanced Editor

let
    Source = Record.ToTable(
        Record.RemoveFields(#shared,
            Record.FieldNames(#sections[Section1])
        )
    ),
    SelectedTypeFunction = Table.SelectRows(Source, each [Value] is function),
    RemovedValue = Table.SelectColumns(SelectedTypeFunction, {"Name"}),
    SortedByName = Table.Sort(RemovedValue, {{"Name", Order.Ascending}})
in
    SortedByName

 

then load the result of that query on a sheet. If you find Pdf.Tables in column [Name] the function is available in your version of Excel/PQ. Per its name the function is looking for table(s) in the PDF...

hi Lz!
I saw Pdf Tables in column name after loading the code, how then do i locate it.
Thanks for the feedback.

@samuel_kodjoe 

Given you don't have the corresponding wizard in Excel user interface, you have to code it yourself. You start with a new blank query and in the formula bar, something like:

= Pdf.Tables(File.Contents("FolderPath\Example.pdf"), [Implementation="1.2"])

Then, assuming the function finds a Table in your PDF, click on it in the [Data] column:

 

Demo.png

NB: re. [Implementation=x.y] the Pdf.Tables doc. says:

  • The newest version should always give the best results
    I've seen a couple of cases where this wasn't true so you have to test what gives you the best result based on your PDF
  • Valid values are "1.3", "1.2", "1.1", or null 
    1.3 isn't avail. yet. Please refer to DanielPerelman's reply on this thread

please this is what i get trying to implement for 1.2. the other values are giving me invalid answers

Carefully read the error message you get, it clearly indicates what the problem is. I checked the formula you entered and it's OK (no syntaxt error nor anything like that)

@L z. 

Small comment, 1.3 is available now. Not sure about exact PQ version.

On Current Channel v2106 b14131.20278 (latest Click-to-Run) "1.3" is still not avail.
(Same goes with PBI Desktop v2.94.921.0 - June 2021)
If you have it on the Insider Channel it should land here in a few weeks...

@L z. 

Yes, that's PQ 2.95.223.0 on insiders channel.