cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: how to create a query that paginates?

Pls check this out:

 

let

Source = Json.Document(Web.Contents("api.mywebsite.com/leads?myapikey=1234567")),
Source1 = {1..Source[page_count]},
#"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("api.mywebsite.com/leads?myapikey=1234567&page="&Text.From([Column1])&"")))
in
#"Added Custom"

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?

FYI: The other finally went through and I'm getting the same ("We cannot convert a vlue of type Record to type List") error.

 

Here's the code:

 

 

let

Source = {1..200},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("https://api.mywebsite.com?myapikey=1234567&page="&Text.From([Column1])&"")){0}[items]),
Custom = #"Added Custom"{0}[Custom]
in
Custom

 

 

 

Query2.jpg

 

Error msg.jpg

Super User
Super User

Re: how to create a query that paginates?

Delete the {0}, so just:

 

let

Source = {1..200},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("https://api.mywebsite.com?myapikey=1234567&page="&Text.From([Column1])&""))[items])
in

#"Added Custom" 

Then check what format is returned in column [Custom] before deciding on how to expand that.

 

 

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?

That worked!!

 

YOU ARE AWESOME!!

 

Thank you so much ImkeF!!

remix Visitor
Visitor

Re: how to create a query that paginates?

Hello @echofoxtrot

I`m also working to get the results from Hubspot Deals. Do you mind sharing your query?

kroll Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hi,

Have you found a solution to your problem?
I'm running into the same issue...

 

Thank you in advance!

echofoxtrot Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

Hi @remix

 

Apologies for the delay. My environment is unique, and the database wound up being to big to refresh, but here's essentially what I was working with on my tests:

 

let

Source = {1..100},

Source1 = ({"abcd", "abcde"}),
    #"Converted to Table1" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Added Custom1" = Table.AddColumn(#"Converted to Table1", "Custom", each Json.Document(Web.Contents("https://test.com/leads?campaign_id="&Text.From([Column1])&"&start=2016-01-01&api_key=123"))[items]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("https://test.com/leads?campaign_id=abcd&start=2016-01-01&api_key=123&page="&Text.From([Column1])&"")...]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"sale_price", "good"})
in
    #"Expanded Custom1"

sterling Visitor
Visitor

Re: how to create a query that paginates?

I have a similar question. Below is my query but Airtable is only returning 100 records. Thist list will be growing so I want it to fetch all records for this query (that it's limiting to 100 right now).

 

 

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/123456/TABLE?api_key=APIKEY")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{0}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "fields", "createdTime"}, {"id", "fields", "createdTime"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"ML#", "Curr Price", "Status", "Address", "Subd/Complex", "Area", "Age Desc", "City", "Zip Code", "Style", "Association Fee", "County", "Total Bedrooms", "Total Full Baths", "Total Half Baths", "Year Built", "Photo Count", "List Date", "Square Footage", "Total Days on Market", "Current Price/SQFT", "Original List Price", "List Price", "SqFt Source", "Type", "Expiration Date", "Approximate Lot Size", "Latitude", "Longitude", "Accuracy Score", "Accuracy Type", "Number", "Street", "City 2", "State", "County 2", "Zip", "Country", "Binding Agreement Date", "Sales Price", "SP/OLP", "Closing Date", "Costs Paid by Seller"}, {"ML#", "Curr Price", "Status", "Address", "Subd/Complex", "Area", "Age Desc", "City", "Zip Code", "Style", "Association Fee", "County", "Total Bedrooms", "Total Full Baths", "Total Half Baths", "Year Built", "Photo Count", "List Date", "Square Footage", "Total Days on Market", "Current Price/SQFT", "Original List Price", "List Price", "SqFt Source", "Type", "Expiration Date", "Approximate Lot Size", "Latitude", "Longitude", "Accuracy Score", "Accuracy Type", "Number", "Street", "City 2", "State", "County 2", "Zip", "Country", "Binding Agreement Date", "Sales Price", "SP/OLP", "Closing Date", "Costs Paid by Seller"})
in
    #"Expanded fields"

 

echofoxtrot Regular Visitor
Regular Visitor

Re: how to create a query that paginates?

Hi @sterling


I'm not an expert at this and I had a lot of help writing the query (thanks again @ImkeF!), but could the API you're using limit you to a certain amount of results? Is there a "HAS MORE" (pages) with a "TRUE" or "FALSE" option for example? I don't see a page option in your URL, but if that can be included, below is the solution that @ImkeF sent me, and it resolved my issue. Notice: p="&Text.From([Column1])&"

 

let
    Source = {1..11},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Web.Page(Web.Contents("http://www.boerse-online.de/index/liste/S&P_500?p="&Text.From([Column1])&"")){0}[Data])
in
    #"Added Custom"

 

 

I wish I had more specific technical advise here, but hopefully that helps.

Super User
Super User

Re: how to create a query that paginates?

Very much agree with @echofoxtrot: You need to find out what your API can deliver. That's not a PBI-isssue.

You need to find a URL that contains a parameter that can be adjusted and "looped" through. We can help you with that once we see the syntax.

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