📊PowerQuery（Excel版）の使い方【SQLユーザ向け・GIFアニメあり】


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


# PowerQueryの良いところ



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

## SQL・Pandasにはないメリット

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

## Excel関数にはないメリット

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



# PowerQuery の概要

## クエリ

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/379783e7-7737-5dea-dabb-0f6f3a669e81.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/34cb3eaa-1778-961d-bac3-4c4c951df746.png)



##  M言語

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


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/1b5cb313-d822-3305-c018-e08d1a8c1866.png)

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

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

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



*   let式には…
    *   「`table型の変数 = （処理内容）`」 の形式で記述する
        *   ちなみに、３行目の「[Table.NestedJoin()](https://docs.microsoft.com/ja-jp/powerquery-m/table-nestedjoin)」や４行目の「[Table.ExpandTableColumn()](https://docs.microsoft.com/ja-jp/powerquery-m/table-expandtablecolumn)」はtableを返す関数です。
    *   処理内容は、前の行の変数を使って記述する。 
        *   ちなみに、table型の変数の名前が、ステップの名前になります。
*   inステートメントには…
    *   最後のtable型の変数を記述する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/80c068c8-7d91-79f6-19d9-2594a90e9598.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/cb024a4c-be96-469e-c149-253d2b14e486.png)


### 日本語の変数名が使える

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


# PowerQuery エディターの便利機能

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


## 数式バーの表示

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/08eebf91-dc80-04d3-de0a-3b06c752f880.png)



## 列の統計情報の表示

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

*   列の品質
*   列の分布

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/29672ada-746c-31fa-0a51-9d5b1f77d501.png)



## 例からの値

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/d47442fb-1ac3-be9b-0224-5e6dba404e8c.png)




## クエリの依存関係を表示

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/9a3940c1-5a30-c252-73c2-0e0c7d8b4e65.png)



# テクニック


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

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


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/fbb3a3b4-431b-d9b8-bc71-cc5794c9b3ee.png)



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

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


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/2b7cb81e-3c98-39b9-378c-95f0730da274.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/07cdab4d-ecde-42d5-ff52-9ae56fedc772.png)




## 既存のステップに統合

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


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

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


# SELECT（列の操作）


## 列の作成


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

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

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

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


![40_例からの列_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/128f8106-40b9-df2c-c1e6-ab6757bc93f3.gif)

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

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


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


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

*   [Text.Start](https://docs.microsoft.com/ja-jp/powerquery-m/text-start)（最初のN文字の部分文字列）
*   [Text.Combine](https://docs.microsoft.com/ja-jp/powerquery-m/text-combine)（文字列連結）
*   [Text.From](https://docs.microsoft.com/ja-jp/powerquery-m/text-from)（整数型の「age」列をテキスト型へ変換）


### カスタム列

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

![41_カスタム列_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/87ef797b-dda4-d9b8-cb73-2d012fe660f6.gif)



### DISTINCT（重複削除）

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

（DISTINCT相当）

![42_DISTINCT_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/a9fb96b9-7431-dd48-2820-62270bc8d996.gif)



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


## 列の編集


### 列の並び替え

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


![43_列の並び替え_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/b0130973-8b5a-3024-c301-bc4d6eceb0b8.gif)



### 型の変更

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

![44_型の変更_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/048cca86-5751-8429-8cd2-e5e39e818202.gif)




### 値の置換

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


例）nullを0に置き換え

![45_値の置換_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/44f26d06-c6ea-e156-f6db-a96bb4747a77.gif)

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


## 列の削除

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

![46_列の削除_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/3b1a5785-1351-8588-2c1a-f321549bc885.gif)



# FROM（データソース）

## INNER / LEFT / OUTER   JOIN

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


### 結合


![01_INNER-JOIN1_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/380e77d6-5ce9-3de5-6cb5-00404022120e.gif)


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

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


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/4951b642-e5c7-e381-4587-c82924753171.png)



### 展開

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

![01_INNER-JOIN2_v2.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/94168d60-6a52-f115-7a97-4d4c7ca00547.gif)



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


### 結合の種類

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



*   INNER JOIN（内部）
*   LEFT JOIN（左外部）
*   FULL OUTER  JOIN（完全外部）

	など

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/3aacb506-a112-da31-850b-b1af7f098573.png)




## CROSS JOIN（クロス結合）

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/b7a6ce88-a34e-e546-8d1c-723d8bfa1571.png)


![02_CROSS-JOIN_v3.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/1e85254f-2418-9ef8-298c-81fac551066e.gif)


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


# GROUP BY（集約）

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

![10_GROUPBY_v2.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/ab43921d-7d44-5836-9c43-784677014def.gif)


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


# WHERE（絞り込み）

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

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


![20_WHERE_v2.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/700446dd-a364-0534-38aa-f9bc5a46586b.gif)


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


# ORDER BY（並び替え）

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

![21_ORDER-BY_v2.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/b2898f72-77cb-3ad3-34f8-0194b8effd58.gif)



# LIMIT（レコード数を制限）

![22_LIMIT_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/128272d4-ba64-1b29-078b-4366acd8548d.gif)




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


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


![23_UNION_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/ea2c5d3e-9740-1e95-a340-596f8c8423ce.gif)





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


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

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

```sql:SQLイメージ
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`というクエリを、「新規としてクエリをマージ」という機能で結合して、新しいクエリを作成する例です。

![24_WITH_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/40259314-55e7-3463-bcfe-af2a83868a45.gif)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/39e31efb-f937-48b7-7f21-d84d4f77c32b.png)



# インデックス列の作成

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


![47_インデックス列_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/de2a1e1f-1b2b-c1fb-aa47-ecaf25c06a21.gif)




# 乱数列の作成

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


```
Number.RandomBetween(0,1)
```



## 問題点

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/39facf28-575d-82e2-f477-93af224134c2.png)



## 対策

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


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



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/2623333d-f89c-d6bd-64c9-c42efdc9364d.png)

 

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

参考：[Custom Column Randbetween - Microsoft PoserBI Community](https://community.powerbi.com/t5/Desktop/Custom-Column-Randbetween/m-p/216939)


## 作業イメージ


![48_乱数列_v1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/186338/bd8637dc-438e-b53b-98d8-eafeb65ea609.gif)