Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.