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 III
Super User III

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

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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




Highlighted
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 III
Super User III

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.

 

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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 III
Super User III

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/

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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 III
Super User III

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.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors