Forum Discussion
Import TopoJson to Shape Map error
Greetings,
I used a posting from the Forum to try and import a TopoJson file into Power BI.
let
Source = Json.Document(File.Contents("C:\Users\user\Documents\EKFB\HS2\GIdata\TopoJSON\Wards__December_2019__Boundaries.json")),
objects = Source[objects],
Wards__December_2019__Boundaries = objects[Wards__December_2019__Boundaries],
geometries = Wards__December_2019__Boundaries[geometries],
#"Converted to Table" = Table.FromList(geometries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "coordinates", "properties"}, {"Column1.type", "Column1.coordinates", "Column1.properties"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"Column1.coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Column1.properties"})
in
#"Removed Columns"
I get an error message for the Lat/Lon coordinates??
The file is over 70MB so I can't upload it..
It starts with:
{
"type" : "Topology",
"arcs" : [
[
[ 650451, 167241 ],
[ -18, 9 ],
[ -232, 112 ],
[ -34, 17 ],
[ -48, 27 ],
[ -69, 36 ],
[ 12, 10 ],
[ 157, -70 ],
[ 33, -13 ],
[ 55, -19 ],
[ 26, -8 ],
[ 23, -7 ],
[ 38, -9 ],
[ 137, -26 ],
[ 11, -2 ],
[ 24, -7 ],
[ 24, -4 ],
[ 16, -2 ],
[ 12, -1 ],
[ 21, -0 ],
[ 30, -3 ],
[ 114, -7 ],
[ 23, -1 ],
[ 27, 0 ],
[ 0, -0 ],
[ 158, 8 ],
[ 271, 21 ]
],
Then I get:
[
[ 172103, 180996 ],
[ -0, 0 ],
[ -31, 22 ],
[ -12, 9 ],
[ -8, 6 ],
[ -9, 7 ],
[ -12, 8 ],
[ -11, 7 ],
[ -11, 7 ],
[ -12, 11 ],
[ -8, 6 ],
[ -10, 8 ],
[ -10, 3 ],
[ -5, -6 ],
[ -2, -7 ],
[ -2, -9 ],
[ -3, -8 ],
[ -11, -16 ],
[ -3, 7 ],
[ -5, 13 ],
[ -3, 7 ],
[ -2, 9 ],
[ -2, 10 ],
[ -1, 7 ],
[ -1, 12 ],
[ -1, 9 ],
[ -0, 10 ],
[ -2, 17 ],
[ -2, 11 ],
[ -1, 7 ],
[ -1, 8 ],
[ -1, 16 ],
[ -1, 11 ],
[ -1, 8 ],
[ 0, 7 ],
[ 2, 9 ],
[ 3, 9 ],
[ 2, 7 ],
[ 3, 11 ],
[ 1, 7 ],
[ 1, 8 ],
[ 2, 8 ],
[ 2, 10 ],
[ 2, 8 ],
[ 3, 9 ],
[ 3, 8 ],
[ 2, 7 ],
[ 2, 7 ],
[ 3, 8 ],
[ 3, 7 ],
[ 3, 7 ],
[ 3, 8 ],
[ 2, 7 ],
[ 3, 7 ],
[ 7, 8 ],
[ 5, 7 ],
[ 4, 7 ],
[ 5, 7 ],
[ 5, 6 ],
[ 5, 8 ],
[ 6, 8 ],
[ 5, 8 ],
[ 5, 7 ],
[ 5, 6 ],
[ 6, 8 ],
[ 6, 10 ],
[ 4, 7 ],
[ 6, 7 ],
[ 5, 6 ],
[ 6, 7 ],
[ 8, 9 ],
[ 5, 8 ],
[ 1, 7 ],
[ -2, 7 ],
[ -4, 7 ],
[ -5, 7 ],
[ 1, 7 ],
[ 6, 7 ],
[ 6, 7 ],
[ 6, 6 ],
[ 9, 11 ],
[ 6, 6 ],
[ 2, 8 ],
[ -3, 8 ],
[ 1, 7 ],
[ 5, 6 ],
[ 6, 6 ],
[ 7, 7 ],
[ 6, 8 ],
[ 5, 6 ],
[ 7, 6 ],
[ 8, 5 ],
[ 8, 7 ],
[ 9, 9 ],
[ 5, 8 ],
[ 6, 8 ],
[ 6, 6 ],
[ 8, 6 ],
[ 11, 7 ],
[ 8, 6 ],
[ 7, 6 ],
[ 6, 6 ],
[ 6, 6 ],
[ 8, 6 ],
[ 7, 7 ],
[ 5, 7 ],
[ 6, 7 ],
[ 9, 8 ],
[ 7, 7 ],
[ 4, 8 ],
[ 3, 7 ],
[ -3, 8 ],
[ -4, 7 ],
[ -7, 10 ],
[ -6, 6 ],
[ -5, 7 ],
[ -5, 7 ],
[ -8, 9 ],
[ -9, 7 ],
[ -8, 7 ],
[ -3, 7 ],
[ 2, 7 ],
[ 4, 7 ],
[ 4, 6 ],
[ 5, 7 ],
[ 6, 8 ],
[ 5, 7 ],
[ 4, 7 ],
[ 2, 8 ],
[ 2, 8 ],
[ -4, 6 ],
[ -0, 8 ],
[ 2, 12 ],
[ -0, 8 ],
[ -3, 7 ],
[ 7, 5 ],
[ 5, 6 ],
[ 1, 8 ],
[ 5, 7 ],
[ 5, 7 ],
[ 7, 11 ],
[ 4, 7 ],
[ 7, 8 ],
[ 6, 6 ],
[ 10, 9 ],
[ 6, 6 ],
[ 8, 6 ],
[ 9, 5 ],
[ 14, 7 ],
[ 9, 4 ],
[ 12, 5 ],
[ -3, 7 ],
[ -10, 4 ],
[ 7, 6 ],
[ 11, 6 ],
[ 8, 6 ],
[ 11, 8 ],
[ 4, 3 ],
[ 6, 4 ]
]
],
"objects" : {
"Wards__December_2019__Boundaries" : {
"type" : "GeometryCollection",
"geometries" : [
{
"type" : "Polygon",
"arcs" : [
[ 0, 1, 2, 3, 4, 5 ]
],
"properties" : {
"objectid" : 1,
"wd19cd" : "E05000026",
"wd19nm" : "Abbey",
"wd19nmw" : null,
"bng_e" : 544433,
"bng_n" : 184379,
"long" : 0.081277,
"lat" : 51.539828999999997,
"st_areasha" : 1282925.001550829969347,
"st_lengths" : 6244.885014677499385
}
},
{
"type" : "Polygon",
"arcs" : [
[ 6, 7, 8, 9, 10, 11, 12 ]
],
"properties" : {
"objectid" : 2,
"wd19cd" : "E05000027",
"wd19nm" : "Alibon",
"wd19nmw" : null,
"bng_e" : 549247,
"bng_n" : 185196,
"long" : 0.150987,
"lat" : 51.545921,
"st_areasha" : 1364442.121561324922368,
"st_lengths" : 6353.917640448425118
}
It ends:
},
"transform" : {
"scale" : [ 0.000010000012213, 0.000010000016815 ],
"translate" : [ -6.419007931397705, 49.864630846832931 ]
}
}
Don't understand why the Lat/Long coordinates are null??
I would appreciate any pointers.
Regards,
Clive
You seem to be doing exactly what I warned against doing.
As I posted in the other thread you need 2 things to use shapemaps.
1. A shapemap
2. Some data
Getting a topojson file and importing it to Power Query to try and 'make' a map is not how it works.
I suggest using this tutorial to work out how to do it. (Grab the sample pbix and test with it)
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map#use-custom-maps
6 Replies
May I just check that importing a json file with polygon shape information is what you really want to do?
Sometimes people get confused about the shapemap functionality and try to import massive json files into powerbi when what they really want to do is use a topojson shapemap and link that to some other data.
You seem to be doing exactly what I warned against doing.
As I posted in the other thread you need 2 things to use shapemaps.
1. A shapemap
2. Some data
Getting a topojson file and importing it to Power Query to try and 'make' a map is not how it works.
I suggest using this tutorial to work out how to do it. (Grab the sample pbix and test with it)
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map#use-custom-maps
Can i point you at this thread -
It's also got some links to shapemap help.
Hi,
Thanks for your input, I followed those posts.
I also followed the steps in this blog https://www.osinsci.xyz/powerbi/uk-postcode-area-map-in-powerbi.
I copied an English postal code topojson map from: https://www.opendoorlogistics.com/downloads .
I imported the topojson file as outlined in the PowerBI blog: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map#getting-map-data.
I don't get a map, it sticks at the table??
Stuck in a loop.
Thanks,
Clive
Hi Anonymous ,
Is this the latitude and longitude data you want?
