PDF to Excel in Power Query automated steps

[MwanunduSai]

Apologies as i am new to the community.

I have a real head scratcher and any assistance would be well appreciated. 

Summary.

I have a pdf with 151 (not significant for my initial question) pages of financial reports. Each page consists of a different report with identical format which i need to get to excel and place in a merged table through Power Query.

sample of 1 of the pdf pages:

 When i bring the pdf into Power Query, each page as the screeshot above is becoming 2 tables.

All of the info that i need is in the first table. (table 2 is not necessary)

Basically I am choosing the first table of every page which are the relevant tables (the odd ones 1,3,5,7,9,11 etc, 309) to transform data.

First Question: Is it possible to automate this step and write some code somewhere (possibly macro) to transform data for just the odd tables that have been created?

Once in the PQE, I am manipulating the data (Removing columns, cleaning up the data, transposing the table). With the steps taken from Advanced editor i understand how to do it for each and every table (copy and paste while changing the table number to match the table).

Question 2: Is it possible to automate these repeated steps without having to manually enter the Advanced editor for each table?

After all of this i am looking to merge all of these tables and take them to excel to begin my analysis.

Is it possible for all of the steps above to be done in as mentioned and possibly by writing a VBA script which when prompted for the PDF then does all the steps above.

Grateful if any guidance can be given to me so i can start to search for a solution for this problem.

[lbendlin]

The usual approach would be to generate a list of all pages from the PDF, and then call the web contents function for each page separately, After that you can combine the results into a single result set.

[MwanunduSai · Accepted solution]

I ended up finding it easier to parse the pdf into separate pdfs and then load folder. That way all transformations i did on the sample table were repeated on all tables. I was then able to merge. Was quite a struggle to get there but i made it in the end. 

Thanks for your input.