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!

PowerBI – Turn on Preview Features

Every month PowerBI releases new features. Some of the features are in preview mode and unless you turn it on you don’t get to use the preview features. This post explains how to turn them on.

Firstly you need to have the latest version of PowerBI to get the latest features. You can download it here.

Next you go to File > Options and Settings > Options

Then select Preview Features and select the preview features you want to use.

You will then be prompted to restart PowerBI in order for the preview features to be available.

Once restarted you will have your preview features available and if they are visuals you will see them appear on the right hand pane. For example we can now see Python in the visualizations pane.

Creating PowerBI Layouts

PowerBI has this great functionality where you can go and download preset layouts which make your PowerBI reports stand out more. There is a nice trick to doing this yourself.

You can create layouts in PowerPoint and then save them as images. Then insert them into your PowerBI report as an image and send the image right to the back.

Here are the steps to go about doing this:
Open up PowerPoint, select Design on the ribbon and then select edit background. You can then change the gradient in the panel on the right hand side.

Next you insert shapes, set the fill to white and the shape effect to shadow centre:


Then design your shapes on your PowerPoint slide and create the layout you want:

Now that you have your layout, we need this to be an image, you simply save this slide as an image:

Then select “Just this one”

Now your layout is saved as an image. Next is to import it into PowerBI.

Open up PowerBI and select Image to import it:

Go to where you saved the image of the PowerPoint slide, select it and click open:

Then resize the image to cover the entire PowerBI page and set the scaling to fill on the right hand side:

Now that the image is in we want to make sure it is right at the back. Select send to back:

And now you have an awesome layout to go and place your data on!

PowerBI Campaign/Brand Management for Twitter

PowerBI has released a fantastic template for Twitter called Campaign/Brand Management for Twitter. After following the “wizard” you will have a complete report filled with sophisticated analytics.

In order to set this up, all you need is a twitter account and an Azure account. If you would like to play with this you can set up a trial Azure account.

To get started you can go to the following link https://powerbi.microsoft.com/en-us/solution-templates/brand-management-twitter/
This will open the following page. Select install to start the “wizard”.

Next you will get a summary of what is needed to complete this Twitter template.

On the Login page you will connect to Azure. If you do not have a subscription there is a link to set up a free Azure trial. You will then select whether you have a personal or work Azure account.

If you are not sure what to put in the Azure Tenant section you can find this in the azure portal. If you hover over your icon on the top right hand corner it shall show you some details. It is the Domain details that you shall place in the Azure Tenant. It will have the “.onmicrosoft.com”

Once you click Connect to Azure you will need to accept that the Business Platform Solution Template can access your Azure account.

Once accepted you shall get a confirmation page that all was successfully validated and you can click on next to progress to the Source page.

On the Source page you will specify the database name to store the Twitter data, a username and a password. Make sure the password contains upper case, lower case and a special character.

Next you will need to check for availability, sometimes the database name you choose is already taken on another azure server.

Once validated you shall get the below screen and you can select next to go onto the Twitter page.

On this page you shall connect to Twitter. This will connect to your actual Twitter account. You can get data about any Twitter account using this. It is not limited to your own just because you are logging in. In this demo I take a look at the @MSPowerBI twitter account.

Complete your Twitter login details and authorize the Azure app to have access.

Once authorised you will get a screen to show you that your Twitter account has been validated and you can move onto the next page.

On the Search Terms page you can place in the twitter handles you would like to take a look at or the keywords you are keen on. You can do both by separating the two with the word OR.

Once validated you move onto the Twitter Handles page. This is where you can search all the Twitter handles you are interested in by separating them with a space. For this I took a look at @MSPowerBI and @Azure

After this you reach the Summary page. Once you click on run it starts to process all the data and build the report.

You then move onto the Deploy page where you can see the progress of the data being extracted and compiled in a report.

Once complete you shall get a notification and you can download your pbix file to view on your desktop.