How to retrieve beyond 1000 items using OData

Created by Joshua Miller, Modified on Tue, 25 Mar at 5:17 PM by Joshua Miller

In some instances, specialized power query is required to retrieve beyond the first 1000 items from the Stratawise OData endpoint.

Instructions

Copy the script below, updating the Tenant ID and Module Name. (Lines 5 & 13)

let    EntitiesPerPage = 1000,     GetEntityCount = () =>         let Json  = Json.Document(Web.Contents("https://core.stratawise.com/odata/350/MA@POU",  [Query=[#"$count"="true"]])),             Count = Json[#"value"]         in  Count,     GetPage = (Index) =>         let Skip  = Text.From(Index * EntitiesPerPage),             Top   = Text.From(EntitiesPerPage),             Json  =  Json.Document(Web.Contents("https://core.stratawise.com/odata/350/MA@POU", [Query=[#"$skip"=Skip, #"$top"=Top]])),             Value = Json[#"value"]         in  Value,     EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),     PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage), //1,     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

Connect to through the existing Odata connection provided through the export prompt in the module grid.
Select Transform Data.
Open image-20200803-211714.png
Select Advanced Editor.
Open image-20200803-211744.png
Paste the updated script from above and select Done.
Select the icon at the top of the data set to expand.
Open image-20200803-211957.png
Then OK.

Open image-20200803-212028.png
Select Advanced Editor again.
Copy the resulting script to a text editor and replace “column1.“ with ““ (nothing).
Paste the updated script in and validate the resulting columns appear correct.