CODEWITHSUNDEEP
powerbipowerquerym
Jay Killeen
Jay Killeen

Reputation: 2922

Call a table using a dynamic identifier

I am trying to call a table in Power Query M dynamically. I have created a simple query below to explain what I am trying to do.

I'd like the result to be "It Worked" as opposed to "Source1" which is what I currently get.

let
  Source1 =  Table.FromList(
    {
      [Word1="It",Word2="Worked"]
    },
    Record.FieldValues,
    {"Word1","Word2"}
  ),
  Source2 = "Source" & "1"
in
  Source2

Use Case Details:

I am trying to do this so that I can call a query based on conditions. such as:

if ENV = "production" then
  Customer_s
else
  Customer_m

Where Customer_s is a query to retrieve customer data found in a CSV file and Customer_m is a query to retrive customer data found on a SQL Server.

Because I will be doing this with many tables (not just customer) I thought I'd create a function where you can pass in the model name (ie "Customer") and it would then string it together to call the query.

I just find that PowerBI grinds to a halt and constantly reruns queries unnecessarily which is killing my PC and network speed. I thought an easy way around this would be to create dummy data that looks like the result of SQL Server but is stored locally on my machine. That way I can also write queries if I don't have access to the internet.

Upvotes: 1

Views: 632

Answers (4)

ellen G
ellen G

Reputation: 1

I know this is an old question and has been solved and I'm a newbie, but I've got a similar solution - sharing in case it's helpful / to get feedback if it's not :-)

TLDR: Reference dynamic set of steps in same query by encasing steps to be referenced in a record

Use Case: Generating a table with information about each node tree where the depth of the tree is dynamic. Specifically, my tree is an org structure where each node is an org unit.

Notes:

  • I still haven't figured how do recursion in M - but I only need less than 10 levels so not a big issue here ... however probably could do better with recursion
  • As I understand it, this is a similar solution to the accepted solution because the let ... in expression is syntactic sugar for a record. Using a record structure allows access to each element rather than just the step referred after in

Code:

Max_Level = ...
fx_Get_Level = ( Level as number , Prev_Level_Data as table ) as table => ...

Level_List = List.Transform( { 1 .. Max_Level } , each "L" & Text.From( _ ) ) ,
Levels = Record.SelectFields( 
    [ L1 = .. 
      , L2 = fx_Get_Level( 2 , L1 ) 
       ... 
      , L10 = fx_Get_Level( 10 , L9 ) 
      ] 
      , Level_List ) ,

TEST_Append = Table.Combine( Record.FieldValues( Levels ) ) ,

Upvotes: 0

Jay Killeen
Jay Killeen

Reputation: 2922

I have made this work thanks to the information provided by @ImkeF.

I first had to split the two queries so Source1 was evaluated with it's full let in expression and could then be accessible through #shared.

QUERY 1

let
  Source1 =  Table.FromList(
    {
      [Word1="It",Word2="Worked"]
    },
    Record.FieldValues,
    {"Word1","Word2"}
  )
in
  Source1

Query 2

let
    Source2 = Expression.Evaluate("Source"&"1", #shared)
in
    Source2

This has QUERY 2 resulting in:

╔═══════╦════════╗
║ Word1 ║ Word2  ║
╠═══════╬════════╣
║ It    ║ Worked ║
╚═══════╩════════╝

Thanks @ImkeF and @Alejandro for your assistance.

Upvotes: 0

ImkeF
ImkeF

Reputation: 1588

If you use this:

= Expression.Evaluate("Source"&"1", #shared)

you can dynamically reference other queries. But not other steps in the same query (as it seems). So it has to sit in a new query.

Or was your question about:

 Text.Combine({"It", "Worked"}, " ")

?

Upvotes: 1

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

#shared contains a list of all of the available queries and library functions, so you can do something like this: Record.Field(#shared, "It " & "Worked")

Upvotes: 2

Related Questions