SQL Server has always had a strong connection with Excel and for two very good reasons:
1) SQL Server is excellent at storing, querying and analysing large data sets.
2) Excel allows users to work with and present data in any way that they want.
Allowing Excel to extract data from SQL Server databases is not new and it has been possible for many years using tools like Microsoft Query. Later extensions include:
- PowerPivot: For extracting and storing data for analysis in Excel.
- Power View: A user reporting tool.
- Power Map: A map drawing tool.
- Power Query: An advanced querying tool (the subject of this blog).
- Power BI: The final touch bringing together the above products in a cloud-based solution on Office 365. It lets users analyse, present and share data online.
So what does Power Query in particular have to offer?
1) It comes as part of Excel and has its own Power Query ribbon.
2) It can connect to a whole range of data sources including SQL Server, Excel, Access, Oracle and none database related data sources including SharePoint lists, Facebook, Active Directory and much more. Part of the menu for other data sources is shown below:
3) You can define queries using the Query Editor to extract data, add calculations and carry out simple transforms. The definition of the query is saved inside the workbook and the query results are stored in Excel worksheets. Multiple queries are supported per workbook. An example below shows a query on the Product table from AdventureWorks in SQL Server:
4) Multiple queries can be combined, even if the data comes from completely different data sources. The example below shows a query from a SQL Server table merged with a query from an Excel table. It includes a ‘Merge’ to combine the results followed by an ‘Expand’ to expand the columns from the second query. The steps used to create the query are shown on the right of the screen and each one can be clicked and changed if required:
5) A further example shows a query that has been filtered and has a computed column. Note this column was added using the Add Custom Column button and the expression shown in the query window was generated by Power Query:
6) For the Excel power users, Power Query even has its own formula language called ‘M’. When using point and click Power Query writes the script in ‘M’ language for you. You can however manually edit or extend as you wish. The screen below, menu View and Advanced Editor, shows the full ‘M’ script for the above example:
7) For those users who have signed up to the Power BI cloud, it’s possible to publish and share queries and hence data with your colleagues. Just click on the My Data Catalog Queries button in the Power Query ribbon:
In summary Power Query enables end users to extract data from all kinds of data sources and work with that data directly in Excel, filtering, merging, adding calculations and so on. They can then go on to process the results using standard Excel functionality such as expressions, tables, charts and pivot tables. Power users can write queries directly in the new M language.
By Geoff Ballard, Chief Technical Officer
Have a comment? Let us know below, or send an email to [email protected]
