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 posi...
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.

     

     

     

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