Forum Discussion

Sven_Pardijs's avatar
Sven_Pardijs
New Member
4 years ago
Solved

Json file will load only 500 rows

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

Dataflow
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

     

     

2 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    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
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.