Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
33
Help us understand the problem. What is going on with this article?
@lilacs

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

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

PowerQueryの良いところ

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

SQL・Pandasにはないメリット

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

Excel関数にはないメリット

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

PowerQuery の概要

クエリ

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

image.png

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

image.png

M言語

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

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

image.png

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

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

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

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

image.png

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

image.png

日本語の変数名が使える

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

PowerQuery エディターの便利機能

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

数式バーの表示

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

image.png

列の統計情報の表示

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

  • 列の品質
  • 列の分布

image.png

例からの値

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

image.png

クエリの依存関係を表示

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

image.png

テクニック

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

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

image.png

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

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

image.png

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

image.png

既存のステップに統合

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

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

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

SELECT(列の操作)

列の作成

例からの列(ロジック推定~条件分岐・文字列操作など)

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

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

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

40_例からの列_v1.gif

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

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

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

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

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

カスタム列

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

41_カスタム列_v1.gif

DISTINCT(重複削除)

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

(DISTINCT相当)

42_DISTINCT_v1.gif

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

列の編集

列の並び替え

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

43_列の並び替え_v1.gif

型の変更

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

44_型の変更_v1.gif

値の置換

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

例)nullを0に置き換え

45_値の置換_v1.gif

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

列の削除

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

46_列の削除_v1.gif

FROM(データソース)

INNER / LEFT / OUTER JOIN

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

結合

01_INNER-JOIN1_v1.gif

0.JOINの左側テーブルとなるクエリ(つまり、receipt)を選択
1.クエリのマージ > 新規としてクエリをマージ
2.右側テーブルを選択
3.結合キーを選択(storeテーブル)
4.結合キーを選択(receiptテーブル)
5.内部結合を選択
6.結合条件を確定
7.(receiptの各行に結合された)

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

image.png

展開

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

01_INNER-JOIN2_v2.gif

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

結合の種類

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

  • INNER JOIN(内部)
  • LEFT JOIN(左外部)
  • FULL OUTER JOIN(完全外部)

    など

image.png

CROSS JOIN(クロス結合)

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

image.png

02_CROSS-JOIN_v3.gif

1.結合先テーブル(product)への参照を作成
2.カスタム列で、固定値の列(_key)を追加
3.結合元テーブル(store)も同様(クエリへの参照、固定値の列を追加)
4.固定値の列を結合キーにして、完全外部結合する
5.必要に応じて部分テーブルを展開する

GROUP BY(集約)

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

10_GROUPBY_v2.gif

1.集約キー(customer_id)を選択しながら、グループ化を選択
2.集約方法(合計)を指定
3.合計する列(amount)を指定
4.集計結果の列名(amount)を指定

WHERE(絞り込み)

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

(WHERE customer_id NOT LIKE 'Z%'に相当)

20_WHERE_v2.gif

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

ORDER BY(並び替え)

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

21_ORDER-BY_v2.gif

LIMIT(レコード数を制限)

22_LIMIT_v1.gif

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

UNION(テーブルを縦に連結)

23_UNION_v1.gif

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

WITH句・副問い合わせ(複数のクエリ)

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

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

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

image.png

インデックス列の作成

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

47_インデックス列_v1.gif

乱数列の作成

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

Number.RandomBetween(0,1)

問題点

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

image.png

対策

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

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

image.png

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

参考:Custom Column Randbetween - Microsoft PoserBI Community

作業イメージ

48_乱数列_v1.gif

33
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
lilacs
手広くプログラミングするエンジョイ勢です。

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
33
Help us understand the problem. What is going on with this article?