Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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
It needs a trick: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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!
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |