If you use Power Query with different SQL Server environments on a regular basis then you probably have run into the problem of changing your server and database names in your queries after you have developed them. A typical scenario is consuming data in a development environment and then changing your solution to pull data from production. This can be a time consuming, irritating and error prone task. Especially if you have many queries to change. Wouldn’t you rather have a table of data sources you can just edit?
Using the M query language and a parameter table in Excel you can define your environments and then change them on the fly when you are ready to move to another environment. This solution basically has three steps.
- Create a Parameter Table in Excel
- Copy the M language code below to a query and edit it
- Enable Fast Combine
For this example I am using AdventureworksDW and ContosoRetailDW databases on my local instances of SQL Server. First you must define a table in Excel named DatabaseParameters with the following columns at a minimum.
PQ_QueryName
DatabaseName
SchemaName
TableName
ServerName
You could add a comments column or additional columns if you wish.
I’m going to use the DimDate table from the 2014 instance and the DimAccount and Fact Finance Tables from the 2012 instance.
Create an Excel table with these columns at a minimum
Now I need to create a query that will read the information from this table and fetch the data. In the Power Query ribbon select “Blank Query” from the “From Other Sources” drop down list.
Select “Blank Query” in “From Other Sources” dropdown
In the Query editor go to the “View” ribbon and select “Advanced Editor”. Replace the stubbed out code with the following code.
// This Query will use any SQL Server and database defined in a table named DatabaseParameters
// You must have the following Columns in the Table
// PQ_QueryName
// ServerName
// DatabaseName
// TableName --This can also be a view
// SchemaName - often dbo
// This is handy for switching between development and production servers
// without changing your query definition.
// Phillip Labry Oct 2014
let
// Set The Query you want to use here
QueryName = "Test1",
// pull in the parameter table from the current workbook
DB_Parameters = Excel.CurrentWorkbook(){[Name="DatabaseParameters"]}[Content],
// Find the index of the Query you are looking for. You can have many query definitions in the table
Index = List.PositionOf(DB_Parameters[PQ_QueryName],QueryName),
// Grab the Values and put them in variables
ServerName = DB_Parameters{Index}[ServerName],
DatabaseName = DB_Parameters{Index}[DatabaseName],
TableName = DB_Parameters{Index}[TableName],
SchemaName = DB_Parameters{Index}[SchemaName],
// Set your Database Source and Table and go
Source = Sql.Database(ServerName, DatabaseName),
Table = Source{[Schema=SchemaName,Item=TableName]}[Data]
in
Table
// end query
Be sure to change the QueryName variable initialization from “Test1” to the corresponding value in the PQ_QueryName column of the table you want to retrieve. Save the query and at this point you will probably run into an error from the formula firewall about being unable to directly access a data source.
To get around this error you need to enable fast combine.
Enable Fast Combine. Very important detail!
Once that’s done you can refresh your query and the data will load into Excel.
You should now be able to switch instances and even tables. Keep in mind that each time you switch data sources Excel will ask you about security. Use the appropriate security settings for your environment. I’m going to switch my Dates query from AdventureWorksDW to ContosoRetailDW. I simply change the value in the table and refresh my Dates query.
At this point you can load the data into a PowerPivot Data model or straight into Excel.
I’m certain this method could be used to pull data from data sources other than SQL Server but I have not had a need to do that yet. You could probably add source type as a variable and modify the code so that you could switch between Oracle and SQL or other data sources.
If using this technique for a power Pivot Data model I recommend using these queries as a reference. I will write another blog post on the utility of referenced queries and link to it here.
You can download a sample excel spreadsheet here.
by 
by