Power BI
istock_pixdeluxe

EZ mini-series: The feature Microsoft tried to conceal

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

Simon Hurst expands his ExcelZone series to explore the revolutionary new Excel feature that Microsoft did their best to conceal.

In the run-up to Christmas we will be uncovering a radical change in the way that Excel works.

With Excel 2010, Microsoft surreptitiously undermined 30 years of spreadsheet development. Although there had been many significant changes in the capabilities of spreadsheets before 2010, the cell remained at the heart of the spreadsheet, with data being entered, and calculations performed, cell-by-cell.

It could be argued that Lotus Improv, and the subsequent development of pivot tables, had already moved spreadsheets away from purely cell-based calculations. But Improv was marketed as a separate product and ended up being a short-lived experiment, while Excel PivotTables had a very specific use in summarising and presenting data.

The 2010 changes introduced two features that liberated almost all types of Excel calculation from their dependence on individual cells. Both of these features were sneaked into Excel ostensibly as part of the external data tools.

The Power Pivot add-in included a data model that allowed tables of data, including tables within Excel worksheets, to be displayed in separate tabs with the possibility of performing column-based, rather than cell-based, calculations using an extensive set of new functions: Data Analysis Expressions or DAX. Significant as this was, it was not the most important of the new features.

Excel 2010 also saw the introduction of another new add-in, Power Query. Disguised as a tool for acquiring and 'shaping' data, Power Query was actually a cover for the introduction of a radical new approach to performing almost any type of standard Excel calculation on the contents of Excel worksheets.

The next stage in Microsoft's Machiavellian plot was to promote the Power Query tools from an optional add-in to an integral part of Excel as they took their place in the Get & Transform group of the standard Excel Data Ribbon tab. Even this wasn't enough – they were then included as part of a completely free new application: Power BI desktop.

ExcelZone has already covered the basics of the Power Query and Get & Transform tools. The forthcoming series will concentrate on applying the tools to common worksheet-based Excel problems, many of which are Any Answers favourites.

Replies (10)

Please login or register to join the discussion.

avatar
By chatman
10th Nov 2016 12:27

Hi Simon - I just do bookkeeping and basic compliance for very small businesses. Is this worth me learning?

Thanks (0)
Replying to chatman:
Simon Hurst
By Simon Hurst
10th Nov 2016 15:15

Hi chatman - if your work involves importing, or even copying and pasting, data from outside of Excel, or if you frequently use lookup functions or conditional sum functions such as SUMIFS() then it would be worth a look. The more you do with transactions and similar data, the more it might be useful. If you just use Excel as automated analysis paper, probably less so.

I hope that helps a bit.

Kind regards

Simon

Thanks (1)
Replying to shurst:
avatar
By chatman
10th Nov 2016 18:49

Thanks for your response Simon. My use is pretty much restricted to pivot tables, text-to-columns, SUMIF, MATCH, VLOOKUP, LEFT, RIGHT, MID, that sort of thing. No importing and no SUMIFS, so it sounds like it would be overkill for me.

Thanks (0)
Replying to chatman:
Simon Hurst
By Simon Hurst
10th Nov 2016 19:19

Now, there's a thing. Of the areas you mention, I would say the forthcoming series should be relevant to all of them. At the risk of spoiling the carefully-engineered suspense, the first episode is of particular relevance to 'text-to-columns, LEFT, RIGHT, MID'. If you do decide to give the series a go, I'd be very interested to hear how relevant you eventually find it to what you do.

Thanks (2)
Replying to shurst:
avatar
By chatman
10th Nov 2016 19:36

Damn! Now I have to find time to fit it in.

Seriously though, thanks very much Simon.

EDIT: I forgot, I use data tables and record the odd macro too

Thanks (0)
Replying to chatman:
Simon Hurst
By Simon Hurst
10th Nov 2016 19:46

On the plus side, if I'm right, you'll more than recoup that extra time!

Data Tables! In the top 0.5% of Excel users then...

Thanks (1)
avatar
By Marlborough
11th Nov 2016 11:17

Hi Simon - I have used Excel since is it was first introduced and also created macros when they could be recorded and edited. I haven't used these new tools, and Pivot Tables are a complete uknown to me, but I now need to get to grips with preparing presentations using Power BI.
What would you suggest as the best way to start out on this daunting venture.

Thanks (0)
Replying to Marlborough:
Simon Hurst
By Simon Hurst
11th Nov 2016 13:34

Hi Marlborough - before I respond in detail, can I just confirm that you will need to use the separate Microsoft Power BI application, as opposed to the Power BI tools within Excel?

Thanks (0)
avatar
By Marlborough
11th Nov 2016 14:07

Hi Simon - I am anticipating that.
I have access to both the cloud and desktop versions and have had a brief insight on a few basics.

Thanks (0)
Replying to Marlborough:
Simon Hurst
By Simon Hurst
11th Nov 2016 15:09

The separate Power BI application combines Power Query with elements of Power Pivot and Power View. The application itself is probably quite easy to get started with, you could begin with the built-in Getting Started option in the File menu of Power BI Desktop. This includes some introductory videos (narrated in an English accent for a change!). There is also a link from the Getting Started page to an extensive set of tutorials on the Power BI website.

The bigger challenge is probably understanding the underlying data concepts such as how to join tables of data together. If this is not an issue for you, I'd be inclined to download and install Power BI Desktop, run through the videos and then start experimenting.

Good luck!

Thanks (0)