Accessing a Record from API Source with PowerQuery

[powerbitotheppl]

Hello!

I just posted this in the Developer forum, but realized it might be better suited here. I'll delete the duplicate shortly

I have built a custom connector for an API that sends its pagination data back in the body of the response, rather than the header. The way to use GetNextLink using header is documented in the GitHub custom connector example:

https://github.com/microsoft/DataConnectors/blob/master/samples/Github/github.pq 

GetNextLink = (response, optional request) =>
    let
        // extract the "Link" header if it exists
        link = Value.Metadata(response)[Headers][#"Link"]?,
        links = Text.Split(link, ","),
        splitLinks = List.Transform(links, each Text.Split(Text.Trim(_), ";")),
        next = List.Select(splitLinks, each Text.Trim(_{1}) = "rel=""next"""),
        first = List.First(next),
        removedBrackets = Text.Range(first{0}, 1, Text.Length(first{0}) - 2)
    in
        try removedBrackets otherwise null;

Getting to the pagination data in the body of the reponse should be easier. But I am stumped.  In the images, you can see the record I am trying to access that comes back through Web.Contents()

This is my code for the GetNextLink function. I am trying to get the "next" record above.

GetNextLink = (response) =>
// response is data already run through Web.Contents()

let
    Source = Lines.FromBinary(response),
    nextPage = Record.Field(Source[paging], "next")
in 
    try nextPage otherwise null;

I get the error back "We cannot convert a value of type Record to type Text". I feel like I am not getting the 'Source" correctly? I could certainly use help as I am a PowerQuery newbie!

Thanks! 

[v-yingjl · Accepted solution]

Hi @powerbitotheppl ,

Please check ther return result of this query from your post:

Source = Lines.FromBinary(response)

In addition, if this query returns correct corresponding result, try to check this one:

Source = Lines.FromBinary(response)[paging]

This query should return a record in Power Query since you have used Record.Field() to extract the special row value from it.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.