Schedule Refresh in Service for Dynamic API Query

jpt1228 · ‎04-24-2021

Hello, I have been working on this issue for over a month. I have searched for topics related to my issue but have not found the solution yet. I have read through 13 different blogs and posts.

My business requirement is to schedule an automatic refresh in the Power BI service for a report I built from an API call.

Here is what I have learned so far:

1) I need to pass a page start function to return all pages of data. Success

2) You cannot schedule a refresh for a Dynamic API call in the service.

3) In order to schedule a refresh you need to hard code your API call with your key and set authorization to anonymous.

I can refresh the desktop model fine, but am not able to publish to the service and schedule a refresh.

This has been the most helpful search result but I can't seem to get it to work for my API endpoints: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

(pagestart as text)=>

let
    Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?", [Headers=[AUTHORIZATION="Basic ABC123KEY="]])),
    Query=[page="&pagestart&"],
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "requisition_id", "notes", "confidential", "is_template", "copied_from_id", "status", "created_at", "opened_at", "closed_at", "updated_at", "departments", "offices", "hiring_team", "custom_fields", "keyed_custom_fields", "openings"}, {"id", "name", "requisition_id", "notes", "confidential", "is_template", "copied_from_id", "status", "created_at", "opened_at", "closed_at", "updated_at", "departments", "offices", "hiring_team", "custom_fields", "keyed_custom_fields", "openings"})

in
    #"Expanded Column1"

I am not a coder so don't typically write my queries from blank.

Thanks in advance