Power Queryのエラー対処事例③

前回は「ステップレベルエラー」のうち、列が見つからないエラー及びFormula.Firewallに関するエラー、それらの対処法(列のデータ型のダイナミック指定等)について見ていきました。

marshal115.hatenablog.com

今回は「セルレベルエラー」について見ていきます。「ステップレベル」エラーとは異なり、プレビュー画面の一部でエラーが起こっているため、データを読み込むまで気づかないことが特徴です。

エラーの種類

エラーの種類と公式ドキュメントは以下の通り。

  • ステップレベルエラー
    特徴:クエリの評価が止まってしまう

  • セルレベルエラー
    特徴:クエリの評価は止まらないことが多いが、データモデルへインポートした際やExcelワークシートへ出力しようとする際にエラーが発生する可能性あり

  • 公式ドキュメント

セルレベルエラーとその対処事例

値エラー① -出力後セル空白

Excelのセルの概念(同じ列でも異なるデータ型を入力可能)とは違い、Power Queryはデータベースの考え方であり、1つの列に1つのデータ型を指定することが基本的な考え方です。Excelユーザーが最も慣れない部分の1つがデータ型を指定することですが、Power Queryを使用する目的がデータモデルの構築及びBIレポートの生成であれば、データ型を指定することは必須です。

前回の記事ではデータ型を指定した列が存在しなかった場合、ステップレベルでエラーが発生することを見ていきましたが、今回は”列は存在するが、指定した型がテキストと数値の両方を含む場合に、セル単位でエラーが発生”するところを見ていきます。

例えば以下のクエリがあります。

//クエリ名: ValueError
let ソース = Table.FromRecords( { [年月 = "201809", 商品 = "CA-J", 店舗 = 203, 数量 = 60, 定価売上 = 804, 売上高 = 804, 売上原価 = 360], [年月 = "201809", 商品 = "OP-J", 店舗 = "X02", 数量 = 34, 定価売上 = 816, 売上高 = 780, 売上原価 = 272], [年月 = "201809", 商品 = "AX-P", 店舗 = "456", 数量 = 20, 定価売上 = 986, 売上高 = 800, 売上原価 = 333] } ) in ソース

結果プレビュー

f:id:marshal115:20211229181323p:plain

店舗(=店舗コード)という列が新たに追加されていますが、レコードとして指定した値が203(数値)、"X02"(テキスト)、"456"(テキスト)で構成されており、プレビュー画面で見ても、数値は右寄り、テキストは左寄りになっているのが分かります。

データは3行しかないので、店舗列はテキスト型になると瞬時に分かるのですが、Power Queryのデータ型の自動設定をクリックしてみると以下のようになります。

f:id:marshal115:20211229182510p:plain

見ての通り、型の指定はtype anyとなっています。Power Queryは通常、プレビュー画面で先頭200行までデータ型を自動的に検出して設定を行います(詳細は下記より)が、この例では数字とテキストが混在しているため、type any(任意 or 全ての値を分類するデータ型)として設定されてしまいました。

docs.microsoft.com

これは言い換えれば、Power Queryはどのデータ型にすべきか悩んで決めかねていることであり、例えば整数型にすると以下のようにエラーが発生するため、エンジンがこれを避けるため任意のデータ型(type any)として設定をしていることになります。ある意味非常にスマートですが、本来であればtype textに設定してほしかったのを考えると、実はそこまで賢いというわけでもない?のかもしれません。

f:id:marshal115:20211229183221p:plain

この例では、type textベースでデータ型を指定するほかなく、Power Queryに任せるのではなく、人間が判断したほうが良いことになります。

f:id:marshal115:20211229183336p:plain

データが多い(あるいは列数が多い)クエリの場合、このような判断は人間ではなくPower Queryに任せたほうが便利な場合が多いが、Power Queryが間違ってデータ型をしてしまう可能性が高いことに留意する必要があります。

なお、セルレベルエラーはステップエラーとは違い、シート等へ出力できることも多く、出力した場合は以下のようになります。エラー数と出力後にセルが空白になっているところが特徴です。

f:id:marshal115:20211229183850p:plain

値エラー② -結果を出力できない

以下の例はステップレベルのエラーであるものの、出力ができずエラーメッセージが出現してしまうパターンです。

クエリは以下のようになっており、最後のステップまで処理が完了した状態です。

f:id:marshal115:20211229184248p:plain

ワークシートに出力しようとすると、以下のメッセージが出現します。

