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

Re: how to create a query that paginates?

ImkeF, you are a legend!

 

It worked, thank-you very much for the guidance (I knew I was close...)

 

Thanks for the quick response as well, looking forward to working with my data now Smiley Happy

kroll Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hi Imke,

You query pulls the data (thank you!), but I cannot load that into a table. I have no idea how to deal with the 'error' in the table that is generated.

 

I run this code:

//Previous code with access credentials 
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170411202029578674183", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last7&limit=10&offset="&[Last_Key]&"&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
     			Last_Key = if [Counter]<=1 then "20170411202029578674183" else WebCall[lastKey] ,// determine the LastKey for the next execution
     			Counter = [Counter]+1,// internal counter
     			#"Converted to Table" = Record.ToTable(WebCall), // steps of your further query
     			Value = #"Converted to Table"{1}[Value] // last step of your further queries
                      ], 
   		each [Value]),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"}, {"Column1.id", "Column1.accessTime", "Column1.entity", "Column1.browser", "Column1.os", "Column1.location", "Column1.conversions", "Column1.type", "Column1.ip", "Column1.isSpider", "Column1.isUnique", "Column1.trackedParameters"})
in
    #"Expanded Column2"

When try to load the table, I get:

Error1.PNG

Which seems to be caused by the last records here:

Error2.PNG

And I cannot simply delete that row:

Error3.PNG

 

 

Do you have any recommendations?

Thank you in advance!

 

Super User
Super User

Re: how to create a query that paginates?

Pls check this code:

 

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170404130408053410572", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "20170404130408053410572" else [WebCall][lastKey] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=10&offset="&Last_Key&"%408693934&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column3" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"hits"}, {"hits"}),
    #"Expanded hits2" = Table.ExpandListColumn(#"Expanded Column3", "hits"),
    #"Expanded hits3" = Table.ExpandRecordColumn(#"Expanded hits2", "hits", {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"}, {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"})
in
    #"Expanded hits3"

 

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




kroll Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

In this thread it's a comment that is repeated a lot:

Imke, you are great!

Thank you for your help!!!

 

moritz1 Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hello!

After reading most of the Thread and a couple of hours of try and error later, frustration and mind block starts to kick in for me.

 

I am trying to adapt the code samples to request data from Airtable.com, which uses the same Pagination with an offset-Parameter like in the last example. Maybe someone can help me?? Smiley Frustrated

 

To make the reproduction easier, I created a sample database with five entries.

 

Example how the API works, I am requesting only two records (with the pageSize=2 Parameter) to allow testing with minimal data...

 

 

// 20170424221134
// https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2

{
  "records": [
    {
      "id": "rec9xyewCK97T01kP",
      "fields": {
        "Room": "Garage",
        "Condition": "Poor",
        "Priority": "Low"
      },
      "createdTime": "2017-04-24T19:53:01.511Z"
    },
    {
      "id": "recZmk4a5kmxdfK7O",
      "fields": {
        "Condition": "Poor",
        "Priority": "Medium",
        "Room": "Kitchen"
      },
      "createdTime": "2015-11-16T22:48:35.000Z"
    }
  ],
  "offset": "itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O"
}

 

 

The offset-Parameter can be inserted in the next call, to get the next two records:

 

 

 

https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset=itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O

 

 

 

To request the whole dataset (without pagination), I would perform the following in Power BI

 

 

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
    #"Expanded Column1.fields"

 

Now I tried to bring together this coding and the example coding from earlier:

 

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else [WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1)
, #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
, #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"})
, #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
  #"Expanded Column1.fields"

But I get an error the column "Column1" of the table could not be found. It seems that my "Pagination" is already empty. 

 

Edit: It seems that the errors comes up because the offset parameter cannot be empty on the first call and can only be filled with valid values, otherwise the API call will fail. I removed the and [Last_Key] <> "" from the third row and now I finally get data (hooray!!) but two records are fetched twice, so instead of five entries it returns seven. Is there any way I can initialize the "Last_Key" and only pass it to the offset-Parameter when it is not initial?

 

Can anyone help me? Thank you very much!! Smiley Happy

 

Best regards

Moritz

 

 

moritz1 Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hello!

After reading most of the Thread and a couple of hours of try and error later, frustration and mind block starts to kick in for me.

 

I am trying to adapt the code samples to request data from Airtable.com, which uses the same Pagination with an offset-Parameter like in the last example. Maybe someone can help me?? Smiley Frustrated

 

To make the reproduction easier, I created a sample database with five entries.

 

Example how the API works, I am requesting only two records (with the pageSize=2 Parameter) to allow testing with minimal data...

 

 

// 20170424221134
// https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2

{
  "records": [
    {
      "id": "rec9xyewCK97T01kP",
      "fields": {
        "Room": "Garage",
        "Condition": "Poor",
        "Priority": "Low"
      },
      "createdTime": "2017-04-24T19:53:01.511Z"
    },
    {
      "id": "recZmk4a5kmxdfK7O",
      "fields": {
        "Condition": "Poor",
        "Priority": "Medium",
        "Room": "Kitchen"
      },
      "createdTime": "2015-11-16T22:48:35.000Z"
    }
  ],
  "offset": "itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O"
}

 

 

The offset-Parameter can be inserted in the next call, to get the next two records:

 

 

 

https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset=itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O

 

 

 

To request the whole dataset (without pagination), I would perform the following in Power BI

 

 

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
    #"Expanded Column1.fields"

 

