Excel Power Query: Variables for Table Name

Question

I'm trying to achieve something that seems like it should be fairly simple but I can't find an answer for... replace the name of a table or power query with a variable.

Currently trying to do this with a merge query so it would look something like this: Table.NestedJoin(VARIABLE1,key1,VARIABLE2,key2,"Append",JoinKind.Inner)

Currently getting all sorts of errors no matter what I try...

Thank you!

// Edit: Not really looking to do a function - hoping for users to utilize as easy as possible so they would be able to update a named table in the workbook, refresh, and then get a table as an output. Here is my current code - hopefully that'll help. My Region code replacements worked fine, but the Days replacements don't - I need each day (Monday-Thursday) to be replaced with my day variables (StartDay, Day2, etc.). Each of those has a separate text query referring back to the excel workbook inputs, and each of them should pull up a query based on the text (ex: StartDay = Monday so should pull the Monday query). This is the error I get, assuming that it is reading it as text "Monday" and not query Monday.

Expression.Error: We cannot convert the value "Monday" to type Table. Details: Value=Monday Type=Type

let

ANDOriginCode = OriginRegion,
ANDDestinationCode = DestinationRegion,
ANDStartDay = StartDay,
ANDDay2 = Day2,
ANDDay3 = Day3,
ANDDay4 = Day4,
ANDDay5 = Day5,
Source = Table.NestedJoin(Monday,{"Tuesday Destination Region Code"},Tuesday,{"Tuesday Origin Region Code"},"Append1 (3)",JoinKind.Inner),
#"Filtered Rows1" = Table.SelectRows(Source, each [Monday Origin Region Code] = OriginRegion),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"ID", "Pickup Day of Week", "Delivery Day of Week"}),
#"Expanded Append1 (3)" = Table.ExpandTableColumn(#"Removed Columns", "Append1 (3)", {"Tuesday Origin Region Code", "Wednesday Destination Region Code", "Tuesday Projected Number of Loads"}, {"Tuesday Origin Region Code", "Wednesday Destination Region Code", "Tuesday Projected Number of Loads"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Append1 (3)",{"Wednesday Destination Region Code"},Wednesday,{"Wednesday Origin Region Code"},"Append1 (4)",JoinKind.Inner),
#"Expanded Append1 (4)" = Table.ExpandTableColumn(#"Merged Queries", "Append1 (4)", {"Wednesday Origin Region Code", "Thursday Destination Region Code", "Wednesday Projected Number of Loads"}, {"Wednesday Origin Region Code", "Thursday Destination Region Code", "Wednesday Projected Number of Loads"})
#"Merged Queries1" = Table.NestedJoin(#"Expanded Append1 (4)",{"Thursday Destination Region Code"},Thursday,{"Thursday Origin Region Code"},"Append1 (5)",JoinKind.Inner)

in

#"Merged Queries1"

Answer 1

This might help:

let
    Source = (VARIABLE1 as table, VARIABLE2 as table) => Table.NestedJoin(VARIABLE1, Key1, VARIABLE2, Key1, "Append", JoinKind.Inner)
in
    Source

You can use parameters for Key1 and Key2. The function will prompt you to select your tables.

You can invoke it from any other query with:

Function.Invoke(Merge,{Table1,Table2})

Replace Merge with whatever you named the first query above and replace Table1 and Table2 with your target tables.

In case you're thinking of it, I have not been able to figure out how to pass tables from parameters. When you do that, the value you enter is recognized as text--for instance, "Table" versus Table--so it won't work. I could not find any information on how to pass a table value, like Table, in a variable. Anyhow, I hope this helps at least a little.

Answer 2

I was searching for this, too!

I finally found it, thanks to Chris Webb at https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/

The key is using Expression.Evaluate with #shared as the second argument.

If you define Query1 as

let
    Source = 1 + 1
in
    Source

Query2 as

let
    Source = 15 * 10
in
    Source

define pIndex as a parameter that is "1" or "2", and

define QuerySwitch as

Expression.Evaluate("Query" & pIndex, #shared)

then QuerySwitch will return

• 2 when pIndex is "1"
• 150 when pIndex is "2"

My example:

I have a query QueryThatTakesFiveMinutes that

• other queries use, and
• writes to an Excel table (also named "QueryThatTakesFiveMinutes")

If I define a query "QueryThatTakesFiveMinutes Cached" by moving my cursor to the output QueryThatTakesFiveMinutes table in Excel and creating a new query from that table then, when I'm testing, I can change all the queries that use QueryThatTakesFiveMinutes to instead use #"QueryThatTakesFiveMinutes cached" and test downstream computation without waiting five minutes every time. Then I just need to remember to change it back when I'm ready.

But that was annoying.

I created a named range in Excel called "ProductionMode" that pointed to a specific cell that holds a value of either TRUE or FALSE

In Power-Query, I defined a very handy power query function called fNamedCellValue as

(rangeName as text) => Excel.CurrentWorkbook(){[Name=rangeName ]}[Content]{0}[Column1]

so that I can define a "ProductionMode" query as

fGetNamedCellValue("ProductionMode")

I use this in a way that's similar to the Index parameter above, but this way I can edit it via Excel.

When I defined "modeQueryThatTakesFiveMinutes" as

if ProductionMode then QueryThatTakesFiveMinutes else #"QueryThatTakesFiveMinutes Cached"

and changed all queries that use QueryThatTakesFiveMinutes to use modeQueryThatTakesFiveMinutes instead, I was very surprised to find that both QueryThatTakesFiveMinutes and #"QueryThatTakesFiveMinutes Cached" were evaluated and it didn't save any time at all!

So then after searching, being overjoyed to find your question only to realize it wasn't answered, then finding Chris Webb's article, I tried redefining modeQueryThatTakesFiveMinutes as

Expression.Evaluate(
    if ProductionMode then
        "QueryThatTakesFiveMinutes"
    else
        "#""QueryThatTakesFiveMinutes Cached""",
    #shared
)

Unfortunately, instead of working, I got an error of

Formula.Firewall: Query 'modeQueryThatTakesFiveMinutes' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

However, I found a way around this, too, by putting the offending code within a function that the consuming query executes.

Deleting ProductionMode and defining a new query fProductionMode of

() => fGetNamedCellValue("ProductionMode") as logical

now doesn't return true or false, it returns a function that will return true or false when evaluated. Why is one legal and the other isn't? I don't know, but it is! Change the definition of modeQueryThatTakesFiveMinutes to

Expression.Evaluate(
    if fProductionMode() then
        "QueryThatTakesFiveMinutes"
    else
        "#""QueryThatTakesFiveMinutes Cached""",
    #shared
)

and it works!