Power BI Api Connection via Query Editor - Unable to Load Complete Data Entries (Only 25 returns)

[jessicaL_51]

Hello everyone,

I am currently facing an issue with my datasets after doing queries. And I am connecting from ConnectWise Manage to Power BI, using a web API key and adding headers.

To be specific, the issue is that I only received limited records after making a live connection. As there're at least 160,000 entries in ConnectWise service tickets, it only returns 25 rows under per 'Record' value in the Power BI query editor.

The code I utilised referred to the post below:

Solved: Api limit of 1000 records - getting the next 1000 ... - Microsoft Power BI Community

My example applied to query editor:

let 
   Source=List.Numbers(0,150,1000),
   #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
   #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Column1", each "[" & _, type text}}),
   #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Column1", each _ & "]", type text}}),
   #"Added Custom" = Table.AddColumn(#"Added Suffix", "WebSource", each Web.Contents("https://" & "urlpart/" & "urlpart/" & "urlpart/urlpart/?limit=160000", [Headers=[Authorization="BASIC Company+PublicKey:PrivateKey",clientID="numbers-numbers-numbers-numbers", ContentType="JSon"]])),
in #"Added Custom"

If  I hope to overcome API call row limit, would it be okay to use "limit" here or other queries that can perform better?

And I have already set the column profiling as "based on entire data set". Would anybody have ideas about why my API activity returns limited rows? I'd appreciate your time and help.

Thanks.

Kind Regards,

JessicaScreenshot 1 - only showing 150 rows

Screenshot 2 - Only 25 items in each row after clicking through

[lbendlin]

Looks like you already set the pagination to 1000 which seems to be a reasonable number.  What is the format of the returned payload? Is it JSON?  For each row in your screenshot 1 you need to covert the binary into a usable table (by parsing the JSON, for example)  before you can then combine the results.

The column profiling feature is unrelated to your issue.

[jessicaL_51]

Hello @lbendlin ,

Thanks very much for your reply. 

Screenshot 1 is the outcome after doing the query above, which has been parsed into JSON. As soon as I clicked the Icon on the top right corner, it shows detailed data entries in a table. (p.s. Screenshot 2 is the result by clicking through each 'Binary' value in screenshot 1, showing 25 items in total)

And I converted it to a table that shows in total 3750(150rows*25items) returns, which does not match with the initial records(should be over 130000 data entries). As there's a huge gap between the data I've got with M query and all records in the data warehouse. I would like to know if you suspect anything wrong with my query or if I didn't query all data. Also, if I increase the list numbers, the result shows a value duplicated several times rather than all data records exported from the platform. I am also thinking about the possibility of API limitation in retrieving the number of data. 

I would appreciate knowing if you have any thoughts on this issue. Thanks.

Kind Regards,

Jessica

[lbendlin]

Here is an article that goes into nice detail on how to handle situations like yours.

Handling paging for Power Query connectors - Power Query | Microsoft Learn

Without access to your API it is unfortunately very difficult to help much further.