The thing is, the offset parameter must be valid and cannot be left empty. For the first API-Call, it must not be sent at all.

I tried to bring together this coding and the example coding from earlier:

 

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else [WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),
    #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"records", "offset"}, {"Column1.records", "Column1.offset"}),
    #"Erweiterte Column1.records" = Table.ExpandListColumn(#"Erweiterte Column1", "Column1.records"),
    #"Erweiterte Column1.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.records", "Column1.records", {"id", "fields", "createdTime"}, {"Column1.records.id", "Column1.records.fields", "Column1.records.createdTime"})
in
    #"Erweiterte Column1.records1"

The thing is now, that the first API call seems to be executed twice, at least I get the first two values doubled in in the result set.

 

Is there any way to "initialize" the Last_Key and only pass the offset-Parameter if the Last_Key is not initialized?

 

I tried this:

 

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "&init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else "&offset="[WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2"&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),

Which would send an empty "init"-parameter on the first call (the API doesn't mind that), and concatenate the offset-Parameter later. But this only returns the first two entries twice.

 

Can anyone help me? Thank you very much!! Smiley Happy

 

Best regards

Moritz

Super User
Super User

Re: how to create a query that paginates?

It's late, so just a quick idea now & maybe more tomorrow :-)

You have to put a condition into the step "WebCall", like :

if counter= 0 then ...CallWithoutOffset/LastKey else YourCurrentString

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




moritz1 Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hello Imke,

thanks for your reply. That did the trick! And the Counter<=1 had to be changed to Counter<1... Thanks a lot!

 

 

let
    Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/Room%20Assessment?api_key=<apikey>&pageSize=2")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/Room%20Assessment?api_key=<apikey>pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1),
    #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}),
    #"Erweiterte Column1.Value" = Table.ExpandRecordColumn(#"Erweiterte Column1", "Column1.Value", {"records", "offset"}, {"Column1.Value.records", "Column1.Value.offset"}),
    #"Erweiterte Column1.Value.records" = Table.ExpandListColumn(#"Erweiterte Column1.Value", "Column1.Value.records"),
    #"Erweiterte Column1.Value.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.Value.records", "Column1.Value.records", {"id", "fields", "createdTime"}, {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}),
    #"Erweiterte Column1.Value.records.fields" = Table.ExpandRecordColumn(#"Erweiterte Column1.Value.records1", "Column1.Value.records.fields", {"Condition", "Priority", "Room"}, {"Column1.Value.records.fields.Condition", "Column1.Value.records.fields.Priority", "Column1.Value.records.fields.Room"})
in
    #"Erweiterte Column1.Value.records.fields"

 

Robi2 Frequent Visitor
Frequent Visitor

Re: how to create a query that paginates?

Hi All,

 

I just wanted to share some code I wrote to get data out of elastic search using its scan and scroll pagination.

So if you have lots of data in elastic search, you must grab it in batches, and this code does exactly that, perhaps it will be helpful to someone:

 

let
FnGetElasticData = (url as text) as list =>
let
	// init params
	size=10000, // the size of the "chunks" of data that will be returned on each batch
	scroll = "1m", // the time in minutes elastic search should keep the "context" alive
	// build url for first fetch
	operator = if Text.Contains(url, "?") then "&" else "?",
	initUrl = url & operator & "scroll=" & scroll & "&size=" & Text.From(size),
	
	// get first batch of results
	initSource = Json.Document(Web.Contents(initUrl, [IsRetry=true])),
	totalResults = initSource[hits][total], // the total number of results to return
	iterations = Number.IntegerDivide(totalResults, size), // the total number of iterations we need to do
	
	// build url for scroll
	scrollId = initSource[_scroll_id], // the scroll id with wich we will fetch the rest of the results
	uriParts = Uri.Parts(url), // for getting the host and scheme of the url
	scrollUrl = uriParts[Scheme] & "://" & uriParts[Host] & "/_search/scroll?scroll=" & scroll & "&scroll_id=" & scrollId,
	
	// this will return a scrolled result from elastic search
	FnGetScrolledPage = (url as text) as list =>
		let
			response = Json.Document(Web.Contents(url, [IsRetry=true])),
			data = response[hits][hits]
		in
			data,
	
	// now loop through all of the iterations and return the data
	resultsList = List.Generate(()=>[i=0, res=initSource[hits][hits]], // Set inital data
		each [i] <= iterations, // Keep going until all of the iterations have been done or there is no more data
		each [i=[i] + 1, res = FnGetScrolledPage(scrollUrl)], // Get next batch of results		
		each [res])
in
    resultsList
in
FnGetElasticData

Basically, it will create a function that you can call with the URL for your elasticsearch query and it will return all of the results using paging.

 

Please note that this code will not work in automatic refreshes using the Power BI Gateway because that expects static URLs, you can get over this pretty easily by changing the code above to include the hard-coded URLs and then scheduled refresh will work.

mbrough Member
Member

Re: how to create a query that paginates?

Hi,

 

I have a similar issue where by I am trying to retreive from a web api but the pagination is only set to 100.

 

My data is Xml, but I have noticed that the above discussion is related to json, so not sure if this will affect how the code needs to be written for me.

 

I need to retreive around 300,000 records but the pagination only returns 100 records at a time.

 

Here is what the URL looks like:

 

http://example.co.uk/example/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&pa...

 

How can I make it so PBI returns all the records, not just 100 at a time?

 

Thank you

 

Mike