Can the list of Tables presented by a Function be filtered?

[jdellasala]

I have a PQ Function that starts like this:

(FredURL as table) =>
let
    SrcURL = FredURL{0}[Column1],

The function works great, but when invoked, a list of ALL tables in the Workbook is presented. Is there a way to filter that list so that only table names that start with "url" are presented?

This relates to my post here which I've already posted one update for.

Your help is appreciated, as is your feedback on the article.

 

[GraH]

Can't you filter the list of tables before invoking the function?

 

[jdellasala]

Can't you filter the list of tables before invoking the function?

Tell me how. The parameter is the first line of the function.

 

[jdellasala]

So I figured out how to get a table of the queries I want and the values associated with them:

let
    Source = #shared,
    ConvertedToTable = Record.ToTable(Source),
    FilteredRowsStartWithurl = Table.SelectRows(ConvertedToTable, each Text.StartsWith([Name], "url")),
    FilteredOutURLFred = Table.SelectRows(FilteredRowsStartWithurl, each ([Name] <> "urlFRED")),
    ExpandedValue = Table.ExpandTableColumn(FilteredOutURLFred, "Value", {"Column1"}, {"URL"})
in
    ExpandedValue

That results in this table:

Name	URL
urlRealGDP	https://api.stlouisfed.org/fred/series/observations?series_id=A191RL1Q225SBEA&frequency=q&observation_start=1959-01-01&observation_end=2023-01-01&api_key=3b1c0dca84ae9431560c2ac624a16aee
urlUNRATE	https://api.stlouisfed.org/fred/series/observations?series_id=UNRATE&frequency=q&observation_start=1959-01-01&observation_end=2023-01-01&api_key=3b1c0dca84ae9431560c2ac624a16aee
urlPCEPI	https://api.stlouisfed.org/fred/series/observations?series_id=PCEPI&frequency=q&observation_start=1959-01-01&observation_end=2023-01-01&api_key=3b1c0dca84ae9431560c2ac624a16aee
urlPCEPILFE	https://api.stlouisfed.org/fred/series/observations?series_id=PCEPILFE&frequency=q&observation_start=1959-01-01&observation_end=2023-01-01&api_key=3b1c0dca84ae9431560c2ac624a16aee
urlDFF	https://api.stlouisfed.org/fred/series/observations?series_id=DFF&frequency=q&observation_start=1959-01-01&observation_end=2023-01-01&api_key=3b1c0dca84ae9431560c2ac624a16aee

I'd like to use the Name column to provide a list for a function which would then use the URL of the Name selected. I've tried several ways, but none are working.
As I said earlier, the function now uses the parameter

(FredURL as table) =>

When the function is invoked all of the queries that produce a Table are listed. I only want the names of the list above to be presented. I want the function to present only the Name from the query above.

 

[RoryA]

It seems to me you shouldn't be invoking the function directly since it expects you to pass a table as a parameter. You should be calling the function from another query and passing whatever table you need (e.g. the ExpandedValue table above).

 

[jdellasala]

It seems to me you shouldn't be invoking the function directly since it expects you to pass a table as a parameter. You should be calling the function from another query and passing whatever table you need (e.g. the ExpandedValue table above).

Thanks Roy. That's kind of my ask. I don't see how to do that. I looked at using a parameter, but a parameter has no access to a Table object. I might be able to do a text string since that's really what I'm after with the function which uses {0}[Column1] of the table, but I don't see a way to provide that automatically.
Can you provide an example?

 

[RoryA]

You haven't really explained how you plan to use it. You could alter the function so that it takes a text argument, then simply call it from an additional column in the query in post 4. You'd then have a table returned for each URL.

 

[jdellasala]

I created a template that creates a URL to fetch data using an API which I posted here and updated here.
The intent is that columns B and C in the update are copied to a new sheet, and then the cell with the constructed URL is given a Name that starts with "url", it is brought into Power Query. Rather than save the URL as a string, it's saved as a table, and then the function's parameter requests a table name, and since it's a standard single column, single row table uses urlTable{0}[Column1] to extract the URL and process its response.
I'm able to generate a table of URL Names and their associated URLs like this:

let
    Source = #shared,
    ConvertedToTable = Record.ToTable(Source),
    FilteredRows = Table.SelectRows(ConvertedToTable, each Text.StartsWith([Name], "url")),
    ExpandedValue = Table.ExpandTableColumn(FilteredRows, "Value", {"Column1"}, {"Column1"})
in
    ExpandedValue

That results in a table like this (with more rows):

Name	Column1
urlFRED	https://api.stlouisfed.org/fred/series/observations?series_id=UNRATE&frequency=m&api_key=abcdefghijklmnopqrstuvwxyz123456

but I haven't been able to find out how that can be used to use as the parameter of a PQ Function.

 

[RoryA]

I haven't been able to find out how that can be used to use as the parameter of a PQ Function.

You need another query that calls the function passing the relevant table as a parameter. (just like a parameterised function in VBA or any other language).

 

[jdellasala]

You need another query that calls the function passing the relevant table as a parameter. (just like a parameterised function in VBA or any other language).

Assuming the table of URL Names and URLs is called URLTable, how would a function use that so that when the function was called from the Queries & Connections pane, that table, or preferably just the Names in that table, were presented to the user. As it stands now, all tables in the Workbook are presented.