I have a power query that makes a call to Google Maps API and which returns Json like this

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

The data looks like this

{
   "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"
}

I then can read the "data" from that power query in another power query, where I am trying to get the zip code data.

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

address_component1 (I know terrible name, but still prototyping) is a Json record that is then used by yet another query.

But the value from the Json list is hard coded as you can see to be the sixth item in the list. But I'm finding that the postal code that I want is not always in the sixth position in the list.

The records have a types list in them that I want to read and determine if the type equals "postal_code"

I can't figure out though how to loop through the list and check each item.

I'd like it to be some thing like

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

Is this possible to loop in this way?

CodePudding user response：

If, in fact, your json is as you show, with regard to:

• a single postal code element
• containing a single postal code

You can extract it with the following code:

• First extract the address_components into a list of records
• Look through each record to see if the first element in types is postal_code
• if it is, then return the long_name

If the json were to include more than one postal_code, different algorithm might be required.

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 will contain the zip code as a text string.