Solved: Re: Json file will load only 500 rows

Sven_Pardijs · ‎12-20-2021

Hi,

For my data load I use an api connection to load my data. I use the web connection. But when I load the data PowerBI will only load 500 rows. But I know there are many more rows.

Is it posible to make PowerBI loop through the other rows/ pages.

Appreciate the help.

Greetings,

Sven Pardijs

v-yanjiang-msft · ‎12-22-2021

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

Choose Get Data > choose More... > choose "JSON" > choose the JSON file
You should see a "List" of "Records" in Power BI, don't panic
Select the list, choose Convert to Table
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.

View solution in original post

v-yanjiang-msft · ‎12-22-2021

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

Choose Get Data > choose More... > choose "JSON" > choose the JSON file
You should see a "List" of "Records" in Power BI, don't panic
Select the list, choose Convert to Table
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.

bcdobbs · ‎12-20-2021

Have a look at Iterate over dynamic Web API Pages with Power Query - How to resolve Cursor-Based Pagination - DataC...

Based on that and other blogs I wrote a M function that I tend to use. It'll need tweaking for your particular API but you're welcome to the code:

let
  Result = (APIKey as text, RelativeURL as text, MaxPages as number, optional QueryRecord as record) => 
    let
      apiKey = "Bearer " & APIKey,

      //Inner function that retrieves a single page of data, depending on API the ?continuation= and Headers may need adjusting.
      FnGetOnePage = (relativePath, optional queryRecord, optional cursor) as record =>
        let
          tempQuery = if queryRecord = null then [] else queryRecord,
          fullQuery = if cursor = null then tempQuery else Record.AddField(tempQuery, "cursor", cursor),
          Source = Json.Document(
            Web.Contents(
              BaseURL, 
              [
                RelativePath = relativePath,
                Query = fullQuery,
                Headers = [Authorization = apiKey]
              ])
            ),
          hasMoreValue = try Source[has_more] otherwise false,
          cursorValue = try Source[cursor] otherwise null,
          res = [RawJSON = Source, HasMore = hasMoreValue, Cursor=cursorValue, Url = relativePath, Query = fullQuery]
        in
          res,

      //Loop through until there are no more pages.
      GeneratedList = List.Generate(
        ()=>[i=0, res = FnGetOnePage(RelativeURL, QueryRecord)],
        each [i] < MaxPages and [res] <> null,
        each [i= [i]+1, res = if [res][HasMore] then FnGetOnePage(RelativeURL, QueryRecord, Number.ToText([res][Cursor])) else null],
        each [res][RawJSON])
    in
      GeneratedList
in
  Result

Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Json file will load only 500 rows

Helpful resources

Fabric certifications survey

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024