Forum Discussion
Canvas LMS pagingation problem
I am trying to retrieve the gradebook from Canvas LMS. I asked ChatGPT to generate the code, and the code successfully retrieved the first page. However, the code which includes pagination gives this error.
Expression.Error: We cannot convert a value of type Binary to type Record.
Details:
Value=[Binary]
Type=[Type]
and this is the code
let
// Define Canvas API information
BaseUrl = "https://<canvas-domain>/api/v1/",
CourseId = "<course_id>", // Replace with the course ID
Endpoint = "courses/" & CourseId & "/enrollments",
ApiToken = "<your_api_token>", // Replace with your API token
// Function to retrieve a single page of data
GetPage = (url as text) as table =>
let
Response = Web.Contents(url, [
Headers = [
Authorization = "Bearer " & ApiToken
]
]),
// Convert binary response to JSON
ResponseJson = Json.Document(Response),
// Convert JSON to a table
Data = Table.FromList(ResponseJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedData = Table.ExpandRecordColumn(Data, "Column1", {"user_id", "course_id", "grades", "role", "user"}, {"User ID", "Course ID", "Grades", "Role", "User"})
in
ExpandedData,
// Function to retrieve next page URL
GetNextPageUrl = (responseHeaders as record) =>
let
// Extract the Link header
LinkHeader = Record.FieldOrDefault(responseHeaders, "Link", ""),
// Debugging step: Output the raw Link header for inspection
DebugLinkHeader = LinkHeader,
// Parse the next page URL from the Link header
NextUrl = try Text.Middle(Text.BetweenDelimiters(LinkHeader, "<", ">")) otherwise null
in
NextUrl,
// Function to retrieve all pages
GetAllPages = (url as text) as table =>
let
InitialPage = GetPage(url),
FetchPages = List.Generate(
() => [CurrentUrl = url, CurrentPage = InitialPage],
each [CurrentUrl] <> null,
each [
CurrentUrl = GetNextPageUrl(Web.Contents([CurrentUrl])),
CurrentPage = if [CurrentUrl] <> null then GetPage([CurrentUrl]) else null
],
each [CurrentPage]
),
AllPages = Table.Combine(FetchPages)
in
AllPages,
// Full API URL
ApiUrl = BaseUrl & Endpoint,
// Retrieve all data
GradebookData = GetAllPages(ApiUrl),
// Expand nested fields (e.g., grades and user information)
ExpandedGrades = Table.ExpandRecordColumn(GradebookData, "Grades", {"current_score", "final_score", "current_grade", "final_grade"}, {"Current Score", "Final Score", "Current Grade", "Final Grade"}),
ExpandedUser = Table.ExpandRecordColumn(ExpandedGrades, "User", {"id", "name", "sortable_name", "email"}, {"User ID", "Name", "Sortable Name", "Email"}),
// Remove unnecessary columns and reorder for clarity
FinalTable = Table.SelectColumns(ExpandedUser, {"User ID", "Name", "Sortable Name", "Email", "Current Score", "Final Score", "Current Grade", "Final Grade", "Role"})
in
FinalTable
I did some tests, and the error occurred when calling the GetNextPage( ) function. I'm unsure how to troubleshoot within the user function. Can anyone suggest what might be causing the error?
I found another thread on this forum with similar problem.
Solved: Retrieving headers from API response using Value.M...
The Power Query Web.Contents() function only returns a subset of the header which does include the Link field 😞
3 Replies
- gancw1Resolver II
I found another thread on this forum with similar problem.
Solved: Retrieving headers from API response using Value.M...
The Power Query Web.Contents() function only returns a subset of the header which does include the Link field 😞 - AnonymousNot applicable
Hi,gancw1 .I am glad to help you.
According to the error message
“We were unable to convert a binary type value to a record type”, the problem may be in the GetNextPageUrl function!
You may need to check your data types!Perhaps the Value.Metadata Function can help you:
Value.Metadata - Power BI Online Training
below is my suggestions:
Checking the Return Value of Web.Contents: Make sure that the response returned by Web.Contents is in the format you expect. You can check the contents of Web.Contents by outputting its results while debugging. For example:Response = Web.Contents(url, [ Headers = [ Authorization = "Bearer " & ApiToken ] ]), ResponseJson = try Json.Document(Response) otherwise null2. Try to modify your original code (please make a backup of your original code before modification to avoid data loss)
GetAllPages = (url as text) as table => let InitialPage = GetPage(url), FetchPages = List.Generate( () => [CurrentUrl = url, CurrentPage = InitialPage], each [CurrentUrl] <> null, each [ Response = Web.Contents([CurrentUrl]), ResponseHeaders = Value.Metadata(Response), CurrentUrl = GetNextPageUrl(ResponseHeaders), CurrentPage = if [CurrentUrl] <> null then GetPage([CurrentUrl]) else null ], each [CurrentPage] ), AllPages = Table.Combine(FetchPages) in AllPagesThe modified code adds the following section:
ResponseHeaders = Value.Metadata(Response),Convert the result of Web.Contents to a record type and pass it to the GetNextPageUrl function.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- gancw1Resolver IIHi Carson,First and foremost, thank you for your assistance in troubleshooting the code and suggesting the necessary changes. I truly appreciate your support.I've also been working on the troubleshooting and reached the same conclusion that the code passes the body instead of the header metadata to GetNextPageUrl(). I went ahead and made the change, which indeed fixed the initial error. However, the code only returns the first page and is unable to navigate to the next page.Upon examining the header returned, I noticed that it does not include the 'Link' field which contains the url for the next pageInterestingly, when I checked the header using Postman, I observed that the header contains 32 fields, and the Link field is present there.Any insights or suggestions on why the returned header does not have the Link field and how to get it to return the full header ? Thanks again for your continued support!
ThanksGan