ExcelでPowerQueryを使ってデータ収集分析

# はじめに

Excelにデータを集めようとするとき、数式とVBAを駆使して行う方法が一般的です。
キーを使って複数のデータを結合するときには、Accessなども用いられます。

ですが、データが増えてくると下記のような課題が発生してきます。

* 収集に必要な手順が増え、数式やVBAが複雑化する
* データ収集に長い時間がかかる
* サイズが巨大化して格納しきれなくなる

これらの課題を解消するツールとして **PowerQuery** を紹介します。 

# PowerQueryとは

2016年に公開されたMicrosoft製のデータ分析用Excelアドインであり、Excel2010以降に対応しています。
ファイル・DB・Webサービス等からデータを読み込み、変換・加工してExcelのシートに出力できます。
VBAのマクロの記録と同じように、画面上で操作するとクエリ（読み込み手順の定義）が生成されます。
複雑な加工をしない限りプログラミングは必要ありません。

Excel内に全ての情報を格納するため、一つのファイルで完結し他のツールは必要ありません。
PowerQueryの入っていない端末では普通のExcelファイルとして閲覧することができます。

中間処理は全てメモリ上で行われ、処理が非常に高速であることが特徴です。
ファイルに出力されるのは最終の出力結果のみであるため、ファイルサイズも少なく済みます。

# PowerQueryのインストール

Excel2010,Excel2013であれば、下記からダウンロードしてインストールします。

https://www.microsoft.com/ja-jp/download/details.aspx?id=39379

Excel2016以降からは標準機能となっているのでインストールの必要はありません。
PowerQueryではなく **取得と変換** と名付けられていますが、同じ機能です。

# PowerQueryの起動

## Excel2010, Excel2013

PowerQueryタブをクリックします。

