PowerBI – Pivot and Unpivot Columns

In PowerBI there is a nifty trick where you can pivot or unpivot your data in the Query Editor. This saves you from having to do this in SQL or which ever source you are using for your data.

There are just 4 simple clicks to pivot your data!

First you need to select the column you want to pivot in the Query Editor, in our case being the Month column:

Next you select the Transform tab at the top and select Pivot Column:

Next you need to select what the values are, in this case its the Users column and click OK:

Then your data will be displayed as pivoted by Month as shown below:

Hope this makes your pivoting needs easier in the future!

PowerBI – Constant Line

The constant line feature in PowerBI seems to be one of those hidden little gems. Lets say you have an SLA with a customer of the amount of downtime allowed on the system and you want to show on a graph once they have gone above this downtime threshold, you can add a constant line to your chart.

In my example below I set the constant line to 1000 and anything above that is great for the purpose of this chart.

To set a constant line, plot your graph, then on the right hand side, select what looks like a magnify glass. This will only show if you have the graph selected.

Then your constant line shall appear on your graph in the colour and density you set it to.

Hope this little gem helps you out in the future!

PowerBI – Table Heat Map

Ever wondered how to create a heat map in PowerBI without having to use a custom visual? There is now a simple way to do this by using Conditional Formatting on a Matrix table!

In my example I took some Google Analytics data showing the times of the day and amount of visitors in that time.

Then plot the values on the Matrix table

Next we go and do some conditional formatting in the pane on the right hand side in PowerBI

Now we can change the colours we want to use by Selecting Advanced Controls

You will see that the table has turned into a heat map table using the conditional formatting with the colours you chose. As you can see below the highest value is pink and the lowest value is a light green and a gradient between the colours as the numbers range.

Just like that we have a heat map table with one simple step!