【図で分かる！】Power Query 入門【上級編】

導入編と中級編では具体的なチュートリアルにて Power Query でデータ取得、データ加工、テーブルの結合などの操作方法を解説させていただきました。

上級編ではパワークエリを使いこなす上で必須の事項について解説します。

上級編のトピックは次の通りです。

この記事のトピック

M関数
テーブル結合の4つの方式
SQL Serverに接続

M関数の基本と応用

従来のExcelの関数ではほとんどの場合、関数について自分で覚えるか、マニュアルを読みながら使うしかありません。

しかし、パワークエリの場合、Power Query エディタのメニューにほぼ全ての機能が揃っています。ボタンを見れば機能かも分かるように出来ていて、直感的に使えるようになっています。

パワークエリの素晴らしさはこれでとどまりません。使いやすさを向上させつつも、プログラミングによる柔軟性も兼ね備えているのです。

このプログラミング言語が M関数 です。ユーザーのマウス操作の手順はPowerQueryエディタの中でM関数のプログラムとして記録されています。

M関数式を確認する

では一度その中身を覗いてみましょう。チュートリアル1で作成したPower Queryをエディタで開いて下さい。

メニューから ホーム＞詳細エディタ をクリックして下さい。

内容は図のようになっています。

これはExcelの関数やVBAのプログラムとも違う形式で、処理手順が上から順番に並ぶように記載されたものです。

“let”で始まり、続いて処理手順が列記され、最後に”in”の後にクエリの出力として処理手順の名前を指定します。通常は最後の処理手順の名前になります。

M関数の処理のしくみ

M関数のプログラムは上の行から順に処理が進むようになっていて、図のように処理手順をリレーのようにつないで書かれています。

Power Query はM関数式のプログラムとなっていることで、これを編集することでマウス操作以上に柔軟な処理を作成することが可能になります。

M関数式の簡単な例

続いて、M関数式をの簡単な例をご紹介します。

例1 テーブルからデータを取得

以下の例はテーブル１からデータを取得して、列のデータ型をそれぞれ日付と整数に設定しています。

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type datetime}, {"売上", Int64.Type}})
in
    変更された型

例2 数値を文字列に変換

以下の例では数値 “2023” を文字列に変換した上で “年” という文字列と結合しています。

数値は文字列とデータの型が違うのでそのままでは結合することができないので、一度数値を文字列に変換する必要があるのです。

let
    値 = Number.ToText(2023) & "年"
in
    値

M関数式のリファレンス

M関数式はExcel関数と同様に様々なものが用意されています。関数を探したり、その使い方を知るにはマイクロソフトのリファレンスが便利です。

Power Query M 関数参照 - PowerQuery M

詳細については、以下をご覧ください。Power Query M 関数参照

M関数式を読み解く

M関数式はExcel関数と異なる名前の形式になっています。例えば、”Table.TransformColumnTypes()” のような形式です。途中にピリオド “.” が入って2つの要素で構成されています。

これは下の図のように読み解きます。英語になっているだけで、非常に理解しやすい構成になっていることが分かります。

この関数の命名の仕方により、Excel関数と比べてぐっと分かりやすくなっています。この命名のルールを理解した上でリファレンスを読むとより理解が進むと思います。

以上がM関数の基本的な使い方です。次に、Power Query の強力な機能の一つであるテーブルの結合について詳しく見ていきましょう。

テーブル結合 4つの種類と使い分け

チュートリアルでは2つのテーブルを結合して情報を１つのテーブルにまとめました。

結合の操作の際にチュートリアルでは設定を変更せずに進めた部分があります。厳密な処理をするためにはこの設定の意味を知っておく必要があります。

テーブル結合の設定ウィンドウにはテーブルの結合方式を選ぶプルダウンメニューがあります。そこで選択できる主な項目について解説します。

内部結合

内部結合は、指定したテーブル間で共通するデータ列をキーとして結合し、新しいテーブルを作成します。この結合方式では、両方にキーが存在するデータだけが出力されます。

例えば、’EMPLOYEE’ テーブルと ‘DEPARTMENT’ テーブルを、2つのテーブルで共通して保持している ‘DEPARTMENT_ID’ をキーにして結合するとします。

内部結合では、両方のテーブルで DEPARTMENT_ID’ の値が一致する行だけが結果に含まれます。

外部結合

外部結合は内部結合と同じように指定したテーブル間で共通するデータをキーに結合します。しかし、内部結合と異なり、片方にキーが存在しないデータも出力されます。

ただし、キーが存在しない側の値はNULLとなります。

外部結合には主に3種類あります:

左外部結合

左テーブル（最初に選択したテーブル）のすべてのレコードと右テーブル（後から選択したテーブル）からの一致するレコードを含みます。一致するレコードが存在しない場合、右テーブルからの結果はNULLになります。

右外部結合

右テーブルのすべてのレコードと左テーブルからの一致するレコードを含みます。一致するレコードが存在しない場合、左テーブルからの結果はNULLになります。

完全外部結合

左テーブルと右テーブルのすべてのレコードを含み、一致するレコードが存在しない場合、そのテーブルからの結果はNULLになります。

このように、内部結合と外部結合を使い分けることで目的に合わせてテーブルの結合をすることができます。

データベースの大規模データを活用する

