DataSource.Error: Web.Contents failed to get contents from {URL} (404): Not Found; works in Postman

[JeffManley]

I'm receiving a 404 error when adding an offset token to the URL to grab the next page in the Power Query Editor; it works in Postman and CURL and returns the next 50 records without any errors. I'm using basic auth. 

The Error:

DataSource.Error: Web.Contents failed to get contents from 'https://api.lever.co/v1/opportunities&offset=%5B3%2C1631586093867%2C%228fe605b4-50ce-4053-904a-508c72fd4c19%22%5D' (404): Not Found Details: DataSourceKind=Web DataSourcePath=https://api.lever.co/v1/opportunities&offset=%5B3%2C1631586093867%2C%228fe605b4-50ce-4053-904a-508c72fd4c19%22%5D Url=https://api.lever.co/v1/opportunities&offset=%5B3%2C1631586093867%2C%228fe605b4-50ce-4053-904a-508c72fd4c19%22%5D

Here is the curl that works:

curl --location --request GET 'https://api.lever.co/v1/opportunities?offset=%5B3%2C1631586093867%2C%228fe605b4-50ce-4053-904a-508c72fd4c19%22%5D' --header 'Authorization: Basic {token here}'

Here is the Power BI code:

let
    baseuri = "https://api.lever.co/v1/opportunities",
    initReq = Json.Document(Web.Contents(baseuri)),
    initData = initReq[data],

    gather = (data as list, uri) =>
        let
            //get new offset from active uri
            newOffset = Json.Document(Web.Contents(uri))[next],
            //build new uri using the original uri so we dont append offsests
            newUri = baseuri & "&offset=" & newOffset,
            //get new req & data
            newReq = Json.Document(Web.Contents(newUri)),
            newdata = newReq[data],
            //add that data to rolling aggregate
            data = List.Combine({data, newdata}),
            //if theres no next page of data, return. if there is, call @gather again to get more data
            check = if newReq[next] = null then data else @gather(data, newUri)
        in check,
    //before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
    outputList = if initReq[next] = null then initData else gather(initData, baseuri),
    //then place records into a table. This will expand all columns available in the record.
    expand = Table.FromRecords(outputList)
in
    expand

I'm new to Power BI / Power Query. What am I doing wrong?

Thanks!!

[v-yingjl · Accepted solution]

Hi @JeffManley ,

Check these parts in the query whether they work:

initReq = Json.Document(Web.Contents(baseuri)),

newOffset = Json.Document(Web.Contents(uri))[next],

newReq = Json.Document(Web.Contents(newUri)),

Consider the basic authentication method in Power Query, you can refer this article to encode it in the query:

Encoding basic authentication in an url with Power Query/M language 

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.