skip to Main Content

By Matt Allington

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.

image

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:

image

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).

image

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.

image

Just follow the wizard steps to import from your Excel file into SQL Server.

image

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).

edit mappings

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.

mappings

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.

image

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.

image


Interested in Learning How to Do this Kind of Thing?

power query

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.

image

Then instead of loading the table to Excel, you need to load it into the Power Pivot data model.

image

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.

image.png

 

Set the SQL Connection (#1) using the relevant credentials for your environment (#2).  This is where you specify the database name too (#3).

image

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).

image

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.

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here.

This Post Has 34 Comments
    1. Power Update has a cool feature where you can trigger an embedded macro before or after refresh (or both). It seems to me that you could write a simple macro to save your data as a CSV (after refresh) and then trigger the macro to run after refresh.

  1. Thanks, Matt. Very handy solution.
    Agree, that currently no alternative to Power Query in getting data from “whatever” with an opportunity simply make cleaning, transformation and preparation before uploading.
    From my experience:
    We started to use this way for our daily data transfer for our self-servise solution several months ago. And have a very good practice, stable work, no issues.
    However, instead of PowerUpdate we use simple vbscripts scheduled in Windows Task Scheduler (cost saving option :-), and also option for those who doesn’t have administrator’s rights on workstation).
    Uploading step also can be scheduled with vbscript + task scheduler (but we went by way of normal SSIS usage of loop through files in folder, more control in this way in case of issues).

    1. You are right, there are a lot of ways to do this. Effectively Power Update is a UI on top of the tools you mention, and it will save you money to do it yourself. Of course we often don’t book our own time as a cost when we do these things, and as a result it is hard to compare $500 with “free”. But as a contractor, my time is a real cost to customers, and hence it is worth their while buying good software tools that will save time.

      1. Matt, you are absolutely right. However, in big companies we often can meet too much bureaucracy, and in some cases it is diffcult to prove that buying of new software is cheaper than write own scripts (which are definitely less effective).

    2. Hi Ivan, your idea is very good with a macro, but in my experience you can quickly run into some limitations:
      • With a macro, you can only use macros if the Power Query results are loaded to an Excel worksheet, but that only supports up to around 1 MM rows(due to Excel limitations), and while saving data from there to a csv file can be relatively fast (as it can be done with a save as on the sheet), importing the data to an SQL database can be quite slow if done by macros or requires an ETL tool like SSIS.
      • Power Update takes advantage of the Power Pivot compression to you can load 10’s of millions of transaction very effectively from Power Pivot to SQL.
      • Additionally, any calculations you do in DAX are also loaded directly to SQL with Matt’s proposed solution, so these are loaded directly to SQL as well for ‘Transforming Data’ with DAX.
      • Lastly, I’d like to point out that Power Update enables email notification on success or error of the scheduled job, and the ability to run macros before or after the refresh.
      You don’t have to be Administrator on the ‘Agent Machine’ when running the Power Update jobs. You only need to be Administrator to define the Task Scheduler job, and you may remove the admin privileges afterwards.
      In summary, both two great solutions that server different use-cases. Thanks for posting your solution.
      Chris.

      1. Hi Chris, you mentioned very important points. Huge thanks for this. Good to know what I can face in future. Current volume of data far not so huge.
        I tried Power Update trial, it left good impression, but we have very strict rules related to admin rights. It is not a solution when for each new scheduled task I have to contact IT department. Therefore I posponed idea to buy a full version (but keep in mind ;))
        Ivan

        1. On thing to consider is that you can create scheduled tasks over folders. So as long as your new file can be refreshed in the same batch and gets placed in the same output location, there is no need to make any changes. Just add the new file to the folder!

  2. Hi Matt,

    I struggle, at times, to use Power Query to import my data as opposed to just using Power Pivot. It seems like using Power Pivot it fairly straight forward and refreshes easily (manually). Am I missing the big picture?

    1. I had dipped my toe in Power Query but never fully committed until Ken Puls happened. I watched him walk through the product with his best practices at the PASS Business Analytics conference last year and now I prefer it to just a naked Power Pivot import. The thing you gain from using Power Query is optionality and a lot more functionality in shaping the dataset — initially this doesn’t seem worth it because the Power Pivot method is just so much simpler but 6 months into it I can say that Power Query can make some really magical things happen.

    2. PowerPivot import (from my limited experience) works fine if the file you are importing from is already setup and ready to analyze or connect to other sources.

      PowerQuery is the bees knees when it comes to loading multiple files of raw data that needs to be modified into a single workable table. (That is the level I am on which is only the basics of PowerQuery)

      Here is a good post on it:
      https://powerpivotpro.com/2015/01/power-query-for-excel-combine-multiple-files-of-different-file-types/

    3. Power Pivot Import and Power Query are 2 different things. Power Pivot has built in Extract and Transform tools. They are very good, and allow you to select columns and rows from the UI, or write detailed SQL code (if you are importing from a DB).

      Power Query on the other hand is an Extract, Transform and Load tool. It is the Transform features that I am talking about here, and yes it is a bit harder. But if the dat you need is not in the right shape, or if it needs to be cleaned, then you simply must use some form of Transform tool.

  3. Matt,

    Lots of traditional BI people laugh at this approach but I’ve done it in a production environment just the way you described and it works great. In fact we built a whole data warehouse just using the Power BI tools (with a final push to SSAS Tabular) — it doesn’t have all the bells and whistles but it was extremely cheap to build and has been used as a rapid prototype for working with our data model.

    Thanks for writing this article and hopefully inspiring other Excel users to move up the value chain and use these tools.

  4. Hi Matt,

    Great article and thanks for sharing!

    I’ve been a Power Pivot geek since 2010. I realize now that Power Query is just as valuable and a great compliment to Power Pivot.

    Like you, I prefer to store my data in SQL Server rather than have spreadsheets all over the network. Also agree that SSIS is a bit much for ad-hoc data loading. I’m hoping that PQ will be enhanced to natively “push” data into SQL Server.

    Thanks Again!
    Dave

  5. Excel lifer, SQL Server dummy here,
    What is the benefit of loading data to SQL Server and then connecting to your power pivot excel workbook rather than just loading all the data into the workbook itself through connections?

    1. Would going through SQL Server NOT making the excel workbook such a big file? Example I have about a 50 CVS files that add up to a gig of data, but when loaded to the excel book, it compacts all the info to about 120 megs.
    By using SQL server, would the excel workbook be even smaller since the data is not loaded, but rather connected to it?

    1. The benefits of loading to a DB first (eg SQL Server) are.
      1. Long term storage of lots of historical data.
      2. Opportunity to pre-summarise the data
      3. Opportunity to use SQL code and views to easily select subsets of the data

      I load daily sales for a client into SQL. There are millions of rows of data each week. I then pre-summarise the data into more manageable buckets (eg weekly data rather than daily). If they ever need to drill into a daily data issue, I have the data stored in the DB and can fetch it as required.

      If your data is of a manageable size (eg no more than 1m rows over the life of the report), is already structured at the level of granularity and shape you need, and is sent to you in a single CSV that grows in length over time to include the new data, there there is probably no benefit.

      There would be no difference in compression benefits of loading from SQL vs CSV (unless there is some pre-summarisation as described above). The Power Pivot Workbook takes a full compressed copy of all the data, and hence once the data is loaded, there is no reliance on the source at all.

      Finally, in my experience, data loads from SQL server faster than it loads from CSV for large data sets.

  6. Great article and you bring more reason for Excel Analysts to begin learning SQL Server. Question – I have install SQL Server and I am lost even during the installation! Anyone seen any types of videos or books for complete beginners? I’m talking a basic knowledge of SQL, a basic knowledge of databases, and some Access skill is all I have. Some other people I know interested in learning SQL Server have even less basic knowledge than that. Thanks again for the solid article.

  7. First, I think it should be called ‘Save to’ instead of ‘Load to’ (old ETL – history). I should be able to ‘Save to Database’ (image6.png above).
    Normal users have read/write access to File servers, but not to Database servers. I think it’s one of the biggest IT-mistakes, and direct responsible for the Excel mess.
    The good thing is that ‘Data Catalog Search’ (database browser) has the possibility to democratize Master database developments. Similar like, from community driven standards (Wikipedia) , to expert driven ISO-(certified)standards. We just need (Self Service) ‘Save to Database’, and a good ‘DataTable browser’ (sorted by relevance). Self organization does the rest.

  8. Nice post Matt – I’m an experienced SSIS & SQL developer but I now push Power Query as far as I can for rapid Agile-style development and in many cases superior functionality. When it runs out of puff I often move as little as I can of the early steps into SQL (Views or Functions) and keep as much of the logic as I can in PQ.

    PQ is also the best tool I’ve found for data exploration/profiling and generating expected results for testing.

    I was a bit bummed to hear PQ integration with SSIS wont make the cut in SQL 2016 – good news for Power Update I guess.

    I just have a minor comment on your Option 1 steps. The default settings you showed on the Column Mappings page will most likely fail on any subsequent runs – it will try to create the table when it already exists. Personally I would edit the SSIS package, but for casual users the best option using the wizard is probably to re-run the wizard, point it at the newly-created table and choose Append.

    If they actually want a full refresh on each run, they should probably choose the “Drop and recreate destination table”, with the caveat that this might break any downstream objects (e.g. SQL Views). Downstream PQ should be fine – it is very resilient (unlike SQL, SSIS etc).

  9. I found a $50 Add-in that can do this. Called SaveToDB. Still learning how to use it.

    I love the idea of using SQL Server Compact / CE to save small tables to, because it is easier to distribute to customers (without SQL server) and have a smaller footprint.

    But Power query does not connect to these databases?

    Is there a way to do that?

    1. Of course this is Power Query with a new set of clothes – but it is an interesting idea and I hadn’t thought of using Power BI like this. I guess if you have Excel with Power Query and you need the data yourself, then there is no benefit. However if your data is distributed, or you need to distribute it, then wow.

    2. We are looking at adding this to Power Query as well, then you will get all the benefits of scheduling, email notifications, partial data loading to SQL as well. The only difference is if you write your Power Queries in Excel or Power BI Desktop. The result is the same, just automated.

  10. This is very useful but I am still not sure where the data is being held. Interested to find out more and where things leads to if some one show me the way.

  11. Hi Matt,

    Thanks for the post.

    I am someone like you who tried SSIS however, not able make it to comfortable level.

Leave a Comment or Question