Forum Discussion
Dynamic URL Refresh (Relative path)
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
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 TableWarning: you use an OData source rather than a standard API. Restrictions apply.
3 Replies
Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
I've tried didfferent variations, but I can't seem to make it work.
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 TableWarning: you use an OData source rather than a standard API. Restrictions apply.
