Over the course of the year we have looked at a variety of uses for the Power BI tools and the separate Power BI application. But this time, we will conclude the year with a festive look at some of Power BI’s other capabilities.
We will use the creation of a Christmas card with Power BI to explore some additional features including using ‘Column From Examples’ to create the formula for new columns automatically, and the use of Parameters to make it easier to change the values used in Power BI queries.
Our Christmas card will be based on a Power BI scatter chart. A scatter chart plots a point depending on a vertical axis value and a horizontal axis value.
We can use this to create a grid allowing us to position our points. We will set both axes up to cover values from 0 to 10. Our Christmas tree will be drawn by creating a series of values that will, in effect, be the coordinates for the bubbles that will make up our tree. Our values will be entered in a simple Excel Table:
As well as plotting points according to the horizontal and vertical axis values, the Power BI scatter chart can represent a third series of values using the size of the bubble.
Our Table includes a Type and SubType field, starting and ending coordinates and a Size value. You can probably work out that the end coordinates will draw the familiar, triangular Christmas tree shape in the middle of our chart.
The start coordinates are set up so our bubbles will descend from the top of our chart at random horizontal positions. We have used the RANDBETWEEN() function to create the horizontal starting positions: =RANDBETWEEN(0,10)
Over to Power BI
From Power BI, we use Get Data, Excel to browse to our Excel workbook and select our Christmas tree Table of data.
At this stage, we won't edit it, but just load it straight into the visualisation part of Power BI. Here, we have added a scatter chart and allocated the EndV field to the Y axis, the EndH field to the X-axis, Size to the size area and SubType to the Legend:
As you can see, the basic shape is correct, but the axes have been automatically set to only show the values present and the colours are also set automatically.
We can use the paint roller icon immediately below the visualisation type icons to adjust the formatting of our chart. We set the Start and End for each of our axes to 0 and 10 respectively, rather than Auto.
We can then set the Data Colours for each of our points separately, using either the pre-set theme colours or the Custom Colour option to be able to select a more vivid pine-needle green:
Rather than leave our tree suspended in mid-air, we can add the coordinates for a trunk and a base to our Excel Table:
When working with Power BI linked to an Excel Table, it's worth remembering that you need to save the workbook before your changes will show up when you choose Home Ribbon tab, External Data group, Refresh in the Power BI application:
We now need to do some work in the Power Query part of Power BI. We want to be able to set the colours for our Base and Trunk shapes and create our animation. We can right-click on our ChristmasTree query in the FIELDS pane of Power BI and choose Edit Query.
The first thing to do is to concatenate our Type and SubType fields so that we have separate points for tree, trunk and base values. We could do this using the Add Column Ribbon tab, Custom Column option and creating our column formula manually, but we need to make sure that both fields are treated as text in order to be able to concatenate them, this involves using the Text.From() function to change the SubType field from a number type:
We can get the Query Editor to do the work for us instead by using the Column From Examples option. This is a relatively recent addition, so if it doesn't appear in the General group of your Add Column Ribbon tab you might want to see if a more recent update is available.
Column From Examples lets you type in the result you want and let the Query Editor work out the formula that will deliver it. The dropdown allows you to choose between considering all columns, or just those selected when working out the answer. Clicking the button directly uses the All Columns option.
Here we have typed in Tree1 as our example for row 1 and, because only the SubType column has a value of 1 for row 1, the query editor correctly works out what we want to do and shows us the predicted results. Clicking OK creates an appropriate formula and adds our new column:
We'll double-click on the column heading to give our column the more meaningful name 'FullType'.
That's the easy bit. Next, we need to work out how to create our animation. We want our bubbles to move from the starting position to the end position in a number of steps. We can use the number of steps to control the speed by making it a Parameter.
From the Query group of the Home Ribbon tab, we select Manage Parameters, New and enter the Name: Steps. We then set the Type to Decimal Number, Suggested Values to List of values and create our list as 50, 100, 200. We'll set the Default Value to 100:
We can now use Steps in any formula to use the current value set for the parameter. We want to create new rows for each one of our Excel Table rows. Each row will include horizontal and vertical coordinates for each of our bubbles, moving from the starting to the ending positions.
First, we create the additional rows using our Steps parameter within the List.Numbers() function in the formula for a new Custom column:
We can then expand this column to give us 100 rows for every initial row, by clicking the Expand icon in the header of our new column and choosing Expand to New Rows. For each row we need to calculate a position that moves our bubble from start to end. We'll create two new custom columns: one for the horizontal position and one for the vertical.
The position will be calculated by multiplying the distance to be moved (End-Start) by the proportion each step is through the overall animation process (Step/Steps):
= Table.AddColumn(#"Expanded StepRows", "Horizontal", each [StartH]+([EndH]-[StartH])*[Step]/Steps)
We repeat this with the vertical coordinates:
It's important that we set the data types of our new columns by clicking in the icon at the left of each of their column headers. Our horizontal and vertical columns should be set as Decimal Numbers and our Step as Whole Numbers.
We can then use File, Close & Apply to load our new data into Power BI. And then comes the fiddly bit. We need to replace our SubType field with our new FullType field and set the colour and shape of each of our bubbles using Data Colours and Shapes. We have set our shapes to circles but then used the ability to Customise shapes to make the top of our tree a diamond shape and our trunk and base shapes squares.
Playing our animation
To be able to run through our animation, we need to replace EndH with Horizontal and EndV with Vertical.
Strangely, we can't add Step to our scatter chart Play Axis to animate the chart as including a Play Axis disables the ability to control the colour of each bubble. Instead, we download the Play Axis custom visual from the store, and add it to our page, using the Step field.
We should now be able to play our animation, step-by-step. We can also to use the Home Ribbon tab, Edit Queries dropdown, Edit Parameters to change our total number of steps and therefore speed. In this example we have turned off the chart legend, axes, and titles and added a picture to Fit the Plot Area.
And here is the result.
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.