Hello! I am making a map which I need to export, I have the zipcodes but I need to find the longitude and latitude, I am using the ArcGis API but when I create the function and I am going to upload the data to the datamart I get the error ErrorMessageOne or more entities references a dynamic data source.
I would appreciate it if you could help me 😞 I'm new to this kind of thing
(location as text) => let Source = Json.Document(Web.Contents("https://geocode.arcgis.com/arcgis/rest/services/World/GeocodeServer/findAddressCandidates?SingleLine..."&location&" USA&forStorage=false&outFields=addr_type&f=pjson&token=XX")), #"Converted to table" = Table.FromRecords({Source}), #"Expanded spatialReference" = Table.ExpandRecordColumn(#"Converted to table", "spatialReference", {"wkid", "latestWkid"}, {"spatialReference.wkid", "spatialReference.latestWkid"}), #"Expanded candidates" = Table.ExpandListColumn(#"Expanded spatialReference", "candidates"), #"Expanded candidates1" = Table.ExpandRecordColumn(#"Expanded candidates", "candidates", {"address", "location", "score", "attributes", "extent"}, {"candidates.address", "candidates.location", "candidates.score", "candidates.attributes", "candidates.extent"}), #"Expanded candidates.location" = Table.ExpandRecordColumn(#"Expanded candidates1", "candidates.location", {"x", "y"}, {"candidates.location.x", "candidates.location.y"}), #"Expanded candidates.attributes" = Table.ExpandRecordColumn(#"Expanded candidates.location", "candidates.attributes", {"addr_type"}, {"candidates.attributes.addr_type"}), #"Expanded candidates.extent" = Table.ExpandRecordColumn(#"Expanded candidates.attributes", "candidates.extent", {"xmin", "ymin", "xmax", "ymax"}, {"candidates.extent.xmin", "candidates.extent.ymin", "candidates.extent.xmax", "candidates.extent.ymax"}), #"Changed column type" = Table.TransformColumnTypes(#"Expanded candidates.extent", {{"spatialReference.wkid", Int64.Type}, {"spatialReference.latestWkid", Int64.Type}, {"candidates.address", type text}, {"candidates.location.x", type number}, {"candidates.location.y", type number}, {"candidates.score", Int64.Type}, {"candidates.attributes.addr_type", type text}, {"candidates.extent.xmin", type number}, {"candidates.extent.ymin", type number}, {"candidates.extent.xmax", type number}, {"candidates.extent.ymax", type number}}), #"Removed columns" = Table.RemoveColumns(#"Changed column type", {"candidates.score", "candidates.attributes.addr_type", "candidates.extent.xmin", "candidates.extent.ymin", "candidates.extent.xmax", "candidates.extent.ymax", "candidates.address", "spatialReference.latestWkid", "spatialReference.wkid"}) in #"Removed columns"
