nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How do I geocode addresses in Power Automate?

Teresa_Blader

I have a flow set up to take address from an excel table and geocode it. However every time I run this the error is Bad Request. AI seems to say I need to input JSON into the addresses field and not a string for the address, but still results in Bad Request. I can't find ANY documentation. So just wondering if someone here can help? Do I have to use Find Candidates first? That seems redundant. Thanks!!

Output shown below for Addresses is string from

trim(
    concat(
        outputs('CleanStreetPart'), ' ',
        outputs('Directional'), ', ',
        outputs('City'), ',',
        outputs('StateZip')
    )
)

Najděte více příspěvků označených jako

Accepted answers

abureaux

I have an Excel file that users can fill out for adding protfolio projects to a database to simplify data collection. For that process, I geocode the locations. This is what I do:

Step 1: List rows present in a table.

Pretty straight forward. The only requirement is that the Excel file has a Table. If yours doesn't, you can dynamically add a table to excel, assuming the file is in a structured/consistent format. I do this for a downstream process for this same workflow for adding lab results.

Step 2: Find address candidates.

All fields are plain text. The more fields you can provide, the better.

Step 3: Condition: Top Address Candidate is greater than 90%

I was a little arbitrary with the 90% match, but it has worked well and I've only had 2 addresses fail (and they were super remote. I actually added a "geocode override" option so users could manually add lat-lon to the Excel file for those remote addresses)

Step 4 (Optional): Append to array variable

If you then want to add those addresses to a Feature Layer, you need to format the schema corretly.

I add a bunch of random data here. All that really matters is this:

{
  "x": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/x']},
  "y": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/y']},
  "wkid": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/spatialReference/wkid']},
  "latestWkid": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/spatialReference/latestWkid']}
}

Step 5 (only if doing step 4): Add records to a feature layer (batch)

Step 4 formats the JSON. Just dump it here and points will be created.

All comments

DougSims_GT

I'm curious about the overall goal of your flow here. Do you have more information on how the data is being created?

All the suggestions I have are to use a different tool to accomplish what I assume you are trying to do. (Data Pipelines, FME, Data Interoperability extension)

Teresa_Blader

There’s an excel file is updated automatically by someone else daily and I need to geocode the addresses. Pretty simple goal I laid out in the post.

I’m looking for someone to provide how to build the array in Power Automate for the geocode addresses action since there’s no documentation. So you’re welcome to provide other workflow options, but I’m trying to learn how to use the action in Power Automate 🙂

abureaux

I have an Excel file that users can fill out for adding protfolio projects to a database to simplify data collection. For that process, I geocode the locations. This is what I do:

Step 1: List rows present in a table.

Step 2: Find address candidates.

All fields are plain text. The more fields you can provide, the better.

Step 3: Condition: Top Address Candidate is greater than 90%

Step 4 (Optional): Append to array variable

If you then want to add those addresses to a Feature Layer, you need to format the schema corretly.

I add a bunch of random data here. All that really matters is this:

{
  "x": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/x']},
  "y": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/y']},
  "wkid": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/spatialReference/wkid']},
  "latestWkid": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/spatialReference/latestWkid']}
}

Step 5 (only if doing step 4): Add records to a feature layer (batch)

Step 4 formats the JSON. Just dump it here and points will be created.

Teresa_Blader

Thank you for those details! Yes - pretty similar. I have an Office Script to set the table range and then list the rows action. 👍

I'm curious about using 'Find Address Candidates' vs 'Geocode Addresses'.

Have you tried both and choose 'Find Address Candidates' anyway? It seems like 'Geocode Addresses' is maybe newer?? And it seems like it supposed to be like how a users would search for an address in map - but requires an 'array of well formed fields' - which I don't get what to put.

I have 'Geocode Addresses' > "add record to feature layer" > "create point from geometry" which lets me input the output geometry from geocode addresses and set the coordinate system so it *seems* like it would save some steps if I could just figure out the expression for the 'array of well formed fields' input for 'Geocode Addresses' vs using 'Find Address Candidates'?

But I probably just don't understand how these function. And maybe Geocode Addresses would try to do a point for every possible match suggestion?

This is what AI told me to do 👎 === Bad Request

👇

👇

abureaux

Have you tried both and choose 'Find Address Candidates' anyway?

Yes. When I was setting up this specific flow, I flip-flopped between the two several times. As you can see, I landed on Candidates. I did have a reason, but it has also been a while since I set this up so I don't 100% remember what that reason was 😅. I think it had something to do with prefering the outputs of this connector over the other.

Ya, that seems like a bunch of extra steps. While I'm probably biased, I'll advocate for the setup I showed above. One thing I didn't show though is that this is in an Apply to each. Basically, the Excel step gets a table. That table is effectivly an array. I use that array to loop through each line and get addresses for every item. If the address is good (>90% match) I put it into my "Valid Items" array, and if it's bad (<90% match) I put it into my "Invalid Items" array. After the apply to each, I use my Valid Items array in the batch action to create all of the points in bulk. Comparing my workflow to what AI has you doing above, you are making 1 call to Portal for every item you create (which can end up being many calls, depending on the workflow) whereas I am making 1 call regardless of the number of additons.

Here is a highlevel overview of my workflow.

I deleted a bunch of unnessecary stuff, so what's left it the core flow.
I left two item attributes (project number and client) to show how to add that type of data. You can add as much or as little as you need. EDIT for clairity: I am referring to "gn_pn" and "gn_client" in the "Append to array variable" and "Select" steps.
I totally forgot about the most important step: The Select. The Select is how I format data to add to Portal. It's also the most finiky step as it needs to be edited in a specific way (I left my original note in there, about editing only in Text Mode [click the "T" near the top-right corner of the Select to swap modes])
- This part of the flow is what took any real amount of time because it was so finiky. If you try this and get an error adding data, check this step. If you see way too many quotes, the JSON got messed up and will need to be fixed. Only editing in text mode then swapping back to Map mode to save tends to work though.
I added some notes throughout so you can see what data I'm using. E.g., If you look at the "Select - Formatted" step, the "@{body('Filter_array_-_Valid_Addresses')}" is referring to the "Body" in the "From" field.

Teresa_Blader

UGH Thanks for your help!!! I finally got it!! The most helpful part was this:

{
  "x": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/x']},
  "y": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/y']},
  "wkid": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/spatialReference/wkid']},
  "latestWkid": @{outputs('Find_address_candidates')?['body/topCandidate/geometry/spatialReference/latestWkid']}
}

And then just skipping the create point geometry action and the geocode address option.

I cried I'm so happy!! 😭

This is my flow to update existing permits, create centroids from parcels if found (which is preferred), create point from geocoded address, or send an email with a survey123 link with url parameters: all from an excel table that is overwritten (so the table ID changes in the workbook).

Připojte se ke konverzaci

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.