Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi! I have a topoJSON file with these structure:
"type": "Topology", "objects": { "Espiras": { "type": "GeometryCollection", "geometries": [ { "type": "Point", "coordinates": [ 538036, 487702 ], "properties": { "Elevation": 8.29999999926, "RefName": "(2012)", "Text": "(2012)" } },
When I import it to MyGeodata Map (a GIS application to represent maps) it works well and I obtain all the points represented in a city in the north of Spain. However, I don`t know how to do it in power bi. Find attached the full file:
Solved! Go to Solution.
Hi @Anonymous,
I'd like to suggest you use json connector to get data from topojson file then convert json file as table:
Full query:
let Source = Json.Document(File.Contents("D:\Test\Espiras.topojson")), #"Converted to Table" = Record.ToTable(Source), #"Transposed Table" = Table.Transpose(#"Converted to Table"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"type", type text}, {"objects", type any}, {"arcs", type any}, {"bbox", type any}, {"transform", type any}}), #"Expanded objects" = Table.ExpandRecordColumn(#"Changed Type", "objects", {"Espiras"}, {"Espiras"}), #"Expanded bbox" = Table.ExpandListColumn(#"Expanded objects", "bbox"), #"Expanded Espiras" = Table.ExpandRecordColumn(#"Expanded bbox", "Espiras", {"geometries"}, {"geometries"}), #"Expanded geometries" = Table.ExpandListColumn(#"Expanded Espiras", "geometries"), #"Expanded geometries1" = Table.ExpandRecordColumn(#"Expanded geometries", "geometries", {"coordinates", "properties"}, {"coordinates", "properties"}), #"Added Custom" = Table.AddColumn(#"Expanded geometries1", "Longitude & Latitude", each Text.From([coordinates]{0}) &","&Text.From([coordinates]{1})), #"Expanded properties" = Table.ExpandRecordColumn(#"Added Custom", "properties", {"Elevation", "RefName"}, {"Elevation", "RefName"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded properties",{"coordinates", "arcs", "transform", "type"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Longitude & Latitude", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Longitude", "Latitude"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Longitude", Int64.Type}, {"Latitude", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"bbox", "Elevation", "RefName", "Longitude", "Latitude"}) in #"Reordered Columns"
After these steps, you can try to use map visual which support to analysis coordinates information to build graph.
Regards,
Xiaoxin SHeng
HI @Anonymous,
According to your description, I'd recommend you to use shape map visual, it support to import custom map information from topojson file.
BTW, when I test with you data, it seems like your data structure not suitable map structure which it require. Please take a look at following link to modify your data structure to use in power bi shape map.
Shape Maps in Power BI Desktop (Preview)
Create Your Own Custom Map for Power BI
Regards,
Xiaoxin Sheng
@v-shex-msft thank you for the info, but I still have the same problem 😞 I am going to try to explain you because I think that it could be a problem of the last version of Power BI Desktop.
I had a TopoJson file with the structure I show you before. You can find attached the original complete file. ( topoJSON file )
When I import it, the power query interface appears and I click convert to table. Then, a new table is imported to the data model with some data of the file.
When I try to represent it I obtain this:
Screenshot of the table imported
When I try to import a topoJSON file to other specific app to represent GIS maps I obtain the correct result that is shown in the image below:
The correct map visualization in a external app
I would like to obtain something like that in Power BI to include it in my report, but I can`t. The official documentation that you pass me is not clear.
Thank you in advanced!
Hi @Anonymous,
I'd like to suggest you use json connector to get data from topojson file then convert json file as table:
Full query:
let Source = Json.Document(File.Contents("D:\Test\Espiras.topojson")), #"Converted to Table" = Record.ToTable(Source), #"Transposed Table" = Table.Transpose(#"Converted to Table"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"type", type text}, {"objects", type any}, {"arcs", type any}, {"bbox", type any}, {"transform", type any}}), #"Expanded objects" = Table.ExpandRecordColumn(#"Changed Type", "objects", {"Espiras"}, {"Espiras"}), #"Expanded bbox" = Table.ExpandListColumn(#"Expanded objects", "bbox"), #"Expanded Espiras" = Table.ExpandRecordColumn(#"Expanded bbox", "Espiras", {"geometries"}, {"geometries"}), #"Expanded geometries" = Table.ExpandListColumn(#"Expanded Espiras", "geometries"), #"Expanded geometries1" = Table.ExpandRecordColumn(#"Expanded geometries", "geometries", {"coordinates", "properties"}, {"coordinates", "properties"}), #"Added Custom" = Table.AddColumn(#"Expanded geometries1", "Longitude & Latitude", each Text.From([coordinates]{0}) &","&Text.From([coordinates]{1})), #"Expanded properties" = Table.ExpandRecordColumn(#"Added Custom", "properties", {"Elevation", "RefName"}, {"Elevation", "RefName"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded properties",{"coordinates", "arcs", "transform", "type"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Longitude & Latitude", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Longitude", "Latitude"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Longitude", Int64.Type}, {"Latitude", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"bbox", "Elevation", "RefName", "Longitude", "Latitude"}) in #"Reordered Columns"
After these steps, you can try to use map visual which support to analysis coordinates information to build graph.
Regards,
Xiaoxin SHeng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |