Forum Discussion
Solved
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...
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.
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.