Build Table/Query Reference Using Other Query Steps as "Variables"

[mm5308]

Been searching for an answer for a while and honestly not even sure if I was using the right search terms...

In Power Query, I'm trying to "build" a table/query reference based on previous table steps as "variables".

For example...

The proper table reference would look like this:

let
  Source = #"QueryA Part1 - QueryA Part2"
in
  Source

So I've tried to achieve the same result by entering these steps:

let
  Variable1 = "QueryA Part1"
  Variable2 = "QueryA Part2"
  QueryReference = "#""" & Variable1 & " - " & Variable2 & """"
in
  QueryReference

In this manner, if I copy the query itself, all I'd have to do is change the Variable1 and Variable2 references to pull in the (different) target query for each new query, like this:

let
  Variable1 = "QueryB Part1"
  Variable2 = "QueryB Part2"
  QueryReference = "#""" & Variable1 & " - " & Variable2 & """"
in
  QueryReference

(The only part that changed from the initial steps is QueryA became QueryB.)

However, I keep getting this error:

Expression.Error: We cannot convert the value "#"QueryA Part 1 - QueryA Part 2" to type Table.
Details:
  Value=#"QueryA Part1 - QueryA Part2"
  Type=[Type]

I've even tried adding a table "identifier" as follows, with no luck.

  QueryReference = "#""" & Variable1 & " - " & Variable2 & """" [Table]

That change results in a different error:

Expression.Error: We cannot apply field access to the type Text.
Details:
  Value="
  Key=Table

I'm not sure if I'm missing something in the syntax or if what I'm trying to achieve isn't possible (although I've found the latter to rarely be true). Hopefully someone with more expertise knows the solution.

Note: I am aware that I could simply type the referenced query directly as a step to achieve the result. The reason I want to "variablize" that reference is those same variables are used several times in later transormation steps. Basing everything on the same variables would mean consistency throughout the query steps and updating less pieces each time I copy them (i.e. reduce error potential).

[slorin · Accepted solution]

Hi

Maybe with Expression.Evaluate

let
  Variable1 = "QueryB Part1"
  Variable2 = "QueryB Part2"
  QueryReference = Expression.Evaluate("#""" & Variable1 & " - " & Variable2 & """", #shared)
in
  QueryReference

 Stéphane

[mm5308 · Accepted solution]

FOLLOW UP - LIMITED USE SOLUTION:

While this solution works in Power Query Editor, it does not work when loading changes into the actual Model (i.e. after pressing "Close & Apply"). For some reason, a blank table is returned.

I narrowed down the issue to using the "#shared" method of referencing a query. Apparently it doesn't work outside the preview/Editor environment. To test this idea, I actually performed the same steps as the = Expression.Evaluate( ..., #shared ) solution provided by @slorin (which, again, works in preview/Editor), except manually.

• Created a step Source = #shared. This pulls up the list of queries and library of functions as expected.
• Clicked on "Table" next to the table I wanted to open.

As expected, in preview/Editor, the table came up as expected. But again, like with the Evaluate.Expression() function, when this query loaded into my Model, it returned a blank table.

Unfortunately, this means what I was trying to accomplish originally is still unsolved.

[slorin · Accepted solution]

Hi

Maybe with Expression.Evaluate

let
  Variable1 = "QueryB Part1"
  Variable2 = "QueryB Part2"
  QueryReference = Expression.Evaluate("#""" & Variable1 & " - " & Variable2 & """", #shared)
in
  QueryReference

 Stéphane

[mm5308]

@slorin Thank you Stéphane!! That's the solution! I appreciate your help.

[v-junyant-msft]

Hi @mm5308 ,

I'm sorry I may not have a good understanding of what you're hoping to accomplish.

Your direct return from this code 

let
  Name1 = "Name1"
  Name2 = "Name2"
  QueryReference = "#""" & Name1 & " - " & Name2 & """"
in
  QueryReference

is shown below:

It simply returns a string without any function.

But in this piece of code:

let
  Source = #"Name1 - Name2"
in
  Source

The #"Name1 - Name2" is a predefined step that contains the function, so instead of just returning a string, it will return the result of the data table you get after all the steps in this function are completed.

If I'm not understanding you correctly, could you please provide me with the sample data if you can and the final results you're hoping to get, that would be helpful, thanks!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mm5308]

Hi @v-junyant-msft,

Thank you for replying. One thing you said made me realize that the way I worded the issue is a little misleading, or perhaps didn't explain it well. I will edit it after this post.

I know that I can directly reference another query to produce a table by directly typing the name of the query as you suggested. That's exactly what I'm trying to avoid though. What I really want is a way to build a query name through "variable" references. That way I can copy and paste the query and change the variables to reference the different tables I want for each new query.

Does that clarify, and do you have any additional insights?