Calling USASpending.Gov From #PowerQuery

This week I was asked several times about connecting to the USASpending.gov API. It can be done with a little help with testing from Postman.

Once you figure out which endpoint you want to query, you need to figure out:

  1. Whether it is a GET or POST request
  2. If you need to pass parameters (some GET requests you don’t need to)
  3. The format of the request body if it is a post request

Below is a video where I walk through pulling info from 2 endpoints (1 GET, 1 POST). Here is my M code for the POST Request:

let
    Url = "https://api.usaspending.gov/api/v2/search/spending_by_geography/",
    Body = "{ ""filters"": {
                    ""agencies"": [
                        {
                            ""type"":""awarding"",
                            ""tier"": ""subtier"", 
                            ""name"":""Department of Veterans Affairs""
                        }
                    ],
                    ""time_period"": [
                            {
                            ""start_date"": ""2022-10-01"",
                            ""end_date"": ""2023-09-30""
                            }
                        ]
                },
                ""scope"": ""place_of_performance"",
                ""geo_layer"": ""district""
                }",
    Response= Web.Contents(Url,
    [
        Content=Text.ToBinary(Body),
        Headers=[#"Content-Type" = "application/json"]
    ]
    ),

    Json = Json.Document(Response),
    #"Converted to Table" = Record.ToTable(Json),
    Value = #"Converted to Table"{2}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"shape_code", "display_name", "aggregated_amount", "population", "per_capita"}, {"shape_code", "display_name", "aggregated_amount", "population", "per_capita"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"per_capita", type number}, {"population", Int64.Type}, {"aggregated_amount", Currency.Type}, {"display_name", type text}, {"shape_code", type text}})

in

 

  #"Changed Type"

The main thing to always remember for POST requests is that in the JSON you need to add a double quote to every single one that is in the JSON ( ” becomes “”). I explain this better in the video.

Here is the video:

Bitnami