How to perform loop in M Query and collate result of the same

[manirohit]

Hi All, 

I am new to PowerBI M query and trying to to use the web data source to connect Veeva APIs. I am able to connect to the API and fecth records from it but the issue is Veeva provides only 1000 records in a single API hit and in my case the data is huge like ~35,000 records. So i need to do a loop on the basis of the first response from Veeva(I'll get the total count in the resposne). and once I have all the records i want to collate all together in a single table. 

My current running code is below through which i am able to get first set of records. I have removed the username and password . also the API URL.

let
    Query1 = let
    auth_url = "https://abc.veevavault.com/api/v17.3/auth",
    GetJson = Json.Document(Web.Contents(auth_url, [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary("username=username&password=password")])),
    AccessToken = GetJson[sessionId],
    query_url = "https://abc.veevavault.com/api/v17.3/query?q=SELECT id, name FROM details",
    GetJsonQuery = Web.Contents(query_url,
        [
            Headers = [#"Authorization"=AccessToken]
        ]
    ),
    FormatAsJsonQuery = Json.Document(GetJsonQuery),
    Entities = FormatAsJsonQuery[data],  
    
    TableFromList = Record.ToTable(Entities),
    Value = TableFromList{1}[Value],
    Value1 = Value{0}

The Veeva API data response is like below 

{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "limit": 1000,
        "offset": 0,
        "size": 1000,
        "total": 2331,
        "next_page": "/api/v17.3/query/abcdefghij123456789?limit=1000&offset=1000"
    },
    "data": [ 
        {record1}, 
        {record2},
         .........
         {record999},
     ]
}

In above response total is total nuber of records, so i need to run a loop on that by increasing the counter by the size from the response. like in above example the loop should run 2 times more to get the total 2331 records. 

Can any one help me out here and let me know the steps how can i achieve it thorugh m query? let me know if any other infromation is required to help in it.

Thanks in advance

[Anonymous]

Now works not only on Power BI desktop but in  Microsoft Power BI Report Server - January 2021 as well

https://www.microsoft.com/en-us/download/details.aspx?id=55329

[v-shex-msft · Accepted solution]

HI @manirohit,

If your result will return empty string if all records has finish receive, you can try to use below custom function to recursive and looping queries.

Recursion function:

(Query as text,Token as text, optional paraTable as table) as table =>
let
    Source=InvokeAPI(Query,Token),
    ExtractedTable=null,//extract records from result
    MergedTable=Table.Combine({paraTable,ExtractedTable}),//combine paratable and extract table
    NextPage="",//extract next page query from result table
    Result=if NextPage= "" then paraTable else Recursion(NextPage,Token, MergedTable)
in 
    Result

InvokeAPI fucntion(used in above function)

(Query as text, Token as text) as table =>

let
    GetJsonQuery = Web.Contents(Query,[Headers = [#"Authorization"=Token]]),
    FormatAsJsonQuery = Json.Document(GetJsonQuery),
    Entities = FormatAsJsonQuery[data],  
    
    TableFromList = Record.ToTable(Entities),
    Result= TableFromList{1}
in 
    Result

Notice:

1. Current custom function only works on power bi desktop, service still not support.

2. Please invoke Recursion function in original query.

3. You need to replace bold part with formula who extract data from result table to instead.

4. Recursion method has poor performance high resource cost so it not suitable with huge amount data.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello Xiaoxin Sheng,

Does the looping function to pass part of URL is supported in Power BI Report Server Oct 2020 latest build (Build 15.0.1104.300)  ? or is it is only supported at Power Desktop only by now (15-Jan-2021) ??

let
sqltext = ( usr as text) =>
let
URL = Web.Contents("https://api.webappl.com.au/users/" & usr & "/tags", [Headers=[ContentType="application/json", Authorization="######"]]),
Source = Json.Document(URL)

[manirohit]

Thanks @v-shex-msft, I'll look into it.