Power BI：QuizAttemptsDetail クエリの読み込みに失敗する場合

更新 2023年01月05日 06:00

２０２２年６月の Power BI スターターキットでは、一部のクライアントで「QuizAttemptsDetail」クエリの読み込みに失敗する場合があります。

以下は、そのエラーの例です。

１．この動作を修正するためには、Power BI レポートに移動し、「Transform Data」ボタンを選択します。

２．まず、エラーメッセージから該当するテーブルを探します。
この場合、テーブルは " QuizAttemptsDetail "クエリです。テーブルを選択し、「Advanced Editor」を選択します。

３．新しいウィンドウで、既存のクエリを以下のクエリに置き換えます。

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"

４．保存して変更を適用すると、クエリが正しく読み込まれ、レポートに表示されます。

検索