cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aexley Kudo Commander
Kudo Commander

Parametising choice for different data sources within query

Hi.

 

Just wondering if it's possible to parametise the selection of different data sources for queries. In production we are using Dataflows that are sourced from a production database, but we'd like to connect the queries to a test version of a SQL database when needed. As there are a large number of queries, and to simplify the user experience, I would like to parametise a simple TEST/PRODUCTION switch.

 

Within the query I was envisioning an IF statement that enabled the Dataflow data source when equal to Production and the SQL DB table when set to Test. Unfortuantely, I haven't quite been able to crack the correct configuration to make this function and I was hoping someone here may have a suggestion.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Community Champion
Community Champion

Re: Parametising choice for different data sources within query

How i would solve this in Power Query is as follows (I've not used dataflows so please let me know if this doesnt work).

 

Create 2 Queries, set as enable load = false (Also known as Expressions in Analysis Services).

  1. source_Production
  2. source_Test

These queries should be a source row to your specific database.  If its SQL it just the table listing, but not the navigation step

 

Create Your Parameter, as a logical.  I called mine fromTest

Have your source row in your standard queries look like this

 

 

Source = if fromTest then source_Test else source_Production

 

Any gateways required will need credentials for both systems and be aware that should test go offline, you could potentially get refersh errors.

View solution in original post

2 REPLIES 2
Ross73312 Community Champion
Community Champion

Re: Parametising choice for different data sources within query

How i would solve this in Power Query is as follows (I've not used dataflows so please let me know if this doesnt work).

 

Create 2 Queries, set as enable load = false (Also known as Expressions in Analysis Services).

  1. source_Production
  2. source_Test

These queries should be a source row to your specific database.  If its SQL it just the table listing, but not the navigation step

 

Create Your Parameter, as a logical.  I called mine fromTest

Have your source row in your standard queries look like this

 

 

Source = if fromTest then source_Test else source_Production

 

Any gateways required will need credentials for both systems and be aware that should test go offline, you could potentially get refersh errors.

View solution in original post

Highlighted
jeffshieldsdev Continued Contributor
Continued Contributor

Re: Parametising choice for different data sources within query

This is a great pattern.  I would just add, parameters in dataflows can't be changed from outside dataflows (yet), so I would use another dataflow to store my true/false value, and then reference that as if it were the parameter.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors