Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gvg
Post Prodigy
Post Prodigy

Dynamic source table name in Power Query

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?

 

1 ACCEPTED SOLUTION
avatorl
Impactful Individual
Impactful Individual

@gvg

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

View solution in original post

12 REPLIES 12
avatorl
Impactful Individual
Impactful Individual

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

avatorl
Impactful Individual
Impactful Individual

@gvg

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

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 

pem
Frequent Visitor

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

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

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.

avatorl
Impactful Individual
Impactful Individual

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors