Power BI Tutorial: How to Parameterize Connection Variables in Power Query

~ nikilprabhakar

While migrating a power query solution across environments (Dev to UAT to Prod) changing connection variables for each power query is a tedious task. Here is a way to make the connection variable dynamic.

The sample here has two power queries connecting to SQL server source.

  • Create an excel table and call it Parameter with two columns as shown below

  • Highlight Parameter table, click on Power Query in the ribbon and select From Table

  • Right click on localhost and select Drill Down

  • Click on Advanced Editor in the ribbon and copy connection

  • Copy following snippet of code

    Source = Excel.CurrentWorkbook(){[Name=”Parameter”]}[Content],

    ParameterValue = Source{0}[ParameterValue]

  • Load and Close the query as connection only query
  • Edit Date Power Query
  • Open Advanced Editor

  • Past the earlier code snippet that was copied and change the variable names (make sure to add a comma at the end of the statement)

    ParameterSource = Excel.CurrentWorkbook(){[Name=”Parameter”]}[Content],

    ParameterValue = ParameterSource{0}[ParameterValue],

  • Click on Edit Permission and select Run

  • Change the Privacy Settings and make it Public
  • Load and Close the query
  • Make the same edit to the Sales
  • Now the query is connected to server localhost
  • Change the server parameter value to change the connection
  • Refresh the query, now the query will be connected to SQLServer

Published by nikilprabhakar

9 thoughts on “Power BI Tutorial: How to Parameterize Connection Variables in Power Query”

  2. I’m getting this error in Office365. Any tips?

    OnPremise error: Sorry, we can’t refresh connections to this kind of data source in Power Query yet.

    Thanks!

    Reply

  3. thanks for the post. my issue is the full table is very big but it will be fine with date range filter . is there any way to popup a input message to enter start/end dates and pass them to query before loading the data and page?

    Reply

  4. Hi,

    Is it possible to have more than one source in a single query? For example, I want to combine table A and table B in a single query?

    Does that make sense?

    Thanks.

    Reply

  5. Hi
    I have another way to pass parameter into Power Query. I make sure it works.

    Step 1: Create a table named “Infodesk” in excel, or you can name it as you wish. My table “Infodesk” contains column “Script” where I place the SQL script in this column.

    Step 2: Create a blank Power Query, click on Advance Editor, and input following code

    let
    Source = Sql.Database(“192.168.23.81”, “dbo.name”, [Query= Excel.CurrentWorkbook(){[Name=”Infodesk”]}[Content][Script]{0}])

    in
    Source
    And then, run your query. The code [Script]{0}] will get data from 1st row, you can place multiple script in the column, then change {0} >> {1} or {2} … to get data from other rows.

    In order to change any parameter in SQL script, you may use the excel function =CONCATENATE() to combine multiple excel cells into a completed SQL.

    Reply

  6. I have a question. We have a db for every client with same schema. We want to build a report and provide a way to run this in multiple client dbs. We are using direct query. This is on premises with power bi server and not cloud.

    Reply

Leave a comment