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.

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

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

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.