Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am working on pagination implementation for my web data sources in PowerBI. However, I read in various places that using this kind of connection disables refresh from within the service (ie scheduled refresh). I did not find a clear/recent source about the current verdict on this.
let
BaseUrl = "https://XXXX/report?",
Password = "XXXX",
Report = "sales",
Fields = "",
EntitiesPerPage = 1000,
GetJson = (Url) =>
let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "pw="& Password & "&report=" & Report &"&count=true&per_page="& Text.From(EntitiesPerPage),
Json = GetJson(Url),
Count = Json[#"total_pages"]
in Count,
GetPage = (Index) =>
let Page = "page=" & Text.From(Index),
PerPage = "per_page=" & Text.From(EntitiesPerPage),
Url = Url = BaseUrl & "pw="& Password & "&report=" & Report & "&page=" & Page & "&per_page=" & PerPage & "&fields=" & Fields,
Json = GetJson(Url),
Value = Json[#"data"]
in Value,
PageCount = GetEntityCount(),
PageIndices = { 1 .. PageCount },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
Above is the code I will be using for the pagination request, in a function in PowerQuery. Can this be refreshed in service?
Solved! Go to Solution.
Hi @Laila92 ,
It depends on the detail situation, you can upload it to Power BI Service and verify if it can configure scheduled refresh, we think if it does not work, the issue maybe url is dynamic , you can try to use following query which use query option to replace combine url manually, you can also use relative path, please refer to following document about query option and relative path: https://docs.microsoft.com/en-us/powerquery-m/web-contents
let
BaseUrl = "https://XXXX/report?",
Password = "XXXX",
Report = "sales",
Fields = "",
EntitiesPerPage = 1000,
GetJson = (QParm) =>
let
Options = [Headers=[ #"Authorization" = "Bearer " & Token ], Query = QParm],
RawData = Web.Contents(BaseUrl, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let
QParm = [pw=Password,report=Report,count="true",per_page=Text.From(EntitiesPerPage)],
Json = GetJson(QParm),
Count = Json[#"total_pages"]
in
Count,
GetPage = (Index) =>
let
Page = "page=" & Text.From(Index),
QParm = [pw=Password,report=Report,page=Page,per_page=Text.From(EntitiesPerPage),fields=Fields],
Json = GetJson(QParm),
Value = Json[#"data"]
in
Value,
PageCount = GetEntityCount(),
PageIndices = { 1 .. PageCount },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
Please also consider the limitation of you api, it may block the request if they are too frequency or reach the api limitation (such as number limitation per day)
Best regards,
Would you mind taking a look at my query for me?
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"
Hi @Laila92 ,
It depends on the detail situation, you can upload it to Power BI Service and verify if it can configure scheduled refresh, we think if it does not work, the issue maybe url is dynamic , you can try to use following query which use query option to replace combine url manually, you can also use relative path, please refer to following document about query option and relative path: https://docs.microsoft.com/en-us/powerquery-m/web-contents
let
BaseUrl = "https://XXXX/report?",
Password = "XXXX",
Report = "sales",
Fields = "",
EntitiesPerPage = 1000,
GetJson = (QParm) =>
let
Options = [Headers=[ #"Authorization" = "Bearer " & Token ], Query = QParm],
RawData = Web.Contents(BaseUrl, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let
QParm = [pw=Password,report=Report,count="true",per_page=Text.From(EntitiesPerPage)],
Json = GetJson(QParm),
Count = Json[#"total_pages"]
in
Count,
GetPage = (Index) =>
let
Page = "page=" & Text.From(Index),
QParm = [pw=Password,report=Report,page=Page,per_page=Text.From(EntitiesPerPage),fields=Fields],
Json = GetJson(QParm),
Value = Json[#"data"]
in
Value,
PageCount = GetEntityCount(),
PageIndices = { 1 .. PageCount },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
Please also consider the limitation of you api, it may block the request if they are too frequency or reach the api limitation (such as number limitation per day)
Best regards,
@v-lid-msft when using your solution, with fields"..." the format we are sending, we are not getting all the fields
Hi @Laila92 ,
How about the result after you follow the suggestions mentioned in my previous post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
The issue was in the variables i put in the link. I was formatting them with %20 for spaces and i should not have. So your solution worked!
Hi @Laila92 ,
Could you please try to verify what is difference between the request posted by the new query and origin? We also use the Relative Path option as fololwing, it should act as the same as combine url in origin query when post the request
let
BaseUrl = "https://XXXX/report?",
Password = "XXXX",
Report = "sales",
Fields = "",
EntitiesPerPage = 1000,
GetJson = (RParm) =>
let
Options = [Headers=[ #"Authorization" = "Bearer " & Token ], RelativePath = RParm,
RawData = Web.Contents(BaseUrl, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let
RParm = "pw="& Password & "&report=" & Report &"&count=true&per_page="& Text.From(EntitiesPerPage),
Json = GetJson(RParm),
Count = Json[#"total_pages"]
in
Count,
GetPage = (Index) =>
let
Page = "page=" & Text.From(Index),
RParm = "pw="& Password & "&report=" & Report & "&page=" & Page & "&per_page=" & PerPage & "&fields=" & Fields,
Json = GetJson(RParm),
Value = Json[#"data"]
in
Value,
PageCount = GetEntityCount(),
PageIndices = { 1 .. PageCount },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
Best regards,