1

I am trying to write a recursive web API call in PBI to collect all 27,515 records, the oDATA feed has a limit of 1,000 rows. I need this data to be refreshable in the PBI service, therefore these 28 requests via M code cannot be formulated in a dynamic way. PBI only allows for static or non-dynamic sources for refresh within the service. Below, I will share two pieces of M code, 1. one that is considered to be a dynamic data source (not what I need, but pulls all 27,515 records correctly) and 2. one that is a static data source (which is giving an incorrect number of 19,000 records, but is the type of data source that I need for this refreshing problem).

Noteworthy: Upon initial API call I receive a table named table "d" (in the photo below) with two rows one row it titled "results" which contains all of the data (1,000 rows) I need per request, the second row is titled "__next" which has the next API URL with an embedded skiptoken from the current calls worth of data. This skiptoken tells the API which rows to skip so that the next request doesn't deliver the data we have already collected.

Table d, Initial Table

M Code for Dynamic Data Source: This dynamic data source is pulling the correct number of records in 28 requests (up to 1,000 records per request) totaling 27,515 rows.

= List.Generate( ()=> Json.Document(Web.Contents("https://my_instance/odata/v2/Table?$format=JSON&$paging=snapshot"))[d],
                    each Record.HasFields(_, "results")= true, 
                    each try Json.Document(Web.Contents(_[__next]))[d] otherwise [df=[__next="dummy_variable"]])

M Code for Static Data Source: This static data source is the type that I need for refreshing in PBI service (I confirmed it does refresh in the service), but is returning an incorrect number of rows, 19,000 versus 27,515. This code is calling 19 requests versus the needed 28 requests. I believe the error lies in the Query portion where I am attempting to call the next API URL with the skiptoken from the previous request.

= List.Generate( ()=> Json.Document(Web.Contents("https://my_instance/odata/v2/Table?$format=JSON&$paging=snapshot"))[d],
                    each Record.HasFields(_, "results")= true, 
                    each try Json.Document(Web.Contents("https://my_instance/odata/v2/Table?$format=JSON&$paging=snapshot", [Query=[q=_[__next]]]))[d] otherwise [df=[__next="dummy_variable"]])

Does anyone see an error in the static code for iteratively calling each new request in the table [d] which has rows labeled [results] (all the data) and another row labeled [__next] which has the next URL with the skiptoken from the previous API call.

jessica4255
  • 31
  • 1

1 Answers1

2

To be clear, in Web.Contents the url must be static, but you can freely use dynamic components in the RelativePath optional option argument (as in this simple example function) which is how you can generate dynamic web API queries that work in the service without the error you are seeing w.r.t. dynamic queries:

(current_page as text) =>
let
    data = Web.Contents(
        "https://my_instance/api/v2/endpoint", // static!
        [
            RelativePath = "?page="&current_page // dynamic!
        ]
    )
in 
   data

So if you can split out the relative path of your _next parameter and feed it into such a function it will be OK for automatic refreshes in the Power BI service.

Marcus
  • 3,346
  • 1
  • 5
  • 23
  • Hi Marcus, Thank you for the reply. I have a list of the skiptokens for each api request split already. Do you see an easy way to build the api calls considering there is a separate list with the skiptokens? – jessica4255 Nov 26 '22 at 16:33
  • If you have one skiptoken per row, simply create a function like the one in my example that forms the API query with the skiptoken - and add a "invoke custom function" column in Power Query using the skiptoken column as argument. – Marcus Nov 26 '22 at 16:37
  • Marcus, thank you for the timely response. I have tried this exact method and I am hitting another wall: Error: Formula.Firewall: Query 'userSkiptoken' (step 'Invoked Custom Function') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination. I am going to troubleshoot some now, but do you see something that stands out immediately from this error code? Thanks again. – jessica4255 Nov 26 '22 at 18:35
  • I was able to bypass the security settings, but for some odd reason the web call is not accepting the request with the skiptoken via invoking a custom column after passing the skiptoken column as an argument in the function created titled "passSkiptoken". '= (skiptoken as text) => let data = Web.Contents( "https://my_instance/odata/v2/Table?", // static! [ RelativePath = "&$format=JSON&$"&skiptoken // dynamic! ] ) in data' This method is not working for some reason. Is it possible to access the skiptoken via the org. post M code? – jessica4255 Nov 26 '22 at 18:56
  • I was able to implement after troubleshooting via the function and invoking a custom column from the function, but the data source is still considered to be a dynamic query. I am not sure what else I can do here. Any tips anyone? – jessica4255 Nov 26 '22 at 19:37
  • Add your current queries to your question. – David Browne - Microsoft Nov 26 '22 at 19:44
  • @DavidBrowne-Microsoft, thank you. Currently, I am attempting to iterate through the table to collect of the skiptokens or API request. I implemented your strategy, but I could not get all the skiptokens in a non-dynamic way. I attempted with your code, but I cannot get all of the data. I am not sure what I can do to loop through table d which has the __next row with the next api request. I am going to continue attempting this problem. – jessica4255 Nov 27 '22 at 00:20
  • see if https://gorilla.bi/power-query/list-generate-api-calls/ helps – horseyride Nov 27 '22 at 10:26