Downloadable Document also attached

When a report is extracted to Excel via the API, by default only the first 200 rows of the report will be extracted. There is however a method to pull out the entire result set if there are more than 200 rows in the report.

If you are not aware of how to extract a report in Excel via API integration then please first refer to the knowledgebase article named ‘API – How to load a report into Excel’

Excel 2016 is being used in this example, so if you are using any other version of excel, the screenshots may vary.

Running a report that contains over 200 records from the KIP instance….

When the report is loaded into Excel via the API only the first 200 rows will have been loaded

1. From the Home tab, select the Advanced Editor option.

The following screen will show:

The above script should be run to pull out the desired results via the API for the report queried.

This is the script that will be amended to extract the entire result set.

Below is the script which has been amended to extract more than 200 records.

This script can be used, however, some changes will be required to allow this script to work with your report.

The code that needs changing on the script:

BaseURL - the report key will need to be amended to match your report key

Expanded Column1 – This contains the fields from the report, this can be copied from the advance editor screen which was shown earlier and replaced with the code in this script

let

    BaseUrl = "https://api.keyedinprojects.co.uk/V3/api/report?key=44",

    GetJson = (Url) =>

        let RawData = Web.Contents(Url),

            Json = Json.Document(RawData)

        in  Json,

        GetTotalEntities = (Page) =>

        let Total = Page[TotalPages]

        in  Total,

    GetPage = (Index) =>

        let Url   = GetUrl(Index),

            Json  = GetJson(Url)

        in  Json,

    GetUrl = (Index) =>

        let Url   = BaseUrl & "&pageNumber=" & Text.From(Index)

        in  Url,

     FirstPage = GetPage(1),

    EntityCount = GetTotalEntities(FirstPage),

    PageIndices = { 1 .. EntityCount },

       URLs        = List.Transform(PageIndices, each GetUrl(_)),

    Pages       = List.Transform(PageIndices, each GetPage(_)[Data]),

    Data = List.Union(Pages),

    #"Converted to Table" = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Resource Name", "Resource Code", "Resource Active"}, {"Column1.Resource Name", "Column1.Resource Code", "Column1.Resource Active"})

in 

#"Expanded Column1"

Copy the amended script and paste it into the advance editor screen, you should have something similar to below:

When the script is copied, please check there are no syntax errors, if no errors then please select ‘Done’

The API for the report will run again and the report will now extract all the results in the report, as you can see now there are 320 rows showing whereas before it had only extracted 200 rows.

The results can now be displayed in Excel table format by clicking on ‘Close & Load’