Help Request. Creating custom function based on table. Cannot select my query for the new parameter?

Occasional Contributor

Excel Formulas and Functions BI & Data Analysis

(Did I do that right? I added the above labels so this post is discoverable. Anyhoo...)


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 "Where to select new query from source". From the source table I have tried creating the new query from both clickable locations. 


See attached "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 "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 "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!


0 Replies