エラーメッセージ
クエリを作成し、データをマージする準備が整ったところで、Excelが謎めいたメッセージを表示して停止することがあります。通常、以下のようなメッセージが表示されます。
Formula.Firewall: Query 'SalesData' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
これはイライラする障害ですが、これには正当な理由があります。なぜこのエラーが発生するのか、そしてどのように解決すればよいのかを見ていきましょう。
このエラーが発生する理由
Power Queryは、情報を保護するために「プライバシー レベルのファイアウォール」を使用しています。これは警備員のようなものだと考えてください。デスクトップ上のローカルCSVファイルなどのプライベートなソースからのデータが、Web APIやSQL Serverなどのパブリックなソースに誤って流出するのを防ぐのがその役割です。
このエラーは、あるクエリがデータソースを定義するために別のクエリに依存している場合に発生します。たとえば、テーブルAの値を使用してテーブルBをフィルタリングする場合、Power Queryはプロセス中にテーブルAのデータがネットワーク経由で送信されないことを保証できないため、この「マッシュアップ」をブロックすることがあります。これは、小さなローカルテーブルを10万行を超える大規模なクラウドデータベースとマージする場合によく見られます。
解決策1：プライバシー レベルの設定を調整する（簡単な方法）
データソースを信頼しており、データ間の流出を心配する必要がない場合は、Excelの設定を変更するだけで済みます。これは、社内プロジェクトで最も一般的な修正方法です。
- Excelで、データタブに移動し、データの取得 > クエリのオプションを選択します。
- 左側のサイドバーを確認します。現在のブックセクションにあるプライバシーをクリックします。
- プライバシー レベルを無視して、パフォーマンスを向上させるを選択します。
- OKをクリックし、クエリを更新します。
この設定はブック固有のものであることに注意してください。このファイルを同僚にメールで送信した場合、同僚が自分の設定を変更するまで、同じエラーが発生する可能性があります。
解決策2：プライバシー レベルを統一する
ファイアウォールを有効にしたままにしたい場合は、すべてのデータソースが同じプライバシー設定を共有していることを確認してください。
- データ > データの取得 > データ ソースの設定に移動します。
- データソースを選択し、アクセス許可の編集をクリックします。
- すべてのプライバシー レベルを「組織」に設定します。
- クエリ内のすべてのソースに対してこれを繰り返し、更新します。
解決策3：Mコードを再構成する（プロフェッショナルな方法）
企業のポリシーによっては、プライバシー設定の無効化が禁止されている場合があります。そのような場合は、クエリの構築方法を変更する必要があります。ファイアウォールは通常、単一のステップで、別のソースから計算したばかりの変数を使用して外部データを取得しようとする構成を拒否します。
問題のあるコード
let
// ローカルのExcelテーブルからAPIキーを取得
SourceKey = Excel.CurrentWorkbook(){[Name="Config"]}[Content]{0}[Key],
// 同じクエリ内でそのキーを使用してWebデータを取得しようとする
// これによりファイアウォールエラーが発生します！
Data = Json.Document(Web.Contents("https://api.example.com/v1/" & SourceKey))
in
Data
修正後の戦略
これを修正する最善の方法は、ステージング クエリを使用することです。まず、APIキーやフィルター条件を取得するためのクエリを作成します。次に、生データを取得するための2つ目の独立したクエリを作成します。最後に、それら2つをマージする3つ目のクエリを作成します。このように分離することで、データの取得が計算から独立していることをPower Queryに伝え、ファイアウォールのチェックをパスできます。
あるいは、Table.Buffer() を使用することもできます。ステップを Table.Buffer でラップすると、Power Queryは次のステップに進む前にそのデータを完全にメモリにロードするため、ファイアウォールが懸念しているリンクを断ち切ることができる場合があります。
検証手順
以下の場合、成功したことになります：
- Power Query エディターのプレビューが、赤いエラーバーなしで読み込まれる。
- プレビューの更新をクリックすると、データの全50列が即座に表示される。
- 閉じて読み込むを実行すると、マージされた結果がExcelシートに正常に反映される。
防止のためのヒント
- データのステージング： マージや結合を行う前に、必ず生データを別のクエリに読み込むようにします。
- 動的なヘッダーを避ける： 同じクエリ内の別のテーブルのロジックを使用して、URLやSQL接続文字列を作成しないようにしてください。
- 入力の検証： IP範囲のマッピングなど、ネットワーク関連のタスクでデータをマージする場合は、まずロジックを再確認してください。私は、Excelの設定にインポートする前に、ToolCraftのサブネット計算機を使用してCIDRブロックを検証しています。これはデータをアップロードしない高速なブラウザツールなので、頭の中の「データ プライバシー ファイアウォール」も安心させることができます。