Power Queryのエラー対処事例①

Power Queryを使用していると、エラーによく遭遇したりします。初心者のうち、エラーが発生すると対処に困るものですが、エラーの種類について知っていれば冷静に対処できますので、事例を交えて対処法について記載してみようと思います。なお、頻出エラーについては下記公式ドキュメントをご参考ください。

docs.microsoft.com

エラーの種類

具体的な事例について紹介する前、まずはエラーの種類について知る必要があります。以下はそれらを簡単にまとめたものとなります(上記公式ドキュメントを引用)。

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

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

エラーの多くがステップレベルエラーとなりますが、主に以下のエラーが含まれます(各エラー及び対処法の詳細は別々に記載します)

  • ステップレベルレベルエラー
    1. ソースデータエラー

      f:id:marshal115:20211226130712p:plain

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

      f:id:marshal115:20211226130637p:plain

    3. Formula.Firewallエラー(英語ですが、日本語でも同じ)

      f:id:marshal115:20211226132720p:plain

ステップレベルエラーと似ていますが、セルレベルのエラーは以下の通り

  • セルレベルエラー
    1. データ型設定時の値のエラー

      f:id:marshal115:20211226133208p:plain

    2. 操作エラー(計算エラー)

      f:id:marshal115:20211226133432p:plain

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

ソースデータエラー

ソースデータエラーはエラーメッセージを見ればすぐに判明できます。ソース先を変更すれば良く、非常にシンプルで悩むことは少ないと思います。ソースの変更はFolderパスであればそれを変更してあげるだけで良く、Power BIでもExcelでも以下のように行います。

  • クエリエディタを立ち上げ、「データソース設定」から「ソースの変更」

    f:id:marshal115:20211226134423p:plain

  • アドレスを正しい参照先へ変更

    f:id:marshal115:20211226134525p:plain

これだけでOKです。なお、このやり方が便利なところは、例えば以下のように、複数のクエリが全て同じファイルを参照していた場合、パラメータを使用することで、参照先を一気に変更できるところにあります。

◆ 複数のクエリが1つのExcelを参照している場合

f:id:marshal115:20211226135726p:plain

①~② クエリエディタを開き、「データソース設定」>「ソースの変更」

f:id:marshal115:20211226141440p:plain

③ 新しいパラメータをクリック

f:id:marshal115:20211226141615p:plain

④ パラメータ名を指定し、⑤  新しいパスを入力、⑥ OK

f:id:marshal115:20211226141705p:plain

⑦ ファイルパスが指定されていることを確認し、⑧ OK

f:id:marshal115:20211226141810p:plain

⑨ 全てのクエリの参照先がExcelPathになっていることを確認

f:id:marshal115:20211226141913p:plain

これでクエリを1つずつ、参照先を変更することなく、一発で全てのクエリを変更できました。

Power BIからインポートした場合、上記やり方が通用しない?

ExcelのデータモデルやPower Query等をPower BIへインポートした場合、上記やり方が通用しないことがあります。Power BIにはインポートという機能があり、Excelで作ったPower Queryやデータモデルなどをインポートできます(下図)。

Power BI Desktopを立ち上げ、ファイル > ①インポートとクリックしていきます。

f:id:marshal115:20211226144854p:plain

③ 開始をクリック

f:id:marshal115:20211226144909p:plain

④ 「接続の保持」をクリック(「データコピー」については後述)

f:id:marshal115:20211226144918p:plain

Excel.Workbook(中略){[Name] = "dGroup"]}[Data]というクエリが付属してきます。dGroupはExcel内のテーブルであり、そのテーブルを指定していることになります

f:id:marshal115:20211226161115p:plain

{[Name] = "dGroup"]}[Data]の意味するところは、[Name]という列の中で、dGroupという名前のレコード、すなわち、ソース{[Name = "dGroup"]}がこれに該当し、そのレコード内の[Data]の中身を抽出せよ、となります。

f:id:marshal115:20211226145728p:plain

ここで、先ほどと同じにように、「データソース設定」からソース先を変更してみようとすると・・・⑦の通り、なぜかボタンがグレイアウトの状態になり、選択できません。

f:id:marshal115:20211226145924p:plain

最初にこの現象に遭遇した時は非常にショッキングでしたが、ステップを1つ追加してあげると対処できるようになります。

上記⑤のクエリを以下のように変更します。

f:id:marshal115:20211226152531p:plain

すると、ソースは以下のように、Excelファイル内の各シートとテーブルが表示されます。

f:id:marshal115:20211226152806p:plain

ここで、数式バーのfxをクリックし、ステップを追加します。

f:id:marshal115:20211226152955p:plain

すると、数式バーには「=ソース」という数式が挿入されますが、これにカットしたM式をペーストします。Power Queryを日頃使っている方であれば見覚えがあると思いますが、「ナビゲーション」というステップが挿入されます(ここは面白いのですが、fxをクリックしただけではステップ名は「カスタム1」になりますが、{[Name = "dGroup"]}[Data]を下図のように入れると、自動的に「ナビゲーション」という名前に変更されます)。

f:id:marshal115:20211226153213p:plain

これでdGourpというクエリに対しては処理が完了しました。これをその他の全てのクエリに対して行っていきます。

f:id:marshal115:20211226153630p:plain

f:id:marshal115:20211226153641p:plain

最後にもう一度「データソース設定」を開くと、先ほど選択できなかった「ソースの変更」が選べるようになりました。

f:id:marshal115:20211226153829p:plain

この事例のポイントは2つ

  1. インポートを使用し、その際「接続の保持」を選択
  2. インポートされたデータは「ナビゲーション」というステップがなく、1つのステップで対象テーブルが抽出されている

上記2つの条件が揃っていた場合、データソースの変更が出来なくなってしまいます。Excelのワークシート上にステージングクエリ(商品マスタ、店舗マスタ、売上実績等)として使用するデータを格納している人で、最終的にPower BI Desktopにそれらも含めインポートしてBIレポートを作る人が影響を受けることになります。

ちなみに、インポート時に「データの保持」ではなく、「データコピー」を選択していた場合、dGroupクエリは以下のようにBinary扱いのテキストとして自動的に書き換えられます。

f:id:marshal115:20211226155212p:plain

これは下図の「データの入力」と同じ扱いになります。

f:id:marshal115:20211226155419p:plain

そのため、「データソース設定」を開いても、事例の3つのクエリのソース先を見ようとしても、下記の通り見つかりません。

f:id:marshal115:20211226155534p:plain

長くなりますので、今回は「ソースデータエラー」の対処法及び派生パターンについて紹介しました。

まとめ

  • Power Queryで発生するエラーは数種類あり、それぞれ特徴があるため、公式ドキュメントを読んで概要について理解しておくとGood
  • ソースデータエラーはパスを変更すればよいというシンプルなものもあれば、Excelからインポートしてクエリステップを分けて記述しないと”パスの変更ができない”面倒なパターンも存在する