One of the many things I love about Power Pivot and Power Query is that these tools have put BI into the hands of users and there is no longer a reliance on highly technical IT skills such as MDX to enable BI reporting. This is game changing for all the reasons you have read about on this blog over the years. Over time, as I have used Power Pivot more and more, I have found increasing value in learning about and working with SQL Server as a tool to manage my data. I know the thought of using SQL Server can be a bit daunting to Excel users (as it was to me at first) but it is actually not that hard to setup and use. Anyone can download and install a free version of SQL Server Express on their PC, and there is a lot of learning material on YouTube to get you started. But there is one thing about SQL Server that I have found to be much more difficult than with other more consumer friendly databases like Microsoft Access – that is how to get the data loaded.
Getting Data into SQL Server
SQL Server uses a tool called SQL Server Integration Services (SSIS) to load data. This is a very powerful piece of software, however I have found it is quite difficult to learn how to use it (as a casual user). I have no doubt that SQL Server professionals have no problems, but there is a big difference between firing up SSIS once every 3 months to load a couple of tables and working with the tool each day. I have never had the time to master SSIS and I cringe each time I have an issue that requires me to edit my SSIS packages. In the past I have outsourced this work to a developer as it just wasn’t worth the inefficient use of my time to try to work it out myself.
Enter Power Query as an SSIS Alternative
Now before I get a million comments from professional SQL Server experts, I am not suggesting that everyone should swap out SSIS for Power Query. I understand the importance of an enterprise strength tool like SSIS and I know it is a great tool for that purpose. I am just talking about people like me that work in the Self Service BI/Excel space, use SQL Server as a tool, but have only limited opportunities to work with SSIS. This is my situation, and if this also sounds like you then Power Query is a great alternative.
The benefits of Power Query over SSIS include:
- Power Query is easy to learn
- It is UI driven so you don’t need to understand the code (although it can help. You just point and click.
- Importantly it is easy to come back 3 months later and make changes without needing to remember how the software works.
Two Ways into Insert SQL Tables using Power Query
Before you do anything else, you need to create a Power Query workbook and shape the table the way you want to see the data. Very Important: make sure you set the data type for all of your columns during this step so that the data will be loaded into SQL Server with the correct format.
Once you have your table shaped the way you need it, you can choose one of the following two approaches to loading the data.
Option 1: Native Excel Tables
The first option is to load the data into an Excel table and then load it to SQL Server from there. To do this, make sure you set your Power Query Workbook so it will “Load To” a Table as follows:
Once the table has been refreshed and loaded into a table in a spreadsheet, simply save the workbook into a known location and close.
Go into SQL Server Management Studio (SSMS), right click on the database (#1), select Task (#2) and Import Data (#3).
Now I realise the irony here, but when you do this, you are actually using SSIS to import the data. But the big difference is that all the data transformation has already been completed in Power Query, so it should be a straight load into SQL Server.
Just follow the wizard steps to import from your Excel file into SQL Server.
When you get to this screen (below), give the new table a name (#1) (or select an existing table from your database) and then select Edit Mappings (#2 below).
After clicking Edit Mappings, you can check and change the column mappings. If this is a new table you will also be able to change the data types for your table. Take the time here to get everything 100% right as it will save you time and rework later.
In the last step, make sure you select “Save SSIS Package to File System” and save the file in the same folder as your Excel Workbook.
The next time you need to load this table into SQL Server again, all you need to do is manually refresh the Power Query workbook (#1 below) and then (#2) double click on the SSIS package and click “Execute” to load the data.
Interested in Learning How to Do this Kind of Thing?
Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.
CLICK HERE to claim your spot.
Option 2: Use Power Update to Load the Table to SQL Server
The second (and I think more exciting) way to load the data from a Power Query workbook into SQL Server is to use a neat little tool that comes as part of Power Update.
First you should rename the Power Query. The name of the Power Query will be the name of the table loaded into Power Pivot and also the name of the table loaded to SQL Server. So it is important to get it right first.
Then instead of loading the table to Excel, you need to load it into the Power Pivot data model.
Once loaded into Power Pivot, navigate to the Power SQL Update menu and select “Update SQL Data”. If you don’t have Power Update loaded, you can get a trial version here.
Set the SQL Connection (#1) using the relevant credentials for your environment (#2). This is where you specify the database name too (#3).
Then select the table from the list of tables (#1) in your data model, configure the settings (#2, #3, #4 below) before saving (#5) and finally run the update (#6).
And that’s it – the table has been added (or appended etc depending on your settings) to SQL Server.
It is then possible to combine this Power SQL Update capability with the regular features of Power Update, and use Power Update to refresh the workbook and automatically insert the new data directly into SQL Server. This approach is a great SSIS replacement for casual users.
You an read more about the capabilities of Power Update here.
Matt Allington is the Author of the soon to be released book Learn to Write DAX, and provides Self Service BI Consulting and training services in Australia.
Get in touch with a P3 team member