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.
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.