Showing results for 
Search instead for 
Did you mean: 

Automatically Prefill City and State using Zip Codes in your App

Obtain the free file from the US postal service. It is available at  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. 


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,, City) & ", " & LookUp(zips,, State)

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

Text(, "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. 



When the zip code is populated in PowerApps it strips the leading 0. Is there a way to get it to show the leading zero? When importing the tables in I made sure the Excel file had the full zip code listed.

Good point. Text(zip,"00000") should do the trick. 

I don't a Text property box. Is it possible to add it to this formula under default?

(LookUp(zips, (State=ComboBox2_2.Selected.Result &&
City=ComboBox1_2.Selected.Result), Zipcode))

(LookUp(zips, (State=ComboBox2_2.Selected.Result &&
City=ComboBox1_2.Selected.Result), Text(Zipcode,"00000")))

Having a bit of a disconnect here.  What are each of the variables and fields being passed.  Do the tables have to be loaded as part of the starup or just a data source.  


I have three tables called Zip1, Zip2, and Zip three with each of these fields City, State, and Zip.  


My PA Text Input fields are called Value23_2 (City), DropDown2 (State), and Value23_$ (Zip)


I have tried this numerous ways and each time I get the "Red Circle of Death".  


Any further assistance is appreciated.  



Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim