How do I do an Excel Web JSON query per row of a data in a table-CodePudding

I am looking for a way to verify Sales Tax rates for transactions that are listed in a table in Excel. My Excel table has an "Address" column, and I would like to query the California sales tax API (

The above Example would use (

We write a custom function which transforms the address into an api call, and then returns a List of the Records of the tax rate info.

I named it fnCaTax

(adr as text)=>

let 
    baseURL = "https://services.maps.cdtfa.ca.gov/api/taxrate/GetRateByAddress?",
    
    x = List.Transform(Text.Split(adr,","),each Text.Trim(_)),
    address = Text.Replace(x{0}," "," "),
    city = Text.Replace(x{1}," "," "),
    stateZip = Text.Split(x{2}," "),
    state = stateZip{0},
    zip = stateZip{1},

    apiCall=baseURL &
                "&Address=" & address &
                "&City=" & city &
                "&Zip=" & zip,

    json = Json.Document(Web.Contents(apiCall)),
    taxRateInfo = json[taxRateInfo]    
    
in taxRateInfo

We then expand the List into new rows, and the Records in the List into new columns

...
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded Column1", "fnCaTax", each fnCaTax([Column1])),
    #"Expanded fnCaTax" = Table.ExpandListColumn(#"Invoked Custom Function", "fnCaTax"),
    #"Expanded fnCaTax1" = Table.ExpandRecordColumn(#"Expanded fnCaTax", "fnCaTax", 
        {"rate", "jurisdiction", "city", "county", "tac"}, {"rate", "jurisdiction", "city", "county", "tac"})
in
    #"Expanded fnCaTax1"

To get:

I did no testing for bad addresses or missing components of the address. You should add that if that is a possibility