Parametising choice for different data sources within query

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

[jeffshieldsdev]

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.