Secret Power Of Web.Contents PQ Function: Options[Query] Parameter – Part 3

This post is the center point of Web.Contents PQ Function. Please learn it well.

In this post, I am going to introduce you to the second parameter of Web.Contents. And That is Options. It is a record parameter with 9 different fields.

If you checked Web.Contents second parameter different fields name,, then you will see different order than below image. I have organized these based on their use. The left ones are being used more of the time than the right ones. So, the Left ones are more important than the right ones.

In this post, I am going to explain Query options. If you read my previous post, I said that there is a better way to call an API without concatenating query parameters.

The query parameter is a key-value option. So, you need to pass a record in the power query for that. Where query name will be the Field name and value will be the Field value.

Here is our first example of this:

let
  URL              = "https://datausa.io/api/data", 
  Source           = Web.Contents(URL, [Query = [drilldowns = "Nation", measures = "Population"]]), 
  ConvertedToTable = fxJsonBinaryToTable(Source, false, "|")
in
  ConvertedToTable

This is the same example that we have seen in our previous post. Here are some benefits to using the Query option than concatenating query parameters in the URL.

1. No need to escape character.
2. Cleaner code.
3. Easy to add or remove query parameters.
4. Easy to change parameter value.
5. Maintainable.

And we get the same result as in our previous post.

And I want you to see another example of this which is slightly different than the previous one. The interesting part of this example is that it has –(hyphen) in its query parameter name and for that, we have to use #” and “ to make it a proper identifier.

Query Param Name Has Space Example.pq Code

let
  Source = Web.Contents(
    "https://us-street.api.smartystreets.com/street-address", 
    [
      Headers = [accept = "application/json"], 
      Query = [
        #"auth-id"    = SMARTY_AUTH_ID, 
        #"auth-token" = SMARTY_AUTH_TOKEN, 
        licence       = SMARTY_LICENSE_NAME, 
        street        = "1660 Avenida Aviare Unit 4", 
        city          = "Chula Vista", 
        state         = "CA", 
        zipcode       = "91913"
      ]
    ]
  ), 
  ConvertedToTable = fxJsonBinaryToTable(Source, true, "|")
in
  ConvertedToTable

Here is the output of this API Call:

We are going to see one more example of query parameters. In this one, we are going to pass multiple values for a parameter using the list.

One Query Param Has Multiple Value Example.pq Code:

let
  Source = Web.Contents(
    "https://quickstats.nass.usda.gov/api/api_GET/?", 
    [
      ApiKeyName = "Key", 
      Query = [
        key         = QUICK_STATS_API_KEY, 
        year        = {"2022", "2023"}, 
        state_alpha = "VA", 
        format      = "JSON"
      ]
    ]
  ), 
  ConvertedToTable = fxJsonBinaryToTable(Source, false, "|")
in
  ConvertedToTable

Look at the year parameter. We are passing a list of years. So this one is another good example.

Sample output of this example

Few takeaways from this post:

1. Options is a record type parameter. So use [] to represent a record.
2. Query is also a record. So use []
3. Use #” and ” to make a proper identifier(Field name here).
4. Query parameter value type needs to be of Text.
5. You can pass Multiple values as a list as well.
6. You can pass a key-value pair using a record too.

Let me know what you think.