SQLに慣れた人向けに、エクセル付属のPowerQueryの使い方をまとめました。

PowerQueryの良いところ

• GUI操作でクエリを作成できる
• クエリの実体はM式というソースコードであり、プレーンテキストとして扱うこともできる。

SQL・Pandasにはないメリット

• 環境構築が不要（エクセルさえあれば）
• エクセルシートにクエリ結果をすぐに出力できるので、シームレスに分析・集計・体裁の調整ができる
• テーブル操作の結果を確認しながら、クエリを作成できる

Excel関数にはないメリット

• JOINができる
  • vlookup関数でも一応できるが、記述が冗長になりがち。
• 104万行以上のデータも集計できる

PowerQuery の概要

クエリ

PowerQueryでは、一連のテーブル操作をクエリという単位でまとめることができます。
クエリはPowerQueryエディターで編集して、フォルダに分けて管理できます。

クエリは複数のテーブルをインプットして、ひとつのテーブルをアウトプットします。
複数のクエリを繋げることで、複雑な処理を行うこともできます。

M言語

Power Query では処理内容を M言語で記述します。
しかし、M言語について知るべきことは多くありません。M言語コードは、GUI操作で入力することでほぼ自動的に生成されるためです。
その上で、M 言語について知っておいた方が良いことについて紹介します。

詳細エディターでソースコードを確認・編集

Query Editor で作成したステップの実体は、M式と呼ばれるソースコードです。詳細エディターから表示できます。
let式の各行が、PowerQueryエディターの各ステップに対応しています。

詳細エディターからステップを生成

詳細エディタのM式を編集して、クエリのステップを組み立てることもできます。
しかし、PowerQueryがステップとして解釈できるように、 以下のようにする必要があります。

• let式には…
  • 「table型の変数 = （処理内容）」 の形式で記述する
    • ちなみに、３行目の「Table.NestedJoin()」や４行目の「Table.ExpandTableColumn()」はtableを返す関数です。
  • 処理内容は、前の行の変数を使って記述する。
    • ちなみに、table型の変数の名前が、ステップの名前になります。
• inステートメントには…
  • 最後のtable型の変数を記述する。

ステップとして解釈できない場合は、以下のように単一のステップとして表示されます。ステップを編集するための歯車アイコンは表示されず、GUI操作でステップを編集することはできなくなります。

日本語の変数名が使える

「#"（変数名）"」のように記述すると、任意の文字列を変数名にできます。
テーブルの変数名はステップ名として表示されるので、これを利用して、ステップ名をコメントのように扱うことができます。

PowerQuery エディターの便利機能

知っておくと便利なPowerQueryの機能を紹介します。

数式バーの表示

「数式バー」にチェックを入れると、ステップのM式を直接編集できます。

列の統計情報の表示

以下の項目にチェックを入れると、上位1000件のデータ分布や null の数が常に表示されます。

• 列の品質
• 列の分布

例からの値

「例からの値」は、「入力列」と「出力値の例」から変換式を推測して、列を追加する機能です。
簡単な条件分岐などは、この機能で記述すると楽です。

クエリの依存関係を表示

クエリを呼び出す順番をフローチャートで確認できます。

テクニック

Excel シートのテーブルをクエリとして取り込む

エクセルのテーブルは、「テーブルまたは範囲から」という機能でクエリとして取り込むことができます。
エクセルの入力値をPower query に渡したいときに便利です。

テーブルを効率よく たくさん取り込む

「テーブルまたは範囲から」という機能は、GUI操作で直感的にわかりやすいですが、動作が遅いのでたくさんのテーブルを取り込みたいときは時間がかかってしまいます。
そんな時は、M式を直接編集すると早いです。
「テーブルまたは範囲から」で生成されるクエリのソースコードは、テーブル名を指定するM式となっています。

このクエリを複製して、M式のテーブル名だけ変更すれば、すぐにたくさんのテーブルを取り込むことができます。

既存のステップに統合

ステップの作成後に、列名の変更や列の削除などをしたいときがあります。
既存の同種のステップの上下にステップを挿入すると、挿入したステップが既存のステップに統合されます。
ステップ数が無駄に増えないので便利です。

＊　以下、クエリの編集方法です　＊

SQL の文法に合わせて操作を分類しています。

SELECT（列の操作）

列の作成

例からの列（ロジック推定～条件分岐・文字列操作など）

以下のような、簡単な条件分岐や文字列操作であれば、M 式を自動生成できます。

• CASE（条件分岐）
• 文字列操作
  • CONCAT（文字列連結）
  • SUBSTRING, LEFT, RIGHT（部分文字列）

例）年齢（age列）から年代（era列）を作成、年代は10歳ごとの階級

1. 例からの列...　＞　選択範囲から
2. 参照する列を選択
3. 二つ以上の例を入力
4. ロジックが推定される

今回の例では、以下のようなM式が自動生成されました。

