Forum Discussion

daforlife123's avatar
daforlife123
Frequent Visitor
4 years ago

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

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. 

Need Help
Tips and Tricks

5 Replies

  • daforlife123's avatar
    daforlife123
    Frequent Visitor
    4 years ago

    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"

  • daforlife123's avatar
    daforlife123
    Frequent Visitor
    3 years ago

    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's avatar
      artemus
      Icon for Microsoft Employee rankMicrosoft Employee
      3 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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