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)

[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]

[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...