Part 2 – Power Query API – Parameterized Data Indeed!

This the second part in the Indeed! Series and we will cover how to parameterize a query in Power Query. In part 1 we setup our Indeed API calls to gather some basic data with Power Query. The file from Part 1 can be downloaded here. This probably didn’t help a whole lot of people out unfortunately; unless you are a software engineer in Austin, TX. Hopefully you have had a chance to go over to Faisal Mohamood’s blog and read up on parameterizing a Yelp query or for a crazy parameterization in Power Query you can check out Devin Knight’s blog post.

The first question I ask myself in this situation is, what is possible to parameterize in our query? Our original example query looks like this
http://api.indeed.com/ads/apisearch?publisher=xxxxxxxxxxxxxxxx&q=java&l=austin
%2C+tx&sort=&radius=&st=&jt=&start=&limit=&fromage=&filter=&latlong
=1&co=us&chnl=&userip=1.2.3.4&useragent=Mozilla/%2F4.0%28Firefox%29&v=2

This is the example on the Indeed Developer XML feeds section. In this section there is also an advanced search feature you can check out to get some possibilities on searching:

The advanced search features introduces a world of possibilities:

If you click on the “Find Jobs” button and pull out the URL it looks something like this:

http://www.indeed.com/jobs?as_and=&as_phr=&as_any=DBA%2C+SSAS%2C+DAX%2C+
Power+Query&as_not=&as_ttl=&as_cmp=&jt=all&st=&salary=150000
&radius=25&l=90731&fromage=any&limit=50&sort=date&psf=advsrch

Now I think we can really start to piece together what’s possible and what we want. Search criteria for a job is probably high on the list; this appears to map to the “&q=” portion of the string in the original URL. “&salary=” might also be nice; if you’re looking for a variety of different salary ranges for different types of jobs or even for the same job. “&l=” maps to the location…

All the parameters I want for the data are below (feel free to make your own list though; other candidates might be sort, radius etc.).

  1. &q= – Search Criteria
  2. &salary= – Salary for the search criteria
  3. &l= -Zip Code I’m searching for
  4. Publisher= – The publisher id you received from the indeed site.

To do what we intend we need to open up the Advanced Query Editor functionality in Power Query. So head on over to the “Power Query” tab and go to “Options” then select “Enable Advanced Query Editing” and click “Ok”.

Next click on “Filter and Shape” in our “Query Results – Power Query” tab; which will bring us back to the Power Query editor. A new and magical option now appears in the upper right had corner. The little script icon just to the left of steps will open up the advanced query editor.

Before going into the advanced query editor; change the name of the query to “GetJobs” by double clicking the Query1 in the upper left hand corner.

Now open up the advanced query editor by clicking the little scroll icon and add the following to the top line of the code before let:

(GetJobs)=>

Click done and you will be presented with the following:

Congrats; you just made your first function in the M query language which is the language behind Power Query! Click on invoke and type just about anything; then click “Ok”.

Your query should run successfully with the original parameters you passed in from Part 1 of this series; we made a function but it’s not doing much. So let’s dive back in to the advanced query editor…

Something is different here….Behind the scenes when you invoked the function Power Query added some necessary code. This works out for us because we want some parameters to work with and not just a useless function; dive back in and add the following so your first four lines appear like this:

let

GetJobs = (zipcode as text,salary as text,searchcriteria as text,publisher as text)=>

let

Source = Xml.Tables(Web.Contents(“http://api.indeed.com/ads/apisearch?publisher=
“&publisher&”&q=”&searchcriteria&”&l=”&zipcode&”&salary=”&salary&”&sort=date&radius=
&st=&jt=&start=&limit=&fromage=&filter=&latlong=1&co=us&chnl=&userip=1.2.3.4&useragent=
Mozilla/%2F4.0%28Firefox%29&v=2”)),

The bottom four lines appear as:

in

RenamedColumns

in

GetJobs

So what is happening is a nested let / in combination. If you hand code this and forget the final “in” statement you will have some quirky results; so be sure to understand your nesting the let / in combination of the function and the API call.

Click on done and you will immediately get an error, which is expected. Power query is expecting one variable in the function and you now have four. If you just plugged the code in directly without first adding “(GetJobs)=>” you would not receive this error.

Head over to the steps; there should only be two; delete the step “InvokedGetJobs”.

Which brings us to the invoke page again; here we can invoke the query with our four parameters and they will be passed to the Indeed API.

Test out your query by invoking it and putting some criteria in there.

That’s it; you have created a Parameterized Query in Power Query. While it’s nice that we can change the criteria we are searching on; I for one do not want to enter this manually every time I search for a job. In part three of the series we will add an additional sheet to the excel workbook and use a table to automatically parameterize and call our function and API call.

The files we have been working on can be downloaded here.

Continue on to Part 3…

3 thoughts on “Part 2 – Power Query API – Parameterized Data Indeed!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s