Seems, Excel charts is an area that till now wasn’t considered in blogs as a target for Power Query application (Get & Transform in Excel 2016).

Nevertheless, PQ can replace some VBA solutions and make your workbooks macro-free.

In far 2015 my colleague Zoltán Kaszaki-Krsjak shared with me a very good example of how Power Query can help with generation of specific tables for specific charts, which are widely used in our organization.

Idea to write a blog post about this technique became dusty in me OneNote, and probably would wait more if only Jon Peltier hadn’t attracted my attention to this topic again by his recent post.

Sample workbook contains a solution for Bar-Mekko chart (or “variable width column chart”)

Such chart allows to easily see share of categories, growth or absolute value. Can be used to compare market segments or productivity of departments / subsidiaries. Red line in this case shows average growth – another small but important detail.

Interested how to build it?

Solution schema

First of all, we need to prepare a basic table

“Average” contains same value as we need to draw a line.

“Tech” is a technical column, which is used for position of labels on X axis. It looks much better than standard Excel axis labels.

Share and Growth – corresponding Share and Growth for categories.

Using Power Query we will transform initial table to

Why and how to build a chart based on such table you may read in Jon Peltier’s post.

Here I will focus on Power Query part.

Zoltan’s version of transformation code is not very long, but contains interesting parts like

Running totals

Reference to previous row (with prevention of error)

Some parts of this M-agic code are really not obvious and do magic.

Let’s start from this row

It calculates running total of specific column and converts percentage to number

Next row calculates moving average (such approach deserves a separate post)

Using index column [ID] and function List.Range we can select necessary number of elements, then use any of List.* functions to calc desired.

In this particular case – List.Average.

X1, X2, X3 – are future parts of Axis X.

We need these values to

1. Draw an area charts – start and finish each chart at right place of Axis X
2. Place labels correctly – in the middle

Consider another interesting row

It helps to shift values of column [Category] one row upwards.

I bet you have a question mark in your head now. No worries, I had the same feeling.

“Why the heck he uses “?” in the end, and how part after “each” works?”.

Step-by-step

Separate expression on parts

Buffer – is a table

[Category] – is a column of this table

{ … } – is a reference to an item in this column

[ID2] – is a field, that is a result of step (add_index = Table.AddIndexColumn(sort_rows, “ID2”, 1, 1) – it contains number of each row. Small but important moment: this index starts from 1, when then first item of list and the first row of table have an ordinal index of zero.

Answer to “question mark problem”, we will find in a documentation to M language (can be found here)

We need part “Item Access”

Looks like an idea for Gil Raviv’s pitfall-series.

Shortly, if you want to simply ignore error-causing references – use “?” to return null instead of error. Once again:

Small challenge – make such shift of cells in a column without additional column.

And final part

Data labels for Axis X using additional Tech series can be updated using macro:

I do not include it into sample-xlsx file to leave it macro-free.

Once you built your solution for Mekko, with your list of categories, you can apply this macro, then delete it. Data label references remain after data refresh.

In conclusion

Having such solution is a great help, it saves a lot of time.

But this is a workaround, hack of Excel charting possibilities – you have to prepare crazy table, then build several series of area charts…

OK, this is a secret knowledge, part of the Force, that saves job for me and other analysts.

However, Excel team added new charts in Excel 2016, maybe at some day we will see default Mekko as well.

Despite, Mekko chart for Power BI is available, it is full-Mekko, I couldn’t make a Bar-Mekko from it.

Bar-Mekko from my point of view is more readable, and can be very informative.

This demo solution doesn’t contain smart-labelling for Excel chart, instead it shows simple approach (not perfect)

For label position we can make an offset

Can use CHAR(10) to add “new row” in label, “+” sign for positive, and custom number format using TEXT() function

As usually, you may find sample workbook here.

3 thoughts on “Bar-Mekko chart in Excel with Power Query”

1. Ivan, this was really worth reading.

I wasted quite some time with your ‘small’ challenge using Table.TransformColumns, but couldn’t make it. However, the following does work:

= Table.ReplaceValue(Buffer, each [Category], each Buffer[Category]{[ID2]}?, Replacer.ReplaceValue, {“Category”})

Interestingly, without the question mark, you won’t get an error, but a wrong value …