Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
manirohit
New Member

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

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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.

18.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
FernSant
Helper I
Helper I

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
Community Support
Community Support

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.

18.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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)

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.