![alt](https://support.content.office.net/ja-jp/media/044960d4-cdb5-4949-a250-2e95d606d203.png)

## Excel2016 以降

取得と変換から新しいクエリをクリックします。
Office365 を利用している場合、最新版では[Excelの標準機能としてデータタブに統合](https://support.office.com/ja-jp/article/%E7%B5%B1%E5%90%88%E3%81%95%E3%82%8C%E3%81%9F%E5%8F%96%E5%BE%97%E3%81%A8%E5%A4%89%E6%8F%9B-ad78befd-eb1c-4ea7-a55d-79d1d67cf9b3?ui=ja-JP&rs=ja-JP&ad=JP)されています。

![alt](https://support.content.office.net/ja-jp/media/f607acc5-f862-4cae-bf29-34ee4fc78390.png)

# PowerQueryを使ってみる

PowerQueryを使ってデータを読み込むサンプルです。
単純な読み込み処理で加工・変換は行っていませんが、その分手順の少なさがわかります。

## フォルダ内のファイルを一覧にする

複数のフォルダに分散したファイルを一覧にしていきます。

### 手順

フォルダーから をクリックします。

![2017-06-17 12_24_33-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/a6410432-ca45-e1f6-edc5-23d2d469db91.png)

取得するフォルダーパスを指定します。

![2017-06-17 12_25_40-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/aa411e67-3e38-7fec-c25f-9b37ee4e10b3.png)

読み込み をクリックします。

![2017-06-17 12_26_09-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/3bc10817-21ad-a687-77bd-ad24b32fc8c3.png)

ファイルの一覧が読み込まれます。

![2017-06-17 12_26_33-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/17ab2228-c2ed-df92-1bc0-fd86db0c3bc6.png)

### 読み込んだデータを更新する

読み込んだデータを最新化するには、**すべて更新** をクリックします。

![2017-06-17 12_28_28-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/f5225777-10fd-fa37-2cce-6b512e88a912.png)

新しく追加されたファイルが読み込まれました。
データの更新はPowerQueryが入ったExcelであれば、どの端末であっても実施できます。

![2017-06-17 12_28_51-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/43195fe2-9af0-9585-352e-1e8f0af85e8a.png)

## 複数のExcelファイルを結合する

複数のファイルを結合して読み込みます。

### 手順

フォルダーから をクリックします。

![2017-06-17 12_24_33-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/a6410432-ca45-e1f6-edc5-23d2d469db91.png)

取得するフォルダーパスを指定します。

![2017-06-17 12_25_40-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/aa411e67-3e38-7fec-c25f-9b37ee4e10b3.png)

結合および読み込み をクリックします。

![2017-06-17 12_31_20-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/57c63b54-ff2a-8fea-c07f-3a6f617644f0.png)

読み込む対象ファイルの例が表示されるので、OKをクリックします。

![2017-06-17 12_32_31-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/5eaed3d0-c992-19ba-780b-653d6aafbf35.png)

Excelに結合したデータの一覧が読み込まれます。

![2017-06-17 12_32_59-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/92f18f38-25e2-3086-9cdf-3f113e25b629.png)

## Webサービスから読み込む

Webサービスから読み込みます。サンプルに用いているのは[Qiitaの記事投稿数が多いタグを取得するAPI](http://qiita.com/api/v2/docs#タグ)です。

### 手順

Webから をクリックします。

![2017-06-17 12_33_14-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/3c15d26a-72fc-6e13-31c1-751c84c3f794.png)

APIのURLを指定します。

![2017-06-17 12_33_26-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/a9de1faa-9f1a-b5a8-bc0c-20723e001252.png)

テーブルへの変換 をクリックします。
JSONはPowerQuery内でリストとして取り込まれ、テーブルに変換することで加工可能になります。

![2017-06-17 12_34_14-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/e19a6ed0-3a7d-ba70-8585-318d03ce7bc1.png)

必要に応じて区切り記号など選択しOKをクリックします。

![2017-06-17 12_34_40-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/74ea735e-44fa-10ff-ee65-6f56ca4dac86.png)

テーブル形式に変換されるので、列タイトルにある展開ボタンをクリックします。

![2017-06-17 20_56_51-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/9178989c-b863-e82a-5aca-021a4a8f5a5f.png)

テーブルが展開されたら、閉じて読み込むをクリックします。

![2017-06-17 12_35_24-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/2b03aedd-07fc-0677-c7c4-9def46a44948.png)

ExcelにAPIから取得したデータが読み込まれます。

![2017-06-17 12_35_48-Clipboard.png](https://qiita-image-store.s3.amazonaws.com/0/24602/878562f9-f60c-79bf-82c5-5d297229f3e6.png)

# その他の機能について

PowerQueryは読み込んだデータを変換・加工することで真価を発揮します。
Excelの数式で実現できることは一通りPowerQueryでも実装されており、
クエリーエディタ上で手順をプレビューしながら変換・加工していくことができます。

## PowerQueryでできることの例

下記は一例ですが、画面操作だけで他にも様々な処理を定義することができます。

* 必要な行・列のみを取得する
* 値を加工して出力する（LEFT,MID,RIGHT関数に相当)
* 特定の条件に該当する行にマークをつける(IF関数に相当）
* 複数の行をグループ化して集計する
* ピボット集計する/ピボット集計されたデータを元に戻す
* 複数のクエリを参照して新しいクエリを作成する
* IDを使って複数のクエリを結合する（VLOOKUP関数に相当）
  * 内部結合、外部結合などSQLで実現できる一通りの結合処理が行えます
  * [ExcelでPowerQueryを使ってデータ収集分析](http://qiita.com/mosugi/items/b252aa9c9188e2ab2bde)に詳しい手順を記載しています。
* 特定の加工をまとめて実施するカスタム関数を定義する
* ファイルパスなどの変化する値をパラメータ化する
* データソースにセキュリティをかけ、アクセス制限を行う

また、PowerQueryで作成したクエリはM言語(PowerQueryFormulaLanguage)のプログラムとして出力されます。
GUI上にない処理であっても、M言語を駆使すると自由にクエリを組み立てられるようになります。
クエリを編集するには **詳細エディター** を開いてください。

![2017-06-17 12_49_47-.png](https://qiita-image-store.s3.amazonaws.com/0/24602/23ad17da-84e6-a6f1-59bb-68ba05b0eea7.png)

下記は Webサービスから読み込む で出力されたプログラムです。

![2017-06-17 12_48_09-詳細エディター.png](https://qiita-image-store.s3.amazonaws.com/0/24602/96956720-5c96-d595-d464-c46e15954f15.png)

# 役に立つリンク

## 基本的な情報

[Power Query - 概要と学習](https://support.office.com/ja-jp/article/Power-Query-%E6%A6%82%E8%A6%81%E3%81%A8%E5%AD%A6%E7%BF%92-ed614c81-4b00-4291-bd3a-55d80767f81d)

Microsoftの公式サイトです。日本語訳がわかりにくいこともありますが、情報が豊富に載っています。

[Road to Cloud Office](https://road2cloudoffice.blogspot.jp/)

PowerQueryに関する情報が日本語で書かれています。
解説もアニメーションGIFを交えて丁寧に行われていてわかりやすいです。

## M言語の情報

[Power Query メモ](https://sites.google.com/site/powerquerymemo/home)

日本語で書かれたM言語の関数およびデータ型のリファレンスです。
必要な関数を検索するときに使っています。
日本語訳は[artfulplace.net](http://artfulplace.net/docs/powerquery/)さんが実施されています。

[Power Query M function reference](https://msdn.microsoft.com/library/1ed840b1-7e20-4419-ad2f-d82054c9b2ab)

Microsoftの公式関数リファレンスです。
日本語訳がなくExampleが少ないのが難点ですが、全ての関数を網羅しています。

[Chris Webb's BI Blog](https://blog.crossjoin.co.uk/)
[The Ken Puls (Excelguru) Blog](https://www.excelguru.ca/blog/)
[Matt Masson](https://www.mattmasson.com/)

M言語のサンプルが豊富に載っています。
GUIだけでは実現できない高度なデータ収集手順を作成するのに役に立ちます。

[Github:pquery](https://github.com/tycho01/pquery)

M言語のカスタム関数集です。
標準の関数で実現できない処理があればここを探してみています。