How To Change Data Sources Dynamically In Excel With Power Query

My Crow Eating Exercise

This past week I asked on this blog whether anyone out there was using the technique I wrote about years ago for dynamically changing the Source Connections for Power Pivot Tables in Excel.  I have noticed that people keep coming to it but no one ever tells me whether they use it or not.

The only response I received was from 2 Power Query “gurus” named Frank Tonsen and a guy who’s name looks like Sam Hide.  I really appreciate their prodding me to look at finding a way to accomplish the same using Power Query.

They detailed to me that it is apparently possible to accomplish the same in Power Query – something I never looked into as I have been satisfied with what I’ve been using and frankly because i don’t use Power Query aggressively to this point.  I admit frankly everyone doesn’t have Power Query installed with Excel 2013 and IT can be a block … and I don’t deal with anyone who uses Office 2016 to this point.

So I decided to look into this and found as always an endlessly complex and frustrating set of Web hits, none of which really seem to resolve this issue specifically. But in all this were snippets of what I appear to need.

My Demands

There were two issues that have to be satisfied for me to make the switch (which I REALLY WANT TO DO)

1. It had to be straightforward and as simple as possible so that many can implement it and

2. It had to accommodate my requirement to change values in an Excel file and have the Data Sources change based on the UI … In other words the user should be able to pick a particular year or Division and retrieve just the data for that … This is to limit the impact of large data sets creating HUGE Excel files and of course eliminate those looooong refreshes.  To accomplish this I create SQL Server Functions and pass parameters to them like this:

select * from fnDataSummary(’01/01/2015′,’12/31/2015′)

where the dates in this formula:

=”select * from fnDataSummary( ’01/01/”&CurYear&”‘,’12/31/”&CurYear&”‘)”

are driven from a Slicer or a pick list inside the spreadsheet … very cool

My Solution

So here we go … This is a way to connect to a SQL Server or SQL Azure database.  I hope you find this simple .

• First I suggest you save as often as you can in this process. I find that when developing in Power Query it does crash from time to time   . This doesn’t bother me as this is not a new thing in Excel and most of the time if you close Excel and reopen the file things go back to normal.  Once the file is complete and functional it appears to stay stable (just like Power Pivot.

• Select a SQL Server that you have Windows rights to (for convenience), a database in that Server and a simple Select Statement against a Table in that database.

• Create a blank Excel Workbook

• Create a simple table like this, make it a table and name it “tblConnections” for our purposes (putting your SQL statement in the SQL Field in row 2):

• Launch Power Query and ‘Launch Editor’ …

• Create a ‘New Source’ … ‘Other Source’ …’Blank Query’, Open its Properties and rename it qryMasterTable …

• Open qryMasterTable in the ‘Advanced Editor’ and Type or copy THIS into that query:

let

txtSQL= Excel.CurrentWorkbook(){[Name=”tblConnections”]}[Content],

#”Changed Type” = Table.TransformColumnTypes(txtSQL,{{“SQL”, type text}, {“Server”, type text}, {“Database”, type text}})

in

#”Changed Type”

• Create a New blank query, rename it ‘qryData’, open the ‘Advanced Editor’ and copy this text into it:

let

Source = Sql.Database(qryMasterTable[Server]{0},qryMasterTable[Database]{0}, [Query=qryMasterTable[SQL]{0}])

in

Source

• Go ‘Close and Load’ …

• Back in Excel while still on the Power Query Tab choose ‘Options’ … ‘Privacy’ … select ‘Ignore the Privacy ….’ … Click ‘OK’ … (see notes)

• In Excel go to the Data Tab and go ‘Refresh All’ …

• This should import the data from the SQL that you entered from the Server and Database you put in the tblConnections table.

And that’s it … two very small queries that drive the querying of data.

Important Notes ..

If you want another data source simply add other rows to tblConnections as necessary and duplicate ‘qryData’ and change the references to ‘{0}’ to [1] and then ‘{2}’ accordingly.  The name of each query in Power Query will become the name of the new table.  (You can even rename the tables by renaming the queries btw …).

And remember that any DAX that you place in any of these tables in the Power Pivot Data Libraries are preserved despite all these changes.

There is to me an odd feature (?) of Power Query called “Fast Combine” which is a mechanism designed to basically prevent you from mixing data from multiple sources in one spreadsheet … ?  I am really unsure why this is so (maybe some auditor or lawyer got to the developers and insisted on this).  While this pretty much would make it almost impossible without great effort to integrate multiple data sources into one Excel file (which I often do btw) there is also a way to override this “feature” by choosing to ignore it… Unfortunately this becomes a problem for us here because that little table in Excel is considered an “alternative data source” and so this rule kicks in unless you choose to “ignore” it…  There are all kinds of warnings of the risks inherent in doing this but frankly it strikes me as the exact same kind of overkill as Macro security settings (only MUCH worse).  This is because it is related to each individual user and the settings “Private”, “None” etc.  of every data source and every user has to personally set this to “Ignore” for every new file they get.  It is nothing short of a pain in the ass to me.

Interestingly though, in Excel 2016 they decided to provide a VBA workaround which to me indicates that I am not the only one who thinks this is a bit much in the REAL WORLD…  That is the capability to add this line in the Workbook_Open() Sub:

ThisWorkbook.Queries.FastCombine = True

This allows the Power Query queries to run just like you clicked “Ignore … “

If it’s a big thing you can make the setting FALSE after you do a refresh or even better make this setting only accessible with a password so that the refresh can only be done by an Admin of some kind.

To me this is a BIG reason to get your clients to move to Office 2016 asap.

Conclusion

So as far as I can tell Sam and Frank are absolutely correct that if you’re using Excel 2016 then Power Query and this relatively simple technique will make your solutions far more responsive to the real needs of your solution. I hope this means that you can teach an old dog new tricks and I hope this simple example makes sense to the most possible people so they won’t have to do all the searching I had to do to get this working..

Enjoy …

Dick

P.s. Ken Puls did a recent post on this exact issue that somehow was missd in all my searching … It’s a typically thoughtful Ken posting and offers an alternative way to accomplish this :

Pass Parameters to SQL Queries – The Ken Puls (Excelguru) BlogThe Ken Puls (Excelguru) Blog

Pass Parameters to SQL Queries

Advertisement