Power Queryのエラー対処事例②

前回の記事では「ソースデータエラー」について話をしました。今回はその続きとなります。

marshal115.hatenablog.com

エラーの種類
ステップレベルエラーとその対処事例
まとめ

エラーの種類

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

ステップレベルエラー
特徴：クエリの評価が止まってしまう
セルレベルエラー
特徴：クエリの評価は止まらないことが多いが、データモデルへインポートした際やExcelワークシートへ出力しようとする際にエラーが発生する可能性あり
公式ドキュメント

ステップレベルエラーとその対処事例

列名が見つからないエラー

列名が見つからないエラーは恐らく最もポピュラーなエラーですが、様々な時に発生します。一番有名なパターンが

データ型が変更された場合、その列名が存在しなかった

となります。

例えば、以下のように、複数の売上ファイルがあり、こちらを結合してみます。

f:id:marshal115:20211226163144p:plain

すると、「サンプルクエリによる結合」で左側に複数のクエリが生成され、最終クエリ「Sales」が作られます。

f:id:marshal115:20211226163535p:plain

ここで、「年月」という列の名前を「ヘルパークエリ」内の「サンプルファイルの変換」というクエリから変更してみると、Salesクエリに！というマークが表示されます。

f:id:marshal115:20211226163614p:plain

Salesクエリをクリックしてみると、下図の通り、「年月」という列が「Date」とう列名に変わっているにもかかわらず、データ型を変更するM式（Table.TransformColumnsTypesで「年月」という列に対して、整数型のデータタイプを適用しているため、エラーが発生したことになります。

f:id:marshal115:20211226163945p:plain

非常に普遍的なエラーですが、列名が存在しなかったことによるエラーの一例となります。このケースの場合、データ型が変更されるのは

データを結合した後の各列

に対して行われているため、「サンプルファイルの変換」というクエリから変更しないことが対処法となります。

データ型のダイナミックな設定方法

Power Queryを使う理由は様々ですが、仮にBIレポートを作るためのデータモデルを構築するのが目的であった場合、データ型の設定は必須となります。列数が少ない場合、個別に設定しても問題ないかもしれませんが、列数が多くなってきた場合、あるいは、データベースを使っていない場合、自分で列のデータ型を指定するのはかなり苦痛なものになってきます。

以下の構文は上図の続きですが、「データ型の指定」をダイナミックに実現しています。

クエリ「DynamicDataType_01」

let
    ソース = 
    Table.FromRecords(
      { [年月 = "201809", 商品 = "CA-J", 数量 = 60, 定価売上 = 804, 売上高 = 804, 売上原価 = 360],
        [年月 = "201809", 商品 = "OP-J", 数量 = 34, 定価売上 = 816, 売上高 = 780, 売上原価 = 272],
        [年月 = "201809", 商品 = "AX-P", 数量 = 20, 定価売上 = 986, 売上高 = 800, 売上原価 = 333]}
    ),
    データ型の指定 = 
    Table.TransformColumnTypes(ソース, 
      List.Transform(
        Table.ColumnNames(ソース), //全ての列に対して
        each if _ = "年月" or _ = "商品" then {_, type text} //もし年月 or 商品であればテキスト型 
        else {_, type number}　　//それ以外は全て数値型
      )
    )
in
    データ型の指定

結果プレビュー

f:id:marshal115:20211226192847p:plain

この例では年月 or 商品という列名であればテキスト型、それ以外は全て数値型に指定していますが、ポイントは

限定的な属性を持つ列（このデータ型しかない列）を最初に指定

して、それ以外はelseで指定することです。

Table.ColumnNames(ソース)はソースステップの列名をリスト化し、

{"年月","商品","数量","定価売上","売上高","売上原価"}

というリストに対して、List.Transformで（each）それぞれの列名 {_, に対して、type 〇}という型を指定していく。その際、「年月」もしくは「商品」という列名であればテキスト型（type text）、それ以外の列は全て数値型（type number）をアサインしていく。最終的に、下記2つのM式は同じ結果となります。

