Forum Discussion
Using a blank query POST API connection that generates a zip file with a CSV file into Power Query
Hey Artemus I'm going to need a little more direction just because I'm not that farmilar with creating functions or establishing POST request via power query. I did get a response from USAspending help desk giving me the correct Raw JSON to bulk download the data I need. Here's is the API they recommend I use LINK. I then tried to use the information they gave me to write a POST request to download the csv data into power query using a blank query. Here is the query:
let
url = "https://api.usaspending.gov/api/v2/bulk_download/awards/",
body = "{
""filters"":{""prime_award_types"":[""A"",""B"",""C"",""D"",""IDV_A"",""IDV_B"",""IDV_B_A"",""IDV_B_B"",""IDV_B_C"",""IDV_C"",""IDV_D"",""IDV_E"",""02"",""03"",""04"",""05"",""10"",""06"",""07"",""08"",""09"",""11""],
""sub_award_types"":[""procurement"",""grant""],
""sub_agency"":""Federal Emergency Management Agency"",
""date_type"":""action_date"",
""date_range"":{""start_date"":""2020-10-01"",
""end_date"":""2021-09-30""},
""agencies"":[{""type"":""awarding"",""tier"":""subtier"",""name"":""Federal Emergency Management Agency"",""toptier_name"":""Department of Homeland Security""}]},
""file_format"":""csv""}",
Source = Json.Document(Web.Contents(url,[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]))
in
Source
Unfortuanetly this doesn't return any csv data. Also, I really have never done a custom function before.
The issue may be that you aren't speficying a TextEncoding when you call Text.ToBinary (e.g. TextEncoding.Utf8). Also, I would recommend using Json.FromValue([filters = [prime_award_types = {"A", "B", ...}, ...], ....], TextEncoding.Utf8).
To do a custom function, simply create a new blank query and paste in the code, and rename the Query to the name of the function you want to use. Custom functions should start with "(param1) =>" or "each" which is shorthad for "(_) =>". If done correctly when you view the function you should see a list of parameters to fill in along with an Invoke button.