Dynamic source table name in Power Query

[gvg]

Hi,

I have two data sources as tables in my query list: tableA and tableB. I want to build another query (myQuery) that dynamically takes either tableA or tableB as source. I know how to use parameters, but my problem is that I can not figure out how to specify the variable part (A or B) in my Source instruction in myQuery in "M". Tried this one, but it doesn't seem to work:

Source="table" & parameter1              //  parameter1 is ="A" or "B"

Can anybody help?

[avatorl]

= if your_parameter="A" then TableA else if your_parameter="B" then TableB else null

you can use simple if statement

[gvg]

OK. But then I am stuck with amending the code every time another table (tableC, etc.) arrives.

[avatorl · Accepted solution]

@gvg

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

[WilliamBueti]

Any suggestions for what to do if values are null in the #shared record?

The previewer shows the queries evaluate to tables, and (assuming a query name of "My Table") using a static reference like #"My Table" returns the query result as expected, but using a dynamic reference such as let Expression.Evaluate(Expression.Identifier("My Table"), #shared) returns a null.

[MikeCannon]

You don't know the rabbit hole I went down looking for this.   Thank you!

[pem]

your welcome ! happy that you can can solve your problem 

[pem]

I have use the it on Origine concatenating "Table" and parameter as follow and is working
Origine{[Schema="dbo",Item="Table"&parameter&")"]}[Data]

[psrivastava43]

Hi Pem,
I am new to power bi, can u please elaborate your solution.
I am facing the similar situation where i am connecting to databricks but want to parameterize the delta tables selection part.
Kindly assist.
Best,

Prajjwal

[gvg]

Yep, exactly what I was looking for. Thank you !

[Shukla_Dixit]

I have a power BI report that needs to be refreshed everymonth. The SQL table that is used to pull data is named "xxxx_202201". I want to change the table date without using the query editor.I think I can use Source = Expression.Evaluate("Table" & parameter, #shared) the solution provide in this thread. But, I am new to power BI, can you please explain in detail what steps I will need to follow? Also, how to edit the query so the table name now uses this parameter for date.

[avatorl]

An article about Evaluate finction:

https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/ 

[psdhillon1]

Hi

Is this solution valid in PowerBi service as well. say providing a parameter in powerBi service & whenever new table added to database with same structure, one can select to see the dashboard KPI for new table. Actually, we are getting the survey results in new table & want to create an option with an self serviced solution where, someone just need to select the new table to see the results from this newly created survey. 

I understand, we can do the same in desktop & can publish from there, but it involves manual efforts daily.

[ChemEnger]

@psdhillon1 I think you make a good point here and I have found that when I use the #shared function in and expression.evaluate I cannot set a scheduled refresh in the Power BI Service as I get an error referring to hand-authored queries (similar to this: https://community.powerbi.com/t5/Power-Query/Expression-Evaluate-shared-in-PowerBI-Service/td-p/7316...