Power Queryとは
Power BIやExcelに搭載されている、データ収集・加工機能。
- データ収集: Excel、CSV、Access、PDF、各種DBサーバー等、非常に多くのデータ形式に対応。
- データ加工: 抽出、持ち方変更（Long⇔Wide）、簡単な計算や集計等、一通りの前処理が可能。
Excel等と同様、基本的にGUIで直感的に操作できるが、裏側で使われているM言語によるプログラミングも可能。
Power Queryを使うメリット
- 一度設定すれば、次回以降は更新ボタンを押すだけでデータ更新可能 → 手作業でのデータ貼り付けから開放
- 従来VBAを使わなければできなかったデータ収集・加工がノンプログラミングで可能 → プログラミング不要で自動化
- Power Pivotのデータモデルとしてデータを取り込めば、Excelの限界を超える行数の取り扱いが可能 → データ種類・量の制限が緩和
Excelとの違い
- 関数はあるが列同士の計算のみ。Excelのテーブルに似ている。
- テーブルの各セルには文字列、数値、日付等の値データ以外にもさまざまなデータを格納可能。ファイルそのものを表す
binary型や、テーブル、リストも格納できる。
やってみるさ
Excel画面のデータタブ -> 新しいクエリ -> ファイルから -> ブックから -> ファイルを選択 -> インポート -> ナビゲーター画面の左側で取得したいテーブルを選択 -> 読み込み で、新しいシートにテーブル形式でデータが読み込まれる。
Power Queryエディター画面の説明
Excel画面のデータタブ -> データの取得 -> Power Query エディターの起動 （Excel 2016はデータタブ -> 新しいクエリ -> クエリの結合 -> クエリ エディターの起動）で、Power Queryエディターを起動。
エディターは以下の4つの部分に分かれている:
- 上部: リボン
- 左側:
クエリペイン - 真ん中: プレビュー
- 右側:
クエリの設定ウィンドウ
以下、各部分について説明する
上部: リボン
Power Queryでできる様々な機能がタブごとにまとめられている。後述の各種機能もここから呼び出す。
詳細エディターとM言語
ホームタブの詳細エディターから、詳細エディターを開けば、クエリーの元になっているM言語を編集できる。M言語とはPower Queryの裏側で動いているプログラミング言語。リボンのボタンから行えないような細かい調整が可能。
左側: クエリペイン
クエリーやパラメーターの一覧。それぞれの意味は以下:
- クエリ: データソース、データ加工等をひとまとめにしたもの。結果は基本的にテーブルだが、単独の値や、複数の値からなるリストやレコードとなることもある。また、クエリは他のクエリから再利用することも可能。
- パラメーター: 値のみを格納したもの。値はエディター上で設定する。
クエリー、パラメーターはグループと呼ばれるフォルダーのようなものに格納可能。クエリー等が多くなったときにグループ分けすれば見やすくできる。
真ん中: プレビュー
クエリペインで選択しているクエリの実行結果のプレビューが表示される。フィルター操作や列の並べ替え等はここで行える。
右側: クエリの設定ウィンドウ
クエリの各ステップ（個々の処理）が表示される。クエリの各ステップは編集の度に自動的に追加される。名前が分かりにくいので、以下のように分かりやすいよう変更すると良い。
Power Queryの主なデータ変換・編集機能
★付きは詳細を説明。
- ★ヘッダー調整
- ★データ型の変換
- ★列追加
- 列選択/削除
- 列の移動
- 行の抽出
- 行の並べ替え
- 重複行の削除
- 値の置換
- 欠損値の補間
- 行と列の入替
- グループ化（集計）
- ★ピボット解除
- ★ピボット
- ★クエリのマージ
- ★クエリの追加
ヘッダー調整
テーブルからデータを取得する場合は問題ないが、シートからデータを取得する場合は、シートの1行目をヘッダーとして使用してしまうため、ヘッダーが2行目以降にあるようなときには修正が必要。ホームタブ -> 1行目をヘッダーとして使用 ボタンを押すたびに、ヘッダー行が1行ずつ下にずれていくので、必要な回数実行する。
データ型の変換
データ型とは、整数、10進数、テキスト等のデータの種類。列ごとに同じデータ型でなければならない。
データ取り込み時に最適な型が自動的に設定されるが、手動設定も可。手動で変換するには、数式バーか詳細エディターで、=Table.TransformColumnTypes(テーブル, {{列1, データ型1}, {列2, データ型2}, …, {列n, データ型n}})のデータ型部分を修正する。
主なデータ型は次のとおり:
| データ型 | アイコン | M言語表示 | 説明 |
|---|---|---|---|
| テキスト |  |
type text |
Unicodeテキスト |
| True/False |  |
type logical |
TrueまたはFalseいずれかの値 |
| 10進数 |  |
type number |
64bit浮動小数点数 |
| 通貨 |  |
Currency.Type |
小数点区切り文字の右側には常に4桁の数字が入り、有効数字は最大19桁 |
| 整数 |  |
Int64.Type |
64bit整数値 |
| パーセンテージ |  |
Percentage.Type |
値がパーセンテージ表示される以外は10進数と同様 |
| 日付/時刻 |  |
type datetime |
日付と時刻 |
| 日付 |  |
type date |
日付 |
| 時刻 |  |
type time |
時刻 |
| バイナリー |  |
type binary |
バイナリー データ（ファイルそのもの） |
| [任意] |  |
type any |
何でも入るが、出力時には型を指定すること |
列追加
列の追加タブ -> カスタム列で、カスタム列エディターを開く。カスタム列の式に関数を入力する。例えば、日付列から年を取り出すには、Date.Year([日付])と入力する。使用できる関数は公式ドキュメントを参照。
ピボット解除
ピポットではない（間違えないように）。
データの持ち方を横持ち（Wide）から縦持ち（Long）に変える操作。データの持ち方についてはこちらを参照。データの蓄積・集計を行うなら、縦持ちの方が便利。また、常に整然データとなることを意識しておくこと。
以下の変換を考える。
横持ち（Wide）
| 国 | 21/5 | 21/6 | 21/7 |
|---|---|---|---|
| 薩摩 | 11 | 12 | 13 |
| 大隅 | 21 | 22 | 23 |
| 日向 | 31 | 32 | 33 |
↓
縦持ち(Long)
| 国 | 年月 | 売上 |
|---|---|---|
| 薩摩 | 21/5 | 11 |
| 薩摩 | 21/6 | 12 |
| 薩摩 | 21/7 | 13 |
| 大隅 | 21/5 | 21 |
| 大隅 | 21/6 | 22 |
| 大隅 | 21/7 | 23 |
| 日向 | 21/5 | 31 |
| 日向 | 21/6 | 32 |
| 日向 | 21/7 | 33 |
列のピボット解除
Power Queryでの操作は、列「21/5」から「21/7」までを選択 -> 変換タブ -> 列のピボット解除 -> 数式バー -> 数式= Table.UnpivotOtherColumns(テーブル名, {"国"}, "属性", "値")の、"属性"を"年月"に、"値"を"売上"に書き換える。
生成された数式を見ればわかるが、選択した列「21/5」から「21/7」以外の列（＝列「国」）をTable.UnpivotOtherColumns関数の引数として使用しており、結果は次のその他の列のピボット解除と全く同じになる。
その他の列のピボット解除
同じデータに対し、列「国」を選択 -> 変換タブ -> その他の列のピボット解除 -> 数式バー -> 数式= Table.UnpivotOtherColumns(テーブル名, {"国"}, "属性", "値")の、"属性"を"年月"に、"値"を"売上"に書き換える。
選択した列のみをピボット解除
同じデータに対し、列「21/5」から「21/7」までを選択 -> 変換タブ -> 選択した列のみをピボット解除 -> 数式バー -> 数式= Table.Unpivot(テーブル名, {"21/5", "21/6", "21/7"}, "属性", "値")の、"属性"を"年月"に、"値"を"売上"に書き換える。
この方法のみTable.Unpivot関数を使用。列「21/5」から「21/7」を引数としているため、この値が変わると使えなくなる。
ピボット
ピボット解除とは逆に、データを縦持ち（Long）から横持ち（Wide）に変換する。表の同じ場所に入るデータが複数あることもあるため、集計を伴う。
以下の変換を考える。
横持ち(Long)
| 国 | 年月 | 売上 |
|---|---|---|
| 薩摩 | 21/5 | 11 |
| 薩摩 | 21/6 | 12 |
| 薩摩 | 21/7 | 13 |
| 大隅 | 21/5 | 21 |
| 大隅 | 21/6 | 22 |
| 大隅 | 21/7 | 23 |
| 日向 | 21/5 | 31 |
| 日向 | 21/6 | 32 |
| 日向 | 21/7 | 33 |
↓
縦持ち（Wide）
| 国 | 21/5 | 21/6 | 21/7 |
|---|---|---|---|
| 薩摩 | 11 | 12 | 13 |
| 大隅 | 21 | 22 | 23 |
| 日向 | 31 | 32 | 33 |
Power Queryでの操作は、列「年月」を選択 -> 変換タブ -> 列のピボット -> 値列のプルダウンから、売上を選択 -> 詳細設定オプションの値の集計関数で合計を選択 -> OK。
クエリのマージ
2つのテーブル同士を特定の列をキーに紐づける。SQLでのJOIN、Pandasでのjoinやmergeに相当。
売上テーブル
| 国 | 年月 | 売上 |
|---|---|---|
| 薩摩 | 21/5 | 11 |
| 薩摩 | 21/6 | 12 |
| 薩摩 | 21/7 | 13 |
| 大隅 | 21/5 | 21 |
| 大隅 | 21/6 | 22 |
| 大隅 | 21/7 | 23 |
| 日向 | 21/5 | 31 |
| 日向 | 21/6 | 32 |
| 日向 | 21/7 | 33 |
四半期テーブル
| 年月 | 四半期 |
|---|---|
| 21/5 | 1Q |
| 21/6 | 1Q |
| 21/7 | 1Q |
↓ クエリのマージ
| 国 | 年月 | 売上 | 四半期 |
|---|---|---|---|
| 薩摩 | 21/5 | 11 | 1Q |
| 薩摩 | 21/6 | 12 | 1Q |
| 薩摩 | 21/7 | 13 | 2Q |
| 大隅 | 21/5 | 21 | 1Q |
| 大隅 | 21/6 | 22 | 1Q |
| 大隅 | 21/7 | 23 | 2Q |
| 日向 | 21/5 | 31 | 1Q |
| 日向 | 21/6 | 32 | 1Q |
| 日向 | 21/7 | 33 | 2Q |
Power Queryでの操作は、売上テーブルを開く -> ホームタブ -> クエリのマージ -> 中段のプルダウンで、四半期テーブルを選択 -> 上段の売上テーブルで年月列を選択 -> 下段の四半期テーブルで年月列を選択 -> 下部の結合の種類プルダウンから、左外部を選択 -> OKでクエリのマージ画面を閉じる -> 売上テーブルに四半期列が追加されるので、四半期列右端の「↰↱」ボタンを押す -> ✔を四半期のみにし、元の列名をプレフィックスとして使用しますの✔を外す -> OK
注意: 結合する列同士のデータの型が合っていないと結合できない。例えば日付型と文字列型等（データの見た目が同じでもダメ）。
結合の種類については、こちらを参照
クエリの追加
2つのテーブル同士を縦につなげる。SQLでのUNION、Pandasでのconcatに相当。
売上テーブル
| 国 | 年月 | 売上 |
|---|---|---|
| 薩摩 | 21/5 | 11 |
| 薩摩 | 21/6 | 12 |
| 薩摩 | 21/7 | 13 |
| 大隅 | 21/5 | 21 |
| 大隅 | 21/6 | 22 |
| 大隅 | 21/7 | 23 |
| 日向 | 21/5 | 31 |
| 日向 | 21/6 | 32 |
| 日向 | 21/7 | 33 |
売上2テーブル
| 国 | 年月 | 売上 |
|---|---|---|
| 薩摩 | 21/8 | 14 |
| 薩摩 | 21/9 | 15 |
| 薩摩 | 21/10 | 16 |
| 大隅 | 21/8 | 24 |
| 大隅 | 21/9 | 25 |
| 大隅 | 21/10 | 26 |
| 日向 | 21/8 | 34 |
| 日向 | 21/9 | 35 |
| 日向 | 21/10 | 36 |
↓ クエリの追加
| 国 | 年月 | 売上 |
|---|---|---|
| 薩摩 | 21/5 | 11 |
| 薩摩 | 21/6 | 12 |
| 薩摩 | 21/7 | 13 |
| 大隅 | 21/5 | 21 |
| 大隅 | 21/6 | 22 |
| 大隅 | 21/7 | 23 |
| 日向 | 21/5 | 31 |
| 日向 | 21/6 | 32 |
| 日向 | 21/7 | 33 |
| 薩摩 | 21/8 | 14 |
| 薩摩 | 21/9 | 15 |
| 薩摩 | 21/10 | 16 |
| 大隅 | 21/8 | 24 |
| 大隅 | 21/9 | 25 |
| 大隅 | 21/10 | 26 |
| 日向 | 21/8 | 34 |
| 日向 | 21/9 | 35 |
| 日向 | 21/10 | 36 |
Power Queryでの操作は、売上テーブルを開く -> ホームタブ -> クエリの追加 -> プルダウンで、売上2テーブルを選択 -> OK
Excel/Power BIへのデータの取り込み
Excelの場合
テーブルとして取り込むか否か
Excelのデータタブ -> クエリの表示で、ブック クエリを表示 -> クエリを右クリック -> 読み込み先… -> ブックでデータを表示する方法を選んでください。で選択する。
-
テーブル: ブック内にテーブルとして取り込まれる。Power Queryからも使える。 -
接続の作成のみ: テーブルは作成されない。Power Queryからのみ使える。Power Query内でのみ一時テーブルとして使用する場合はこちらを選択する。
データモデルに取り込むか否か
上記と同じ画面で、このデータをデータ モデルに追加するに✓を入れると、Power Pivotで使用可能になる。Excelの限界行数を超えるデータを扱う場合には、上記で接続の作成のみとし、ここで✔を入れる。
Power BIの場合
データモデルに取り込むか否か
Power Query エディターでクエリを右クリック -> 読み込みを有効にするのチェックを外すとPower BIに取り込まれなくなる。
その他のTips
参考
- E-Trainer.jp (2021) 『Excel Power Query データ収集・整形 自動化入門』 秀和システム