[Power BI] Building a Bump Chart with Charticulator

Hello everyone,

In this post, I will show you how to build a bump chart in Power BI which using chart template from Charticulator. Charticulator is a new great way for data visualization which includes many outstanding charts. Charticulator is a product from Microsoft Research, there is some papers from the team for it which you can read at here. For the scope of this article, we just go into the applicational aspect and the chart we want to build is Bump Chart.

In general, there will be 3 parts to do this. First, we need to get raw data and preprocessing the data. In this article, we will build a football ranking table for Vietnamese League 1 in 2020. Second, we will build a bump chart in Charticulator and save its template. Finally, we will use the Charticulator template and build the chart directly in Microsoft Power BI in which the chart can interact with other Power BI visuals.

 

We will get the ranking table data of Vleague 1 2020 from the open source Wikipedia. Since this is the raw data, we need to process this raw data into a good tabular format. There is 3 tables from Wikipedia site and we want to combine these 3 tables into 1 table only for visualization purpose.

Let's look at the 3 tables that we want to get data from Wikipedia page.

Now we create a new project in Power BI Desktop and get data from Wikipedia.

Step 1: Choose Get Data in Power BI with data source as Web, then click Connect button.

Step 2: Copy the Wikipedia URL into From Web textbox and click OK button.

Step 3: Choose Anonymous connect option and click Connect button.

Step 4: Tick choose 3 tables we want to have and click Transform Data button.

Step 5: In the Query Editor, you should have a screen similar to this.

Now we have 3 separate tables loaded into Power BI Query Editor. We need to clean these tables, then combine them into one table only. Our target table will have 3 columns : Team, Round and Position. In this section, we will go through the data processing for one table (Table 3 in my case) and you can do the rest for remaining 2 tables with exactly same steps.

Step 1: Click choose Use First Row as Headers on menu toolbar.

We will have the headers like this.

Step 2: Click Remove Rows, then Remove Top Rows from menu bar.

Enter 1 into textbox to delete the only one first row with null value.

Now we have removed the row with null value.

Step 3: We want all rounds will into 1 column named Round. To do that, click choose the first column (Name / Round), right click and then choose Unpivot Other Columns option. All the round columns now will be merged into 2 new columns with column names Attribute and Value.

Step 4: Now change the column names to Team, Round and Position. You should have a table that look similar like this.

Also, look at the M Query box on the right of the screen, you should have the same steps as below.

At now, we have done transform data for one table, you need to do the same transform steps for the rest 2 tables before we can combine them together into one final table.

Step 5: Do transformation for the remaining two tables ( let do it by your own).

Step 6: Now we can combine them into one table. Choose Append Queries in Combine section in menu toolbar.

Then select the tables we want to combine. In our case, we want select all 3 tables. Select Three or more tables radio button and add 3 tables into Tables to append combo box.

Now we have all data combined into one table ( my case is Table 3).

Look at the M Query box for Table 3, you should have applied steps similar as below.

Finally, we can load our tables into Power Bi data model. Click Close & Apply to load tables into data model.

Since Charticulator can only work well with csv format file, we need to export this format from Power BI visual screen. I will show you how to do this trick.

Step 1: Choose Table visual from Visualization pane, drag and drop Table 3 columns into the visual as below.

Step 2: Then on the top right of table visual, select the [...] icon, and choose Export Data.

Save the export file as csv format. Let's name it as vleague2020ranking.csv.

Open vleague2020ranking.csv file, you should see something similar as below.

Since file under csv format may cause some annoying effect to the Vietnamese words with accented letters, we need to convert accented letters into non-accented letters. For example, "Quảng Nam" need to change to "Quang Nam". There is many third party tools that can help us for this task. For me, I usually use the Unikey Toolkit function in Unikey software to do the letters convert.

Another thing we need to finetune is change the numeric value of Round column into text format by adding prefix "r". This will help to sort the column in alphabet format and create a correct link in Charticulator.

After the csv finetune, your csv data should be similar as below.

Now we are ready to build the chart in Charticulator with the vleague2020ranking.csv file.

Step 1: Go to Charticulator's home page and press Launch button.

A sidebar will pop up as below.

Step 2: Drag and drop your csv file into Data rectangle (we don't have file for Links in this project). You should see something similar to this.

Step 3: Now we go into the chart area. Just take a look on how the Charticulator area look like. On the left is the Fields area which we can see all the columns of the csv file. There is areas called Glyph, Layers and Attributes. We will work in all these sections. On the right is the chart area.

Step 4: Drag and drop the Symbol icon into Glyph area. You should see something similar to this.

Look at the Chart area, you can see the marker show up.

Step 5: Drag and drop Round column into X-axis of the chart.

Step 6: Drag and drop Position column into Y-axis of the chart.

Step 7: Drag and drop Team column into the Fill row of the Attributes of the Symbol we created in Glyph before.

Now look at the chart, you should see something similar to this.

Step 8: Choose PlotSegment1 and change the Range value of Y-axis to (15 - 1).

Step 9: Choose Links icon, set Link using as Line and Connect by Team column. Then, click Create Link button.

Now look at the chart, you should have something like this.

Step 10: In Layers section, choose Link1 and set Line Type as Bezier and Curveness to 10. This change will help to make the link line looks smoother.

Step 11: Now we need to add the name for each marker. Drag and drop the Label icon into Glyph area.

Step 12: Then, drag and drop Team column into the Text label in Glyph section.

Step 13: Drag and drop the Round column into the Visibility row of the Text in Glyph.

Step 14: Click on the right most icon of the Visibility row in previous step and click Clear button. It will make the text invisible for all round.

Step 15: Then, check again for only the round 18 (r18). It will show the name of the team for the marker of this round only.

Step 16: Until now, you should have something similar as below.

As you can see, our chart is not perfect yet. The team name is overlapped with the marker for the top 8 teams as we bind the text to the marker of round 18, the final round of the bottom 6 teams. We can fix the overlapping issue by adjusting the text label position in Glyph further right from the marker to create a wider distance between marker position and label position.

After that, we can have a very nice chart like this.

Step 17: This step you can discover by your own. It will take you some time but it's not difficult indeed. After fine tuning the chart, you should have something similar as mine. If your chart doesn't look exactly like mine, it's OK. It doesn't affect the remaining progress of this post.

Step 18: Save the chart as Charticulator template. Choose Export button.

Then save the chart as Charticulator Template.

Step 19: Save the template name as RankingChart.tmplt.

 

Finally we are here. In this section, we will use the Charticulator template that we exported in previous section and use it to build the chart directly in Power BI.

Open a new Power Bi project and load the csv file as data source.

Step 1: We need to import the Charticulator custom visual in Visualization panel as below.

Step 2: After importing, drag and drop the charticulator visual into visual zone. Then, drag and drop all columns into Data box of the chart as below.

Step 3: Then, click the [...] on the top right of the visual and choose Edit.

You will see the visual screen change to this.

Step 4: Click Import template and load our RankingChart template into the visual.  A Map your data screen will show up, just double check the information and then click Save mapping button.

And dzoo.. We got the chart in our Power BI visual. Click Save and click Back to report.

We have done building an table ranking visualization in Power BI with bump chart template from Charticulator. There is 3 main steps that we did in this post: Get data and preprocessing data in Power BI, create chart and export chart template in charticulator and finally, using chart template to build chart directly in Microsoft Power BI.

Here is how my project look like.

I hope you have gained a new skill for working with Charticulator in Power BI after this article. If you have something to discuss, feel free to leave a message.