Wards Map

[aghonaim]

Is there anyone can help me shape map data based on the wards within the city ?

[deldersveld · Accepted solution]

Here's the process I followed for Toronto:

Download the WGS84 version of Toronto's wards from the city's Open Data site. This is a shapefile with a zip extension. Go to mapshaper and import the shapefile and export it to TopoJSON. You can then load the resulting JSON file into Power BI by clicking Add Map under the Shape Map's "Shape" options. Any data that you want to connect should join by keys (click View Map Keys). It looks like you might be able to join based on NAME, LCODE_NAME, or SCODE_NAME.

I've posted a sample PBIX with a Shape Map showing Toronto's wards as well as the TopoJSON file that you can re-use in your own PBIX files here.

[deldersveld · Accepted solution]

Here's the process I followed for Toronto:

Download the WGS84 version of Toronto's wards from the city's Open Data site. This is a shapefile with a zip extension. Go to mapshaper and import the shapefile and export it to TopoJSON. You can then load the resulting JSON file into Power BI by clicking Add Map under the Shape Map's "Shape" options. Any data that you want to connect should join by keys (click View Map Keys). It looks like you might be able to join based on NAME, LCODE_NAME, or SCODE_NAME.

I've posted a sample PBIX with a Shape Map showing Toronto's wards as well as the TopoJSON file that you can re-use in your own PBIX files here.

[aghonaim]

Thank you deldersveld so much! Your PBIX works perfectly the way I wanted! 🙂

I did all the steps as you descibed exactly but for unknown reason I wasn't able to get the "map keys" when I clicked on "view map keys" after I added the icitw_wgs84.json to PowerBI. There was no table associated that has: LCODE NAME, Object ID, SCODE NAME, Ward Name as you have them in your PBIX file. Any clue ?

[deldersveld]

For the dataset, I simply used Get Data / Power Query and pointed it to the icitw_wgs84.json file as a source. You could use that as a starting point and join your own data to it, or you could import your own dataset that contains ward data and use Power Query to create a column that matches the ward name + number from the JSON file.

For the first route where JSON is used as the source, here's a sample query:

let
    Source = Json.Document(File.Contents("C:\[...]\icitw_wgs84.json")),
    objects = Source[objects],
    icitw_wgs1 = objects[icitw_wgs84],
    geometries = icitw_wgs1[geometries],
    #"Converted to Table" = Table.FromList(geometries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"arcs", "type", "properties"}, {"Column1.arcs", "Column1.type", "Column1.properties"}),
    #"Expanded Column1.properties" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.properties", {"GEO_ID", "CREATE_ID", "NAME", "SCODE_NAME", "LCODE_NAME", "TYPE_DESC", "TYPE_CODE", "OBJECTID", "SHAPE_AREA", "SHAPE_LEN"}, {"Column1.properties.GEO_ID", "Column1.properties.CREATE_ID", "Column1.properties.NAME", "Column1.properties.SCODE_NAME", "Column1.properties.LCODE_NAME", "Column1.properties.TYPE_DESC", "Column1.properties.TYPE_CODE", "Column1.properties.OBJECTID", "Column1.properties.SHAPE_AREA", "Column1.properties.SHAPE_LEN"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.properties",{"Column1.arcs", "Column1.type", "Column1.properties.GEO_ID", "Column1.properties.CREATE_ID"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.properties.NAME", "Ward Name"}, {"Column1.properties.SCODE_NAME", "SCODE NAME"}, {"Column1.properties.LCODE_NAME", "LCODE NAME"}, {"Column1.properties.TYPE_DESC", "TYPE CODE"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"TYPE CODE", "Column1.properties.TYPE_CODE", "Column1.properties.SHAPE_AREA", "Column1.properties.SHAPE_LEN"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Column1.properties.OBJECTID", "Object ID"}})
in
    #"Renamed Columns1"

[aghonaim]

I'm so sorry I'm still stuck and not able to reproduce what you did. Could you please walk me through what you did step by step after you you Get Data from this file"icitw_wgs84.json" as shown in the picture ?

[deldersveld · Accepted solution]

You can do some additional renaming and column removal, but here are the basic steps in a video.

[Volunteer1]

would you be able to share this video with me, pretty please?

[kbuckvol]

Oh no, the video is no longer available!  Did it get moved?  I send folks here often for this tutorial 🙂

[kbuck]

@deldersveld, just what the doctor ordered, thanks for posting this video!

[aghonaim]

Excellent! I really appreciate it deldersveld! 🙂

[aghonaim]

Sorry to bother you deldersveld once again! Would it be possible to do the same using the new esri ArcGis Map feature ? Toronto wards map is already one of the online Reference Layers there but I don't know how to join/link my data to the wards id/# in the layer! any clue ?

[deldersveld]

At present, it is not possible to link by the data. The only functionality available now is to select PBI data points by clicking on a reference layer (which I believe simply searching for latitude/longitude points within the bounds of the reference polygon). 

[aghonaim]

OK thanks deldersveld for your reply. I hope this can be done soon. Otherwise I don't see any much value from esri ArcGIS Map than what we used to get from the other Map, Filled Map and Shape Map visuals. Do you see any additional value ?

[v-sihou-msft]

@aghonaim

Even the Bing map tile in map visual can show districts of a city automatically, but it can only display the geo data field and associated anlytical data field from your dataset. So you must have distrit field (geo data) in your dataset, otherwise you can't group/shape your data on district level.

Regards,