Steel with sparks
istock_michal-rojek

Supercharge Excel: Healing Excel's Achilles heel

by
9th Dec 2016
Partner The Knowledge Base
Share this content

This is the second part of a series of articles that looks at how Excel's Power Query/Get & Transform tools can be used for a great deal more than just acquiring data from external sources.

Introduction

The first part of the series included an overall introduction to how these tools work, what they are capable of and how they might require a different way of thinking compared to 'standard' Excel techniques. As stated in that introduction, for convenience, we will refer to Power Query throughout, but Excel 2016 Get & Transform and Power BI Get Data operate in a similar way.

Dealing with dynamic ranges

The purpose of this series is to see how Power Query can help with worksheet, rather than just data, problems. One area that often causes an issue in Excel is the ability to cope with ranges of cells that need to expand to accommodate additional information – usually adding rows to a table of data. The introduction of Excel Tables in Excel 2007 helped to some extent by allowing references to ranges to expand automatically to include added rows.

However, there are still some operations where even the advantages of Tables don't provide a complete solution. For example, you might have several tables of information that you want to combine into a single table that you can use as the basis for a PivotTable or the use of Excel summary functions. If the number of rows in each table is static, you could do this by using normal Excel formulae and functions with references to each row, but as soon as you introduce the possibility of the number of rows in each table changing, the whole process becomes a great deal more complicated.

In this example, we have the sales results for three countries held in Excel Tables in three separate worksheets. We want to combine them into a single Table to use as the basis for a series of PivotTables that analyse the entire set of data:

Simon Hurst

Just to make things a little bit more complicated, some of our columns don't have consistent names as we can see for the Customers and Product columns.

Tables to Queries

We will use Power Query to consolidate our data. There are several different methods we could use to do this, but we'll start with what is probably the most straightforward. We will create queries based on each of our three Tables and then combine the queries. By default, each query will be given the same name as the Table name that it was based on, but this can be changed if required.

To create a query that includes the content of an Excel Table we just need to select any cell within the Table and choose the From Table command. This displays our Table data in the Query Editor. For the UK Table we can just use the Close & Load dropdown, Close and Load To… option to just create the connection to our Table without loading it back into another worksheet:

Simon Hurst

We do the same for our other two Tables but, before we use Close & Load, we double-click in the Customers.CompanyName and ProductName column headings to change them to be consistent with the UK Table:

Simon Hurst

Combining queries

We now have the data in our three Tables available as three separate queries. We can use the New Query dropdown, Combine Queries, Append command to join them together:

Simon Hurst

Until a recent update, combining more than two tables was a slightly messy process. Either you had to combine two tables and then combine each additional table to the previous combined query, or you could add the query names directly to the underlying code that was created. However, there is now a 'Three or more tables 'option which makes things much easier. We can just select each of our tables in turn and add them to our new query:

Simon Hurst

Because we ensured that each of our queries used the same column headings, the append operation will create one table with the same columns as each of our tables, but including the rows from all of them. Had our columns had different names, additional columns would have been created for each different column name:

Simon Hurst

We can see our three Table queries in the left-hand query pane. We have changed the name of our new query from the default of Append to Consolidated. We can then go ahead and Close & Load our query, this time choosing to load it into a new Excel worksheet:

Simon Hurst

Here we can see that our consolidated table includes the 641 rows from our original UK, France and Germany Tables.

Why not just use Copy and Paste?

There are benefits in setting up the process in this way. We haven't had to use any VBA code or type in any complicated Excel formulae or functions. Instead, we have just used some of the Power Query interface options to create our consolidated output. However, perhaps the greatest advantage is not the comparative ease of set up, but how we deal with changes to the data. If this had been a one-off requirement, it would have been quite simple to have just used Copy and Paste to create our single table. However, where we need to cope with changes to our data this would have meant repeating the process whenever the data changed. Because this involves a series of manual operations, it not only takes time, but also introduces several opportunities for making a mistake. Using the Power Query method can reduce the update process to a single click, or keyboard shortcut, or even automate the process completely.

To demonstrate this, we'll add a few more rows to our UK Table:

Simon Hurst

Because our data is in an Excel Table, as we add the rows our Table automatically expands to encompass the new content. If we refresh our queries, the tblUK query will include the new rows as being part of the Table upon which it is based, and our Consolidated query will included all the rows now within the tblUK query, and our other queries:

Simon Hurst

Clicking the Refresh All command in the Connections group of the Data Ribbon tab (or using the Ctrl+Alt+F5 keyboard shortcut) refreshes our queries and we can see that our Consolidated query now loads the additional 8 rows from our expanded UK Table.

We can go even further and set our Data Connections to refresh periodically, although it should be pointed out that this is still not the same as Excel's automatic formula recalculation. With queries, the refresh happens based on elapsed time, not on whether any 'precedent' value has been changed.

