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

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