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

Power BI redefines the spreadsheet

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

More advanced versions of Excel 2013 offer extra analysis and viewing tools that take the spreadsheet into the realm of analytical databases. Simon Hurst explores what these tools can deliver.

The Power BI suite

Excel Zone has previously covered several components of what has now become the Microsoft Power BI suite. Excel 2010 saw the introduction of PowerPivot, with Power View being added in Excel 2013. A few months ago, Power Query was made available as an add-in for Excel 2010 and 2013 and Power Map was introduced for Excel 2013.

However, things are not quite as straightforward as that. Although PowerPivot was a free add-in that could be installed in all versions of Excel 2010, in Excel 2013 the Power add-ins are only available with certain editions of Excel 2013. Until the early part of this year, the only edition of Excel 2013 that supported the power add-ins was Professional Plus. Not only is this the most expensive of the Excel 2013 editions it is not available as a ‘retail’ purchase, but only through the Office 365 rental route or the volume licensing program.

Fortunately, the retail version of Excel 2013 standalone, at a cost of around £100, now also provides access to these add-ins.

Power Query

We have looked at some of the things that Power Query can do in the Working with Accounts Data series. When approached as a whole, the Power BI suite is likely to change the way we use spreadsheets. The Power BI add-ins all work with database-style data, whether it’s stored externally or within Excel itself. The other add-ins bring some database features to Excel, but Power Query goes even further in bridging the gap between spreadsheets and databases.

PowerPivot includes the ability to collate data from a variety of sources and create simple links between tables of data from different sources. However, as its name suggests, PowerPivot concentrates on turning the underlying data into PivotTables. Power Query includes many database tools not readily available in PowerPivot and the end result can just be an Excel table. Power Query has a Merge option to connect tables where there is a suitable field in each table to make the link. Unlike PowerPivot, Merge in Power Query doesn’t require the field on at least one side of the join to contain unique values. Power Query also includes an Append option that allows multiple tables with common fields to be combined into a single table.

Append has real practical potential for working with accounting data. For example, problems of organising data for reporting in Excel often involve combining data from different sources – perhaps offices or branches – so you can report across the whole data set. Power Query’s Append command lets you do this, including allowing you to combine fields that start off having different names and enabling the addition of an identifier to all the rows showing which branch those rows came from.

As well as sorting out problems that seem clearly the province of a database, Power Query could start muscling in on tasks that would otherwise have us reaching for some of Excel’s more complicated functions. If you have two tables in Excel and you want to look up a value from one to the other, you could add a column and use an appropriate VLOOKUP() formula. However, you could also add both tables of data to Power Query and then create a link between them using the field you would have used in your VLOOKUP().

Unlike VLOOKUP(), Power Query can cope with multiple results per match, giving the option of aggregating the multiple values into a total, or creating additional rows:

Excel's Power Query can aggregate multiple results  into a total or create additional rows.

So, as well as VLOOKUP(), Power Query could also replace the whole set of conditional sum formulae and functions including SUMIFS(), SUMPRODUCT() and array formulae.

Power Query is a bit more picky about text case and field value types than Excel. By default, Red won’t match red and columns containing numbers won’t necessarily be recognised as being value fields rather than text fields. If your aggregate only gives you options to Count, rather than to Sum, then Power Query hasn’t recognised the column as containing values and you will need to define the column data type within Power Query. In fact, this is a solution to another common Excel data problem – converting numbers that are interpreted by Excel as text, perhaps because they have been imported from a third-party application. The data can just be loaded into Power Query where the correct data types are set for each column.

The various transformations you apply to your data are saved as steps which will be re-applied to the raw data source whenever the query is refreshed. Each step can be edited if required.

There are many other Excel functions under threat. For example, Power Query includes an Unpivot option which allows you to transpose columns into rows, including unpivotting only selected columns.

Tables

Where the end result of PowerPivot is PivotTables or PivotCharts, Power Query loads its results into tables of data. These can be optionally displayed in a worksheet making the results much more visible than the hidden Power Pivot Data Model, whilst allowing you to base PivotTables, charts or any other relevant Excel object, such as the other Power BI add-ins, on the data table.

A refreshing change…

Although there are many positives in using Power Query as opposed to Excel formulae, there is a key difference. Usually, as long as you have the Excel calculation mode set to Automatic, formulae results will be recalculated whenever a change is made, ensuring the whole workbook is always up to date. With Power Query, the different tables and queries need to be refreshed to reflect changes in the underlying data so, if you forget, your results won’t necessarily reflect the latest changes.

Conclusion

Firstly, Power Query and most of the other Power BI features, are only relevant for certain types of Excel work. If you are producing a cash flow forecast rather than analysing large quantities of data, Power Query is unlikely to be of much help.

Where you are analysing data, because it’s so easy to work with Excel tables in Power Query and the results can be loaded into another Excel table, Power Query may well be a real alternative to making manual changes to data and to using several different types of Excel function. There are consequences for the robustness of models also. On the plus side, there could be very few, if any, Excel formulae to go wrong and need to be checked.

The database approach also ensures that changing volumes of data are dealt with automatically without needing to copy formulae or extend ranges. To set against this, there is not only the refresh question, but also the lack of transparency and lack of familiarity. Power Query manipulates the data in the background rather than in formulae displayed in cells, and database techniques are nowhere near as well understood in general as spreadsheet formulae.

Replies (2)

Please login or register to join the discussion.

avatar
By paulwakefield1
10th Oct 2014 09:25

Refresh

Simon,

Thanks for a really useful post. I have been wondering about the relationship between Power Query and Power Pivot. Power Query is being developed so fast with monthly releases, I agree it could become (and maybe already is) incredibly useful.

The lack of auto refresh is a bore but under the query's connection properties, you can set a minimum refresh period of 1 minute and also "Refresh on open" which together nearly get around the issue.

Thanks (0)
Simon Hurst
By Simon Hurst
10th Oct 2014 21:43

Power Query refresh

Thanks Paul - yes, that's a good point, particularly as Power Query connections to Tables within Excel allow for automatic refresh, whereas PivotTables based on Excel Tables in the same spreadsheet don't.

Thanks (0)