Using a blank query POST API connection that generates a zip file with a CSV file into Power Query

[daforlife123]

Hello Everyone, 

I'm new to using GET and POST API connectors in power query but I'm trying figure out if its possible to upload data in power query using an POST API that generates a zip file. Here is the link (USAspending API) to the POST API that generates the zipped file with the CSV.  

Thanks Ahead for any responses,

D.A. 

[Anonymous]

@daforlife123 - Also trying to figure out this problem, let me know if you figure out how to do it. 

[daforlife123]

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. 

[artemus]

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.

[daforlife123]

Hey Artemus,

This is what I was initially putting into the Advance editor for a blank query based on the instructions of the API:

let
url = "https://files.usaspending.gov/generated_downloads/COVID-19_Profile_2020-07-09_H17M39S27272793.zip",
body = " {
""filters"": {
""def_codes"": [""L"", ""M"", ""N"", ""O"", ""P"", ""U""]
}
}",
Source = Json.Document(Web.Contents(url,[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]))
in
Source

With the json query you gave me I see I add the url but I don't know what to put in place for the parameters. Would your option look like this:

let

    Source = Web.Contents("https:/api.usaspending.gov//api/v2/download/disaster/"),

    #"Imported JSON" = Table.FromRecords(Json.Document(Source,65001)[value])

in

    #"Imported JSON"

[artemus]

1. To send a json query over post, you can use something like Web.Contents("https://myurl.com", [Content=Json.FromValue([Parameter1 = "abc", Parameter2 = {1, 3, 5})])

2. See a post I made ealier on how to extract zip files: Solved: Re: How to connect Azure DevOps REST API in to pow... - Microsoft Power BI Community