web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Agent Academy Hackathon is happening now!
🚀 Season of Sharing Community Challenge Launch!
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Hitting API Limit of 1...
Power Apps
Suggested Answer

Hitting API Limit of 10,000 records from usaspending.gov

(1) ShareShare
ReportReport
Posted on by MJ-25071643-0 6
Hello, 
I am new to power query and have been working in Power BI on an API connection to an end point from usaspending.gov. Here is the github documentation for the end point. The API has a normal limit of 100, but with the (significant) help of ChatGPT I have included a script for pagination to pull more records. However, I am hitting a wall of 10,000 records being pulled in, and ChatGPT doesn't seem to be able to get me past this. Is there a way of getting past this? I don't see anything in the documentation about an overall limit of 10,000. It should be pulling 22,616 records. Thanks for any help you can offer! 
Here is my code block:
let
    // Function to get data from a specific page
    GetPage = (PageNumber as number) =>
    let
        url = "https://api.usaspending.gov/api/v2/search/spending_by_award/",
        body = "{
            ""filters"": {
                ""time_period"": [
                    {""start_date"": ""2023-10-01"", ""end_date"": ""2024-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2022-10-01"", ""end_date"": ""2023-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2021-10-01"", ""end_date"": ""2022-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2020-10-01"", ""end_date"": ""2021-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2019-10-01"", ""end_date"": ""2020-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2018-10-01"", ""end_date"": ""2019-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2017-10-01"", ""end_date"": ""2018-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2016-10-01"", ""end_date"": ""2017-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2015-10-01"", ""end_date"": ""2016-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2014-10-01"", ""end_date"": ""2015-09-30"", ""date_type"": ""new_awards_only""}
                ],
                ""award_type_codes"": [""A"", ""B"", ""C"", ""D""],
                ""agencies"": [
                    {""type"": ""awarding"", ""tier"": ""toptier"", ""name"": ""Agency for International Development""}
                ]
            },
            ""fields"": [
                ""Award ID"", ""generated_internal_id"", 
                ""Awarding Agency"", ""Awarding Agency Code"", ""Awarding Sub Agency"",""Awarding Sub Agency Code"",
                ""Funding Agency"", ""Funding Agency Code"", ""Funding Sub Agency"", ""Funding Sub Agency Code"",
                ""Recipient Name"", ""recipient_id"", ""Prime Award Recipient ID"", ""Recipient DUNS Number"",
                ""Description"",""Place of Performance Country Code"",
                ""Start Date"", ""End Date"", ""Award Amount"", ""Total Outlays"",""Contract Award Type"", ""Base Obligation Date""
            ],
            ""page"": " & Number.ToText(PageNumber) & ",
            ""limit"": 100,
            ""sort"": ""Award Amount"",
            ""order"": ""desc"",
            ""subawards"": false
        }",
        response = Json.Document(Web.Contents(url, [Content=Text.ToBinary(body), Headers=[#"Content-Type"="application/json"]])),
        awards = response[results],
        hasNext = response[page_metadata][hasNext]
    in
        [Data = awards, HasNext = hasNext],

    // Loop to fetch all pages manually
    GetAllPages = () =>
    let
        // Initialize the first page and accumulated data
        InitialResult = GetPage(1),
        InitialData = InitialResult[Data],
        HasNext = InitialResult[HasNext],

        // Define a recursive function to fetch subsequent pages
        FetchData = (PageNumber, AccumulatedData) =>
        let
            Result = GetPage(PageNumber),
            NewData = List.Combine({AccumulatedData, Result[Data]}),
            NewHasNext = Result[HasNext]
        in
            if NewHasNext then @FetchData(PageNumber + 1, NewData) else NewData,

        // Start fetching data from the first page
        AllData = FetchData(2, InitialData)
    in
        AllData,

    // Fetch all data
    CombinedData = GetAllPages(),
    TableData = Table.FromList(CombinedData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Expand the records into columns
    #"Expanded Column1" = Table.ExpandRecordColumn(TableData, "Column1", 
        {"Award ID", "generated_internal_id", "Awarding Agency", "Awarding Agency Code", "Awarding Sub Agency", 
        "Awarding Sub Agency Code", "Funding Agency", "Funding Agency Code", "Funding Sub Agency", 
        "Funding Sub Agency Code", "Recipient Name", "recipient_id", "Prime Award Recipient ID", 
        "Recipient DUNS Number", "Description", "Place of Performance Country Code", "Start Date", 
        "End Date", "Award Amount", "Total Outlays", "Contract Award Type", "Base Obligation Date"}, 
        {"Award ID", "generated_internal_id", "Awarding Agency", "Awarding Agency Code", "Awarding Sub Agency", 
        "Awarding Sub Agency Code", "Funding Agency", "Funding Agency Code", "Funding Sub Agency", 
        "Funding Sub Agency Code", "Recipient Name", "recipient_id", "Prime Award Recipient ID", 
        "Recipient DUNS Number", "Description", "Place of Performance Country Code", "Start Date", 
        "End Date", "Award Amount", "Total Outlays", "Contract Award Type", "Base Obligation Date"}),

    // Change column types
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{
        {"Start Date", type date}, 
        {"End Date", type date}, 
        {"Award Amount", type number}, 
        {"Total Outlays", type number}, 
        {"Base Obligation Date", type date}})
in
    #"Changed Type"
 
 
 
Categories:
Power Query
I have the same question (0)
  • Michael E. Gernaey Profile Picture
    Michael E. Gernaey 53,963 Moderator on at
    Soon as you said ChatGPT you lost me... I will not debug ChatGPT Code but I do hope one of the others will.
  • lbendlin Profile Picture
    lbendlin 8,716 Super User 2026 Season 1 on at
    Your scenario can be covered much simpler via List.Generate. There is extensive documentation on this.
     

    let
    // Function to get data from a specific page
    GetPage = (PageNumber as number) =>
    let
        url = "https://api.usaspending.gov/api/v2/search/spending_by_award/",
        body = "{
            ""filters"": {
                ""time_period"": [
                    {""start_date"": ""2023-10-01"", ""end_date"": ""2024-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2022-10-01"", ""end_date"": ""2023-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2021-10-01"", ""end_date"": ""2022-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2020-10-01"", ""end_date"": ""2021-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2019-10-01"", ""end_date"": ""2020-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2018-10-01"", ""end_date"": ""2019-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2017-10-01"", ""end_date"": ""2018-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2016-10-01"", ""end_date"": ""2017-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2015-10-01"", ""end_date"": ""2016-09-30"", ""date_type"": ""new_awards_only""},
                    {""start_date"": ""2014-10-01"", ""end_date"": ""2015-09-30"", ""date_type"": ""new_awards_only""}
                ],
                ""award_type_codes"": [""A"", ""B"", ""C"", ""D""],
                ""agencies"": [
                    {""type"": ""awarding"", ""tier"": ""toptier"", ""name"": ""Agency for International Development""}
                ]
            },
            ""fields"": [
                ""Award Amount"",""Award ID"", ""generated_internal_id"", 
                ""Awarding Agency"", ""Awarding Agency Code"", ""Awarding Sub Agency"",""Awarding Sub Agency Code"",
                ""Funding Agency"", ""Funding Agency Code"", ""Funding Sub Agency"", ""Funding Sub Agency Code"",
                ""Recipient Name"", ""recipient_id"", ""Prime Award Recipient ID"", ""Recipient DUNS Number"",
                ""Description"",""Place of Performance Country Code"",
                ""Start Date"", ""End Date"",  ""Total Outlays"",""Contract Award Type"", ""Base Obligation Date""
            ],
            ""page"": " & Number.ToText(PageNumber) & ",
            ""limit"": 100,
            ""sort"": ""Award Amount"",
            ""order"": ""desc"",
            ""subawards"": false
        }",
        response = Json.Document(Web.Contents(url, [Content=Text.ToBinary(body), Headers=[#"Content-Type"="application/json"]]))
    in
        [Data = response[results], HasNext = response[page_metadata][hasNext]],

    // Loop to fetch all pages 
    Results = List.Generate(()=>[n=1,r=GetPage(1)],each [r][HasNext] and [n]<5,each [n=[n]+1,r=GetPage([n]+1)]),
    #"Converted to Table" = Table.FromList(Results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Data" = Table.ExpandListColumn(Table.ExpandRecordColumn(Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"n", "r"}), "r", {"Data"}), "Data"),
    #"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"internal_id", "Award ID", "generated_internal_id", "Awarding Agency", "Awarding Agency Code", "Awarding Sub Agency", "Awarding Sub Agency Code", "Funding Agency", "Funding Agency Code", "Funding Sub Agency", "Funding Sub Agency Code", "Recipient Name", "recipient_id", "Prime Award Recipient ID", "Recipient DUNS Number", "Description", "Place of Performance Country Code", "Start Date", "End Date", "Award Amount", "Total Outlays", "Contract Award Type", "Base Obligation Date", "awarding_agency_id", "agency_slug"}, {"internal_id", "Award ID", "generated_internal_id", "Awarding Agency", "Awarding Agency Code", "Awarding Sub Agency", "Awarding Sub Agency Code", "Funding Agency", "Funding Agency Code", "Funding Sub Agency", "Funding Sub Agency Code", "Recipient Name", "recipient_id", "Prime Award Recipient ID", "Recipient DUNS Number", "Description", "Place of Performance Country Code", "Start Date", "End Date", "Award Amount", "Total Outlays", "Contract Award Type", "Base Obligation Date", "awarding_agency_id", "agency_slug"})
in
    #"Expanded Data1"
    Having said that - the API indeed caps at 100 results, and is extremely slow.  You may need to cut your calls into yearly pieces. I haven't figured out yet how to avoid the sorting, that seems to be mandatory, and slows everything down a lot.

  • Suggested answer
    lbendlin Profile Picture
    lbendlin 8,716 Super User 2026 Season 1 on at
    I got a "working"  version (read: still slow)  that at least fetches all the data. The key seems to be to cut the request into pieces that are each smaller than the 10000 item limit, I used the years for that but you can find other ways too.
     
     
    let
    // Function to get data from a specific page
    GetPage = (PageNumber as number,Year as text) =>
    let
        url = "https://api.usaspending.gov/api/v2/search/spending_by_award/",
        body = "{
            ""filters"": {
                ""time_period"": [
                    {""start_date"": """ & Year & "-01-01"", ""end_date"": """ & Year & "-12-31"", ""date_type"": ""new_awards_only""}
                ],
                ""award_type_codes"": [""A"", ""B"", ""C"", ""D""],
                ""agencies"": [
                    {""type"": ""awarding"", ""tier"": ""toptier"", ""name"": ""Agency for International Development""}
                ]
            },
            ""fields"": [
                ""Award Amount"",""Award ID"", ""generated_internal_id"", 
                ""Awarding Agency"", ""Awarding Agency Code"", ""Awarding Sub Agency"",""Awarding Sub Agency Code"",
                ""Funding Agency"", ""Funding Agency Code"", ""Funding Sub Agency"", ""Funding Sub Agency Code"",
                ""Recipient Name"", ""recipient_id"", ""Prime Award Recipient ID"", ""Recipient DUNS Number"",
                ""Description"",""Place of Performance Country Code"",
                ""Start Date"", ""End Date"",  ""Total Outlays"",""Contract Award Type"", ""Base Obligation Date""
            ],
            ""page"": " & Number.ToText(PageNumber) & ",
            ""limit"": 100,
            ""subawards"": false
        }",
        response = Json.Document(Web.Contents(url, [Content=Text.ToBinary(body), Headers=[#"Content-Type"="application/json"]]))
    in
        [Data = response[results], HasNext = response[page_metadata][hasNext]],
    // Year range
    Years = {2014..2024},
    #"Converted to Table1" = Table.FromList(Years, Splitter.SplitByNothing(), {"Year"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table1",{{"Year", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> List.Generate(()=>[n=1,p=0, r=GetPage(1,k[Year])],each [p]=0,each [p=[p]+1-Int64.From([r][HasNext]),n=[n]+1,r=GetPage([n]+1,k[Year])])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"n", "p", "r"}, {"n", "p", "r"}),
    #"Expanded r" = Table.ExpandRecordColumn(#"Expanded Custom1", "r", {"Data", "HasNext"}, {"Data", "HasNext"}),
    #"Expanded Data" = Table.ExpandListColumn(#"Expanded r", "Data"),
    #"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"internal_id", "Award Amount", "Award ID", "generated_internal_id", "Awarding Agency", "Awarding Agency Code", "Awarding Sub Agency", "Awarding Sub Agency Code", "Funding Agency", "Funding Agency Code", "Funding Sub Agency", "Funding Sub Agency Code", "Recipient Name", "recipient_id", "Prime Award Recipient ID", "Recipient DUNS Number", "Description", "Place of Performance Country Code", "Start Date", "End Date", "Total Outlays", "Contract Award Type", "Base Obligation Date", "awarding_agency_id", "agency_slug"}, {"internal_id", "Award Amount", "Award ID", "generated_internal_id", "Awarding Agency", "Awarding Agency Code", "Awarding Sub Agency", "Awarding Sub Agency Code", "Funding Agency", "Funding Agency Code", "Funding Sub Agency", "Funding Sub Agency Code", "Recipient Name", "recipient_id", "Prime Award Recipient ID", "Recipient DUNS Number", "Description", "Place of Performance Country Code", "Start Date", "End Date", "Total Outlays", "Contract Award Type", "Base Obligation Date", "awarding_agency_id", "agency_slug"})
in
    #"Expanded Data1"
     
  • MJ-25071643-0 Profile Picture
    MJ-25071643-0 6 on at
    Thank you so much! List.generate does definitely simplify that part of the code. I will read up on that piece. 
    I will test you latest code very soon and get back to you.
     
    Could you explain to me what you did in this code or how it's working? I know you said you are splitting the request into years so that you get a chuck smaller than 10,000 records. It looks like you are creating a list of years and then using that in the time period filter. So, if I wanted to modify the years, I would update the "Years - {2014..2024}," piece of the code, correct? 
  • lbendlin Profile Picture
    lbendlin 8,716 Super User 2026 Season 1 on at
    yes, you can modify that year list as needed.
     
    The code filters by year, and then fetches the responses in chunks of 100 rows until the flag changes.  By returning each chunk separately you avoid having to lug the resultset around in memory.   This is one of many ways to tackle your issue -   You can use Query Diagnostics to determine which version of the code is most efficient (given that the API is excruciatingly slow).  Might be an idea to keep the historical data static, and only refresh the current year?
  • MJ-25071643-0 Profile Picture
    MJ-25071643-0 6 on at
    Thank you so much for your help. This has solved my problem! 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Agent Academy Hackathon is happening now!
🚀 Season of Sharing Community Challenge Launch!
Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the April Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Vish WR Profile Picture

Vish WR 428

#2
Haque Profile Picture

Haque 338

#3
timl Profile Picture

timl 333 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans