Import Tables from PDF into Excel

I am trying to import multiple PDF tables into Excel but the table appear on different pages. In one pdf (2020.pdf), the table can appear on Page 1, in 2021.pdf the table appears on Page 3 and in 2022.pdf the table appears in page 5. In this case the table headers are the same. How can I use Excel VBA to find this table header and pull these tables into excel? I have tried Power Query but it did not work for me.

I have attached a sample PDFs by putting the tables on random pages, and the excel "output.xls" sheet is result that I am looking for.

Overall, I plan to read through a folder that has about 700 pdf files with these tables but these tables do no appear on specific pages.
Thanks for the anticipated help.

Try

let PullPDF = (variable)=> let
Source = Pdf.Tables(File.Contents(variable), [Implementation="1.3"]),
List = List.Union(List.Transform(Source[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", List,List) ,
Row=Table.RemoveFirstN(#"Expanded Data",List.PositionOf(#"Expanded Data"[Column2],"Volume")),
#"Removed Other Columns" = Table.SelectColumns(Row,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"A3 Unit", type text}, {"Volume", Int64.Type}, {"∆V", Int64.Type}, {"Method", type text}, {"Volume_1", Int64.Type}, {"∆V_2", Int64.Type}, {"Delta#(lf)Volume", Int64.Type}})
in #"Changed Type",


Source2 = Folder.Files("c:\temp5"),
#"Filtered Rows" = Table.SelectRows(Source2, each ([Extension] = ".pdf")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Data", each PullPDF([Folder Path]&[Name])),
List = List.Union(List.Transform(#"Added Custom"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", List,List)
in #"Expanded Data"

How do I implement this? Do I put it in VBA?

Originally Posted by horseyride

Try

let PullPDF = (variable)=> let
Source = Pdf.Tables(File.Contents(variable), [Implementation="1.3"]),
List = List.Union(List.Transform(Source[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", List,List) ,
Row=Table.RemoveFirstN(#"Expanded Data",List.PositionOf(#"Expanded Data"[Column2],"Volume")),
#"Removed Other Columns" = Table.SelectColumns(Row,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"A3 Unit", type text}, {"Volume", Int64.Type}, {"∆V", Int64.Type}, {"Method", type text}, {"Volume_1", Int64.Type}, {"∆V_2", Int64.Type}, {"Delta#(lf)Volume", Int64.Type}})
in #"Changed Type",


Source2 = Folder.Files("c:\temp5"),
#"Filtered Rows" = Table.SelectRows(Source2, each ([Extension] = ".pdf")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Data", each PullPDF([Folder Path]&[Name])),
List = List.Union(List.Transform(#"Added Custom"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", List,List)
in #"Expanded Data"

WOW...I tried it in PowerQuery and it worked.