f:id:marshal115:20211229184402p:plain

データベースからデータを取り出せません

というのがキーワードですが、実は結構やっかいで、メッセージを見ただけでは何がエラーの要因になっているかまったく不明のため、デバッグのやり方がポイントとなります。以下のステップを踏めばエラーを特定できるようになります。

① プレビュー画面のセルを(どこでも良いので)クリックし、Ctrl + Aを押す(全てのセルがハイライトされる)

② ホーム > 行の保持 > エラーの保持

f:id:marshal115:20211229184930p:plain

③ エラーメッセージが出現

f:id:marshal115:20211229184956p:plain

さっきより少し分かりやすいメッセージが出現しました。どうやら「Date」が関連しており、「登録フォーマット」という名称を含む何かが悪さをしているようです。

④ 「年月」列を見てみると、[DataFormatError]というものがエラーの原因となっており、「年月」列にtype date(日付型)以外の何かが混ざっていると推測されます。

f:id:marshal115:20211229185131p:plain

⑤ 「年月」列をクリックし、ホーム > 行の保持 > エラーの保持

f:id:marshal115:20211229185345p:plain

⑥ 年月列でErrorとなっている項目が出現(下図左側)

f:id:marshal115:20211229185446p:plain

上図の左側ではファイルの一番前にYYYYMMMDD(年月日)が入っており、右側では一番右になっています。右側が正しい結果ですので、ファイル名にあるYYYYMMDDを一番右に持ってくることでエラーを解消できることになります。

このように、エラーメッセージが不明確な場合、クエリエディタから自分でデバッグしないといけないのですが、経験上、上述のやり方を行えば殆どのエラー要因を探ることができるようになります。

値エラー③ -追加した列が全てエラー

追加した列が全てエラーになるケースもあります。例えば、下図のようなケースです。

f:id:marshal115:20211230190600p:plain

//クエリ名: CalculationError_01
let ソース = Table.FromRecords( { [年月 = "201809", 商品 = "CA-J", 店舗 = 203, 数量 = 60, 定価売上 = 804, 売上高 = 804, 売上原価 = 360], [年月 = "201809", 商品 = "OP-J", 店舗 = "X02", 数量 = 34, 定価売上 = 816, 売上高 = 780, 売上原価 = 272],
       [年月 = "201809", 商品 = "XX-X", 数量 = 0, 定価売上 = 0, 売上高 = 100, 売上原価 = 0],
[年月 = "201809", 商品 = "AX-P", 店舗 = "456", 数量 = 20, 定価売上 = 986, 売上高 = 800, 売上原価 = 333] } )
   全てテキスト化 = Table.TransformColumns(ソース, {}, Text.From),
    販売単価の計算 = Table.AddColumn(全てテキスト化, "販売単価", each [売上高] / [数量])
in
  販売単価の計算

この例は非常に単純で、ソースクエリを下記M式で全ての列をテキストに変換した後、

Table.TransformColumns(ソース, {}, Text.From)

数字列であるべき「売上高」と「数量」で「販売単価」を算出しています。Power Queryはテキストと数字を厳密に分けて考える必要があるため、この場合、エラーが出現してしまいます。

この例は非常にシンプルで対処法は複数ありますが、まずは「販売単価の計算」というステップの前にデータ型の設定を行うことでエラーを回避することができます(詳細は前回記事)。

marshal115.hatenablog.com
個人的には

データ型の指定は最後の最後

というのを心掛けているので、他の列をタッチすることなく、下記のような数式(Number.From)を持ってきて列同士が計算できるようにしておくやり方もあります。

//クエリ名: CalculationError_01
let ソース = Table.FromRecords( { [年月 = "201809", 商品 = "CA-J", 店舗 = 203, 数量 = 60, 定価売上 = 804, 売上高 = 804, 売上原価 = 360], [年月 = "201809", 商品 = "OP-J", 店舗 = "X02", 数量 = 34, 定価売上 = 816, 売上高 = 780, 売上原価 = 272],
       [年月 = "201809", 商品 = "XX-X", 数量 = 0, 定価売上 = 0, 売上高 = 100, 売上原価 = 0],
[年月 = "201809", 商品 = "AX-P", 店舗 = "456", 数量 = 20, 定価売上 = 986, 売上高 = 800, 売上原価 = 333] } )
   全てテキスト化 = Table.TransformColumns(ソース, {}, Text.From),
    販売単価の計算 = Table.AddColumn(全てテキスト化, "販売単価", each
Number.From([売上高]) / Number.From([数量]), type number)
in
  販売単価の計算

