Plotting time series in Power BI

Today we’ll match up the data visualization power in Power BI to the ARR in R.
Every time I see one of these post about data visualization in R, I get this itch to test the limits of Power BI.

Tonight I read a post about Plotting time series in R using Yahoo Finance data by Joseph Rickert on the Revolution Analytics blog.
In his blog he describes, in its most simple form, how he gets stock data from the Yahoo Finance API and plots it on a chart.
Sounds like something Power BI can do!

Plotting time series in Power BI - 1

He then goes on to describe making the chart interactive using fancy R magic.


Plotting time series in Power BI - 2

Let’s break down what we’re seeing here.

  • Data for IBM and LNKD stock
  • Linechart
  • Mouseover legend
  • Date range selector
  • Interactive HTML presentation

The date range selector will be a hard one, but let’s get this party started!


Importing the data

With anything Power BI, the first step is getting the data.
In his blog, Joseph posts the following links as his sources

IBM Stock data: http://real-chart.finance.yahoo.com/table.csv?s=IBM&a=07&b=24&c=2010&d=07&e=24&f=2015&g=d&ignore=.csv

Linkedin stock data: http://real-chart.finance.yahoo.com/table.csv?s=LNKD&a=07&b=24&c=2010&d=07&e=24&f=2015&g=d&ignore=.csv

Opening these in a browser, downloads a csv file. However, we want to be able to open the data in the fancy R way with the variables in the URL. This also enables us to easily adjust the time period in the future.

Importing this data in Power BI is done via the “Web” data source

Plotting time series in Power BI - 3

 

In the next, overly large dialog box, with a very small input box we enter our first URL

Plotting time series in Power BI - 4

 

Pressing “OK” gives us the following dialog box.

Plotting time series in Power BI - 5

The “Edit” button will give us the Power Query screen which gives us a lot of options to edit our existing data or even create new features in our dataset. To compete with the abilities of a specialized tool like R we’ll need to use the full potential of Power BI.
So press that “Edit” button and then import the next dataset as well using the same method.
You should end up having 2 datasets loaded, seeing the below screen.

Power Query

Cleaning the data

As you might have noticed, the values were imported as if the stock was worth millions to billions. I’m sure IBM and LinkedIn will be happy to hear this. However, you might have noticed that stock is usually worth several dollars for small companies to only hundreds of dollars for the top companies.

I’m thinking that the data might appear correctly for you, depending on your locale. The period vs comma decimal dilemma is something typical for Belgium.

To work around this problem in our data import we’ll take the following steps

  • Convert the numerical columns to text

Plotting time series in Power BI - 6

  • Replace the period by commas

Plotting time series in Power BI - 7

  • Convert the decimal columns to a decimal data type
  • Convert the whole number column (Volume) to a whole number datatype

Et voila! We’re done cleaning our data.

Joining the data

To show both datasets in one chart we need to join the data first.
But to identify the joined data in one chart, we’ll need to add a column in both datasets before we join them.

Adding a column is done via the aptly called menu item “Add Column”.
There’s a plethora of choices here but we’ll just go with “Add Custom Column”

Plotting time series in Power BI - 8

 

For each dataset, add a column called “Company” and mark it as having data for the specific query using a formula as seen in this screenshot.

Plotting time series in Power BI - 9

Next we’ll need to combine the 2 datasets.
This can be done from the home menu using the “Append Queries” item.

Combine Queries

After pressing the “Append Queries” item, you’ll get the following dialog box

Plotting time series in Power BI - 10

It conveniently shows you which dataset you’ve currently got selected. That way you can wisely pick the dataset that you want to append to the current one.

Next you can click the “Close & Apply” button

Plotting time series in Power BI - 11

Creating the report

Click the “Report” tab and let’s get to work!

4 steps will get you the basic setup

  • Click the 3 fields you want
    • Close
    • Company
    • Date
  • Click the line chart visualization

Plotting time series in Power BI - 12

You’ll see this beautiful chart that you’ll recognize from the original article.

Plotting time series in Power BI - 13

We got our data, we got a line chart and we got our mouse over information. Now we need to create our date range selector.

There are several ways to implement this.
Sadly, the cool slider thingamajig that R has isn’t one of them.

So what can we do to create a cool date range selector in Power BI?
Power BI enables us to use slicers or even use a data visualization, like a treemap, as a sort of slicer.

Adding features to our data

We’ll probably want to slice on year, quarter and month. Let’s add the relevant data to our dataset.
For this, we need to edit our dataset, so let’s press the “Edit Query” button again.

Adding these fields is a breeze, we select a date field and go to the Add Column menu.
From there we see one of the most incredible options. We can add different time columns with the click of a button.

Add Column Year

Adding custom features to our dataset

Adding custom features to your dataset can be done using DAX.
DAX can be the subject for a lot of other blog posts but for an easy start check out Dustin Ryan’s blog post on some often used DAX formulas.

For our little experiment we don’t need to go this far however. So feel free to explore or give your suggestions in the comments or via twitter.

Adding slicers

In Power BI you can filter a view, that’s one page in a report. There are several possibilities to create filters, a slicer is one of them. A slicer is a filter that is present on the report page itself. Like the one in the original R visualization. Filtering works with data that belongs to the same query or with queries that have a relationship defined between them.

One of the slicers we’ll create is by using a treemap.
Create a treemap visualization and put the quarter name in there like in the screenshot.

Treemap options

 

This gives us a quite ugly slicer with different colors. We can change these colors via the format menu.

Treemap data colors

 

Changing these colors gives a great result that will fit with the overall look and feel of our dashboard. It will especially help us avoid making this thing look like a coloring book.

Treemap slicer

The result

If you’ve been following the process yourself, you probably noticed how fast and easy it is to toy around in Power BI.
Leave a comment if you have a question or encountered a problem. Below is the result I got.

result

 

If you haven’t played around in Power BI yet, just download the free Power BI Desktop application today and get started with the Power BI Zero to Hero series.

It’s a lot more user friendly than writing some lines of R magic. I’d even argue that it’s more fun!

Plotting time series in Power BI - 14

Plotting time series in Power BI - 15

Follow me on twitter to see when I create another wine fueled Power BI post.

PowerBiEvening

 

 

 

1 thought on “Plotting time series in Power BI”

  1. Nice work, thank you for sharing. I wanted to know if we could take this to the next level: I’m struggling and trying to make a timeseries plot where the data is coming from two different tables. I have table A with [DateTime],[Pressure] , and the table B with [DateTime],[Measured_Variable]. The measured variable has sporadic measurements with no particular frequency.

    How can I plot both together, for example, to see trends and how one affects the other?

    Reply

Leave a Reply to Patricio Cancel reply

%d bloggers like this: