回圈遍歷ExcelPowerQuery中的Json串列以檢索記錄

我有一個電源查詢，它呼叫 Google Maps API 并像這樣回傳 Json

let
    baseurl = "https://maps.googleapis.com/maps/api/geocode/json?",
    cellAddress = Excel.CurrentWorkbook(){[Name="Address"]}[Content]{0}[Column1],
    stepOneAdress = Replacer.ReplaceText(cellAddress, "Addrs: ", ""),
    noSpaceAdress = Replacer.ReplaceText(stepOneAdress, " ", " "),
    noCommasAdress = Replacer.ReplaceText(noSpaceAdress, ",", ","),
    fullUrl = baseurl&"address="&noCommasAdress&"&key=AIzaSyCwcLo1bl8iTSWhU3vgHNuq3rJHbSGH-Pw",

    webdata = Web.Contents(fullUrl),

    response = Json.Document(webdata),
    results = response[results],
    data = results{0}
    
in
    data

資料看起來像這樣

{
   "results" : [
      {
         "address_components" : [
            {
               "long_name" : "Steamboat Springs",
               "short_name" : "Steamboat Springs",
               "types" : [ "locality", "political" ]
            },
            {
               "long_name" : "Routt County",
               "short_name" : "Routt County",
               "types" : [ "administrative_area_level_2", "political" ]
            },
            {
               "long_name" : "Colorado",
               "short_name" : "CO",
               "types" : [ "administrative_area_level_1", "political" ]
            },
            {
               "long_name" : "United States",
               "short_name" : "US",
               "types" : [ "country", "political" ]
            },
            {
               "long_name" : "80487",
               "short_name" : "80487",
               "types" : [ "postal_code" ]
            }
         ],
         "formatted_address" : "Steamboat Springs, CO 80487, USA",
         "geometry" : {
            "bounds" : {
               "northeast" : {
                  "lat" : 40.5286781,
                  "lng" : -106.7801651
               },
               "southwest" : {
                  "lat" : 40.439399,
                  "lng" : -106.886848
               }
            },
            "location" : {
               "lat" : 40.4849769,
               "lng" : -106.8317158
            },
            "location_type" : "APPROXIMATE",
            "viewport" : {
               "northeast" : {
                  "lat" : 40.5286781,
                  "lng" : -106.7801651
               },
               "southwest" : {
                  "lat" : 40.439399,
                  "lng" : -106.886848
               }
            }
         },
         "place_id" : "ChIJYUZWCYF7QocRfc9uSNGjqBs",
         "types" : [ "locality", "political" ]
      }
   ],
   "status" : "OK"
}

然后我可以在另一個電源查詢中從該電源查詢中讀取“資料”，我正在嘗試獲取郵政編碼資料。

let
    data = Coordinates,
    address_components = data[address_components],
    address_components1 = address_components{6}
in
    address_components1

address_component1（我知道可怕的名字，但仍然是原型）是一個 Json 記錄，然后被另一個查詢使用。

但是 Json 串列中的值是硬編碼的，您可以看到它是串列中的第六項。但我發現我想要的郵政編碼并不總是在串列中的第六位。

記錄中有一個型別串列，我想閱讀并確定型別是否等于“postal_code”

我不知道如何遍歷串列并檢查每個專案。

我希望它像

address_component1,
foreach(item in address_components){
   type_list = item["types"],
   if type_list = "postal_code"
      address_component1 = item,

這可以以這種方式回圈嗎？

uj5u.com熱心網友回復：

事實上，如果你的 json 是你所展示的，關于：

• 單個郵政編碼元素
• 包含一個郵政編碼

您可以使用以下代碼提取它：

• 首先將其提取address_components到記錄串列中
• 查看每條記錄中的第一個元素types是否是postal_code
• 如果是，則回傳 long_name

如果 json 包含多個postal_code，則可能需要不同的演算法。

let
    Source = Json.Document(File.Contents("C:\Users\ron\Desktop\new 3.json")),

//extract the address_components
    address_components = Source[results]{0}[address_components],

//find the postal code and extract it
    postalCode=List.Accumulate(address_components,"", (state, current)=>
         if Record.Field(current,"types"){0} = "postal_code" then state & Record.Field(current,"long_name") else state)
in
    postalCode

postalCode 將包含郵政編碼作為文本字串。

標籤：json 擅长 电源查询

上一篇：時間戳vba簡化

下一篇：匯出到ServletOutputStream的Excel檔案已損壞