= Table.TransformColumnTypes(
     ソース,  //下式と同じ結果
      List.Transform( 
       Table.ColumnNames(ソース), 
       each if _ = "年月" or _ = "商品" then {_, type text}
       else {_, type number}
      )
)

= Table.TransformColumnTypes( //上式と同じ結果
     ソース,
      {
       {"年月", type text}, 
       {"商品", type text}, 
       {"数量", type number}, 
       {"定価売上", type number}, 
       {"売上高", type number}, 
       {"売上原価", type number}
      }
)

見ての通り、前者のほうは年月と商品という列名だけを指定しており、それ以外は列名に関係なく、全て数値列として指定できることから、列名をハードコーディングする必要がありません。

列型の指定（Table.TransformColumnTypes）は基本的に

最後に行うことがベストプラクティス（列に対する処理はメモリ消費が大きくなる）
最後のステップはデータモデルに必要な列だけで構成されている

という2点から考えても、List.Transformを使った列データ型の指定は非常に便利なやり方であると言えます。

同じ例で列名が英語だった場合、以下のように設定することも可能です。

//ステップ名はRenamed
= Table.RenameColumns(
　　　ソース,
　　　　{
　　　　　{"年月", "Date"}, 
　　　　　{"商品", "Product"}, 
　　　　　{"数量", "SalesQuantity"}, 
　　　　　{"定価売上", "SalesTicket"}, 
　　　　　{"売上高", "SalesAmount"}, 
　　　　　{"売上原価", "SalesCost"}
　　　　}
　　)

= Table.TransformColumnTypes(
     Renamed,  //列名が英語の場合
      List.Transform( 
       Table.ColumnNames(Renamed), 
       each if Text.Conains(_, "Sales") then {_, type number}
       else {_, type text}
      )
)

結果プレビュー

f:id:marshal115:20211227155415p:plain

この例ではSalesという名前が入っている列は全て数値列であることが分かっているので、最初にeach if Text.Contains(_, "Sales")でSalesを含む列であれば・・と、列名に含まれるテキストに特徴があると、データ型の指定が非常に楽になることがあります。

データ型をダイナミックに指定することが重要である理由は、

キーワードを絞り込んでデータ型を指定することでハードコーディングの手間やエラーの発生を防ぐ

ことができるという意図があります。

余談ですが、なぜ英語列にする必要があるのかという質問もあるでしょうが、それについては下記記事をご参照ください。

marshal115.hatenablog.com

また、話が少し反れますが、CSVの結合のベストプラクティスについては、下記記事をご参考ください。

marshal115.hatenablog.com

Formula.Firewallエラー

Formula.Firewallエラーは異なるソースからのデータを結合する際によく見られるエラーです。

f:id:marshal115:20211226132720p:plain

発生要因は主に以下のものがあげられます。

同じクエリにおいて、異なるプライバシーレベルが設定されている
あるデータソースの値を使用して、別のデータソースのデータを動的に取得/結合/フィルタリングしようとしたときに頻繁に発生します（ExcelのパラメータテーブルやWebベースのデータソースやデータベースを使用しているときによく発生）

上記2番目のエラーの発生要因はPower Query がクエリの結果を評価する前に、クエリ全体をスキャンして、使用中のデータソースを検出し、そのプライバシー互換性をチェックするようにデザインされているためです。このプロセスは静的解析と呼ばれ、Power Query はデータソース関数とそれらのデータソース関数に渡される引数を検出します。

説明すると長くなってしまうので、詳細は下記公式ドキュメントを読んでみてください。

docs.microsoft.com

なお、使用するデータソースが全て安全なものであると分かっている場合、このエラーの最も簡単な対処法は以下の通りです。

f:id:marshal115:20211227162034p:plain