導入編 と 中級編 では具体的なチュートリアルにて 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関数と同様に様々なものが用意されています。関数を探したり、その使い方を知るにはマイクロソフトのリファレンスが便利です。
M関数式を読み解く
M関数式はExcel関数と異なる名前の形式になっています。例えば、”Table.TransformColumnTypes()” のような形式です。途中にピリオド “.” が入って2つの要素で構成されています。
これは下の図のように読み解きます。英語になっているだけで、非常に理解しやすい構成になっていることが分かります。
この関数の命名の仕方により、Excel関数と比べてぐっと分かりやすくなっています。この命名のルールを理解した上でリファレンスを読むとより理解が進むと思います。
以上がM関数の基本的な使い方です。次に、Power Query の強力な機能の一つであるテーブルの結合について詳しく見ていきましょう。
テーブル結合 4つの種類と使い分け
チュートリアルでは2つのテーブルを結合して情報を１つのテーブルにまとめました。
結合の操作の際にチュートリアルでは設定を変更せずに進めた部分があります。厳密な処理をするためにはこの設定の意味を知っておく必要があります。
テーブル結合の設定ウィンドウにはテーブルの結合方式を選ぶプルダウンメニューがあります。そこで選択できる主な項目について解説します。
内部結合
内部結合は、指定したテーブル間で共通するデータ列をキーとして結合し、新しいテーブルを作成します。この結合方式では、両方にキーが存在するデータだけが出力されます。
例えば、’EMPLOYEE’ テーブルと ‘DEPARTMENT’ テーブルを、2つのテーブルで共通して保持している ‘DEPARTMENT_ID’ をキーにして結合するとします。
内部結合では、両方のテーブルで DEPARTMENT_ID’ の値が一致する行だけが結果に含まれます。
外部結合
外部結合は内部結合と同じように指定したテーブル間で共通するデータをキーに結合します。しかし、内部結合と異なり、片方にキーが存在しないデータも出力されます。
ただし、キーが存在しない側の値はNULLとなります。
外部結合には主に3種類あります:
左外部結合
左テーブル（最初に選択したテーブル）のすべてのレコードと右テーブル（後から選択したテーブル）からの一致するレコードを含みます。一致するレコードが存在しない場合、右テーブルからの結果はNULLになります。
右外部結合
右テーブルのすべてのレコードと左テーブルからの一致するレコードを含みます。一致するレコードが存在しない場合、左テーブルからの結果はNULLになります。
完全外部結合
左テーブルと右テーブルのすべてのレコードを含み、一致するレコードが存在しない場合、そのテーブルからの結果はNULLになります。
このように、内部結合と外部結合を使い分けることで目的に合わせてテーブルの結合をすることができます。
SQL Server（データベース）に接続する方法【実践版】
Power Query は単独でもデータベース機能として強力ですが、本物のデータベースと連携させて利用すると更に活用の幅が広がります。
ここでは Microsoft の SQL Server との接続の方法を紹介します。SQL Server は容量制限はあるものの無料で使える Express版 が提供されています。誰でも本格的なデータベースを使い始められるので非常にオススメできます。
ちなみに、この容量制限は超えようと思っても簡単には超えられない程大きいので、当面は心配の必要は無いと思います。
更に、Microsoft製品同士ということもあり、Power Query との相性の心配もありません。
では早速、実践的なデータベースとの連携について内容を見ていきましょう。
実務でデータベースと連携する上での課題
通常データベースには膨大なデータが保管されており、ユーザーは自分が必要な範囲のデータを取り出してデータを活用します。
しかし、 Power Query の通常の接続方法ではデータベース上の テーブルやビューの全体を取得してしまう ことになります。
これではダウンロードに時間がかかり過ぎたり、ユーザーのPCのメモリが不足してフリーズしてしまうということになりかねず、実用的では無くなってしまいます。
そこでこれを解決するために、ユーザーの必要なデータのみを取得 する 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行ごとに読んで理解して頂くと次から応用できるようになると思います。
コード解説
- 「ユーザー入力１」と名前がついた セルから値を取得 します。
- 続いて、SQLクエリ文を作成します。SELECT文にWHERE句をつけて出力を絞り込みます。
この例では ‘SALES’ というテーブルからデータを取得しますが、‘SHOP’ 列をユーザーが入力した内容で絞り込みます。 - できた SQLクエリ文を SQL Server に送信 して実行結果のデータを取得します。
操作は以上です。
以上のようなクエリを作成することで、ユーザーのニーズに応じたデータをデータベースから取得できました。
より大きなデータベースであっても、このやり方で複数の絞り込み条件を指定することで実用的て軽量な Power Query のクエリを作成することができます。
是非皆さんの実務に合わせて Power Query を設定して活用してみて下さい。
コラム
Excelのセルに名前をつける方法は簡単ですが、メニューを探しても分かりにくいので解説させて頂きます。
下の図のように名前をつけたいセルを選択して、メニューの セルアドレスが表示されている欄 につけたい 名前を上書きするだけです。
ちなみに、つけた名前を削除したい場合は、メニューから 「数式」＞「名前の管理」 とすると名前の一覧が出てきますので、ここで削除します。
まとめ
上級編ではより実務で役に立つ知識として以下について紹介いたしました。
1. M関数式 Power Query の本当の姿とも言えるM関数式にアプローチすることで、その 強力な機能を柔軟に使う方法を紹介しました。
2. テーブルの結合方式 Power Query の強力な機能の一つである テーブル結合のより詳しい内容 を紹介しました。
3. SQL Server 接続 実用的に データを絞って SQL Server からデータを取得する方法 を詳しく紹介しました。
導入編から上級編までで得た知識を使いこなすことで実務においてかなり高度なデータ処理をこなすことができるようになると思います。
他の記事では、Power Query を活用したDX化の取り組みなどを紹介していきますので、よろしければ是非読んでみて下さい。
