Json file will load only 500 rows

Hi Sven_Pardijs ,

By my reserch, you can try these methods for your problem.

1.Made a function to access the API:

let API = (relPath as text, optional queries as nullable record) =>
    let
        Source = Json.Document(Web.Contents("https://api.com/", 
            [ Query = queries, 
              RelativePath=relPath]))
    in
        Source
in 
    API

Then retrieve the records in another query using List.Generate to perform multiple calls to the API as required.

let
    Source = API("path/to/records", [rows_per_page="1000"]),
    pages = Source[total_pages],
    records = 
        if pages = 1 then Source[records] 
        else List.Combine(List.Generate(
            () => [page = 1, records = Source[records]], 
            each [page] <= pages, 
            (x) => [page = x[page] + 1, records = API("path/to/records", [page = Text.From(x[page] + 1), rows_per_page = "1000"])[records]], 
            each [records]))
in
    records

Reference:api - JSON Query in Power BI only returning first 1000 rows, how to return all rows - Stack Overflow

2.Add the following code in the capabilities.json file:

"dataViewMappings": [
    {
        "table": {
            "rows": {
                "for": {
                    "in": "values"
                },
                "dataReductionAlgorithm": {
                    "window": {
                        "count": 100
                    }
                }
            }
    }
]

Reference:Fetch more data from Power BI - Power BI | Microsoft Docs

3.Power BI has a default JSON document connector you can use that to import JSON data.

To use JSON from a file in Power BI

1. Choose Get Data > choose More... > choose "JSON" > choose the JSON file
2. You should see a "List" of "Records" in Power BI, don't panic
3. Select the list, choose Convert to Table
4. On the individual column headers, look for a splitter icon.  Choose split to expand fields.

Reference: Load all records and lists from json file - Microsoft Power BI Community

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.