cancel
Showing results for 
Search instead for 
Did you mean: 
Drrickryp

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

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.  

 

NOOB

Hi @Drrickryp , thanks for this very interesting tip. I am currently applying to my data source. I have downloaded the excel file and is over 80K records. I would like to split those by state. If you don't mind, could you please explain how to split the entire table into separates queries using the powerquery editor? One table per state.

 

Thanks in advance

 

sajarac_0-1617712308692.png

 

Hi. Never mind.

 

Sorry to bother you.

 

I got it now.

 

Thanks

Hi @Drrickryp , Sorry to bother you  AGAIN!. I did this but for some reason I can't figure out now. I am stuck in the power query part. I need to split my excel data table which is over 500K items into sub tables of 15000 as per your instruction. But it seems a little hard to find documentation on how to do this.

 

Could you please point me in the right direction?

Thanks in advance

@sajarac 

Sure.  In Excel, choose Data on the ribbon and position the cursor inside a table. Then, in Power Query  use the keep rows and remove rows to create subtables. _2.png_1.png

@sajarac 
_2.png_1.png

Make sure the data is formatted as a table in Excel.  Then select From data/range to open Power Query.  Use the Keep rows to select the records and save them with Save and load into individual tables to be reassembled in PowerApps.  

Hi @Drrickryp , thank you very much for your prompt response. This is what I am doing and this is the output. I don't know what I have missing.

sajarac_0-1623956524456.png

 

sajarac_1-1623956559135.png

 

sajarac_2-1623956599044.png

 

sajarac_3-1623956657551.pngsajarac_4-1623956684185.png

 

sajarac_5-1623956713346.png

 

As you can see only one table with 14999 rows

 

@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 

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

@sajarac 

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

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 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!

@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.

 

@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.

@Aeon 

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

@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".