Excel convert parameter as text to query name?

Answer to question-as-asked

To reference a query via the text of its name, requires using a fairly well-hidden intrinsic variable - #sections. You can read the documentation, but I personally found this Q&A about lists of queries more helpful. As noted in the Q&A, do be cautious about potential recursion issues when using #sections.

For your function in particular, changing Source2 as shown below should work. However, be aware that the function will error out if there are no queries with the specified name.

let
    fnGetLeague = (leagueName as text) =>
    let
        Source = #"Matches Today",
        Source2 = List.First(Table.SelectRows(Record.ToTable(#sections[Section1]), each [Name] = leagueName)[Value]),
        join = Table.NestedJoin(Source, "homeTeam.team_id",Source2, "team_id", "leagueData" )
        
    in
        join
in
    fnGetLeague

Note: This function will return a new query that does a left outer join from #"Matches Today" to the specified query. This will not alter #"Matches Today" in any way. If you want to add data from the league-specific queries directly to #"Matches Today" (instead of to a "copy"), then I would recommend one of the methods below.

Alternative

Change your function as shown below, then invoke the function through the table options in the #"Matches Today" query. This will add the data to the #"Matches Today" query instead of creating a new query.

let
    fnGetLeague = (leagueName as text, teamID as number) =>
    let
        Source = Record.ToTable(#sections[Section1]),
        LeagueTable = List.First(Table.SelectRows(Source, each [Name] = leagueName)[Value]),
        LeagueTeam = if Value.Is(LeagueTable, type table) then Table.SelectRows(LeagueTable, each [team_id] = teamID) else null
    
    in
        LeagueTeam
in
    fnGetLeague

Invoking a Custom Function

Troubleshooting

Depending on the function results, you may not see the "table-column" options (Expand/Aggregate). If so, you can sometimes force it by adding , type table in the end of the Table.AddColumn() function (created when invoking custom function) as shown below.

If the expand/aggregate button is visible, but attempting to use it results in a

No columns were found

message, then there are two possible options:

1. Manually add the appropriate expand/aggregate step.
2. Create a "blank" table to be returned by the function instead of null

Manual steps

Expansion

Adding a manual expansion has a few, mostly simple, steps:

1. Add the custom step. This can be accomplished be right-clicking the step you want the manual step to follow, then clicking the "Insert Step" option.

   

2. The custom step will have the equation = #"Previous Step" (where #"Previous Step" will be the table from the previous step. If you added the custom step directly after the source, the equation will be = Source, etc.).

3. Replace the existing equation in the custom step with the Table.ExpandTableColumn() equation (PQ Doc). For example, if I wanted to expand the columns "count" and "Price" from the existing table column "Data" (and assuming the previous step is "Grouped Data") then the expansion equation would look like

   = Table.ExpandTableColumn(#"Grouped Data", "Data", {"count", "Price"}) 
   

   and if I wanted "count" to be renamed to "units", the equation would be

   = Table.ExpandTableColumn(#"Grouped Data", "Data", {"count", "Price"}, {"units", "Price"})
   

Aggregation

For Aggregating, there are two options:

1. Expanding the relevant columns, then add a grouping step
2. Use the Table.AggregateTableColumn() equation (PQ Doc)

For example, finding max "Price" from column "Data" (previous step is "Grouped Data") would be:

= Table.AggregateTableColumn(#"Grouped Data", "Data", {{"Price", List.Max, "max of Price"}})

There are more examples in the documentation.

Blank table

This method may be best if the columns you're interested in won't be changing, but the potential aggregations or expansions might. i.e. if the format and column names of the league tables are mostly consistent between tables, and shouldn't be changing.

Instead of having the custom function return null, it should return a "blank" table - a table with the desired columns, but no rows. For example, if the "blank" table should have a "price" column and a "name" column, the code for the blank table would be:

#table(type table [#"price"=number, #"name"=text], {})

However, since expanding a column with an empty table results in a row of null values, it would be equivalent to use:

#table(type table [#"price"=number, #"name"=text], {{null, null}})

You could also choose to seed the table with a zero or an empty string:

#table(type table [#"price"=number, #"name"=text], {{0, ""}})

Below, I have a copy of the custom function returning a blank table with columns {price, name} instead of null in the LeagueTeam line of code:

let
    fnGetLeague = (leagueName as text, teamID as number) =>
    let
        Source = Record.ToTable(#sections[Section1]),
        LeagueTable = List.First(Table.SelectRows(Source, each [Name] = leagueName)[Value]),
        LeagueTeam = if Value.Is(LeagueTable, type table) then Table.SelectRows(LeagueTable, each [team_id] = teamID) else #table(type table [#"price"=number, #"name"=text], {})
    
    in
        LeagueTeam
in
    fnGetLeague