cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gotmike Frequent Visitor
Frequent Visitor

how to create a query that paginates?

I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the results.

 

For instance, if you send:

https://api.hubapi.com/deals/v1/deal/all?hapikey=demo

 

at the very end of the query, you see the following JSON:

 

"hasMore":false
"offset":27939158

so, if hasMore is true, the NEXT query should look like this:

https://api.hubapi.com/deals/v1/deal/all?hapikey=demo&offset=27939158

 

and then, we would want to repeat the process until hasMore comes back with false.

 

i'm completely new to power bi, so would love to know how to handle this type of query process.

 

in another language, this would just be do { } while (hasMore == false);

or something like that...

171 REPLIES 171

Re: how to create a query that paginates?

Hi @gotmike,

 

there is no concept of loops in M (Power Query), but you can use recursive functions, to reach your goals. The following article by Chris Webb uses this concept to flat a parent/child hierarchy. I am not sure how to use this in combination with your API call, but maybe this is one more step into the right direction for you Smiley Happy

 

Regards,

Lars

tristanstcyr Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

You may also want to avoid recursion as well since we don't do tail recursion optimization. It depends how many results you need to iterate over. If the result is large, look into using functions such as List.Accumulate or List.Generate.

Super User
Super User

Re: how to create a query that paginates?

This article contains of very good explanation of how List.Generate works: http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




tempranello Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

EDIT:  I posted this to the wrong thread(!).  I should have replied here, and I'll continue and close the thread there.

 

Thanks ImkeF

 

My apologies for taking so long to acknowledge your support.  I've finally got around to having a go at List.Generate() and I reckon that I'm on the right path.  However, I think that I've created an infinite loop and thus launch a small-scale DoS attack against the Web API I'm trying to hit :-) 

 

Would you mind taking a look at my query text and giving me your opinion?  When I run it, I receive no errors, but the query appears to run forever.  At this point all that I'm trying to do is generate a list of URIs where I supply the first, and generate the rest based on the initial return.

 

