Forum Discussion

LP2803's avatar
LP2803
Icon for Responsive Resident rankResponsive Resident
5 years ago

PowerBI Service having isssues in data refresh

Hi all,

 

In PowerBI Desktop i have a function which generates a list of numbers {1,101,201,.....}

I use this list to pass value to "offset" parameter in a Web API.  

 

This works fine in PowerBI Desktop but not in Powerbi.com

 

I have the below issue while i try to schedule the data refresh. 

 

I read the below article on how to use the RelativePath but not sure how i can use it in my case.

 

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

 

my codes look like below :

 

Function : getPage 

(page as text) =>
let
Source = Json.Document(Web.Contents("https://webexapis.com/v1/meetings?meetingType=meeting&from=2019-11-01T00:00:00Z&to=2020-12-31T00:00:00Z&offset="& page &"&max=100", [Headers=[Authorization="Bearer PF84_ea939dd2-dd40-4d98-b942-28e87fe4cc1f"]])),
items = Source[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "meetingSeriesId", "scheduledMeetingId", "title", "agenda", "meetingType", "state", "timezone", "start", "end", "hostUserId", "hostDisplayName", "hostEmail", "webLink"}, {"Column1.id", "Column1.meetingSeriesId", "Column1.scheduledMeetingId", "Column1.title", "Column1.agenda", "Column1.meetingType", "Column1.state", "Column1.timezone", "Column1.start", "Column1.end", "Column1.hostUserId", "Column1.hostDisplayName", "Column1.hostEmail", "Column1.webLink"})
in
#"Expanded Column1"

 

 

Query1 :

let
List = List.Numbers(1,99,100),
#"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 getPage([Column1])),
#"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {"Column1.id", "Column1.start"}, {"Data.Column1.id", "Data.Column1.start"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each true),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Data.Column1.start", Order.Ascending}, {"Column1", Order.Ascending}})
in
#"Sorted Rows"