Power Query だけでも単純なデータベース的な仕組みを構築できます。しかし、本物のデータベースと連携させて利用すると更に活用の幅が広がります。

実務でデータベースと連携する上での課題

データベースは、ユーザーが膨大なデータの一部にアクセスして利用するというのが一般的です。

しかし、 Power Query の通常の接続方法ではデータベース上の テーブルやビューの全体を取得してしまう ことになります。そうすると Power Query で大規模なデータを扱うことになり、以下の課題が生じてしまいます。

大規模データの処理における課題

パフォーマンスの低下: データ量が多いと、クエリの実行速度が遅くなり、応答性が低下します。
メモリ使用量の増加: 大量のデータをメモリ上で処理するため、メモリ使用量が増加し、システムの安定性が損なわれることがあります。
ロード時間の延長: データのインポートやロードに時間がかかるため、全体の処理時間が長くなります。

この課題への対策は、必要なデータに絞って扱うデータ量を縮小 することです。

クエリフォールディングを使う

クエリフォールディングとは Power Query で組んだデータ処理の内容を可能な限りデータベース側で処理させる機能です。

大量のデータを Power Query で受信してから、Power Query で様々なデータ処理をすると、パソコン側の負担が大きくなります。

反対に、データベース・サーバー側で様々なデータ処理をした上で、コンパクトになったデータを Power Query で受信するとパソコン側の負担は小さくなります。

Power Query には自動でこのような処理をする機能がついているのです。これをクエリフォールディングと呼びます。

クエリフォールディングを機能させる

Power Query でデータベースのデータ処理の手順を組んでいると自動でクエリフォールディングが適用されますが、それでも全ての処理に対してクエリフォールディングが適用される訳ではありません。

クエリフォールディングが適用されるためには、そのデータ処理がデータベース側で実行できる必要があります。

クエリフォールディングが適用できるデータ処理

列、行の絞り込みの処理
四則演算
グループ集計

クエリフォールディングが適用できないデータ処理

SQL言語に無い Power Query の M関数の処理
データベース・サーバ側に無いテーブルとの結合
それまでの処理で一度クエリフォールディングが途切れている

データ量を縮小する

クエリフォールディングでデータを縮小するために、データベースのテーブル読み込みに続けて次の処理をします。

列の絞り込み：　不要な列を削除
行の絞り込み：　いくつかの列でフィルター処理をしてデータを絞り込む

このようなデータ処理手順を組むことで、 Power Query は データベースに必要なデータだけを要求するコマンド を送信します。

データベースに送信するコマンドのことをPower Query エディターでは「ネイティブクエリ」と呼んでいます。これはデータベースで使われる SQLのコマンド そのものです。

クエリフォールディングの処理を確認する

実際にデータベースに接続した後に、不要な列と行の削除を行った Power Query でクエリフォールディングの処理がなされていることを確認してみます。

図のようにデータの削除処理まで行ったステップを右クリックしてメニューを出して、「ネイティブクエリを表示」を選択します。

すると、図のようにネイティブクエリ（SQLコマンド）の内容が表示されます。中身を見ると Power Query で組んだ列の絞り込みと、行の絞り込みが適用されていることが分かります。

これによりデータベースから取得するデータを最初から小さくして、大規模データの課題を回避することが可能です。

ユーザーのニーズをクエリに反映する

前の項のようにあらかじめ決められたデータの絞り込みをするクエリを作成することは比較的簡単です。

しかし、ユーザーのニーズは状況により変化するので、 Power Query の処理をユーザーのニーズに応じて変わるようにしたいケースもあります。

この課題の解決策として、以下の流れで処理する方法をご紹介します。

ユーザーニーズに応じたデータ取得の流れ

ユーザーに データを絞り込む条件 をセルに入力して貰う
Power Query の M数式を活用して ユーザーが入力したセルの値を取得
読み込んだ値を使って、Power Query 上で SQLクエリ文を作成
作成した SQLクエリ文を Power Query の SQL Server 接続コマンドで実行してデータを取得

設定方法

では、具体的な設定を順番にご説明します。

操作手順

Excelシートのどこかにユーザーの入力欄を作成し、そのセルに名前をつけます。ここでは「ユーザー入力１」としておきます。
Power Query の詳細エディターに下記のコードを記述します。

let
    ShopName = Excel.CurrentWorkbook () {[Name = "ユーザー入力1"]} [Content] {0} [Column1],
    sqlcom = "select * from SALES where SHOP = '" & ShopName & "'",
    ソース = Sql.Database("ServerName,1433", "TestDB", [Query=sql])
in
    ソース

上記のコードは下記の処理をしています。1行ごとに読んで理解して頂くと次から応用できるようになると思います。

コード解説

「ユーザー入力１」と名前がついた セルの値を “ShopName” という変数に格納。
続いて、SQLクエリ文を作成します。SELECT文にWHERE句をつけて出力を絞り込みます。
この例では [SHOP]列 = ShopName となる行のみを出力。
できた SQLクエリ文（変数 “sqlcom”）を SQL Server に送信 して実行結果のデータを取得します。

操作は以上です。

以上のようなクエリを作成することで、ユーザーのニーズに応じたデータをデータベースから取得できました。

大規模なデータであっても、このやり方で複数の絞り込み条件を指定することで実用的て軽量な Power Query のクエリを作成することができます。