Does Power Query Table.FromLIst have a 25 or 100 record limit for Drill Down? Where?

[AnnaKSmith]

Hi,

Using Web Query, I am able to ingest a JSON object with 898 records. However, when I right-click on List, I only get 25 records/rows. Where is this 25 limit coming from? I don't see anything in the docs for Table.FromList (https://docs.microsoft.com/en-us/powerquery-m/Table-FromList). In a different query from the same sorce, Power Query can see 148 records, but only 100 come through... and of course, these limitations are passed to the visualizations.

Here is a portion of my code. It seems the problem appears between the 1st and 2nd line.

data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Incident"}}),
#"Expanded Incident" = Table.ExpandRecordColumn(#"Renamed Columns", "Incident", {"id", "version", "modified", "sortValues", "roles", "previousRoles", "hasRole", "dbotCreatedBy", "ShardID", "account", "autime", "type", "rawType", "name"})

[camargos88 · Accepted solution]

Hi @AnnaKSmith ,

Have you checked if this API has any parameter for the quantity of itens ? 

[AnnaKSmith]

Feeling that all was lost in Power BI, I switched to Jupyter Notebooks/Python. But there I saw the same record limitation. So I began to believe that I was in the same situation like so many others: my API only allows the retrieval of so many records at the time. I checked the documentation, and yes, THERE IS a page parameter. Back in Power BI, hard coding in different pages gave me different results. So now I am trying to write a query in M that will loop through all the possible pages. Thanks for your responses.

[camargos88]

Hi @AnnaKSmith ,

It may help you:

https://docs.microsoft.com/en-us/power-query/helperfunctions#tablegeneratebypage

[AnnaKSmith]

Thanks for the tip. In the end, the pagination took LOTS of effort: I had to List.Generate a list, then manipulate the list with Table commands, then convert the Table back into a list, then concatenate using "&" (ha! Not Text.Combine) a prefix string, my list item, and a suffix string. Finally, I figured out how to send the longer string as a slice to my API function. Not elegant code here! 

```

total = Total records learned from prior code,

Increment = 25,

GeneratedList = List.Generate (
() => [i=0], // initialize loop variables
each [i] < Number.RoundDown(total/Increment) +2,
each [i=[i]+1]
),
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "J"}}),
#"Expanded J" = Table.ExpandRecordColumn(#"Renamed Columns", "J", {"i"}, {"J.i"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded J",{{"J.i", "K"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"K", type text}}),
AllFixed = Table.ToList(#"Changed Type", Combiner.CombineTextByDelimiter(",")),
AllFixedNicely = List.Transform(AllFixed, each "{
""filter"":{
""fromDate"":""2020-08-01T00:00:00Z"",
""toDate"":""2020-12-31T00:00:00Z"",
""page"":"&(_)&"}}"),

GetPage = (pageof) =>
let
Source = Json.Document(Web.Contents("https://<URL>/search",
[ Headers=
[#"Content-Type" = "application/json",
#"accept" = "application/json",
#"Authorization" = "<TOKEN>"],
Content=Text.ToBinary(pageof)
]
))
in Source,

AllPages = List.Transform(AllFixedNicely, each GetPage(_)),
#"Converted to Table1" = Table.FromList(AllPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

```

[lbendlin]

How do you add these M extensions to Power BI?

[AnnaKSmith]

Clicking the Transform Data leads to the Query Editor. In the Query Editor home, I open the Advanced Editor, and I develop my M code there. To get started learning M, I used the buttons in the Query Editor, and then opened the Advanced Editor to see the code interpretation.

[lbendlin]

"total" is just a number, a field in your return JSON. Doesn't meant that the list has to have "total" number of records.  Check your JSON for that, also check for any special characters that might throw Power Query off. Adjust encoding as needed.