結果プレビュー

f:id:marshal115:20211230192315p:plain

ご覧の通り、販売単価という列がきちんと算出された状態となっています。ただし、この場合、他の列がまだテキスト型になっていますので、結局のところ、ステップをもう一つ追加してデータ型の指定が必要になってきます。このやり方はあくまで好みの問題ですので、前述した方法(先に全ての列のデータ型を決めてから、「販売単価」列を追加)のどちらを採用しても問題はありません。

値エラー④ -メジャーが算出されない

上記算出された「販売単価」ですが、infinityという表記になっていますが、これは数値をゼロで割った場合に出現する表記になります。

f:id:marshal115:20211230193143p:plain

クエリエディタ上ではこのように見えますが、Excelワークシートに出力すると#NUM!エラーとして出現します。

f:id:marshal115:20211230193239p:plain

同様に、ゼロ同士を割った場合はNaNという表記になり、シートへ出力した場合、同じく#NUM!エラーとなります。

f:id:marshal115:20211230193348p:plain

どちらの例でもステップが止まったり、出力できなかったりの現象はないものの、データモデルにデータを入れた後に問題が発生します。これを確認するため、当該クエリをデータモデルに読み込みます。

f:id:marshal115:20211230194257p:plain

インポートが完了すると、以下①~⑥を行います。

①~② Power Pivotを開く

f:id:marshal115:20211230194923p:plain

③ [単価合計]というメジャーを作る

f:id:marshal115:20211230194957p:plain

④ Pivotを作る

f:id:marshal115:20211230195004p:plain

⑤ シートを指定

f:id:marshal115:20211230195011p:plain

⑥ 年月×商品という切り口で、[単価合計]を見る

f:id:marshal115:20211230195017p:plain

見ての通り、エラーとなっているセル(infinity)は計算ができず、結果的にその商品だけでなく、総計までも#NUM!エラーとなってしまいます。これを防ぐためには

クエリエディタでinfinityNaNの発生を未然に防ぐ

ことが必要となります。

f:id:marshal115:20211230202123p:plain

infinityは実質的にという文字列を取っているため、以下のように置換を行いますが、この場合、計算結果にinfinityが発生することを予め予想する必要があります。

f:id:marshal115:20211230195653p:plain

一方、「値の置換」というステップを入れたくない場合は、以下のIF構文を入れることで事前にエラーが発生しないように対処できます。

//クエリ名: CalculationError_01
let ソース = Table.FromRecords( { [年月 = "201809", 商品 = "CA-J", 店舗 = 203, 数量 = 60, 定価売上 = 804, 売上高 = 804, 売上原価 = 360], [年月 = "201809", 商品 = "OP-J", 店舗 = "X02", 数量 = 34, 定価売上 = 816, 売上高 = 780, 売上原価 = 272],
       [年月 = "201809", 商品 = "XX-X", 数量 = 0, 定価売上 = 0, 売上高 = 100, 売上原価 = 0],
[年月 = "201809", 商品 = "AX-P", 店舗 = "456", 数量 = 20, 定価売上 = 986, 売上高 = 800, 売上原価 = 333] } )
   全てテキスト化 = Table.TransformColumns(ソース, {}, Text.From),
    販売単価の計算 = Table.AddColumn(全てテキスト化, "販売単価",
   each if [数量] = "0" then 0 else Number.From([売上高]) / Number.From([数量]) , type number)
in
  販売単価の計算

infinityとは違い、ゼロをゼロで除算した場合はNaNが発生しますが、この場合もif..then..else構文で事前に解決できます。が、このNaNが結構やっかいで、∞のように「値の置換」では対処ができないのです。

よって、NaNは事前処理(IF構文の使用)を行うことでその発生を防ぐことが重要ですが、以下のようにNumber.IsNaNというM関数を使っても対処ができます。この関数は馴染みがないと思いますので、予備知識として覚えておく程度で良いでしょう。

f:id:marshal115:20211230203249p:plain

まとめ

  • ステップレベルエラーとは異なり、セルレベルエラーはユーザーが自分でデバッグを行う必要がある
  • デバッグのやり方はいくつかあるが、エラーに慣れることも重要である
  • エラーは場合によってはデータモデルにデータがインポートされてからでないと気づかないものもある
  • エラーの対処法は1つだけでなく、複数存在する