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.
Open 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.
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
-
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 meThanks.
-
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".
-
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.
-
Automatically Prefill City and State using Zip Codes in your App
Use a textbox to enter the zipcode and set the defaults of the city and state as lookups against the zipcode in the table.
-
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!
-
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.
-
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
-
Automatically Prefill City and State using Zip Codes in your App
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.
*This post is locked for comments