cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markholland Regular Visitor
Regular Visitor

Scheduled refresh and pagination - can't refresh query

Hi,

 

I'm having problems scheduling a refresh on the service with a query that's looking at multiple API pages. When I try this with one page it all works fine.

 

If I add the following Web Source it all works fine:

 

https://api.harvestapp.com/v2/time_entries?access_token=******&account_id=******&page=10

 

In this example I'm using page 10 as a test. But I want to be able to return pages 1-50. I've tried starting with a list of number in a table from 1-50:

 

01.PNG

 

This I've added a custom column to merge these page numbers with the URL above:

 

= Json.Document(Web.Contents("https://api.harvestapp.com/v2/time_entries?access_token=******&account_id=******&page=" & Text.From([Page])))

 

I found I needed to add in "Json.Document(Web.Contents" in order to make the URLs active. Could this be where I'm going wrong?

 

This brings me back a list of all the records, which I then expand out:

 

02.PNG

 

But for some reason it's not working. I'd previously tried adding pagination coding to my query but found that doesn't work with a scheduled refresh, so tried this as a way to pull in each URL individually, but it's not working.

 

Any ideas on where I'm going wrong?

 

As always, your help is very much appreciated.

Mark

7 REPLIES 7
Super User
Super User

Re: Scheduled refresh and pagination - can't refresh query

markholland Regular Visitor
Regular Visitor

Re: Scheduled refresh and pagination - can't refresh query

HI @ImkeF,

 

This one is so close but still not quite there. Here’s the URI I’m using to pull back my data:

 

https://api.harvestapp.com/v2/time_entries?access_token=****&account_id=****

 

If I want to bring back a specific page my URIL looks as follows:

 

https://api.harvestapp.com/v2/time_entries?page=10&access_token=****&account_id=****

 

When I use the code in this post it replaces the ‘?’ with ‘/’ then introduces ‘page=10?’ after it, which doesn’t work with my URI:

 

https://api.harvestapp.com/v2/time_entries/page=10?access_token=****&account_id=****

 

When I put it into a brower it gives me a 404 error.

 

Any ideas?

Super User
Super User

Re: Scheduled refresh and pagination - can't refresh query

Hi Mark,

To my understanding, your code should more look like this:

 

Json.Document(
	Web.Contents("https://api.harvestapp.com/v2/time_entries?access_token=******&account_id=******&page=10",
					[Query=[q="access_token=******&account_id=******&page=" & Text.From([Page])]])
			)

The dynamic part has to be added as a query parameter and the URL has to be an unparametrized sample.

 

 

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




fmr5000 Frequent Visitor
Frequent Visitor

Re: Scheduled refresh and pagination - can't refresh query

hi @ImkeF - hoping you can help. trying to trick powerbi into having a scheduled refresh for pagination. I've tried all the different suggestions but can't seem to get it to work. I am posting my current code that works today without scheduled refresh:

 

(page as text) =>
let
    Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/time_entries?access_token=****&account_id=****&page="&(page))),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{0}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table1"

and

 

let
    Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/time_entries?access_token=****&account_id=****")),
    List = {1..Source[total_pages]},
    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each getPages([Column1])),
    #"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {"Column1"}, {"Column1.1"}),

thank you in advance!

Super User
Super User

Re: Scheduled refresh and pagination - can't refresh query

Hi @fmr5000,

you cannot use the dynamic elements in the core URL-string. Instead you have to put it into a separate record like described here:

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

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




fmr5000 Frequent Visitor
Frequent Visitor

Re: Scheduled refresh and pagination - can't refresh query

Thank you @ImkeF

 

I think I am close but when applying the following code I receive an error when adding the custom column in the table query: “Unexpected error: Operation is not valid due to the current state of the object.”

 

getPage Code:

(page as text) =>
let
Source = Json.Document(Web.Contents(“https://api.harvestapp.com/v2/time_entries?access_token=*****&account_id=*****&page=”&(page),
[Query=[page=(page)]])),
#”Converted to Table” = Record.ToTable(Source),
Value = #”Converted to Table”{0}[Value],
#”Converted to Table1″ = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#”Converted to Table1″

table code:

let

Source = Json.Document(Web.Contents(“https://api.harvestapp.com/v2/time_entries?access_token=*****&account_id=*****”)),
List = {1..Source[total_pages]},

#”Converted to Table” = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Page”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Page”, type text}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Custom”, each getPage([Page]))
in
#”Added Custom”

 

 

Thank you for your help in advance.

Super User
Super User

Re: Scheduled refresh and pagination - can't refresh query

That's a strange error. I got it ages ago when my data model was corrupt.

So maybe you transfer your queries to a new file.

Otherwise I have no idea unfortunately.

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