Excel Power Query only returning 100 Rows

I am using Excel Power Query to connect to a table in my database. For some reason it will only return the first 100 records. Here is the query I am using, does anyone have any experience with this or suggestions?

apiUrl = “https://api.tadabase.io/api/v1/data-tables/----------/records?limit=1000”,
options = [Headers =[
#“X-Tadabase-App-Key”= “------------”,
#“X-Tadabase-App-Secret”= “----------------------------------------------”,
Records = Web.Contents(apiUrl , options),
#“Imported JSON” = Json.Document(Records,1252),
items = #“Imported JSON”[items],
#“Converted to Table” = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Expanded Column1” = Table.ExpandRecordColumn(#“Converted to Table”, “Column1”, {“id”, “field_37”, “field_38”, “field_39”, “field_40”, “field_42”, “field_45”, “field_46”, “field_47”, “field_48”, “field_50”, “field_51”, “field_53”, “field_54”, “field_55”, “field_56”, “field_177”, “field_178”, “field_184”, “field_185”, “field_186”, “field_187”, “field_200”, “field_201”, “field_223”, “field_235”, “field_239”, “field_241”, “field_242”, “field_243”, “field_246”, “field_247”, “field_248”, “field_249”, “field_250”, “field_253”, “field_254”, “field_255”, “field_256”, “field_257”, “field_260”, “field_278”, “field_279”, “field_280”, “field_281”, “field_341”, “field_344”, “field_345”, “field_378”, “field_395”, “field_147”, “field_148”, “field_278_val”, “field_280_val”}, {“Column1.id”, “Column1.field_37”, “Column1.field_38”, “Column1.field_39”, “Column1.field_40”, “Column1.field_42”, “Column1.field_45”, “Column1.field_46”, “Column1.field_47”, “Column1.field_48”, “Column1.field_50”, “Column1.field_51”, “Column1.field_53”, “Column1.field_54”, “Column1.field_55”, “Column1.field_56”, “Column1.field_177”, “Column1.field_178”, “Column1.field_184”, “Column1.field_185”, “Column1.field_186”, “Column1.field_187”, “Column1.field_200”, “Column1.field_201”, “Column1.field_223”, “Column1.field_235”, “Column1.field_239”, “Column1.field_241”, “Column1.field_242”, “Column1.field_243”, “Column1.field_246”, “Column1.field_247”, “Column1.field_248”, “Column1.field_249”, “Column1.field_250”, “Column1.field_253”, “Column1.field_254”, “Column1.field_255”, “Column1.field_256”, “Column1.field_257”, “Column1.field_260”, “Column1.field_278”, “Column1.field_279”, “Column1.field_280”, “Column1.field_281”, “Column1.field_341”, “Column1.field_344”, “Column1.field_345”, “Column1.field_378”, “Column1.field_395”, “Column1.field_147”, “Column1.field_148”, “Column1.field_278_val”, “Column1.field_280_val”}),
#“Kept First Rows” = Table.FirstN(#“Expanded Column1”,1000)
#“Kept First Rows”

Hey @frinksterj

What you’re seeing is a result of our API response using pagination. This means only 100 records will be returned per page - I’d have to take a look at Power Query to see how to paginate through the response as I’ve never used Power Query.

1 Like

Looking further into this and it appears as though it’s quite an in-depth process to work this out in Power Query. Not impossible, just convoluted. Thanks for the tip.

1 Like


I came across this thread and I’m having the same issue with it limiting me to only 100 rows. I see you indicated you may have found a solution. Would you please share this with me?


After some research I did come across how to do this but it was not simple and I cannot remember where I found the article/instructions. I am sorry I don’t have an answer for you.