Hey everyone
Sorry for reaching out as i have tried so hard to solve this myself. I have a data source to an API via Json, and it loops through the pages to get the info. Code is below.
Runs the refresh perfectly on the desktop service but refuses on the web based version. It says "Dataset includes a dynamic data source" - Done loads of googling and i know its because of the relative hyperlink, but have tried all the combinations out there to get it to work and it just refuses.
Can someone with a massive brain save a man from loosing any more hair! - Please!
This is my code
let
StartUrl = "https://app.timetastic.co.uk/api/holidays",
Token = "xxxxxxx",
GetJson = (Url) =>
let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData),
NextList = Json[holidays],
NextPageUrl = Json[nextPageLink],
Result = if @NextPageUrl = "" then @NextList else @NextList & @GetJson(@NextPageUrl)
in Result,
Output = GetJson(StartUrl),
Table = Table.FromList(Output, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
HolidayDetails = Table.ExpandRecordColumn(Table, "Column1", {"id", "startDate", "startType", "endDate", "endType", "userId", "userName", "requestedById", "leaveTypeId", "duration", "deduction", "actionerId", "createdAt", "updatedAt", "reason", "declineReason", "status", "autoApproved", "bookingUnit", "leaveType"}, {"id", "startDate", "startType", "endDate", "endType", "userId", "userName", "requestedById", "leaveTypeId", "duration", "deduction", "actionerId", "createdAt", "updatedAt", "reason", "declineReason", "status", "autoApproved", "bookingUnit", "leaveType"}),
#"Changed Type" = Table.TransformColumnTypes(HolidayDetails,{{"endDate", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"endDate", type date}, {"startDate", type datetime}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"startDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Dates", each List.Dates([startDate],[#"duration"],Duration.From(1))),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
#"Added Custom1" = Table.AddColumn(#"Expanded Dates", "DayOfWeek", each Date.DayOfWeek([Dates],Day.Monday)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([DayOfWeek] <> 5 and [DayOfWeek] <> 6)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "WeekCom", each Date.StartOfWeek([Dates])),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom2", "DaysCalc", each if [deduction] >= 1 then 1 else if [deduction] < 1 then [deduction] else null),
#"Added Custom3" = Table.AddColumn(#"Added Conditional Column", "Hours", each ([DaysCalc])*7),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom3", each true),
#"Grouped Rows" = Table.Group(#"Filtered Rows1", {"userName", "Dates"}, {{"Total Hours", each List.Sum([Hours]), type number}})
in
#"Grouped Rows"
