API Pagination in JSON Body - How to Access with Power Query in Custom Connector?

Hello lbendlin,

Thank you! I managed to grab the next page and place it in the metadata as evidenced by the GetPage and GetNextLink functions below. I confirmed the metadata is there in PowerBI visually. However, when I use the connector in PowerBI, it does loop, but it is not grabbing the "next" metadata. It's stuck repeating on page 1. 

Do you have any idea what I might be doing wrong?  Any help is greatly appreciated!!

This is my .pq file for the custom connector.

// This file contains your Data Connector logic
section Vimeo_Connector;


// Vimeo OAuth2 values
client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
token_uri = "https://api.vimeo.com/oauth/access_token";
authorize_uri = "https://api.vimeo.com/oauth/authorize";
logout_uri = "https://login.microsoftonline.com/logout.srf";



// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;


[DataSource.Kind="Vimeo_Connector", Publish="Vimeo_Connector.UI"]
shared Vimeo_Connector.Contents = Value.ReplaceType(_Vimeo.Contents, type function (url as Uri.Type) as any);

/*
[DataSource.Kind="Vimeo_Connector"]
shared Vimeo_Connector.PagedTable = Value.ReplaceType(_Vimeo.PagedTable, type function (url as Uri.Type) as nullable table);
*/

// Data Source Kind description
Vimeo_Connector= [
    TestConnection = (dataSourcePath) => { "Vimeo_Connector.Contents", dataSourcePath },
    Authentication = [
        OAuth = [
            StartLogin=StartLogin,
            FinishLogin=FinishLogin,
            Refresh=Refresh,
            Logout=Logout
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

_Vimeo.Contents = (url as text) as table =>
    let
        a =  GetAllPages(url)
     in
        a;

GetPage = (url as text) as table =>
    let
        content = Json.Document(Web.Contents(url)),
        link = GetNextLink(content),
        table = Record.ToTable(content)
        
     in
        table meta [next=link];


GetNextLink = (response, optional request) =>
    let
        // extract the "Link" header if it exists
        link = (response)[paging][next]
    in
        try link otherwise null;


// Data Source UI publishing description
Vimeo_Connector.UI = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = Vimeo_Connector.Icons,
    SourceTypeImage = Vimeo_Connector.Icons
];

// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
            response_type = "code",
            client_id = client_id,  
            redirect_uri = redirect_uri,
            state = state
           // scope = GetScopeString(scopes, scope_prefix)
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];

FinishLogin = (context, callbackUri, state) =>
    let
        // parse the full callbackUri, and extract the Query string
        parts = Uri.Parts(callbackUri)[Query],
        // if the query string contains an "error" field, raise an error
        // otherwise call TokenMethod to exchange our code for an access_token
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;

Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);

Logout = (token) => logout_uri;



// see step 4 access token: https://developer.vimeo.com/api/authentication
TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = grantType,
            redirect_uri = redirect_uri,
            client_id = client_id,
            client_secret = client_secret
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        authKey = "Basic " & Binary.ToText(Text.ToBinary(client_id & ":" & client_secret),BinaryEncoding.Base64),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Authorization" = authKey,
                #"Content-Type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/vnd.vimeo.*+json;version=3.4"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Value.IfNull = (a, b) => if a <> null then a else b;

GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
    let
        prefix = Value.IfNull(scopePrefix, ""),
        addPrefix = List.Transform(scopes, each prefix & _),
        asText = Text.Combine(addPrefix, " ")
    in
        asText;
        

Vimeo_Connector.Icons = [
    Icon16 = { Extension.Contents("Vimeo_Connector16.png"), Extension.Contents("Vimeo_Connector24.png"), Extension.Contents("Vimeo_Connector32.png") },
    Icon32 = { Extension.Contents("Vimeo_Connector32.png"), Extension.Contents("Vimeo_Connector40.png"), Extension.Contents("Vimeo_Connector48.png"), Extension.Contents("Vimeo_Connector64.png") }
];

GetAllPages = (url as text) as table =>
    Table.GenerateByPage((previous) => 
        let
            // if previous is null, then this is our first page of data
            nextPageToken = if (previous = null) then null else Value.Metadata(previous)[next]?,
            // if NextLink was set to null by the previous call, we know we have no more data
            page = if (nextPageToken <> null) then GetPage(url) else if (previous = null) then GetPage(url) else null
        in
            page
    );


// The getNextPage function takes a single argument and is expected to return a nullable table
Table.GenerateByPage = (getNextPage as function) as table =>
    let        
        listOfPages = List.Generate(
            () => getNextPage(null),            // get the first page of data
            (lastPage) => lastPage <> null,     // stop when the function returns null
            (lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
        ),
        // concatenate the pages together
        tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
        firstRow = tableOfPages{0}?
    in
        // if we didn't get back any pages of data, return an empty table
        // otherwise set the table type based on the columns of the first page
        if (firstRow = null) then
            Table.FromRows({})
        else        
            Value.ReplaceType(
                Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
                Value.Type(firstRow[Column1])
            );

This is one of the best threads I've ever seen on this forum. I wish this was around 2 years ago when I had to solve this problem! 

That's actually simpler than if pagination is done in the header.  Parse your received body as JSON (which you likely are already doing) and grab the pagination fields (either offset, or nexturl, or whatever - depending on your API. Then stitch all results together as needed.