The built-in mapping functionality in Power BI paginated reports is fairly basic. However the integration of Power Query into Power BI paginated reports gives you an interesting new way of creating maps in paginated reports: you can call the Azure Maps API using Power Query and display the image returned in an Image report item. In this blog post I’ll show you how.
Here’s a quick summary of what I’m going to do:
- Call the API from https://data.police.uk/ (specifically the Crimes At Location endpoint) using Power Query to get all the recorded crimes within a one mile radius of a given latitude and longitude in a given month for any location in England, Wales or Northern Ireland
- Take this list of crimes and pass them to the Azure Maps API Get Map Static Image endpoint to return an image of a map with the crime locations on it
- Display this image in an Image report part in a paginated report
And here’s an example of what the final paginated report will look like:
Step 1: Sign up for the Azure Maps API
In order to call the Azure Maps API you’ll need to go to the Azure Portal and create a resource. The pricing is very reasonable: the first 1000 calls to the endpoint used here are free and after that it’s $4.50 per month for up to 500,000 calls, which should be more than enough for BI purposes.
Step 2: Create Shareable Cloud Connections
To connect to data sources in Power Query in paginated reports you need to create Shareable Cloud Connections in the Power BI portal. You’ll need two connections for this report: one for the Azure Maps API with the URL https://atlas.microsoft.com/map/static/png and one for the Crime API with the URL https://data.police.uk/api/crimes-street/all-crime. Both SCCs should have the authentication method Anonymous and the privacy level Public and have the Skip Test Connection option checked:
Step 3: Create a paginated report and Power Query query to call APIs
After creating a new paginated report in Power BI Report Builder you need to create a dataset (called AzureMap here) to get data from the APIs. This dataset uses Power Query as a source and has one main query (also called AzureMap) and four parameters:
- lon and lat, to hold the latitude and longitude of the location to get crime data for, which will also be the centre point of the map
- zoom, which is the zoom level of the map
- yearmonth, which is the year and month in YYYY-MM format to get crime data for:
Here’s the M code for the query:
let
CallCrimeAPI = Json.Document(
Web.Contents(
"https://data.police.uk/api/crimes-street/all-crime",
[
Query = [
lat = Text.From(lat),
lng = Text.From(lon),
date = yearmonth
]
]
)
),
ToTable = Table.FromList(
CallCrimeAPI,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
First50 = Table.FirstN(ToTable, 50),
ExpandColumn1 = Table.ExpandRecordColumn(
First50,
"Column1",
{"location"},
{"location"}
),
Expandlocation = Table.ExpandRecordColumn(
ExpandColumn1,
"location",
{"latitude", "street", "longitude"},
{
"location.latitude",
"location.street",
"location.longitude"
}
),
JustLatLon = Table.SelectColumns(
Expandlocation,
{"location.longitude", "location.latitude"}
),
TypeToText = Table.TransformColumnTypes(
JustLatLon,
{
{"location.longitude", type text},
{"location.latitude", type text}
}
),
MergedColumns = Table.CombineColumns(
TypeToText,
{"location.longitude", "location.latitude"},
Combiner.CombineTextByDelimiter(
" ",
QuoteStyle.None
),
"LongLat"
),
PrefixPipe = Table.TransformColumns(
MergedColumns,
{{"LongLat", each "|" & _, type text}}
),
GetString = "|"
& Text.Combine(PrefixPipe[LongLat]),
QueryRecord = [
#"subscription-key"
= "InsertYourSubscriptionKeyHere",
#"api-version" = "2022-08-01",
layer = "basic",
style = "main",
#"zoom" = Text.From(zoom),
center = Text.From(lon) & ", " & Text.From(lat),
width = "768",
height = "768"
],
AddPins = try
Record.AddField(
QueryRecord,
"pins",
"default|sc0.5" & GetString
)
otherwise
QueryRecord,
CallAzureMapsAPI = Web.Contents(
"https://atlas.microsoft.com/map/static/png",
[Query = AddPins]
),
ToText = Binary.ToText(
CallAzureMapsAPI,
BinaryEncoding.Base64
),
OutputTable = #table(
type table [image = text],
{{ToText}}
)
in
OutputTableYou need to put all this code in a single M query to avoid the Formula.Firewall: Query ‘Query1’ (step ‘xyz’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination error. You can find out more about this error by watching my data privacy video here.
A few things to note:
- The CallCrimeAPI step calls the Get Crimes At Location API endpoint to get all the reported crimes within a one mile radius of the given latitude and longitude in the given year and month.
- Because of the way I’m sending the crime location data to the Azure Maps API I limited the number of locations to 50, in the First50 step, to avoid hitting errors relating to the maximum length of a URL.
- The GetString step returns a pipe delimited list of longitudes and latitudes of crime locations for the Azure Maps API to display as pins on the map. However, some error handling is needed in case there were no reported crimes in the given location or month and that happens in the AddPins step.
- The QueryRecord step contains all the parameters to send to the Azure Maps Get Map Static Image endpoint. This docs page has more information on what’s possible with this API – I’m barely scratching the surface of what’s possible in this example.
- Authentication to the Azure Maps API is via a subscription key which you’ll need to pass to the subscription-key parameter. You can get the key from the resource created in step 1 in the Azure Portal.
- The API returns an image binary which is converted to text and returned in a table with one column and one row in the ToText and OutputTable steps. The code is similar to what I showed in this blog post but luckily I didn’t seem to need to break it up into multiple rows.
Step 4: Create Power Query query to return values for Zoom parameter
The Zoom parameter of the Get Map Static Image API endpoint accepts a value between 0 and 20, which represents the zoom level of the displayed map. You need to create a separate dataset and M query to return a table containing those values with the following code:
let
Source = {0 .. 20},
#"Converted to table" = Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Changed column type"
= Table.TransformColumnTypes(
#"Converted to table",
{{"Column1", Int64.Type}}
),
#"Renamed columns" = Table.RenameColumns(
#"Changed column type",
{{"Column1", "Zoom"}}
)
in
#"Renamed columns"
Step 5: Create paginated report parameters
Next you need to create four parameters in the paginated report for the longitude, latitude, zoom level and year month:
To make it easy for end users to select a zoom level, you need to bind the available values for the zoom parameter to the table returned by the dataset from the previous step:
Step 6: Display the map in an Image report part
In the paginated report itself the only interesting thing is the configuration of the Image report part in the centre of the report:
You need to set the image source to “Database”, bind it to the following expression
=First(Fields!image.Value, "AzureMap")…which gets the text value from the sole row and column in the table returned by the AzureMap dataset created in step 3, and set the MIME type to be “image/png”.
And that’s it! After publishing you can enter any latitude and longitude in England, Wales or Northern Ireland, a year and month, and a zoom level, and get all the reported crimes on a map:
You can download the .rdl file with the paginated report in here (remember to edit the AzureMaps query to insert your Azure Map API key).
