Excel Power Query调用API认证失败问题求助

已成功通过API获取Token，但调用数据接口时收到「无法验证提供的凭据，请重试」错误，该接口在Postman中可正常运行，Power Query代码如下：

let
    // Your token retrieval function
    GetAuthToken = () =>
        let
            body = [UserName="user", Password="pass"],
            apiUrl = "https://test.com/test/api/v1/interop/logon",
            headers = [#"Content-Type" = "application/json"],
            response = Web.Contents(apiUrl, [Headers = headers, Content = Json.FromValue(body)]),
            responseText = Text.FromBinary(response),
            token = responseText
        in
            token,

 // Your API data retrieval function
    GetApiData = (apiUrl as text, token as text) =>
        let
                    headers = [
                #"Authorization" = "Bearer " & token,
                #"Content-Type" = "application/json",
                #"accept" = "application/json"
            ],
            requestBody = [
                ResultType = "Hierachical",
                FilterConditions = {
                    [EntityName = null, Filter = null, Sort = null]
                },
                PageNumber = 1,
                PageSize = 25,
                ProjectionOnly = false,
                Parameters = {}
            ],
            response = Json.Document(
                Web.Contents(
                    apiUrl,
                    [
                        Headers = headers,
                        Content = Json.FromValue(requestBody),
                        ManualStatusHandling = {400} // Add this line to handle 400 Bad Request responses
                    ]
                )
            )
        in
            response,

    // Call the token retrieval function
    Token = GetAuthToken(),

    // Call the API data retrieval function using the token and get data
    ApiData = GetApiData("https://test.com/test/api/v1/interop/test", Token)  

in
ApiData

以下是针对性的解决方法：

• 修正Token提取逻辑
  登录接口返回的通常是JSON格式响应（如{"token":"xxx"}），而非纯Token字符串。直接将响应文本当作Token会导致Authorization头格式错误，需解析JSON提取具体字段：

  GetAuthToken = () =>
      let
          body = [UserName="user", Password="pass"],
          apiUrl = "https://test.com/test/api/v1/interop/logon",
          headers = [#"Content-Type" = "application/json"],
          response = Web.Contents(apiUrl, [Headers = headers, Content = Json.FromValue(body)]),
          responseJson = Json.Document(response), // 解析JSON响应
          token = responseJson[token] // 替换为实际Token字段名，如access_token、token等
      in
          token,
  

  可单独运行GetAuthToken()确认返回值为纯Token字符串。

• 对齐请求头与Postman一致
  部分API对请求头大小写敏感，且Postman会自动添加部分默认头，需确保Power Query请求头完全匹配：

  headers = [
      #"Authorization" = "Bearer " & token,
      #"Content-Type" = "application/json",
      #"Accept" = "application/json", // 修正为大写开头
      #"User-Agent" = "PostmanRuntime/7.32.3" // 复制Postman中的User-Agent值
  ],
  

• 修复请求体拼写错误
  代码中ResultType = "Hierachical"存在拼写错误，正确应为Hierarchical，参数错误可能触发API返回非预期的权限提示。

• 调整数据源隐私设置
  Power Query的隐私隔离可能影响请求发送：

  1. 点击「文件」>「选项和设置」>「数据源设置」
  2. 找到API对应的域名，选择「编辑权限」
  3. 将隐私级别设置为「公开」（或与登录接口一致的级别）
     操作后清除缓存，重新运行查询。

• 添加调试步骤对比请求
  在GetApiData中输出请求头和Token，与Postman中的请求详情逐一对比：

  GetApiData = (apiUrl as text, token as text) =>
      let
          headers = [
              #"Authorization" = "Bearer " & token,
              #"Content-Type" = "application/json",
              #"Accept" = "application/json"
          ],
          // 调试输出，查看实际发送的Token和头信息
          debugToken = "Bearer " & token,
          debugHeaders = headers,
          requestBody = [
              ResultType = "Hierarchical",
              FilterConditions = {
                  [EntityName = null, Filter = null, Sort = null]
              },
              PageNumber = 1,
              PageSize = 25,
              ProjectionOnly = false,
              Parameters = {}
          ],
          response = Json.Document(
              Web.Contents(
                  apiUrl,
                  [
                      Headers = headers,
                      Content = Json.FromValue(requestBody),
                      ManualStatusHandling = {400,401} // 新增401状态码处理
                  ]
              )
          )
      in
          response,
  

内容的提问来源于stack exchange，提问作者nik