Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Working with accounts data in Excel: Power Query

by
11th Apr 2014
Save content
Have you found this content useful? Use the button above to save it to your profile.

In the previous instalment in this series we looked in some detail at the Power View add-in for Excel. Microsoft has recently introduced two new “Power” add-ins and given the entire Power ensemble the name Power BI.

The two additional add-ins are currently previews with some known ‘issues’ and warnings about possible changes in the final release, so it’s worth reading the accompanying release notes before getting started. Power Map is a data visualisation add-in that starts where last time’s Power View playable charts finished, but this time we will concentrate on the other end of the BI process – getting at the data and preparing if for use. This is what the Power Query preview is for.

Microsoft describes Power Query as providing ‘an intuitive user interface for data discovery, data transformation and enrichment.’

Power Query

There are three stages to the Power Query process. First you connect to one or more sources of data, then you can ‘shape and transform’ the data prior to returning the result to an Excel worksheet and, optionally, adding it to the Excel Data Model.

Data sources

As well as a wide range of database data sources, Power Query can also extract data from web pages and less obvious data sources such as Microsoft Exchange, Facebook and a computer’s file system:

Excel 2013 Power Query - extract data from a variety of sources

In addition, the From Web option allows you to navigate to a web URL and Power Query will identify all the tables of data on that web page and allow you to select one or more of them. For example, here we have entered the URL of the BBC business, Market Data, Currency page.

Power Query identifies seven tables on the page. Hovering over each one will show the contents:

Import currency data from website

Once you have identified the table or tables you want to use, you can either Load the result directly to the worksheet or Edit the query first. Here we have selected the columns that we want to keep and then used the Remove Other Columns option from the Remove Columns dropdown in the Reduce group. This ribbon group includes a range of other options to remove unwanted information, including being able to select a number of rows, alternate rows or duplicates based on the columns selected:

Remove data columns from Excel Power Query import

In this case we also need to use the first row of the data in the table as our Headers. The Transform group includes a “Use First Row As Headers” option to achieve this:

Use data header option

Once the data has been loaded to the worksheet as an Excel Table, the Table Tools ribbon tab will include a Query tab that allows you to go back to the Edit Query screen.

There is another method of locating data to analyse. The Online Search option allows you to type in a search term. Power Query then suggests Internet pages that contain tables of data relevant to your search. Currently, the search seems to be heavily dependent on Wikipedia and US Government data, but no doubt the range of data available will increase in the future. Here we have entered a search term and then hovered over one of the options to see the detailed data:

Import data table from website search results

Just as with the From Web option, the data can either be added directly to the worksheet or you can choose to edit the query first.

Shaping and transforming data

We have already seen some of the simpler techniques for working with our raw set of data but there are many more. The Transform group includes options to set the data type for any column and there is also an option to split a column using a delimiter character or by number of characters:

Splitting Power Query data columns by delimiter characters

However, this is only the start of Power Query’s capabilities to shape data. It is also possible to add custom columns to external data which use formulae to transform data values. If you have ventured beyond the boundaries of Excel’s own functions and mastered all of the Power Pivot Data Analysis Expressions (DAX), then you might be searching for a further Excel expression challenge. Power Query includes its own formula language: Power Query formulae (also known as ‘M’). Power Query formulae can be used to perform all sorts of operations on the data.

Some of these formula expressions are equivalent to normal Excel functions – for example, Text.Start and Text.End perform the same operations as the LEFT() and RIGHT() functions in Excel. One point worth noting is that the Power Query operators are case-sensitive: text.start rather than Text.Start will generate an error. Just like the Power Pivot DAX expressions Power Query formulae go far beyond operations on single values and include database-type calculations. There are several dozen operators that work with lists or tables rather than individual values; you can split or combine data in many different ways and there are comprehensive options for manipulating text, number and date entries. There are many other types of operator including ones for working with specific types of database. This includes the Excel.Workbook() function that can return the names of all the sheets in a workbook:

Power Query has its own formula language

Append

Once you have sorted out your individual tables of data, there is still more that you can do. One particularly interesting option is the Append command in the Combine group. This can be used to append tables of data in a single, or multiple, Excel workbooks to create a single table that could, for example, be the basis for a PivotTable. An obvious use would be to combine the trial balance totals for several departments or offices of an organisation.

In this example we have 3 tables in our Consol1 workbook and another block of data in Sheet1 of the Consol2 workbook. We can use the Append command to append each of tables together. The result will be a single table combining all the rows from the other 4 tables:

Using Power Query for consolidation

We can add rows to any of our tables or to our separate workbook. As long as we save Consol2 after making changes, we can then right-click in our appended summary and choose Refresh All to incorporate the additional rows into our summary.

Conclusion

This was just an overview of some of the current capabilities of the Power Query Preview. As Microsoft points out in the release notes, any features in the Preview version are not guaranteed to make it through to the final version intact. Even so, Power Query provides further evidence of Excel’s journey from spreadsheet to spreadsheet/database hybrid.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.