Use Power Query to Feed a Web Form

D

drew

New member
Joined
Oct 4, 2016
Messages
6
Reaction score
0
Points
0
The problem I am trying to solve is that i have a list of about 500 zip codes, looks like this;

CodeCityState
01003AmherstMA
01810AndoverMA
02205
02241
02284
02860

Problem is I need to fill in the other blanks of this gigunda list.

I went out to the web and after three virus alerts, went to USPS , but could only find a onesie twosies interface for a code at a time. So i put it a zip code and opened the page source, not that i know what to do with it, but i did happen to find three tags that seemed to reuse an input i just made on the form. They look like this..the Red Text is the argument, 01810, i fed the USPS tool

<a href="https://tools.usps.com/go/ZipLookupAction!input?state=Select&address2=&zip=&address1=&isEdit=true&city=&mode=2&refresh=false&urbanCode=&tZip=01810&companyName=" class="multi-link">English</a>
</div>
<div class="multi-option odd">
<a href="https://es-tools.usps.com/go/ZipLookupAction!input?state=Select&address2=&zip=&address1=&isEdit=true&city=&mode=2&refresh=false&urbanCode=&tZip=01810&companyName=" class="multi-link">Español</a>
</div>
<div class="multi-option last">
<a href="https://zh-tools.usps.com/go/ZipLookupAction!input?state=Select&address2=&zip=&address1=&isEdit=true&city=&mode=2&refresh=false&urbanCode=&tZip=01810&companyName=" class="multi-link chinese"></a>
</div>


So my question is how can i use power query to feed the list of zip codes for which i do not have city and state, into the USPS finder tool?
I have a sense this should work somehow. I don't need a lot of precision in the result.

Thanks very much
 
Hi drew,

It would be easier to just fill all the codes, instead of just the missing ones.