Simon Hurst

Conclusion

Although we have automated the process of dealing with changes in a defined set of Tables, we would still need to make manual changes to add any complete new Tables. In the next episode of the series we will see how to fully automate the process with variable numbers of Tables as well as just changing numbers of rows.

Tags:

Replies (14)

Please login or register to join the discussion.

avatar
By AndrewV12
16th Dec 2016 13:09

A good solid article, well done Simon.

Thanks (0)
Replying to AndrewV12:
Simon Hurst
By Simon Hurst
17th Dec 2016 09:21

Hi AndrewV12 - many thanks for your comment, glad you liked it.

Thanks (0)
avatar
By AfinNet
16th Dec 2016 14:26

False. Without a graphical query wizard the newest tools from Microsoft are usable for IT only. Super fat end user is typical for Excel we all know, but the best way is a combination fat and thin server but probably you don't know what i'm talking about.
Where is the FileId if there is no column with country id?
Where is SQL's WHERE clause? (criterias)
Where are joins? To take all data onto Excel side and then to filter? We have the XXI century!
See: http://afin.net/webcasts/ABC_ConsolidateExcelFiles.swf and some other webcast there.
Microsoft has no idea how to teach analysts the professional analysis. We do http://www.szkolenia.ue.wroc.pl/117,165,sql_basics_for_excel_users.html

Thanks (0)
Replying to AfinNet:
Simon Hurst
By Simon Hurst
17th Dec 2016 09:39

Hi AfinNet, this particular series deliberately concentrates on using the Power Query tools with worksheet data rather than external data. When working with external data I believe Power Query uses query folding to improve performance by doing as much work as possible at the server, rather than client, end.

Given the content of your video, I hope you will find the next part of the series useful, where we will be looking at automating the consolidation of data from separate workbooks.

As I'm sure you know, PowerPivot and Power BI do include the ability to create joins graphically. I find the method used in Power Query to be straightforward and, in coping with a wide range of join types, powerful. We should be looking at this in a later episode.

Thanks (0)
avatar
By MLewis1960
19th Dec 2016 11:08

is there a way to get a copy of the file you are starting with so as to follow along doing as I read?

Thanks (0)
Replying to MLewis1960:
Simon Hurst
By Simon Hurst
19th Dec 2016 15:07

Thanks for your interest, good idea, just discussing how best to make it available. I will let you know soon.

Thanks (0)
Replying to MLewis1960:
Simon Hurst
By Simon Hurst
20th Dec 2016 18:37

Sorry for the delay. Try this link to an embedded version of the file on a blank page on my website:

http://www.tkb.co.uk/Supercharge2Append.html

There should be a download icon at the bottom right of the spreadsheet.

Thanks (1)
avatar
By androo235
16th Jan 2017 12:36

Had posted here saying, "yes a link" would be good. But have seen now that you've provided one. That said I read through this, and having already used PQ quite a bit, realised that what's here is pretty basic (nevertheless I didn't know that using the from table option with a cell of a table selected not only created the table but also the query, so that's a useful shortcut).

Thanks (0)
Replying to androo235:
Simon Hurst
By Simon Hurst
16th Jan 2017 12:53

This particular series isn't designed to be particularly advanced but to just show ways in which Power Query can be used to solve 'normal' Excel issues rather than just being a data analysis tool. Having said that, the next part takes the ability to automate a stage further:
https://www.accountingweb.co.uk/tech/excel/supercharge-excel-consolidate...

and future parts will feature some more advanced techniques such as merging queries.

Thanks (0)
avatar
By markandrew.saunders
31st Jan 2017 16:03

Hi Simon
I found this topic very helpful.
I've applied it to a real life example with two tables.
However, in the combined table I get errors that are caused by "cents" after a decimal point.
For example $20.00 will be included in the combined table but $20.05 will not be. Any idea why?

Thanks (0)
Replying to markandrew.saunders:
Simon Hurst
By Simon Hurst
31st Jan 2017 16:26

Hi - glad you found it helpful.

What is the data type of the column that contains your $ values? If it isn't set to Decimal Number or Currency, try changing the type to one of those two and seeing whether any of your values are now reported as 'Error'. If they are, it might suggest that there is a problem with the format of the original values that is causing them not to be recognised as numeric.

Thanks (0)
avatar
By CS Accountancy
21st Apr 2017 17:00

Hi, very good article. Is there anywhere were we can download the worked data above to practice with?

Thanks

Conor

Thanks (0)
Replying to CS Accountancy:
Simon Hurst
By Simon Hurst
21st Apr 2017 17:28

Thanks for your comment. It should be here:
http://www.tkb.co.uk/Supercharge2Append.html

Thanks (0)
Replying to shurst:
Simon Hurst
By Simon Hurst
21st Apr 2017 17:59

I should have mentioned that there is a button to download the workbook at bottom right.

Thanks (0)