let
    URIList = List.Generate(
    ()=> [SourceURI="https://api.dovico.com/TimeEntries/?version=5"],
    each Text.PositionOf([SourceURI],"N/A") = -1,
    each [
        Source = Web.Contents([SourceURI],[Headers=[#"Authorization"="WRAP access_token=""client=XXX&user_token=YYY"""]]),
        ImportedXML = Xml.Tables(Source,null,1252),
        ChangedType = Table.TransformColumnTypes(ImportedXML,{{"PrevPageURI", type text

Super User
Super User

Re: how to create a query that paginates?

I cannot spot anything obvious.

Have never used it without the counter, but cannot see why this shouldn't work.

Do you know how many loops to expect?

 

Although it should be lazy evaluating - in case it doesn't, you can shorten the command like this:

 

Source = Web.Contents([SourceURI],[Headers=[#"Authorization​"="WRAP access_token=""client=XXX&user_token=YYY"""]]),
        ImportedXML = Xml.Tables(Source,null,1252)[NextPageURI]{0}

  ],
    each [SourceURI]

 

This should only retrieve the value from column "NextPageURI" in the first row {0}

 

Well actually, maybe it's this: Text.PositionOf([SourceURI],"N/A") = -1 : You're searching for something that shouldn't be there. Could you replace it by a positive identification?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




nakia Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hi,

 

Just wondering if you ever got this to work?

 

I have almost the exact same need right now that I would like to resolve.

 

The API I am talking to has a starting URI and if it has more that 10 records to return it will paginate and provide in the initial response the "NextURI" for me to retrieve the next 10 records.

 

Appreciate any help I could get!

 

 

tempranello Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

Hi there

 

Sorry for the delay in getting back to you.  I did get it to work, but it is slow.  I'm unsure where the bottleneck is (my code, or the service I'm hitting).  Either way, here is the code I pulled together:

 

let
    /*
      Purpose: Request all timesheet records from Dovico      
    */

    /*
      Generate a list of XML objects based on the Dovico's pagination of its return set
      The initial URL is:  https://api.dovico.com/TimeEntries/?version=5
      Pagination is controlled by return randomly-generated values in URI attributes PrevPageURI and NextPageURI
      The limit in each direction is denoted by the value "N/A"
      Date filtering can be introduced by using:  https://api.dovico.com/TimeEntries/?daterange=2016-04-01%202016-04-15&version=5
      See the Dovico API for more information:  http://apideveloper.dovico.com/Time+Entries
    */
    DataList = List.Generate(
                    ()=> [SourceURI="https://api.dovico.com/TimeEntries/?version=5",ImportedXML=""],
                    each Text.PositionOf([SourceURI],"N/A") = -1,
                    each [
        Source = Web.Contents([SourceURI],[Headers=[#"Authorization"="WRAP access_token=""client=CLIENT_TOKEN&user_token=USER_TOKEN"""]]),
        ImportedXML = Xml.Tables(Source,null,1252),
        ChangeType = Table.TransformColumnTypes(ImportedXML,{{"PrevPageURI", type text}, {"NextPageURI", type text}}),
        SourceURI = Record.Field(Table.First(ChangeType),"NextPageURI"),
        TimeEntries = ChangeType{0}[TimeEntries],
        TimeEntry = TimeEntries{0}[TimeEntry]
        ],
    each [[SourceURI],[ImportedXML]]
    ),

    /*
      Now expand and shape the list of XML objects into a single table of data
    */
    ConvertToTable = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"ImportedXML"}, {"ImportedXML"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([ImportedXML] <> "")),
    #"Expanded ImportedXML" = Table.ExpandTableColumn(#"Filtered Rows", "ImportedXML", {"TimeEntries"}, {"TimeEntries"}),
    #"Expanded TimeEntries" = Table.ExpandTableColumn(#"Expanded ImportedXML", "TimeEntries", {"TimeEntry"}, {"TimeEntry"}),
    #"Expanded TimeEntry" = Table.ExpandTableColumn(#"Expanded TimeEntries", "TimeEntry", {"Sheet", "Client", "Project", "Task", "Employee", "Date", "TotalHours", "Description"}, {"Sheet", "Client", "Project", "Task", "Employee", "Date", "TotalHours", "Description"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded TimeEntry",{{"Date", type date}, {"TotalHours", type number}, {"Description", type text}}),
    #"Expanded Project" = Table.ExpandTableColumn(#"Changed Type1", "Project", {"Name"}, {"Project.Name"}),
    #"Expanded Task" = Table.ExpandTableColumn(#"Expanded Project", "Task", {"Name"}, {"Task.Name"}),
    #"Expanded Employee" = Table.ExpandTableColumn(#"Expanded Task", "Employee", {"Name"}, {"Employee.Name"}),
    #"Expanded Client" = Table.ExpandTableColumn(#"Expanded Employee", "Client", {"Name"}, {"Client.Name"}),
    #"Expanded Sheet" = Table.ExpandTableColumn(#"Expanded Client", "Sheet", {"Status"}, {"Sheet.Status"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Sheet",{{"Client.Name", "Customer"}, {"Sheet.Status", "Approval status"}, {"Project.Name", "Project"}, {"Task.Name", "Task"}, {"Employee.Name", "Raw Name"}, {"TotalHours", "Effort (hrs)"}}),
    #"Added Week Ending" = Table.AddColumn(#"Renamed Columns", "Week ending", each Date.EndOfWeek([Date],Day.Saturday), type date),
    #"Added Name" = Table.AddColumn(#"Added Week Ending", "Name", each Text.Combine({List.Last(Text.Split([Raw Name],",")), List.First(Text.Split([Raw Name],","))}, " "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Name",{"Raw Name"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Name", Text.Trim}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Trimmed Text",{"Name", "Date", "Week ending", "Customer", "Project", "Task", "Effort (hrs)", "Description", "Approval status"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Description", "Approval status"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Date", Order.Descending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Customer", type text}, {"Project", type text}, {"Task", type text}})
in
    #"Changed Type"
Super User
Super User

Re: how to create a query that paginates?

Hi @tempranello,

thanks for getting back and providing such a nice code.

 

Nothing really obvious in the List.Generate part, although: What about "TimeEntries" and "TimeEntry" - cannot see where you use them there and you don't return them in the output. So could you omit them? (Although I wouldn't believe that this is a performance killer).

 

The next step to localize the performance-problem would be to skip all steps after #"Expanded ImportedXML" This seems to be the step where the first "real" value from the procedure is shown and before further transformations take place.

 

You can try to put a buffer here:

ConvertToTable = Table.FromList(List.Buffer(DataList), Splitter.SplitByNothing(), null, null, ExtraValues.Error),

making sure to avoid multiple web-calls.

 

Or you use Fiddler to check how long the web-calls actually take.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




echofoxtrot Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

Hi,

 

I'm trying to understand this code and manipulate it for my own query, but I'm having difficulty. Do you have any thoughts on how I would edit this in order to display all pages on this query?

 

I've attached a screen shot of what I see when I first bring the query in. There are 1132 pages and 56580 records, but when I expand it out, I only get 50 records.

 

Initial Query.jpg

 

Screen shot above and here is the final code below (the URL is bogus for security purposes):

 

 

 

let
Source = Json.Document(Web.Contents("https://api.thewebsite.com/stats/leads?&start=2016-01-01&end=2016-11-25api_key=myapikey")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{2}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"bad", "campaign_id", "campaign_name", "converted", "created_at", "delivered", "delivery_summary_id", "error_delivery_summary_id", "good", "id", "invalid", "margin", "marked_for_wipe_at", "pickled_at", "profile_id", "profit", "purchase_price", "recipient_id", "rejected", "returned", "sale_price", "source_id", "source_lead_id", "values", "wiped_at"}, {"bad", "campaign_id", "campaign_name", "converted", "created_at", "delivered", "delivery_summary_id", "error_delivery_summary_id", "good", "id", "invalid", "margin", "marked_for_wipe_at", "pickled_at", "profile_id", "profit", "purchase_price", "recipient_id", "rejected", "returned", "sale_price", "source_id", "source_lead_id", "values", "wiped_at"})
in
#"Expanded Column1"

 

 

 

Thanks for any advise!!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 888 guests
Please welcome our newest community members: