LoginSignup
36

Power Query入門

Last updated at Posted at 2021-10-29

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

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

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

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

上部: リボン

Power Queryでできる様々な機能がタブごとにまとめられている。後述の各種機能もここから呼び出す。

詳細エディターとM言語

ホームタブの詳細エディターから、詳細エディターを開けば、クエリーの元になっているM言語を編集できる。M言語とはPower Queryの裏側で動いているプログラミング言語。リボンのボタンから行えないような細かい調整が可能。

左側: クエリペイン

クエリーやパラメーターの一覧。それぞれの意味は以下:

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

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

真ん中: プレビュー

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

右側: クエリの設定ウィンドウ

クエリの各ステップ(個々の処理)が表示される。クエリの各ステップは編集の度に自動的に追加される。名前が分かりにくいので、以下のように分かりやすいよう変更すると良い。image.png

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

★付きは詳細を説明。

ヘッダー調整

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

データ型の変換

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

データ型 アイコン M言語表示 説明
テキスト text_20.png type text Unicodeテキスト
True/False logical_20.png type logical TrueまたはFalseいずれかの値
10進数 number_20.png type number 64bit浮動小数点数
通貨 currency_20.png Currency.Type 小数点区切り文字の右側には常に4桁の数字が入り、有効数字は最大19桁
整数 wholenumber_20.png Int64.Type 64bit整数値
パーセンテージ percentage_20.png Percentage.Type 値がパーセンテージ表示される以外は10進数と同様
日付/時刻 datetime_20.png type datetime 日付と時刻
日付 date_20.png type date 日付
時刻 time_20.png type time 時刻
バイナリー binary_20.png type binary バイナリー データ(ファイルそのもの)
[任意] anytype_20.png 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

参考

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
36