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?
Solved! Go to Solution.
Source = Expression.Evaluate("Table" & parameter, #shared)
= if your_parameter="A" then TableA else if your_parameter="B" then TableB else null
you can use simple if statement
OK. But then I am stuck with amending the code every time another table (tableC, etc.) arrives.
You don't know the rabbit hole I went down looking for this. Thank you!
your welcome ! happy that you can can solve your problem
Yep, exactly what I was looking for. Thank you !
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.
An article about Evaluate finction:
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.
@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...
Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.
Find out more about the March 2023 update.
Find out more about the online and in person events happening in March!