Bar-Mekko chart in Excel with Power Query

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

Source = Excel.CurrentWorkbook(){[Name="input_table"]}[Content],
add_empty_row = Table.Combine({Source, Table.FromRecords({[ID=0, #"Share %"=0] })}),
fill_constants = Table.FillDown(add_empty_row,{"Tech", "Average"}),
sort_id = Table.Sort(fill_constants,{{"ID", Order.Ascending}}),

add_running_sum = Table.AddColumn(sort_id, "X2", each 
  List.Sum(List.Range( sort_id[#"Share %"], 0, [ID]+1)) * 100),

add_running_avg = Table.AddColumn(add_running_sum, "X1", each 
  if [ID] = 0 then 0 else List.Average(List.Range( add_running_sum[X2], [ID]-1,2))),

duplicate_col = Table.DuplicateColumn(add_running_avg, "X2", "X3"),

#"unpivot_X1-X3" = Table.UnpivotOtherColumns(duplicate_col, 
  Table.ColumnNames(Source), "Attribute", "Axis X"),

sort_rows = Table.Sort(#"unpivot_X1-X3",{{"ID", Order.Ascending},
  {"Attribute", Order.Ascending}}),
add_index = Table.AddIndexColumn(sort_rows, "ID2", 1, 1),

#"Added New Label Y" = Table.AddColumn(add_index, "tmp", each 
  if [Attribute] = "X1" then [Label Y] else null),
#"Removed Label Y" = Table.RemoveColumns(#"Added New Label Y",{"Label Y"}),
#"Renamed Label Y" = Table.RenameColumns(#"Removed Label Y",{{"tmp", "Label Y"}}),
#"Added New Label X" = Table.AddColumn(#"Renamed Label Y", "tmp", 
  each if [Attribute] = "X1" then [Label Value] else null),
#"Removed Label X" = Table.RemoveColumns(#"Added New Label X",{"Label Value"}),
#"Renamed Label X" = Table.RenameColumns(#"Removed Label X",{{"tmp", "Label Value"}}),
#"Added New Share" = Table.AddColumn(#"Renamed Label X", "tmp", each 
  if [Attribute] = "X1" then [#"Share %"] else null),
#"Removed Share" = Table.RemoveColumns(#"Added New Share",{"Share %"}),
#"Renamed Share" = Table.RenameColumns(#"Removed Share",{{"tmp", "Share %"}}),

Buffer = Table.Buffer( #"Renamed Share" ),

Categoty_tmp = Table.AddColumn(Buffer, "Category_tmp", each 
  Buffer[Category]{[ID2]}? ),

Growth_tmp = Table.AddColumn(Categoty_tmp, "Growth_tmp", each 
  Buffer[#"Growth %"]{[ID2]}?),

#"Replaced Value" = Table.ReplaceValue(Growth_tmp,null,"",
  Replacer.ReplaceValue,{"Category_tmp"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(
  #"Replaced Value"[Category_tmp]), "Category_tmp", "Growth_tmp", List.Sum),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",
  {"ID", "Category", "Growth %", "Attribute", "ID2", ""}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns", 
  List.Combine({ {"Axis X", "Average", "Tech", "Share %", "Label Y", 
  "Label Value"}, Source[Category] } ) )
in
  #"Reordered Columns"

Let’s start from this row

add_running_sum = Table.AddColumn(sort_id, "X2", each 
  List.Sum( List.Range( sort_id[#"Share %"], 0, [ID]+1) ) * 100),

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


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

add_running_avg = Table.AddColumn(add_running_sum, "X1", each
if [ID] = 0 then 0 else 
  List.Average(List.Range( add_running_sum[X2], [ID]-1,2))),

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

Categoty_tmp = Table.AddColumn(Buffer, "Category_tmp", 
  each Buffer[Category]{[ID2]}? ),

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   [Category]   { [ID2] }  ?

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”



And part about “?”


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:

Categoty_tmp = Table.AddColumn(Buffer, "Category_tmp", 
  each Buffer[Category]{[ID2]}? ),

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:

Sub update_tech_labels()

Dim r As Range
Dim s As Series
Dim i As Integer
Const series_name As String = "Tech"
Const offset_value As Integer = 1

For Each s In ActiveChart.SeriesCollection
  If s.Name = series_name Then
    Set r = Range(Split(Replace(s.Formula, "=SERIES(", ""), ",")(0))
    For i = 1 To s.Points.Count
     With s.Points(i).DataLabel
      .Formula = "=" & r.Offset(i, offset_value).Address(External:=True)
      .Font.Size = 9
     End With
    Next i
   Exit For
  End If
Next s

End Sub

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.

Advertisements

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

  1. franktonsen 2017-02-13 / 18:50

    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 …

    What’s your solution?

    BTW, you can pick data labels from cells easily manually. Nonetheless, a nice macro.

    • Ivan Bondarenko 2017-02-13 / 21:42

      Thanks, Frank. I used Method 2 from previous post (Table.TransformRows). I find this topic quite interesting, so I’ll write a new post with possible ways to achieve this. Thankful for your solution!

  2. franktonsen 2017-02-14 / 14:40

    Ivan, I would appreciate, if you could show your formula using Method 2.

    While Method 1 does work for me, I can’t get Method 2 running on the same column as field access for null values will be denied – the same problem I had with Table.TransformColumns.

    It’s definitively worth a new post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s