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])
            );