Power Query, Creating a custom function to automate steps?

Occasional Contributor

I am using Power Query to clean up some very messy data imported from a pdf. The pdf imports as several (300+) pages. Each page has its own query (... and I need to use the imported pages, not the imported tables, as they do not all import for some reason, but all pages do).  With help from others, I have determined the Applied Steps to transform a given page to a useful table that can be loaded to a worksheet. Next hurdle!

Automate these Applied Steps to all Page Queries imported. 

Any help or nudge toward this would be greatly appreciated!  

Below are my failed steps thus far:

I believe the path is to create a custom function. I have tried following these steps in the "Custom Function Example":


However, in my case I will use a Page from my source table instead of a binary like the example. 

See attached 1st pic, "Where to select new query from source". https://photos.app.goo.gl/gfVwjnWDrExHTnoS8

From the source table I have tried creating the new query from both clickable locations. 

See attached 2nd pic, "Query drop menu not active".


Both new queries are visible in the query list, one has data type of text, one has data type of table. But neither are available to select in the query drop menu when I create the new parameter as shown in the "Custom Function Example".

See attached 3rd pic, "Parameter with Current Value same as query".


I have tried to force it a bit by naming the current value the same as the table-type query created and creating the parameter called "Transform File". Then continuing the steps shown in the "Custom Function Example"

> From this "Transform File" parameter, create a Reference called "Tranform Sample File"

>From this "Transform Sample File" Reference, create function called "Transform File"

See attached 4th pic, "Query in new function not linked to table".


Then as shown in the "Custom Function Example", I thought at this point my Applied Steps would be used to transform the "Transform Sample File" query associated with this new function. But something is not right: there is no table data, the "Transform" menu options are different, I cannot manipulate the Advanced Editor, etc, etc.

Again, thank you to anyone willing to offer time and help!

I will add some additional background on the pdf in a reply below.

1 Reply
Additional Background on the pdf:

It's pretty diabolical. this 300+ pdf has a table on every page, of mostly, but not all the same size/shape. excel PQ naturally imports the PDF as each page is its own query, of course, and each table is it's own query. but not all tables are recognized and imported for some reason, but all pages are imported. so, thus far, I have based my transformation on the 300+ page queries.

at this point I have set of applied steps that I can manually copy into the advanced editor of any page query that transforms it to a useful table to later load to an excel worksheet.

I am trying to learn how to use a custom function to automate this manual copy-into-every-advanced-editor process.

(after which I will append all transformed queries to one giant table query, and load it to a worksheet. but the automated custom function is the current hurdle)