PowerQuery Functions: One Query against Many Databases
Let’s Consolidate…
Okay- so you’ve got one, maybe more, SQL servers with many DBs, what’s an analyst to do?
One way to do it is to have a ‘source’ for each Server/DB combination. This method will not scale well and is VERY time consuming. One change in the query can translate to 20+ mins of copy and pasting the query over and over. This also means you’ll have to add a connection to your Gateway for each DataBase/Server combo.
In our scenario the list of DB’s we want to consolidate and run the query against is always changing, so constantly editing the gateway is unrealistic. I also know that the query is changing everyday during development, and I really don’t want to spend time copying and pasting a query dozens of times a day.
PowerQuery Functions are exactly what we need.
Ahhhh. Much better…
We can run a specific query against as many DBs as we want by using PowerQuery Functions. This method won’t clog up the Gateway with 100s of data sources either.
Ew.. Gross….
Power Query Functions- why are they better?
Duplication is reduced:
Only one copy of the query is required, no matter how many DBs you want to run it against. This will stop incorrect queries from slipping through and stop the copy and paste exercise from taking place.
It’s less overhead:
I don’t feel like I’m cheating my client or wasting my time by using this method. I can spend more time finessing the report instead of focusing on the building blocks.
It’s supported by the current PowerBI / Excel infrastructure:
It’s so nice to find something feels like a hack but is actually intended use! PowerBI and Excel love this method. As a bonus, On-Premise Data Gateways for PowerBI know exactly how to handle them.
Getting Started:
First, make sure your server is setup to handle cross DB queries.
Similar to how the ‘EXECUTE’ function in SQL could be used, we’ll be assembling the queries as text entries and then executing them against a ‘master’ DB as native PowerQuery functions.
So from a list of DB names, we’ll be passing through the ‘slave’ DBs’ name to a query and running them against the master DB.
On your master DB, make sure that a query like the below can run:
SELECT * FROM [SLAVE_DB].[dbo].[DataTable]
In the image, I’m running my query for the Slave DB ( MN* ) against the Master DB ( FR* )
Designing PowerQuery Function
Next we need to create our Function. Start by creating a ‘blank’ query and opening the ‘Advanced Editor’.
You can create your SQL query in PowerBI as a stand alone query first and then convert it to a function. My Query is small enough to just replace the contents of my blank query, just type it out in the following format and then paste it over the contents inside the Advanced Editor
let
Source = (#"slaveDB") => let
QUERY = Sql.Database("serverName", "MASTERDB", [Query="SELECT *#(lf)FROM ["& #"slaveDB" & "].[dbo].[DataTable]"])
in
#"QUERY"
in
Source
If you’re copying it from an existing PowerQuery SQL Query, then the #(lf) indicates a line break.
To quickly convert complex queries that reference the slave DB often, I use the find-replace function in Notepad/Notepad++ to replace the name of my Slave DB with the variable #”slaveDB” and replace the human readable linebreaks ( \r\n ) with the PowerQuery compatible #(lf) linebreaks.
If everything has gone well, you’ll notice that your new PowerQuery entry will have a function icon on the left hand side.
Implementing the Function:
Implementing the function requires a list of DBs to run the function against.
I usually use an Excel Table as my starting list. This makes it easy to quickly change which DB’s the query runs against.
Let’s start by grabbing the table from the ‘Get Data’ menu and transforming the sheet as needed.
Once you have your list, we’re going to add a line to the ‘Advanced Editor’ as follows.
#"Final"= Table.AddColumn(#"previousStepName", "GetDataBaseQuery", each nameOfMyFunction([columnContainingMySlaveDbName]))
previousStepName = The name of the step previous.
GetDataBaseQuery = The name of the new column.
nameOfMyFunction = The name of the function we created in the previous step.
columnContainingMySlaveDbName = This is just a column reference.
You can see an example of the code I used in the last image.
Expanding your column and buttoning it all up:
Finally, click the expand column button and Volia! All your Queries for each DB expand in to one big table.
By using this method we only need one entry on the Gateway for our MasterDB- very clean!
Our Latest Blogs
About FreshBI
Operating throughout USA and Canada, FreshBI is a boutique Microsoft Partner using Power BI to unlock the value trapped in your data so that your business can succeed.