CODEWITHSUNDEEP
excelpowerquerym
Bjorn Morrhaye
Bjorn Morrhaye

Reputation: 731

Excel convert parameter as text to query name?

I have a function that takes some text input and I want to convert it to a query name. So source2 should be the query #"input text". How do i do this ? I have searched the web everywhere and cannot find the answer.

let
    fnGetLeague = (leagueName as text) =>
    let
        Source = #"Matches Today",
        Source2 = #"leagueName",
        join = Table.NestedJoin(Source, "homeTeam.team_id",Source2, "team_id", "leagueData" )
        
    in
        join
in
    fnGetLeague

my main goal is to join some data from a specific query (different leagues) inside my "Matches Today" Query.

In my "matches today" query i have a column called league, so i want to pass this in as a variable in my function, assign it to the correct query, and join some data from that specific query inside my matches today query.

enter image description here

enter image description here

In the end there will be many league queries, so dynamically pointing to the right query is my main goal.

i want to avoid using power pivot at this moment because it's so slow.

Upvotes: 1

Views: 770

Answers (2)

Mistella
Mistella

Reputation: 1728

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

table options

enter image description here

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.

enter image description here

enter image description here

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.

    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

Upvotes: 2

Mike Honey
Mike Honey

Reputation: 15027

I would build a query that appends all the data from all the leagues together, with each set of rows having a leagueName column filled with the appropriate value e.g "Greece - Super League 2".

Then I would add a Filter Rows step that references your leagueName parameter, to restrict the output to just the rows for that league.

Upvotes: 0

Related Questions