Dataset does not refresh in PBI, works fine in PBI desktop

[Anonymous]

Hi all,  I am pulling data from external APIs and using web.content function. The dashboard refreshes without any issue in PBI desktop, however gives the error below when published. "-

--------

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

• Data source for Query1

------

There is no Query1 in the whole report, but someonehow it manages to mention this name. Below are some functions and a query to give an idea.  Note that I have tried out steps given here and elsewhere around using web.content in a different manner, but nothing seems to work :-(.

---GetPages function---

(URL_part as text) as number =>
let
Source = pole_authenticate_jwt,
token=Text.Combine({"Bearer ",Source{0}[Value]}),
Full_URL=Text.Combine({Pole_URL, URL_part}),
json = Json.Document(Web.Contents(Full_URL, [Headers=[#"Authorization"=token]])),
#"meta" = json[meta],
#"Converted to Table" = Record.ToTable(#"meta"),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "total")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
#"Removed Other Columns" = Number.IntegerDivide(#"Removed Columns"{0}[Value]-1,Page_size)
in
#"Removed Other Columns"-

---Project Pages Query---

let
Source = {0..GetPages("/api/common/v0/projects?page[limit]=10&page[offset]=0")},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each GetProjects([Column1])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1.id", "Column1.attributes.name", "Column1.relationships", "Column1.links", "Column1.meta"}, {"Custom.Column1.id", "Custom.Column1.attributes.name", "Custom.Column1.relationships", "Custom.Column1.links", "Custom.Column1.meta"}),
#"Expanded Custom.Column1.links" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom.Column1.links", {"self"}, {"Custom.Column1.links.self"}),
#"Expanded Custom.Column1.meta" = Table.ExpandRecordColumn(#"Expanded Custom.Column1.links", "Custom.Column1.meta", {"organization-id", "in-trash"}, {"Custom.Column1.meta.organization-id", "Custom.Column1.meta.in-trash"}),
#"Expanded Custom.Column1.links.self" = Table.ExpandRecordColumn(#"Expanded Custom.Column1.meta", "Custom.Column1.links.self", {"href"}, {"Custom.Column1.links.self.href"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.Column1.links.self",{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.Column1.id", "Project-ID"}, {"Custom.Column1.attributes.name", "Project-name"}, {"Custom.Column1.links.self.href", "Project-URL"}, {"Custom.Column1.meta.organization-id", "Project-org"}})
in
#"Renamed Columns"

--------------GetProjects Function-----------

(page as number) as table =>
let
Source = pole_authenticate_jwt,
token=Text.Combine({"Bearer ",Source{0}[Value]}),

url= Text.Combine({Pole_URL,"/api/common/v0/projects?page[limit]=" & Number.ToText(Page_size) & "&page[offset]=" & Number.ToText(Page_size*page)}),
json = Json.Document(Web.Contents(url, [Headers=[#"Authorization"=token]])),
data = json[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "attributes", "relationships", "links", "meta"}, {"Column1.id", "Column1.attributes", "Column1.relationships", "Column1.links", "Column1.meta"}),
#"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", {"name"}, {"Column1.attributes.name"})
in
#"Expanded Column1.attributes"

--------------------------------------

[v-kelly-msft]

Hi  @Anonymous,

Check the similar thread below：

https://community.powerbi.com/t5/Service/Unable-to-refresh-schedule-refresh-WEB-API-call-on-Power-BI/td-p/504461

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft, When going from option1 to option 2 (see below), I get an "invalid identified" error right in the advanced editor. Any clude how to get passed this?

---Option 1--

json = Json.Document(Web.Contents(Full_URL, [Headers=[#"Authorization"=token]])),

---Option 2--

json = Json.Document(Web.Contents(Base_URL, [RelativePath=URL_part, Query=[page[limit]=10, page[offset]=0],  Headers=[#"Authorization"=token]])),

[v-kelly-msft]

Hi @Anonymous ,

Check whether below reference help：

https://docs.microsoft.com/en-us/powerquery-m/web-contents

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly, 

As mentioned I have tried the RelativePath, Query etc and it did not work. Not sure if you got a chance to read my post end to end.

--RR

[v-kelly-msft]

Hi @Anonymous ,

I have read your each reply from begin to end,but I cant reproduce your senario,I can only guess or suggest,not quite sure which caus ed your issue,if what I have suggested still cant solve your issue,pls create a support ticket via below link and our relevant personnel will help to handle it.
https://powerbi.microsoft.com/en-us/support/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thanks for responding to my post. 

The provided link leads to the same blog post, that I had referred to in my post. Have tried that out already, no luck !