Home Speaking
Category:

Speaking

In my recent talk for PASS BA Marathon, I showed few formatting tricks in Power BI with my super slow laptop. I promised to write a blog post but due to some other priorities, I didn’t manage to publish this blog post in time, well better late than never!

You can watch recorded Session here:

or just some highlights here:

Adding Gif images to Power BI:

People who knew me in person, I am sure they know I like pictures. However, so many pictures on a dashboard are undoubtedly not a good idea. Stephen Few mentioned, “Art has its place but not on the Dashboard”. Here, I am not at all recommending to put loads of GIF’s on the dashboard, but if you want to go crazy with your dashboard or want to get people attention, it’s not a bad idea. Well as Stephen Few mentioned in another article, It’s nice to highlight what needs attention.

Adding GIF images is quite easy. First I created a measure with value 0. I named this measure as BGGif, so I know I have to hide it from users, and it will be easy to identify in the model. It is not mandatory to create a measure, you can use an existing one, but creating a separate one makes it easy. Then I placed Line Chart( you can use other charts, but Line Chart makes it a bit easy). Then I went to format options of Line Chart, under Plot Area, for Add Image option I chose the GIF image. Based on the data colours, there is a chance of spotting a Lil dot; if you don’t want it, just move BGGif value to tool tips. I like the idea of putting Gif images; you can create some cool ones like below 🙂  I read or heard using Plot Area of charts for gif images, I don’t remember where exactly it was. Whoever it was, thank you 🙂

Dynamic Background:

There are several ways we can do this based on our use case. In my demo, I just wanted to show a background in green if the price <=100 if not Red.

First I created a measure with a bit of DAX. Again, I am going to prefix it with BG and make sure I hide it for business users.

PriceBG = if([Sum Of Price]<=100,"http://cdn30.us1.fansshare.com/image/wallpapergreen/pix-for-gt-plain-mint-green-wallpaper-mint-green-plain-background-wallpaper-wallpaper-mint-green-999507947.jpg" , "http://www.publicdomainpictures.net/pictures/200000/velka/plain-red-background.jpg")

In the measure, it is just a URL with Red and Green backgrounds. I changed the data category of measure to Image URL. Then I used Image custom visual to display PriceBG measure. One problem with this approach is if URL brakes, visuals will not work.

Using Unicode:

I first saw this technique when Will Thomson showed at UK Power BI Summit also recently Chris Webb blogged about using Unichar() function.  In my demo, I just copied Emoji’s from the Web and placed them in my measure, but if you want you can use UNICHAR() function as Chris explained.

To show #Beds – I created a measure like below:

#BedsEmoji = if((AirBnB[bedrooms])>=5,"?️ +",

IF(AirBnB[bedrooms]=1,"?️",

IF(AirBnB[bedrooms]=2,"?️ ?️",

IF(AirBnB[bedrooms]=3,"?️ ? ?️️",

IF(AirBnB[bedrooms]=4,"?️ ?️ ?️ ?️","0")))))

And for an overall review, I used Sentiment Analysis, based on sentiment analysis value; I created another measure:

ReviewEmoji = if(SUM([Score])<=.50,"?","?")

These emoji looks great on Power BI Service than PowerBI Desktop. I love the functionality of using strings in DAX measures, it enables us to use Unicode and advantages are unlimited. As I blogged in one of my previous blog posts, you can use these to highlight a specific data point in a chart. Or as Chris blogged, we can use Unicode in many ways.

Hope this blog post gives you some ideas, and again I am sorry for not publishing blog post earlier. You can download the PBI file here – https://1drv.ms/f/s!Avm7gbgZtlMlpx4qFeK1Qu15NcpE and view reports here – https://app.powerbi.com/view?r=eyJrIjoiNzUxYzRmNjUtNDA4Mi00MWI0LWJiNWQtMDRiZWQwZmFjYjE0IiwidCI6IjM4YmU3MjU5LTMyMGQtNDZkYy04YzNiLWNkZDA5OTM1NGExMyIsImMiOjh9

Keep Smiling,

Prathy ?

 

0 comment
1 FacebookTwitterPinterestEmail

Don’t wait for the opportunity CREATE it.

I was just trying to create an opportunity to work with Power BI; that was the initial plan. Later I created a session and I presented a version of this session at NTK Conference, Slovenia, next at SQL Supper and recently Channel 9 Techdays online. None of them was likewise, but the heart of the topic was same Power BI + Property Data.

I first looked at the price paid data a few years ago when Chris Webb presented a power query session in a London User group. Fascinating dataset and as usual Chris did the fantastic job showing nearly all cool things you can do with the dataset. I thought what else I could do with it.After few glasses of wine, I thought why the only price paid data why don’t I pull data from Zoopla. Then I added a bit of personal touch by adding schools and commuting information. I want to do a Power BI data story with Ofsted data and schools admission boundary (working mum syndrome 😉 )

