It’s said that good things come to those who wait. I detest waiting and assume you do to, so instead of scrolling all the way to the bottom of the blog post in order to try the interactive sample report, you get it here in the very beginning! To see how I built it, please proceed your reading. (WordPress doesn’t allow me to use iFrames so you have to click on the image to get to the interactive report).

I found my data sample on this site: https://support.spatialkey.com/spatialkey-sample-csv-data/
I basically just wanted some data with longitude and Latitude in it for me to play around with it this was perfect!

First things first – connecting to the data and transforming it in Power Query.
I did some stuff to the date column as it was formatted for US standards and my installation of Power BI didn’t really like that when I tried to format the column as dates. Not going into details as there are probably much simpler ways of doing that and I already learned 5 new things today so my ever-aging brain is starting to get filled up for now.
I did however split the date and time into separate columns and extracted the name of the weekday as well as the day of the week number into their own columns.

The reason for these columns are as follows:

• The name of the day in a week is for a nice visualization
• The day of the week numbers are for sorting the names of the days in the week
• The time will be binned so I want that separately
• I just like the way dates look better without the time merged (yes, this is a very valid reason)

When creating a column chart with data over the week days, it looks great if you have the names of the days as the axis. It looks less great when the days come in alphabetic order.

That’s why I wanted the number as well. When the dataset is imported to the report view, I can just highlight the column with names and under the modeling tab I can sort this data based on the numbers in another column. This will make Mondays come first as that has the lowest number.

To get the amounts of crimes I created a measure in the table and simply counted the rows using this formula: Total Amount = COUNTROWS(SacramentocrimeJanuary2006)

This is the measure I’ll be using in all the visualizations in the report.

Next up is the Time column. I’m not really interested in showing the data by the exact minute it was committed (or perhaps reported? I’ll admit to not have done that thorough research on what I’m actually digging around with here). It’ll be enough to see closes hour. For this I’ll use Binning! By creating a bin on the Times column, I can just specify I want increments of 1 hour segments calculated automatically.

Just to show off, I’ll create one that has a bin size of 15 minutes as well. I’ll use both in my Column chart in the report like this.

So basically it’s a chart showing the amount of crimes by whole hour but segmented by closest quarter. The blue represents whole hour, yellow is 15 minutes, red is 30 minutes and green is 45 minutes. Giving me a clear indication that most crimes in my sample file is time stamped close to the full hour. I will keep this valuable information in my archive of things to discuss during dinner parties as I’m rather convinced everyone needs to now this. Should this be false, at least you now are aware of the fact that you could do this in Power BI.

All right time to add my first map! I’ll just put in Longitude, Latitude and the total amount of crimes as the size of my bubble.

That’s a lot of crimes! And my sample data is only covering one single month. I’ll admit, it’s not super easy to make any insights about this so far. It’s sort of reminds me of when Windows would freeze back in the days and you could create beautiful art on the screen while waiting. Was it during the Windows XP era?

I know what you’re thinking. We need this to be a heat map instead!

Heat maps was announced in the March update 2019 and it’s super easy to create! I just copied my map from above and in the new visual went to the paint roller menu. Here you just select the Heat map so it’s set to On. That’s it!

And here are the results!

I want a third map. Good things come in threes and this report should be no exception!

In this map I want to group areas together. Another excellent task for binning that we used before on the time column. This time we’ll use it on the Longitude and Latitude columns instead.

I right clicked on Latitude and choose “New group”. By putting the bin size to 0.05 I’ll group together all the Latitudes that are within those segments. I’m doing the same thing for Longitude.

Now I just copy the old map with all the bubbles in it and change my old Longitude and Latitude fields to my new binned ones.

This groups my crimes into a neat little grid. If I wanted the net to be narrower, I would have used a lower number for the bin size, creating more bins. Thus for fewer bubbles, I would have used a higher number.

Using this, I’m able to zoom into areas chunk by chunk! You could try it on the published sample report in the top of this blog post.

And that’s all for this time! You want more details on this, please reach out! You have raw data but no idea on how to use it? Reach out and I’ll get you started in no time!

Oh and don’t forget to sign up for the TechDays Pre day where me and my colleague Magnus Göransson will talk more about the Power platform and Power Apps in particular.