Skip to main content

Notifications

Automatically Prefill City and State using Zip Codes in your App

Obtain the free file from the US postal service. It is available at http://federalgovernmentzipcodes.us/  and consists of a single .cvs file for more than 42,000 cities and states by their zip codes.  It also contains several other columns but once in Excel, I just delete these. Open it with Excel and format it as a Table. Then use PowerQuery to separate the table into 3 tables of no more than 15000 rows each.  Save each table with a different name in the same Excel file.  I made mine Table1, Table2, and Table3. The reason for splitting the large table into three smaller ones is because PowerApps has an import limit of 15000 items for Static files.

 

 

zip2.PNGOpen one of your apps and add the 3 tables using the add Static files from Excel connector. This takes seconds to do. 

For this example, I used a file of free sample data for testing from the internet, so the names and addresses are bogus. https://www.briandunning.com/sample-data/ 

 

Put a button on the screen and set the OnSelect property to Collect(zips, Table1); Collect(zips, Table2); Collect(zips, Table3). (If you use this formula in the OnStart property of the App, it will always be available and you don't need the button.) Moreover,creating this collection from Static tables only takes a few seconds and the user won't notice.zip1.PNG

 

As you can see, the resulting collection consists of 42,522 rows and is called zips. Now where ever a zip code is found in your own dataset, you can use the LookUp() function to display the city and state. So, in the example, the Zip Code is abbreviated zip in the bogus datasource. In the gallery, I placed a City and State label, with the Text property as follows. 

LookUp(zips, Zipcode=ThisItem.zip, City) & ", " & LookUp(zips, Zipcode=ThisItem.zip, State)

To maintain the 5 digit format with leading zeros, wrap ThisItem.zip as follows

Text(ThisItem.zip, "00000")

This will preven PowerApps from stripping the 0 as shown in the example for Venere, Art.

Once you have the static data in your app you will never need to enter City and State again as you can look it up from the Collection. 

 

Comments

*This post is locked for comments

  • PowerApps11 Profile Picture PowerApps11 521
    Posted at
    Automatically Prefill City and State using Zip Codes in your App

    @Drrickryp 
    Please can you share the zip files, I would like to impalement this but the site the download not working for me 

    Thanks.

  • Automatically Prefill City and State using Zip Codes in your App

    @Drrickryp That's fine I don't really care just need to autopopulate any accurate zip code in a cell from the cell populated with the "City, State".

  • Drrickryp Profile Picture Drrickryp
    Posted at
    Automatically Prefill City and State using Zip Codes in your App

    @Anonymous 

    One city can have many zip codes but each zip code can have only one city.

  • Automatically Prefill City and State using Zip Codes in your App

    @Drrickryp I'm using Google Sheets and I need to look up a zip code off the city and state alone. Everything I find is looking up a zip code with a full address. @austin_brown22 just asked exactly what I need. Can either of you elaborate how to do this please.

     

    I am simply provided with a city, state and never provided the zip code. I just need any accurate zip code for the city, state and the zip code to be looked up in the next cell.

  • Drrickryp Profile Picture Drrickryp
    Posted at
    Automatically Prefill City and State using Zip Codes in your App

    @austin_brown22 

    Use a textbox to enter the zipcode and set the defaults of the city and state as lookups against the zipcode in the table.

     

  • austinbrown_22 Profile Picture austinbrown_22
    Posted at
    Automatically Prefill City and State using Zip Codes in your App

    @Drrickryp Any idea how this would be different if incorporating it into a form where the user enters the zip code and it will fill in state and city? I am having troubles with it. Thank you!

  • Sajarac Profile Picture Sajarac 3,251
    Posted at
    Automatically Prefill City and State using Zip Codes in your App

    A PAIN!......LOL

     

    Is an slow process unless there is a magic formula like the previous one.

     

    Or maybe I keep doing wrong things. I am selected each table, right click "Add as new query" once the 60 tables are done then I will load into the excel file. I just tested with the first 10 tables 150K rows and it takes a while.

     

    sajarac_0-1623964436731.png

     

  • Drrickryp Profile Picture Drrickryp
    Posted at
    Automatically Prefill City and State using Zip Codes in your App

    @sajarac 

    I haven't tried it on anything near that scale. Let me know how it goes please.

  • Sajarac Profile Picture Sajarac 3,251
    Posted at
    Automatically Prefill City and State using Zip Codes in your App

    AWESOME !!!!!

    Thank you very much. It is going to be fun is almost 900K records so I will need to save about 60 tables. But is OK.

     

    Thank you so much.

    Regards

  • Drrickryp Profile Picture Drrickryp
    Posted at
    Automatically Prefill City and State using Zip Codes in your App

    @sajarac 

    After you upload your master table into Power Query, you can use the function = Table.Split(Source,15000) to split your data into smaller tables.  The result will show a number of tables, each containing 15000 records.  By clicking on the table you can rename it and save it. In the screenshot on the left, I used it to split a smaller table of 3k into three 1k tables.  _3.png