Text.Combine({Text.Start(Text.From([age], "ja-JP"), 1), "0"})

また、以下の関数が自動選択されました。

• Text.Start（最初のN文字の部分文字列）
• Text.Combine（文字列連結）
• Text.From（整数型の「age」列をテキスト型へ変換）

カスタム列

自分で式を入力して、新しい列を追加できます。

DISTINCT（重複削除）

以下の例では、「customer_idとsales_ymdの組」の重複を削除します。

（DISTINCT相当）

1. customer_idとsales_ymdを選択
2. 行の削除 > 重複の削除

列の編集

列の並び替え

列名の部分をドラッグして、列の並び替えができます。

型の変更

列名の左のアイコンをクリックすると、型を選択できます。

値の置換

値やnullを別の値に置き換えることができます。

例）nullを0に置き換え

1. 右クリック　＞　値の置換...
2. 置換条件を設定

列の削除

右クリックメニューの「削除」コマンドだけでなく、Delete キーでも削除できます。
ちなみに、ステップ挿入箇所の上下に、「列の削除」ステップがあれば、ひとつのステップに統合されます。

FROM（データソース）

INNER / LEFT / OUTER JOIN

結合と展開の２段階の手順が必要です。

結合

０．JOINの左側テーブルとなるクエリ（つまり、receipt）を選択
１．クエリのマージ　＞　新規としてクエリをマージ
２．右側テーブルを選択
３．結合キーを選択（storeテーブル）
４．結合キーを選択（receiptテーブル）
５．内部結合を選択
６．結合条件を確定
７．（receiptの各行に結合された）

receiptの各行に、storeテーブルの内部結合した一部分がテーブルとして保持されている状態です。Tableと書かれているセルをクリックすると、 部分テーブルの中身を見ることができます。

展開

部分テーブルを展開して、表データとして使えるようにします。

１．テーブル列のアイコンをクリック
２． 出力に含めるカラムを選択
３．「プレフィックス」のチェックを外す
４．展開条件を確定
５．部分テーブルが展開された

結合の種類

以下のような結合の種類を選べます。

• INNER JOIN（内部）
• LEFT JOIN（左外部）

• FULL OUTER JOIN（完全外部）

  など

CROSS JOIN（クロス結合）

標準の機能にクロス結合はないので、固定値１の結合キーを用意して完全外部結合することで、クロス結合を実現します。

１．結合先テーブル（product）への参照を作成
２．カスタム列で、固定値の列（_key）を追加
３．結合元テーブル（store）も同様（クエリへの参照、固定値の列を追加）
４．固定値の列を結合キーにして、完全外部結合する
５．必要に応じて部分テーブルを展開する

GROUP BY（集約）

以下の例では、customer_idをキーにamountを合計します。

１．集約キー（customer_id）を選択しながら、グループ化を選択
２．集約方法（合計）を指定
３．合計する列（amount）を指定
４．集計結果の列名（amount）を指定

WHERE（絞り込み）

以下の例では、顧客IDが’Z’から始まるものを除外します。

（WHERE customer_id NOT LIKE 'Z%'に相当）

１．テキストフィルター　＞　次の値で始まらない...
２． 絞り込み条件を設定

ORDER BY（並び替え）

列名の右のアイコンをクリックして、「昇順で並び替え」か「降順で並び替え」を選択します。

LIMIT（レコード数を制限）

1. 行の保持　＞　上位の行を保持
2. 表示件数を確定

UNION（テーブルを縦に連結）

1. クエリの追加
2. レコードを追加するテーブルを選択

WITH句・副問い合わせ（複数のクエリ）

With句や副問い合わせのように、複数のクエリを組み合わせた複雑なクエリを作成できます。
PowerQueryでは、他のクエリの結果を入力することで実現できます。

WITH _039_days AS (
    ...
),
_039_amount AS (
    ...
)
SELECT COALESCE(d.customer_id, a.customer_id), d.come_days, a.buy_amount
FROM _039_days d
FULL JOIN _039_amount a
ON d.customer_id = a.customer_id;

「新規としてクエリをマージ」でFULL JOINする例

039-daysと039-amountというクエリを、「新規としてクエリをマージ」という機能で結合して、新しいクエリを作成する例です。

「クエリの依存関係」で表示できるフロー図から、039-daysと039-amountの結果を組み合わせて、最終的に039というクエリを作成していることが分かります。

インデックス列の作成

整数の連番の列を簡単に作成できます。

乱数列の作成

以下のM式で、乱数の列が作成できます。

Number.RandomBetween(0,1)

問題点

しかし、全ての行に同じ乱数の値がセットされてしまいます。

対策

これを回避するには、インデックス列を加減すると良いです。

Number.RandomBetween(0,1) + [id] - [id]

「[id] - [id]」の部分が各行の乱数の再計算を強制するため、行ごとに異なる乱数になるそうです。

参考：Custom Column Randbetween - Microsoft PoserBI Community

作業イメージ