skip to Main Content

Comparing Rows in Power Query Part 2

We are back to finish our discussion on the visualizations for Comparing Rows in Power Query!

How to visualize the prices and daily changes

The two metals have very different price ranges so it is difficult to see them in one chart.  The daily changes are relative so they can be used to compare the price behavior over long periods of time.

I decided to use four line charts, two for gold and two for silver.

The main tool to drive historic exploration is the timeline slicer (which was added to Excel in 2013). In this case, it is just what the doctor recommended.

starting point

This is our starting point. We have fifty years of data and we plot it in four pivot charts all connected to the timeline.  Each pivot chart is connected to a pivot table and all pivots are in a separate sheet that can also be hidden.  In the Insert tab, you can see an entry for insert pivot chart that is supposed to add a pivot chart without a pivot table.

For some reason, it works only if the pivot is based on the data model and not in our case where all pivots are based on a table. Weird I know.

Now I need to build the pivots one by one.  I add a pivot chart to each from the design tab and move the charts to their own sheet.  I decided to place them with gold on top and silver at the bottom.  I could also have placed prices on top and changes at the bottom.

Notice the very subtle color selection for the charts In love

The most important element is the timeline slicer that occupies the entire upper part of the canvas.

It starts by showing the entire 50 years. We can use the charts on the left to identify ranges that contain extreme fluctuations and let’s assume we want to investigate them.

Here is an example of the year 1983. By selecting 1983 I can see the days for just this year.

1983

We can see a huge up and down spike in silver prices around February of this year.

Now we can change the timeline to use months and select January to March.

Jan to Mar

If we choose to we can switch to days and traverse this very long time span following the data.

What about Power BI?

I tried to recreate the workbook using Power BI instead. The query could pretty much be used as is. The only change was that I connected it to an external Excel workbook, instead of an Excel table in the same workbook (It is a best practice to do so, even with Excel).

I am not using any DAX, so the logic is still implemented in the query.  The charts do look a bit nicer.  Not trying to show 365X50 days in the x-axis helps.  The slicer on the years is not quite as flexible as in the Excel version. There are some custom visualizations for Power BI that support time hierarchies, but I didn’t try them out with this dataset.

I used the same type of charts as I did in the Excel workbook, but I invite our readers to come up with a better visualization in PBI.  You can download a .zip with all of the files here.

Where It’s At:  The Intersection of Biz, Human, and Tech*

We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”

In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂

* – unless, of course, you have two turntables and a microphone.  We hear a lot of things are also located there.

Dany Hoter

Dany Hoter has been using Microsoft BI tools, especially Excel, since 1998. He was part of the Excel and Power BI teams in different positions for most of this time.

Dany lives in Jerusalem, Israel and shares his time enjoying his grandkids (6 of them), teaching Excel and Power BI, and writing articles.

This Post Has 5 Comments
  1. Hi Dany, I loved the example. Learned alot from part 1. I did have a question about your statement “The only change was that I connected it to an external Excel workbook, instead of an Excel table in the same workbook (It is a best practice to do so, even with Excel).” Why would that be the best practice?

    1. It’s better because the Excel you use is much smaller as it only contains the compressed version of the data
      Also it is easier to migrate to Power BI if you need to at some point
      In general it is cleaner to separate the report and the input data

      1. OK. That makes sense that you would separate the data from the report and that the data would be compressed. I was curious about the table vs the sheet comment. When possible, I usually try to put my data in an Excel table instead of a sheet. Is there any issue with that (as long as it is in an external workbook)?

Leave a Comment or Question