Loop through Json List in Excel Power Query to retrieve record

Question

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, " ", "%20"),
    noCommasAdress = Replacer.ReplaceText(noSpaceAdress, ",", "%2C"),
    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?

Ron Rosenfeld · Accepted Answer · 2021-12-04 20:33:35Z

1

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.

edited Dec 4, 2021 at 20:33

answered Dec 4, 2021 at 20:28

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Loop through Json List in Excel Power Query to retrieve record

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related