QuizAttemptsDetail Query Fails to load - Power BI

In the June 2022 Power BI Starter Kit, some clients may encounter an issue where their QuizAttemptsDetail query fails to load.

Here is an example of what the error may look like,

LocalImage.png

 

1. In order to fix this behavior, please go to the Power BI Report, and then select the Transform Data button at the top.
mceclip0.png

 

2. From here, find the affected table from the error message. In this case, the table is "QuizAttemptsDetail" query and select "Advanced Editor"
mceclip2.png

 

3. In the new window, replace the existing query with the query below.

 

let
APIUrlParamValue = #"API URL",
RawQuizAttempts = OData.Feed(APIUrlParamValue &"/QuizAttempts?$filter=PassingTimeTicks le 86400000000 and CreatedAt ge 2021-06-01&$select=Id, EnrollmentId, QuizId, State", null, [Implementation="2.0"]),
#"Renamed Columns" = Table.RenameColumns(RawQuizAttempts,{{"Id", "QuizAttemptId"}}),
#"Parsed JSON" = Table.TransformColumns(#"Renamed Columns",{{"State", Json.Document}}),
#"Expand State" = Table.ExpandRecordColumn(#"Parsed JSON", "State", Record.FieldNames(Record.Combine(List.Select(Table.Column(#"Parsed JSON", "State"), (x) => x <> null)))),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expand State", {"QuizAttemptId", "EnrollmentId", "QuizId"}, "Attribute", "Value"),
#"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Other Columns", "Value", {"Percent", "AnswerResult"}, {"Percent", "AnswerResult"}),
#"Expanded AnswerResult" = Table.ExpandRecordColumn(#"Expanded Value", "AnswerResult", {"Answer", "QuestionType", "PositionX", "PositionY", "Answers"}, {"AnswerResult.Answer", "AnswerResult.QuestionType", "AnswerResult.PositionX", "AnswerResult.PositionY", "AnswerResult.Answers"}),
#"Expanded AnswerResult.Answers" = Table.ExpandListColumn(#"Expanded AnswerResult", "AnswerResult.Answers"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded AnswerResult.Answers",{{"Percent", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "IsCorrectAnswer", each if [Percent] = 100 then 1 else if [Percent] > 0 then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"IsCorrectAnswer", Int64.Type}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "IsIncorrectAnswer", each if [Percent] = 0 then 1 else 0),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"IsIncorrectAnswer", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "QuizAttemptQuestionsCounter", each 1),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"QuizAttemptQuestionsCounter", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Attribute", "QuestionId"}}),
fieldForRec = Table.AddColumn(#"Renamed Columns1","Rec",each if Value.Is([AnswerResult.Answer], type record) then [AnswerResult.Answer] else null,type record),
#"Expanded Rec" = Table.ExpandRecordColumn(fieldForRec, "Rec", {"_gap1", "_gap2"}, {"Rec._gap1", "Rec._gap2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Rec",{{"Rec._gap1", "AnswerResult.Answer._gap1"}, {"Rec._gap2", "AnswerResult.Answer._gap2"}}),
#"Merged Columns" = Table.CombineColumns(#"Renamed Columns2",{"AnswerResult.Answer._gap1", "AnswerResult.Answer._gap2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Added Conditional Column2" = Table.AddColumn(#"Merged Columns", "Custom", each if Value.Is([AnswerResult.Answer], type record) then [Merged] else [AnswerResult.Answer]),
#"Renamed Columns3" = Table.RenameColumns(#"Added Conditional Column2",{{"Custom", "Answer"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Answer", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Merged", "AnswerResult.Answer"}),
fieldForList = Table.AddColumn( #"Removed Columns", "List",each if Value.Is([AnswerResult.Answers], type list) then [AnswerResult.Answers] else null,type list),
#"Expanded List" = Table.ExpandListColumn(fieldForList, "List"),
#"Added Conditional Column3" = Table.AddColumn(#"Expanded List", "Answers", each if [List] = null then [AnswerResult.Answers] else [List]),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Conditional Column3",{{"Answers", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type5",{"List", "AnswerResult.Answers"}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns1", {{"AnswerResult.PositionX", type text}, {"AnswerResult.PositionY", type text}}, "da-DK"),{"AnswerResult.PositionX", "AnswerResult.PositionY"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"PositionXPositionY"),
#"Added Conditional Column4" = Table.AddColumn(#"Merged Columns1", "Custom", each if [Answer] = null then [Answers] else [Answer]),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Custom.1", each if [Custom] = null then [PositionXPositionY] else [Custom]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column5",{"Answer", "Answers", "Custom"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns2",{{"Custom.1", "Answers"}}),
#"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns4",{{"Answers", type text}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type6",{"PositionXPositionY"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns3",{{"Answers", "GivenAnswers"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns5", {"GivenAnswers", "QuestionId"}, RightAnswerToAnswerOption, {"AnswerOption", "QuestionId"}, "RightAnswerToAnswerOption", JoinKind.LeftOuter),
#"Expanded RightAnswerToAnswerOption" = Table.ExpandTableColumn(#"Merged Queries", "RightAnswerToAnswerOption", {"RightAnswerText"}, {"RightAnswerText"}),
#"Renamed Columns6" = Table.RenameColumns(#"Expanded RightAnswerToAnswerOption",{{"RightAnswerText", "AnswerText"}})
in
#"Renamed Columns6"

4. Save and apply your changes and your query should load properly now and the report.

 

NOTE   

Sometimes, even after updating the query, you might still encounter the same error as shown above. This indicates that certain quiz attempts were made using mobile phones, resulting in null values in the State parameter. As a solution, you can adjust the query using the Advanced Query editor to exclude Quiz Attempts with this issue. To accomplish this, access the Advanced Query Editor for the Quiz Attempt Details query and add "State ne null and" between the "$filter=" and "PassingTimeTicks" lines in the query.

image

However, you should be aware that those attempts will be omitted from the report.

To facilitate the incorporation of these updates, could you kindly undertake the subsequent steps:

  1. Retain the existing version of the LMS365 mobile app without deletion.
  2. Proceed to install the most recent version.
  3. For each course containing quizzes with absent inputted answers within the mobile app, kindly guide the users to the Course Home Page. They should remain on this page until the course content has fully loaded. By following this procedure, the previously entered answers will synchronize with LMS365 (Learn365).

Please note that this process necessitates manual action on the part of learners who are affected.

Contact LMS365 Support if you need additional clarification regarding this process.

Was this article helpful?
1 out of 1 found this helpful

Comments

Article is closed for comments.