Power Query入門

# 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エディターを起動。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/2adae0b8-1723-f008-84eb-c78089a97152.png)

エディターは以下の4つの部分に分かれている:

- 上部: リボン
- 左側: `クエリ`ペイン
- 真ん中: プレビュー
- 右側: `クエリの設定`ウィンドウ

以下、各部分について説明する

## 上部: リボン
Power Queryでできる様々な機能がタブごとにまとめられている。[後述の各種機能](https://qiita.com/drafts/7c0a093865b64e7c0911/edit#power-query%E3%81%AE%E4%B8%BB%E3%81%AA%E3%83%87%E3%83%BC%E3%82%BF%E5%A4%89%E6%8F%9B%E7%B7%A8%E9%9B%86%E6%A9%9F%E8%83%BD)もここから呼び出す。
### 詳細エディターとM言語
`ホーム`タブの`詳細エディター`から、`詳細エディター`を開けば、クエリーの元になっているM言語を編集できる。M言語とはPower Queryの裏側で動いているプログラミング言語。リボンのボタンから行えないような細かい調整が可能。

## 左側: `クエリ`ペイン
クエリーやパラメーターの一覧。それぞれの意味は以下:

- クエリ: データソース、データ加工等をひとまとめにしたもの。結果は基本的にテーブルだが、単独の値や、複数の値からなるリストやレコードとなることもある。また、クエリは他のクエリから再利用することも可能。
- パラメーター: 値のみを格納したもの。値はエディター上で設定する。

クエリー、パラメーターはグループと呼ばれるフォルダーのようなものに格納可能。クエリー等が多くなったときにグループ分けすれば見やすくできる。

## 真ん中: プレビュー
`クエリ`ペインで選択しているクエリの実行結果のプレビューが表示される。フィルター操作や列の並べ替え等はここで行える。

## 右側: `クエリの設定`ウィンドウ
クエリの各ステップ（個々の処理）が表示される。クエリの各ステップは編集の度に自動的に追加される。名前が分かりにくいので、以下のように分かりやすいよう変更すると良い。![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/418984cb-652d-53ff-5713-d1093249697a.png)


# Power Queryの主なデータ変換・編集機能

★付きは詳細を説明。

- ★[ヘッダー調整](https://qiita.com/k_maki/items/7c0a093865b64e7c0911#%E3%83%98%E3%83%83%E3%83%80%E3%83%BC%E8%AA%BF%E6%95%B4)
- ★[データ型の変換](https://qiita.com/k_maki/items/7c0a093865b64e7c0911#%E3%83%87%E3%83%BC%E3%82%BF%E5%9E%8B%E3%81%AE%E5%A4%89%E6%8F%9B)
- ★[列追加](https://qiita.com/k_maki/items/7c0a093865b64e7c0911#%E5%88%97%E8%BF%BD%E5%8A%A0)
- 列選択/削除
- 列の移動
- 行の抽出
- 行の並べ替え
- 重複行の削除
- 値の置換
- 欠損値の補間
- 行と列の入替
- グループ化（集計）
- ★[ピボット解除](https://qiita.com/k_maki/items/7c0a093865b64e7c0911#%E3%83%94%E3%83%9C%E3%83%83%E3%83%88%E8%A7%A3%E9%99%A4)
- ★[ピボット](https://qiita.com/k_maki/items/7c0a093865b64e7c0911#%E3%83%94%E3%83%9C%E3%83%83%E3%83%88)
- ★[クエリのマージ](https://qiita.com/k_maki/items/7c0a093865b64e7c0911#%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AE%E3%83%9E%E3%83%BC%E3%82%B8)
- ★[クエリの追加](https://qiita.com/k_maki/items/7c0a093865b64e7c0911#%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AE%E8%BF%BD%E5%8A%A0)

## ヘッダー調整
テーブルからデータを取得する場合は問題ないが、シートからデータを取得する場合は、シートの1行目をヘッダーとして使用してしまうため、ヘッダーが2行目以降にあるようなときには修正が必要。`ホーム`タブ -> `1行目をヘッダーとして使用` ボタンを押すたびに、ヘッダー行が1行ずつ下にずれていくので、必要な回数実行する。

## データ型の変換
データ型とは、整数、10進数、テキスト等のデータの種類。列ごとに同じデータ型でなければならない。
データ取り込み時に最適な型が自動的に設定されるが、手動設定も可。手動で変換するには、数式バーか詳細エディターで、`=Table.TransformColumnTypes(テーブル, {{列1, データ型1}, {列2, データ型2}, …, {列n, データ型n}})`のデータ型部分を修正する。
主なデータ型は次のとおり:

| データ型 | アイコン | M言語表示 | 説明 |
|:-|:-:|:-|:-|
| テキスト      | ![text_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/7c949c49-600f-6cdf-b33f-2fcd4de69d5a.png) | `type text`        | Unicodeテキスト |
| True/False    | ![logical_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/759c3790-7b53-f3ba-6e54-4ddd9f0c5949.png) | `type logical`     | TrueまたはFalseいずれかの値 |
| 10進数        | ![number_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/8f5de213-e858-c7f6-6c03-979946b4f7a7.png) | `type number`      | 64bit浮動小数点数 |
| 通貨          | ![currency_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/cb6e27f3-27bf-9e5b-d52b-c6e5a37e8392.png) | `Currency.Type`    | 小数点区切り文字の右側には常に4桁の数字が入り、有効数字は最大19桁 |
| 整数          | ![wholenumber_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/de5be816-8d65-32f5-7d78-7807309cad24.png) | `Int64.Type`       | 64bit整数値 |
| パーセンテージ | ![percentage_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/a1fcbf30-afaa-e36c-6e44-a70a482337b1.png) | `Percentage.Type` | 値がパーセンテージ表示される以外は10進数と同様 |
| 日付/時刻     | ![datetime_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/317fd9f6-f929-91ea-b6e9-ad659bfc582c.png) | `type datetime`    | 日付と時刻 |
| 日付          | ![date_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/e8dfbee7-aa4b-2d93-5308-9d0239987d5e.png) | `type date`        | 日付 |
| 時刻          | ![time_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/28d29ed0-1075-12d9-3e01-72f92ca8204e.png) | `type time`        | 時刻 |
| バイナリー | ![binary_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/4874576f-4806-4d37-bb81-637687e6c81a.png) | `type binary` | バイナリー データ（ファイルそのもの）
| [任意]        | ![anytype_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/2975c6e9-9bce-0aa1-58d9-8d3b3794f100.png) | `type any`         | 何でも入るが、出力時には型を指定すること |

## 列追加
`列の追加`タブ -> `カスタム列`で、カスタム列エディターを開く。カスタム列の式に関数を入力する。例えば、`日付`列から年を取り出すには、`Date.Year([日付])`と入力する。使用できる関数は[公式ドキュメント](https://docs.microsoft.com/ja-jp/powerquery-m/power-query-m-function-reference)を参照。


## ピボット解除
**ピポットではない**（[間違えないように](https://dic.nicovideo.jp/a/%E3%83%8E%E3%83%BC%E3%83%90%E3%83%B3%E5%A7%8B%E7%90%83%E5%BC%8F)）。

データの持ち方を横持ち（Wide）から縦持ち（Long）に変える操作。データの持ち方については[こちら](https://newssdx.kcme.jp/tableau_beginner_data_vertical_horizon/)を参照。データの蓄積・集計を行うなら、縦持ちの方が便利。また、常に[整然データ](https://id.fnshr.info/2017/01/09/tidy-data-intro/)となることを意識しておくこと。

以下の変換を考える。

横持ち（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`

**注意**: 結合する列同士のデータの型が合っていないと結合できない。例えば日付型と文字列型等（データの見た目が同じでもダメ）。

`結合の種類`については、[こちら](https://note.com/excel_dokata/n/n97d4216f5200)を参照

## クエリの追加
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
- [Power Queryでよく使う関数](https://qiita.com/k_maki/items/6dd22c81e1f268ec0938)
- [Power QueryのM言語でテーブルから行番号と列名を使って値を取得する](https://qiita.com/k_maki/items/2ca67b8dea203d43a741)
- [Power Queryのヘルパークエリは良いやつだし、カスタム関数はデバッグできる](https://qiita.com/k_maki/items/cab7a340ffc717ef1a4d)

# 参考
- E-Trainer.jp (2021) 『[Excel Power Query データ収集・整形 自動化入門](https://www.shuwasystem.co.jp/book/9784798064857.html)』 秀和システム