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

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Business Intelligence, Excel, Excel 2013, Office 2013, OLAP, Power BI, Power BI Desktop, Power Query, PowerPivot, Process Automation, Spreadsheets, VBA. Bookmark the permalink.

11 Responses to How To Change Data Sources Dynamically In Excel With Power Query

  1. FrankT says:

    In Excel 2013 there is a workbook option to switch off “Fast Combine”, in Excel 2016 (unfortunately these days one has to be more precise: click-to-run, first release, office insider, up-to-date) there is even a general UI-option.

    BTW, regarding Power Query always check Chris Webb’s blog first, e.g.
    https://blog.crossjoin.co.uk/2015/11/24/using-parameter-tables-to-control-data-refresh-in-power-bi/

    Stay tuned …

    • Biggus Dickus says:

      Thanx Frank …

      The problem with that switch in Excel 2013 is that the user has to do that in the UI … I don;t want that … I want it automatic so the user doesn’t have to have instrux on how to use the file .. that’s why that VBA in 2016 is so attractive to me.

      Interesting post there .. I have had issues with how to do just that in Power BI. I am actually really interested in a way to use an Excel file’s Data Library as a source in Power BI … in other words pull in the data from the Excel file’s data library itself rather than importing the connections behind those tables and then refreshing them from the source.

      This way I could create a Power Query/Power Pivot solution in Excel, refresh it on a timely fashion by an admin with access to the source data and then just publish that SNAPSHOT of data for consumption in Power BI through OneDrive for example and be able to refresh THAT FILE’s data only so it reflects changes I make in the Excel file. No need for a Gateway, no security risks beyond the data in the file. I am having a tough time convincing people that this has value .. But I guess I’m nobody.

      Dick

  2. Pingback: How To Change Connection Type In Power Pivot | Dick Moffat's Spreadsheet Blog

  3. kenpuls says:

    Hey Dick, I thought we talked about this in Amsterdam last year? 🙂

    FYI, you need to be a bit careful knocking up a dynamic query like you’ve done, as this breaks query folding. That means you’ll bring all your data back into the Excel workbook (or Power BI Desktop file) and have to reach to their calc engines for further filtering/processing. I actually blogged on this a couple of weeks back here if you’re interested: http://www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/

    Cheers!

    Ken

    • Biggus Dickus says:

      Can’t remember that specifically but thanx for reminding me😉 .

      Interesting that the big difference between my method and yours is in the creation of the Sql statement… I suggest creating the statement in the spreadsheet itself , pulling whatever variables you need from there and concatenating until you end up withe sql in a cell in the parameters table… What I like about that is that my PQ code is generic and doesn’t need to be modified much if at all.

      So this formula :

      =”select * from fnGetData(’01/01/”&curyear&”‘,’12/31/”&curyear&”‘)” passes as a complete sql statement that’s actually calling a very large and complex Sql server function with 2 parameters…

      I know this doesn’t help with PowerBI but that’s not my concern right now😉 ..

      I really believe that this is more approachable and far easier managed but the result is exactly the same. In fact you could take the contents of the Sql function I call here and pull all of it together in a cell and send all that as well if you don’t have rights to create a function on the actual server … Easy peasy for an Excel person…

      I am a big fan of using Sql server’s sql to do the work there and then if I want I can add value to the resulting records using PQ once pre-filtered and most importantly pre-aggregated data in Excel.

      I will link to yours and the Folks can decide which works for them I guess ..

      And yes the Privacy business really sucks but as we both said in 2016 it gets easier if not perfect.

      • kenpuls says:

        Actually, I agree on creating the SQL statement in the spreadsheet. My blog was targeted pretty specifically for that user, but with a little modification, the dbQuery could be set up to use fnGetParameter(“SQL Query”) instead of building it inside Power Query. 🙂

    • Biggus Dickus says:

      One more thing … I would think that way to make this kind of thing dynamic in Power Bi woukd be by using their Slicers … But in the end you have to pull in all the data you want (another argument for pre-aggregation I guess –

      • kenpuls says:

        Yes, agreed. We have a bit more control over the slicer/refresh process in Excel to my feeling than we do in Power BI.

      • Biggus Dickus says:

        Fumble fingers on iPhone …

        But if you’re staying in Excel where capacity and the time it takes to refresh are an issue then either of our techniques would be very useful … Again both filtering in the Sql and also pre-aggregating as much as the report allows for…🙂

  4. sam says:

    “There is to me an odd feature (?) of Power Query called “Fast Combine” ”
    @Dick

    Few days ago there has been an update to Power Query (11-May)

    You now a setting in Options – Security – “Always Ignore Privacy…..on this machine”
    So if you choose this you wont get any of the “irritating and confusion messages” regarding formula firewalls etc

    This is a huge improvement in deploying Power Query based solutions

    Regards
    Sameer Bhide (sam)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s