Dynamic URL Refresh (Relative path)

[ElvirBotic]

Hello, I am using an API to pull in data from an API provide by a third party tool we use. I have the API link and a bearer token I pass to use to pull the data. It rturns multiple pages. I then came across an article by Mark Tiedemann which tells power query to loop through a bunch of pages from the API and then append all the pages into one. It works great until you want to use it in the service. I see there are ways to overcome this by using relative path, but I cannot seem to make it work. I pasted the sample code provided in the article to pull the data from the API I just need help figuring out how to adjust the URL to allow refresh in the service. 


let
BaseUrl = "https://fake-odata-api.com/v1/Entities?",
Token = "F4K3-T0K3N-D0NT-U5E-L0L",
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 & "$count=true&$top=0",
Json = GetJson(Url),
Count = Json[#"@odata.count"]
in Count,

GetPage = (Index) =>
let Skip = "$skip=" & Text.From(Index * EntitiesPerPage),
Top = "$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Skip & "&" & Top,
Json = GetJson(Url),
Value = Json[#"value"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table

[lbendlin]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[ElvirBotic]

I've tried didfferent variations, but I can't seem to make it work. 

[lbendlin · Accepted solution]

let
BaseUrl = "https://fake-odata-api.com/v1/Entities",
Token = "F4K3-T0K3N-D0NT-U5E-L0L",
EntitiesPerPage = 1000,

GetJson = (Url,Query) =>
let Options = [Query=Query, Headers=[ #"Authorization" = "Bearer " & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,

GetEntityCount = () =>
let 
Json = GetJson(BaseUrl,[$count="true",$top="0"),
Count = Json[#"@odata.count"]
in Count,

GetPage = (Index) =>
let Json = GetJson(BaseUrl,[$skip= Text.From(Index * EntitiesPerPage),$top= Text.From(EntitiesPerPage)]),
Value = Json[#"value"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table

Warning: you use an OData source rather than a standard API.  Restrictions apply.

Using OData.Feed and the Query option to avoid the dynamic data sources error in Power BI (crossjoin...