Dynamic datasource automatic refresh issue.

[Bhanu_chPBI]

Hi,

Can any one help to modify my code give below. I used this following Web.Content code to get data. It works fine on desktop, but it will not allow me to update data in Service – I get the error message that dynamic data sources can’t be refreshed.

Code:-

let
fAllWorklogs = (offset) =>
let
Source = Json.Document(Web.Contents("https://api.tempo.io/core/3/worklogs?limit=1000&offset="&Text.From(offset), [Headers=[Authorization="Bearer xcvefmbsjlfgfwhjkjbdlmn"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"self"}),
#"Expanded metadata" = Table.ExpandRecordColumn(#"Removed Columns", "metadata", {"count"}, {"metadata.count"}),
#"Expanded results" = Table.ExpandListColumn(#"Expanded metadata", "results"),
#"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"tempoWorklogId", "jiraWorklogId", "issue", "timeSpentSeconds", "billableSeconds", "startDate", "startTime", "description", "createdAt", "updatedAt", "author", "attributes"}, {"Tempo Worklog ID", "Jira Worklog ID", "Issue", "Time Spent In Seconds", "Billable Seconds", "Start Date", "Start Time", "Description", "Created At", "Updated At", "Author", "Attributes"}),
#"Expanded Issue" = Table.ExpandRecordColumn(#"Expanded results1", "Issue", {"key", "id"}, {"Issue Key", "Issue ID"}),
#"Expanded Author" = Table.ExpandRecordColumn(#"Expanded Issue", "Author", {"displayName"}, {"Author.displayName"}),
#"Expanded Attributes" = Table.ExpandRecordColumn(#"Expanded Author", "Attributes", {"values"}, {"Attributes.values"}),
#"Expanded Attributes.values" = Table.ExpandListColumn(#"Expanded Attributes", "Attributes.values")
in
#"Expanded Attributes.values",

Source = List.Generate( () =>
[worklogTable = try fAllWorklogs(0) otherwise null, offset = 0],
each [worklogTable]{0}[metadata.count] <> 0,
each [worklogTable = try fAllWorklogs([offset]+1000) otherwise null, offset = [offset]+1000],
each [worklogTable]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"metadata.count", "Tempo Worklog ID", "Jira Worklog ID", "Issue Key", "Issue ID", "Time Spent In Seconds", "Billable Seconds", "Start Date", "Start Time", "Description", "Created At", "Updated At", "Author.displayName", "Attributes.values"}, {"metadata.count", "Tempo Worklog ID", "Jira Worklog ID", "Issue Key", "Issue ID", "Time Spent In Seconds", "Billable Seconds", "Start Date", "Start Time", "Description", "Created At", "Updated At", "Author.displayName", "Attributes.values"}),
#"Inserted Division" = Table.AddColumn(#"Expanded Column1", "Division", each [Time Spent In Seconds] / 3600, type number),
#"Rounded Off" = Table.TransformColumns(#"Inserted Division",{{"Division", each Number.Round(_, 2), type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Rounded Off",{{"Division", "Hours"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"metadata.count", Int64.Type}, {"Tempo Worklog ID", Int64.Type}, {"Jira Worklog ID", Int64.Type}, {"Issue Key", type text}, {"Issue ID", Int64.Type}, {"Time Spent In Seconds", Int64.Type}, {"Billable Seconds", Int64.Type}, {"Start Date", type date}, {"Start Time", type time}, {"Description", type text}, {"Created At", type datetime}, {"Updated At", type datetime}, {"Author.displayName", type text}, {"Attributes.values", type any}, {"Hours", type number}})
in
#"Changed Type"

[gauthamboppana · Accepted solution]

@Bhanu_chPBI  - It's a known issue esp. when you are trying to make the API calls dynamic. Please refer to the work around below and see if it works.

https://medium.datadriveninvestor.com/setting-a-scheduled-refresh-on-a-dynamic-data-source-in-power-...

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

https://community.powerbi.com/t5/Power-Query/REST-API-Dynamic-Datasource-aren-t-refreshed/td-p/18116...

Did I answer your question? Mark my post as a solution! If not, please feel free to ask me.

Also, I would ❤ Kudos if my solution helped.  It is a token of appreciation!

Thank you very much !