PowerBI通过Web查询调用Azure Maps地理API端点报错

需求 #

将地址（邮政编码+城市名称）转换为[经度, 纬度]，并提取usageTypes为Route的几何点。

示例响应结构 #

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "address": {
          "countryRegion": {
            "name": "United States"
          },
          "adminDistricts": [
            {
              "shortName": "WA"
            },
            {
              "shortName": "King County"
            }
          ],
          "formattedAddress": "15127 NE 24th St, Redmond, WA 98052",
          "streetName": "NE 24th St",
          "streetNumber": "15127",
          "locality": "Redmond",
          "postalCode": "98052",
          "addressLine": "15127 NE 24th St"
        },
        "type": "Address",
        "confidence": "High",
        "matchCodes": [
          "Good"
        ],
        "geocodePoints": [
          {
            "geometry": {
              "type": "Point",
              "coordinates": [
                -122.138681,
                47.630358
              ]
            },
            "calculationMethod": "Rooftop",
            "usageTypes": [
              "Display"
            ]
          },
          {
            "geometry": {
              "type": "Point",
              "coordinates": [
                -122.1386787,
                47.6302179
              ]
            },
            "calculationMethod": "Rooftop",
            "usageTypes": [
              "Route"
            ]
          }
        ]
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          -122.138681,
          47.630358
        ]
      },
      "bbox": [
        -122.14632282407,
        47.626495282429325,
        -122.13103917593001,
        47.63422071757068
      ]
    }
  ]
}

已尝试的调用方式 #

• 直接在Web查询中传入API密钥（触发报错）
• 通过PowerBI参数存储并传入API密钥（触发报错）
• 从本地文本文件加载API密钥（密钥加载正常，但调用仍报错）

测试查询代码 #

let
Query1 = let

// load API-KEY from .txt
FilePath = "C:\Users\<my local path to the .txt>\API-KEY.txt",
#"API-KEY" = Text.Trim(Text.FromBinary(File.Contents(FilePath))),

url = "https://atlas.microsoft.com/geocode?api-version=2025-01-01&query=10115 Berlin&subscription-key=" & #"API-KEY",
rawResponse = Web.Contents(url),

jsonResponse = Json.Document(rawResponse),
features = jsonResponse[features], 
geocodePoints = features{0}[properties][geocodePoints], 

filteredRoutePoints = List.Select(geocodePoints, each List.Contains(Record.Field(_, "usageTypes"), "Route")),
routePoint = if List.Count(filteredRoutePoints) > 0 then List.First(filteredRoutePoints) else null,

coordinates = if routePoint <> null then routePoint[geometry][coordinates] else {null, null},

latitude = coordinates{1},
longitude = coordinates{0}
in
    [Latitude = latitude, Longitude = longitude],
    #"Converted to Table" = Record.ToTable(Query1)
in
    #"Converted to Table"

问题现状 #

三种方式均触发相同的“无法访问服务”错误，已尝试多个Azure Maps端点。

内容的提问来源于stack exchange，提问作者cheese steak jimmy's