Excel 2016 “Get and Transform” and the “M” language for advanced Business Analysis

September 3, 2016 david.taylor No comments exist

Exploiting business opportunities ahead of the competition relies upon strong analytical tools which are agile yet are also powerful. Analysts within the business need such tools to be useful out of the box but will also need them to support advanced techniques when necessary.

Spreadsheets have always been the tool of choice for financial analysis and financial modelling. What about for data analysis and data modelling? Legacy spreadsheets had lookup functions that helped with data, non-numeric analysis, but as mentioned in an earlier post the fit was clumsy and error prone. Modern Excel, 2010 and beyond, include data tools either built right in or as add-ins from Microsoft. Power Query is a case in point. It’s built into Excel 2016 under the Data tab as “Get and Transform”.

Power Query uses a language informally called “M”. In this blog post with just a few lines of code a table of sales records is put through a “Get and Transform” process. The example here will add a cumulative running total automatically using the M language.

2016-09-03

The Excel Query Editor contains three items:

  1. A query to extract the Sales data from a source and to add the index column
  2. A function called YTD() which returns a summation of the sales value for the given index plus all earlier periods; and
  3. A query that references the first query but adds the column “Running Total” which transforms the table into a richer data set to be loaded into Excel.
 Now for a look at the code for the YTD() function:
2016-09-03 (1)How it works is via recursion.
For example to calculate a result for the index value of 9, the function call is made “=YTD(9)” in another query. It will evaluate the sum of 285 plus 3714 automatically. Here’s the advanced part; It gets 3714 from @YTD(8) which it calls itself from within the “else” calculation inside the function itself. That inner result is evaluated as sum 280 plus 3434. Similarly it gets 3434 from YTD(7) which is evaluated as sum 368 plus 3066 and so on….until it reaches YTD(0) which is a terminal value detected by the “If index=0” then 497

The way that M handles recursion is that a function can call itself from inside that same function provided that it uses the @ symbol when calling itself. so @YTD(Index-1) is used each time that Index is above the value 0.

Here is a link to the above solution in a workbook.
https://1drv.ms/x/s!Agc3uI9a_2aOiUhhXmb5HS7G528j

At the heart of Excel’s “Get and Transform” or Power Query capability in the case of Power BI, the M language provides advanced transformational capability for the savvy analyst. The M language is not procedural but is a declarative and functional language which does not require the analyst to spell out every step; in contrast to VBA which does. So looping through data record by record is handled using elegantly concise function blocks, leaving time to instead analyse the insights and get ahead of your competition.

Leave a Reply

Your email address will not be published. Required fields are marked *