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 t...
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
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
Community Support
Community SupportAnonymous,
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