That’s the story behind the session thought process then after I presented some people asked me to share the PBI Model, and some asked me how I extracted data from different data sources. So here comes the blog post which I promised long ago …

The data story:

Schools Data:

First I extracted Ofsted data. This database has all the information related to schools like number of pupils, when did the last time inspection happen, etc. It was a simple single file. After I had imported files into Power BI, I can see I had an extra row, and I just clicked on the first row as column step, twice, and it was sorted. This two click Power BI task will be tedious in any other ETL tool. M is amazing !

Price Paid Data:
Next data set was price paid data. All price paid data, and additional sale information freely available to download from the Land Registry Price Paid data, and it gets monthly updated. Again a simple CSV file.

NEXT comes the cool bit…

BING Maps API:

I need to confess; this idea was purely stolen from Chris Webb, M presentation from London Power BI user group. He showed how to use Google Maps to find the distance, and I used to BING Maps API. Again, it is very easy. You can get API key by going to BING Maps Developer site. I went for transit option to find the commute time. I used Power Bi WEB data source with following URL

http://dev.virtualearth.net/REST/V1/Routes/Transit?wp.0=”&FromPostCode&”&wp.1=WC2H8HR&timeType=Departure&dateTime=9:00:00PM&output=xml&key=YourAPIKeyGoesHere

I used M function to find the duration.I am going to use this function in my next dataset to find the distance from my preferable postcode.

M code:

let FindDuration = (FromPostCode as text)=> let Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/V1/Routes/Transit?wp.0="&FromPostCode&"&wp.1=WC2H8HR&timeType=Departure&dateTime=9:00:00PM&output=xml&key=YourAPIKeyGoesHere")), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}), ResourceSets = #"Changed Type"{0}[ResourceSets], ResourceSet = ResourceSets{0}[ResourceSet], #"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}), Resources = #"Changed Type1"{0}[Resources], Route = Resources{0}[Route], #"Changed Type2" = Table.TransformColumnTypes(Route,{{"Id", type text}, {"DistanceUnit", type text}, {"DurationUnit", type text}, {"TravelDistance", type number}, {"TravelDuration", Int64.Type}, {"TravelDurationTraffic", Int64.Type}, {"TrafficDataUsed", type text}, {"TrafficCongestion", type text}}), #"Expanded RouteLeg" = Table.ExpandTableColumn(#"Changed Type2", "RouteLeg", {"ActualEnd", "ActualStart", "Cost", "EndLocation", "EndTime", "ItineraryItem", "RouteRegion", "RouteSubLeg", "StartLocation", "StartTime", "TravelDistance", "TravelDuration"}, {"RouteLeg.ActualEnd", "RouteLeg.ActualStart", "RouteLeg.Cost", "RouteLeg.EndLocation", "RouteLeg.EndTime", "RouteLeg.ItineraryItem", "RouteLeg.RouteRegion", "RouteLeg.RouteSubLeg", "RouteLeg.StartLocation", "RouteLeg.StartTime", "RouteLeg.TravelDistance", "RouteLeg.TravelDuration"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded RouteLeg",{"TravelDistance", "TravelDuration"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "DurationInHours", each ([TravelDuration]/60)/60), #"Inserted Rounding" = Table.AddColumn(#"Added Custom", "Inserted Rounding", each Number.Round([DurationInHours], 2), type number), #"Renamed Columns" = Table.RenameColumns(#"Inserted Rounding",{{"Inserted Rounding", "TransitDuration"}}) in #"Renamed Columns" in FindDuration

ZOOPLA API:

You can create Zoopla API account from Zoopla Developer API site, pretty simple API. With the power of Power BI, I quickly imported data using WEB data source from Power BI desktop. Here is the URL I used

http://api.zoopla.co.uk/api/v1/property_listings.xml?postcode=wd17&api_key=xxxxxxx&radius=30.0&maximum_price=800000&page_number=20&page_size=100

I have given different parameters; you can get all parameters list from here. Once I imported the data, I renamed and expanded all the required columns. I used FindDuration function from the above query to find the distance from each property

Date Table:

I created the date table by following blog post by Matt Masson. There are different ways to do it; I preferred it

I imported few other datasets like UK postcodes data for maps and to relate different data sets. Also recession data, I created a table with static values just to show the recession period in the pulse chart.

Finally, my data model looked like this

2016-10-28_02-07-49.jpg

And I managed to do some cool dashboards with this data. You can have  a look at the reports from https://goo.gl/TqPUoo or some screenshots below

www.GIFCreator.me_VSM4YQ.gif

Creating this model was super easy, finding all those data sources were tricky and editing all the properties of different visualisations was time-consuming, but rest was simple. I chose this topic to present to show how easy it is to work with data from various data sources and create stunning visualisations in a jiffy.

Let me know what you think?

Power BI Model – https://goo.gl/mkiGLh

Prathy??

2 comments
1 FacebookTwitterPinterestEmail