Assuming you have a table like this in your workbook (I've called it "Codes" for this example:

Code
01003
01810
02205
02241
02284
02860


Create a blank Query called "GetData" and paste this code:

Code: 
(PC as text) =>
let
    Source = Web.Page(Web.Contents("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=2&companyName=&address1=&address2=&city=&state=Select&urbanCode=&postalCode=" & PC & "&zip=")),
    //Source = Web.Page(Web.Contents("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=2&companyName=&address1=&address2=&city=&state=Select&urbanCode=&postalCode=01003&zip=")),
    Data = Source{0}[Data],
    Children = Data{0}[Children],
    Children1 = Children{1}[Children],
    Children2 = Children1{0}[Children],
    Children3 = Children2{4}[Children],
    Children4 = Children3{14}[Children],
    Children5 = Children4{0}[Children],
    Children6 = Children5{0}[Children],
    Children7 = Children6{0}[Children],
    Children8 = Children7{1}[Children],
    Children9 = Children8{1}[Children],
    Children10 = Children9{1}[Children],
    Children11 = if Children10{0}[Children] = null then Children10{1}[Children] else Children10{0}[Children],
    Children12 = if Children10{0}[Children] = null then Children11{0}[Children] else if Table.RowCount(Children11) > 3 then Children11{5}[Children] else Children11{2}[Children],
    #"Removed Other Columns" = Table.SelectColumns(Children12,{"Text"}),
    HandleInvalid1 = if Children10{0}[Children] = null then Table.FirstN(#"Removed Other Columns",1) else  #"Removed Other Columns",
    HandleInvalid2 = if Children10{0}[Children] = null then Table.ReplaceValue(HandleInvalid1,"Sorry,","Invlaid Invalid",Replacer.ReplaceText,{"Text"}) else  HandleInvalid1,
    #"Split Column by Delimiter" = Table.SplitColumn(HandleInvalid2,"Text",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Text.1", "Text.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Text.1", "City"}, {"Text.2", "State"}})
in
    #"Renamed Columns"

Then in a second Query use this:


let
Source = Excel.CurrentWorkbook(){[Name="Codes"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "WebData", each GetData(
Code: 
)),
    #"Expanded WebData" = Table.ExpandTableColumn(#"Added Custom", "WebData", {"City", "State"}, {"City", "State"})
in
    #"Expanded WebData"[/QUOTE]
Click to expand...
 
You should note however that this won't be 100% reliable and can easily break if they change the website ever so slightly.
 
thanks so much!

Than you so much Comfy.
It *almost* worked for me, not sure what i am doing wrong...i get a few rows, then this

An error occurred in the ‘’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
Table
When i open the error row, it reveals
An error occurred in the ‘’ query. Expression.Error: We cannot convert the value null to type List.
Details:
Value=
Type=Type

But the input list looks solid, one column wide and 500 rows in a table named Codes...so i dont know how to fix it or what it wants... i also got a privacy nag, but since i was working with city state and zip, i threw caution to the winds and went public, but probably has nothing to do with the error.
thanks again for your help, i really appreciate it.


Comfy said:
Hi drew,

It would be easier to just fill all the codes, instead of just the missing ones.

Assuming you have a table like this in your workbook (I've called it "Codes" for this example:

Code
01003
01810
02205
02241
02284
02860


Create a blank Query called "GetData" and paste this code:

Code: 
(PC as text) =>
let
    Source = Web.Page(Web.Contents("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=2&companyName=&address1=&address2=&city=&state=Select&urbanCode=&postalCode=" & PC & "&zip=")),
    //Source = Web.Page(Web.Contents("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=2&companyName=&address1=&address2=&city=&state=Select&urbanCode=&postalCode=01003&zip=")),
    Data = Source{0}[Data],
    Children = Data{0}[Children],
    Children1 = Children{1}[Children],
    Children2 = Children1{0}[Children],
    Children3 = Children2{4}[Children],
    Children4 = Children3{14}[Children],
    Children5 = Children4{0}[Children],
    Children6 = Children5{0}[Children],
    Children7 = Children6{0}[Children],
    Children8 = Children7{1}[Children],
    Children9 = Children8{1}[Children],
    Children10 = Children9{1}[Children],
    Children11 = if Children10{0}[Children] = null then Children10{1}[Children] else Children10{0}[Children],
    Children12 = if Children10{0}[Children] = null then Children11{0}[Children] else if Table.RowCount(Children11) > 3 then Children11{5}[Children] else Children11{2}[Children],
    #"Removed Other Columns" = Table.SelectColumns(Children12,{"Text"}),
    HandleInvalid1 = if Children10{0}[Children] = null then Table.FirstN(#"Removed Other Columns",1) else  #"Removed Other Columns",
    HandleInvalid2 = if Children10{0}[Children] = null then Table.ReplaceValue(HandleInvalid1,"Sorry,","Invlaid Invalid",Replacer.ReplaceText,{"Text"}) else  HandleInvalid1,
    #"Split Column by Delimiter" = Table.SplitColumn(HandleInvalid2,"Text",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Text.1", "Text.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Text.1", "City"}, {"Text.2", "State"}})
in
    #"Renamed Columns"

Then in a second Query use this:
Click to expand...
 
From what I understand the Privacy request is to do with Query folding. It's not about the data you are accessing it's about the data that you have in your source.

If you state your data is suitable for public consumption Power Query will send the data along with the Query to a database for the database to do the heavy lifting. If you state private etc Power Query will execute the query and will not send any data to the database.

With regards to the error you are getting. I think this is related to how the website was built and I don't know a way around it, it's what I was refering to in my previous post :(

You will find that sometimes they work and sometimes they don't. Because the website uses javascript Power Query doesn't know when the website has finished loading so I guess, it guesses!

These errors occur when PQ tries to run the M against a website that hasn't completely loaded.


This is a looooooonng shot but you could investigate to see if they have an api?
 
thanks so much for your help

I will use the scaffold you provided me and see what i can learn about it...it occurred to me that the limit may be built in to the interface to thwart someone like me trying to use it in batch when it was clearly intended for instance.

How did you determine which trail of children to follow? I opened the source of the page, and it was just dumb luck that i happened to see the string i entered in it so i knew where the parameter had to go, but your first query steps through all that very handily...did you do it by inspection or if not, how did you know which path to follow and how deep to follow it?

thanks again.

Comfy said:
From what I understand the Privacy request is to do with Query folding. It's not about the data you are accessing it's about the data that you have in your source.

If you state your data is suitable for public consumption Power Query will send the data along with the Query to a database for the database to do the heavy lifting. If you state private etc Power Query will execute the query and will not send any data to the database.

With regards to the error you are getting. I think this is related to how the website was built and I don't know a way around it, it's what I was refering to in my previous post :(

You will find that sometimes they work and sometimes they don't. Because the website uses javascript Power Query doesn't know when the website has finished loading so I guess, it guesses!

These errors occur when PQ tries to run the M against a website that hasn't completely loaded.


This is a looooooonng shot but you could investigate to see if they have an api?
Click to expand...
 
If there are no tables in the web source then the navigation is quite painful.

I used PQ on one screen and Chrome on another.

In Chrome's Developer tools if you hover over the html Chrome will highlight the section it refers to.

Just find the right one and keep clicking, then select the relevant section in PQ.
 
thanks again

Thanks Comfy
whole new world for me


Comfy said:
If there are no tables in the web source then the navigation is quite painful.

I used PQ on one screen and Chrome on another.

In Chrome's Developer tools if you hover over the html Chrome will highlight the section it refers to.

Just find the right one and keep clicking, then select the relevant section in PQ.
Click to expand...
 
That looks great, thanks again Comfy, you've gone above and beyond!
 
You must log in or register to reply here.
Back
Top