Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      jeffshieldsdev
      Solution Sage
      6 years ago

      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.