Forum Discussion

Luka2893's avatar
Luka2893
Regular Visitor
2 years ago
Solved

Power Query API request URL error in Excel

Hello,

I am trying to connect Excel 2021 spreadsheet with data from BIS (Bank of International Settlements) database. They have API key builder. I retrieved the URL and put in Excel Power Query but the error poped up: "We encountered an error while trying to connect. Details: "Web.Contents failed to get contents from 'https://stats.bis.org/api/v1/data/BIS%2CWS_CBPOL%2C1.0/M.JP/all?detail=full' (404) ".
Can anyone suggest whats wrong here and how can I make it work?
https://stats.bis.org/api-doc/v1/#/Data%20queries/get_data__flow___key__all  -  API Builder
https://data.bis.org/topics/CBPOL/BIS,WS_CBPOL,1.0/M.JP?  -  Time series I'm trying to connect

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi Luka2893 

     

    I tinkered around a bit and came up with this (.xlsx attached too):

    let
  apiCall = Web.Contents(
    "https://stats.bis.org/api/v1",
    [
      RelativePath = "data/BIS,WS_CBPOL_M,1.0/M.JP/all"
      //Query        = [startPeriod = "1960", endPeriod = "2023", detail = "full"]
    ]
  ),
  #"Imported XML" = Xml.Tables(apiCall, null, 65001),
  #"Navigation: Table" = #"Imported XML"{1}[Table],
  #"Navigation: Namespace" = #"Navigation: Table"{0}[#"Namespace:"],
  #"Navigation: Series" = #"Navigation: Namespace"{0}[Series],
  #"Navigation: Obs" = #"Navigation: Series"{0}[Obs],
  #"Changed Type" = Table.TransformColumnTypes(
    #"Navigation: Obs",
    {
      {"Attribute:TIME_PERIOD", type date},
      {"Attribute:OBS_VALUE", type number},
      {"Attribute:OBS_STATUS", type text},
      {"Attribute:OBS_CONF", type text}
    }
  ),
  #"Rename Columns" = Table.TransformColumnNames(#"Changed Type", each Text.AfterDelimiter(_, ":"))
in
  #"Rename Columns"

     

    • I compared with some examples and WS_CBPOL_M seemed to be the required format.
    • You can optionally include Query parameters, but I commented them out (add a comma before the Query field if you do this).
    • The steps from "Imported XML" onwards may need to be tweaked.

    Does this help with what you were trying to do?

     

    Regards

    Owen

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi Luka2893 

     

    I tinkered around a bit and came up with this (.xlsx attached too):

    let
  apiCall = Web.Contents(
    "https://stats.bis.org/api/v1",
    [
      RelativePath = "data/BIS,WS_CBPOL_M,1.0/M.JP/all"
      //Query        = [startPeriod = "1960", endPeriod = "2023", detail = "full"]
    ]
  ),
  #"Imported XML" = Xml.Tables(apiCall, null, 65001),
  #"Navigation: Table" = #"Imported XML"{1}[Table],
  #"Navigation: Namespace" = #"Navigation: Table"{0}[#"Namespace:"],
  #"Navigation: Series" = #"Navigation: Namespace"{0}[Series],
  #"Navigation: Obs" = #"Navigation: Series"{0}[Obs],
  #"Changed Type" = Table.TransformColumnTypes(
    #"Navigation: Obs",
    {
      {"Attribute:TIME_PERIOD", type date},
      {"Attribute:OBS_VALUE", type number},
      {"Attribute:OBS_STATUS", type text},
      {"Attribute:OBS_CONF", type text}
    }
  ),
  #"Rename Columns" = Table.TransformColumnNames(#"Changed Type", each Text.AfterDelimiter(_, ":"))
in
  #"Rename Columns"

     

    • I compared with some examples and WS_CBPOL_M seemed to be the required format.
    • You can optionally include Query parameters, but I commented them out (add a comma before the Query field if you do this).
    • The steps from "Imported XML" onwards may need to be tweaked.

    Does this help with what you were trying to do?

     

    Regards

    Owen