Forum Discussion
How to import topoJSON file to make a shape map?
Hi everyone!
Does anybody know how to import a topoJSON file to represent some points in a shape map?
When I try to import it, query assistant openns and I dont know how to continue.
This is the structure of the topoJSON file:
{"type":"Topology","objects":{"Espiras":{"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":[538036,487702],"properties":{"Elevation":8.29999999926,"RefName":"(2012)","Text":"(2012)"}}....
Thank you in advanced!
Anonymous,
Yes, but don't need to write a loop maually in M code.(power bi will do automatically), suppose a new point(point2) has been added to your json file like below:
{ "type":"Topology", "objects":{ "Espiras":{ "type":"GeometryCollection", "geometries":[ { "type":"Point1", "coordinates":[ 538036, 487702 ], "properties":{ "Elevation":8.29999999926, "RefName":"(2012)", "Text":"(2012)" } }, { "type":"Point2", "coordinates":[ 500000, 400000 ], "properties":{ "Elevation":8, "RefName":"(2013)", "Text":"(2013)" } } ] } } }Click query editors and use M code below:
let Source = Json.Document(File.Contents("C:\Users\JimmyTao\Desktop\TPO_JSON.json")), objects = Source[objects], Espiras = objects[Espiras], geometries = Espiras[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"Regards,
Jimmy Tao
4 Replies
v-yuta-msftCommunity Support
Anonymous,
After imported to power query, click "convert to table", then use power query below to find the point coordinates:
let Source = Json.Document(File.Contents("C:\Users\JimmyTao\Desktop\TPO_JSON.json")), #"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{1}[Value], Espiras = Value[Espiras], geometries = Espiras[geometries], geometries1 = geometries{0}, coordinates = geometries1[coordinates] in coordinatesFinally you can achieve:
You may also refer to the appendix.
Regards,
Jimmy Tao
Thank you so much! It works well:) My last question is: In the file I have 250 records, and each record has a pair of coordinates. Is there any way to obtain the 250 points without doing manually? I mean, is it possible to do a loop?
Thank you in advanced!:)
- v-yuta-msft
Anonymous,
Yes, but don't need to write a loop maually in M code.(power bi will do automatically), suppose a new point(point2) has been added to your json file like below:
{ "type":"Topology", "objects":{ "Espiras":{ "type":"GeometryCollection", "geometries":[ { "type":"Point1", "coordinates":[ 538036, 487702 ], "properties":{ "Elevation":8.29999999926, "RefName":"(2012)", "Text":"(2012)" } }, { "type":"Point2", "coordinates":[ 500000, 400000 ], "properties":{ "Elevation":8, "RefName":"(2013)", "Text":"(2013)" } } ] } } }Click query editors and use M code below:
let Source = Json.Document(File.Contents("C:\Users\JimmyTao\Desktop\TPO_JSON.json")), objects = Source[objects], Espiras = objects[Espiras], geometries = Espiras[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"Regards,
Jimmy Tao