カテゴリー: Power Query

Power Queryとは何か、どうやって使い始めることができるのかをご紹介します。

Power Queryとは？

いわゆるETL機能のことで、ExcelやPower BIに実装されているものです。

Power BIでデータの可視化を行う場合は、グラフ化をする前にこのPower Queryを使用して、グラフ化するためのデータ整形・加工を行います。

また、Excelでデータの二次利用等を行う方は、このPower Queryの使い方を覚えることで作業効率を飛躍的に向上させることができます。
Power Queryを知らないExcelワーカーは多く、Power Queryを使う方と比べて作業時間に10倍以上の差が出てくるように筆者は感じます。
裏を返せば、Power Queryを使用する方は、それを使用しない方と比べて10倍以上の作業をこなすポテンシャルを持っています。

Power Queryで出来ること

Power QueryはMicrosoft製品のExcel・Power BIに実装されたETL機能のことです。

主に下記３つのことができます。

1. データ抽出
   csvファイルやExcelファイル、システムやデータベース等のデータをExcel・Power BIに取り込む（抽出）ことができます。
2. データ整形（変換）・加工
   抽出したデータに対してデータ型の変更、列の追加、値の置換等を行うことができます。
3. データ書き出し
   Excelの場合はシートに書き出すことができます。シートへの書き出しはExcelの制約（行の上限等）を受けるため注意が必要です。
   また、Power pivotによる集計を行うためのデータモデルとして書き出すことができます。
   Power BIの場合は、グラフ化する元データとなるデータモデルとして書き出すことができます。
   Excel・Power BIどちらもデータモデルへの書き出しは、Excelのシートに書き出すこととは意味が異なります。
   Excelのシートに書き出した場合はシート上でデータを確認することができますが、データモデルに書き出した場合はデータベース上でデータを確認するイメージとなります。

ExcelやPower BIのPower Queryの位置づけを図示してみましたので、ご参考になれば幸いです。

Power Query（エディター）の始め方

ExcelとPower BIの両方を見ていきたいと思います。

なお、Power Queryを使ったデータの整形や加工に関する具体的なやり方は別の記事でご紹介していきますので、ここではPower Queryをどうやって使い始めることができるのか？についてお話します。

ExcelにおけるPower Query

Excelファイルでは、”データ”タブ(①) > データの取得(②) > Power Query エディターの起動(③)をクリックすることで、Power Queryを使うためのエディター画面が立ち上がります。

Power Queryを使ったデータ抽出をする場合は、エディターから行うこともできますが、”データの取得”横のボタンから行うこともできます。

Power BIにおけるPower Query

Power BI Desktop アプリからPower Queryを使用する方法をご紹介します。

アプリを起動し、”ホーム”タブ(①) > データの変換(②)をクリックすることで、Power Queryを使うためのエディターを起動することができます。

エディター画面

ExcelとPower BIのエディター画面です。
手前がPower BI、奥がExcelです。

画面としての違いは微妙にありますが、データの整形・加工という観点では使い方もできることも同じです。

さいごに

ExcelでPower Queryの使い方を覚えれば、必然的にPower BIのBIを作るための元データとなるデータ整形・加工もできるようになります。

Power BIは、BI化するデータがないと経験を積むことも難しいですので、Power BIを使ってみたいという方もまずはExcelでPower Queryの使い方を習得することをおすすめします。

Power Queryで、リスト化を覚えると応用が効くようになります。
リスト化の方法を覚えておきましょう。

リスト化とは？

小難しいことはやめて、ざくっとイメージでご説明します。

アルファベット(alphabet)カラムに文字通りアルファベットが値としてはいっているテーブルがあるとします。

リスト化すると {“a”, “b”, “c”, “d”, “e”, “f”} という形に変わります。
ちなみに、値のサイドにダブルクォーテーションマークがついているのは値の型がテキストであるためです。

あくまでイメージで、こう理解しておけば問題ありません。

では、カラムが複数ある場合のテーブル(↓)の場合はどうでしょうか。

リスト化すると {{“a”, “A”}, {“b”, “B”}, {“c”, “C”}, {“d”, “D”} , {“e”, “E”} , {“f”, “F”}}となります。

リスト化の方法

カラムが１つの場合・・・

この２つの覚えておけばよいです。

やり方❶

該当のテーブルを選択(①)し、変換タブ(②)の中の”リストに変換”をクリック(③)する。

やり方❷

該当のテーブルの選択(①)し、カラム名のところで右クリック(②)し、”ドリルダウン”をクリック(③)する。

どちらの方法でやっても結果は一緒です。

複数カラムの場合・・・

一般的には詳細エディターからM言語を書いていく手法が一般的に紹介されています。
が、エンジニアではない方とか言語があまり得意でない方向けにやり方をご紹介します。

まず、該当のテーブルを選択(①)し、”ホーム”タブ(②)の”列の選択”(アイコンの方)をクリック(③)し、”列の選択”というポップアップ画面が表示されるので、OKをクリック(④)します。

作業を終えると、このように(↓)なります。
黄色で塗りつぶしたクエリの記述を次は変更します。

クエリの記述を”= Table.ToRows(変更された型)”に変更します。
より具体的にいうと、”SelectColumns”の部分を”ToRows”に変更し、”,{“alphabet”, “Large”}”の部分を削除します。

作業を終えると、このように(↓)なります。

何に使えるのか？

リストの存在を知り、クエリの記述を見ていくと、{…} や {{…}, {…}} という記述があることに気付けると思います。

具体的な内容で例をあげると、カラム名の変更が挙げられます。

前出の複数カラムのテーブルでカラム名を変更してみます。
“alphabet”を”アルファベット”、”Large”を”大文字”に変えます。
作業を終えると、 {{…}, {…}} (黄色塗りつぶし箇所)が確認できると思います。

これはリストを表しています。

今回の例でいえば、 {{“alphabet”, “アルファベット”}, {“Large”, “大文字”}} というリストをつくっておけば、そのリストに置き換えても同様の結果が得られるわけです。

この辺の具体的な使い方はまた今度にします。

データをフィルターするというと、やり方はオートフィルターと思う方もいるかと思いますが、手段を問わず結果としてフィルターする方法はいくつかありますので、ご紹介します。

オートフィルター以外のフィルターとは？

• リストでフィルター
• Joinを使ったフィルター

リストでフィルター

これはGUIでは実装ができず、クエリを記述することが求められます。
あらかじめ用意したリストの値に一致するレコードだけにフィルターすると言ったことができます。

リストって何？という方はこちらをご参照ください。

Power Query｜テーブルをリストとして扱う方法

リストを使ったフィルターの具体的なやり方を、非常に単純な例で説明していきます。

営業担当者の売り上げが入力されたデータがあるとして、この中からAさんとBさんの売り上げにフィルターしたい場合を考えます。

この場合、作業として上述の”条件でフィルター”する方が早いと思いますが、リストで管理する対象が増減する場合は、リストの編集を通してフィルターが動的に変わる方が効率が上がることは想像ができるかと思います。

今回、AさんとBさんの売り上げにフィルターするので、AさんとBさんをリストとして用意します。

上述の表を”営業売り上げ”テーブルとして、リストとして用意する内容(Aさん、Bさん)を”対象者”テーブルとして取り込み、”対象者”テーブルはリスト化します。

するとPower Queryエディター上では以下のように表示されるかと思います。

次に”営業売り上げ”テーブルの営業担当者をオートフィルタで”Aさん”のみにフィルターします。

クエリの内容を書き換えます。
数式バーを見ると、このように記述されています。
= Table.SelectRows(変更された型, each ([営業担当者] = “Aさん”))

この後半部分の ([営業担当者] = “Aさん”) を List.Contains(対象者,[営業担当者]) と書き換えます。
以上で、目的のAさんとBさんの売り上げのみにフィルターしたテーブルを取得できます。

考え方として、 ”営業担当者”列で”Aさん”であるもの を “営業担当者”列で対象者を含む(List.Contains)もの に書き換えたということをイメージいただくと、ほかでも応用が利かすことができると思います。

ただし、取り扱うにデータ量が多くなると処理スピードが遅いように感じます。
その場合は次にご紹介するJoinを使った方法が良いかと思います。

Joinを使ったフィルター

クエリのマージを使います。
これが一番、高速で手っ取り早いフィルター方法かと思います。

リストによるフィルタではリストを使いましたが、ここではすべてテーブルを使用します。
ここでも前出の例と同じ例を使ってご説明します。前出の例と異なる部分は”対象者”をリストではなくテーブルで用意していることです。

早速、具体的なやり方をご説明していきます。

”営業売り上げ”を選択(①)し、”ホーム”タブ(②)から”クエリのマージ”をクリック(③)します。

”マージ”という画面が立ち上がりますので、マージするテーブルとして”対象者”を選択(④)し、照合列として、”営業売り上げ”テーブルの”営業担当者”、”対象者”テーブルの”対象者”をクリック(⑤)します。
最後に結合の種類で”右外部”※を選択(⑥)し、OKをクリック(⑦)して、作業終了です。
※ここの使い方は使い倒していくと理解できると思うので、ここでは詳細を割愛します。

結果としては以下のようになります。
AさんとBさんのみ売り上げにフィルターできています。
なお、対象者という列が追加になっていますが、Tableの状態で結合されている状態であるため、展開しない限り”営業売り上げ”テーブルの列として表示されることはありません。

Power Queryエディターで、標準機能として用意されている”列のピポット解除”をご紹介します。

ピボットを解除するということを意外とイメージできず、利用されていないことがあるように思います。非常に便利な機能で、データの整形・加工を行う上で欠かせないものですので、覚えてデータハンドリングのレベルをぜひ上げてください！

”列のピポット解除”をするとどうなる？

列のピボット解除すると・・・

列の列名がレコード？・・・どういうこと？・・・と思った方は、簡単な例でご説明しますので、以下をご参照ください。

営業担当Aさんの売り上げが4月は100円、5月も100円、営業担当Bさんの売り上げが・・・という表があるとします。

4月と5月の列のピボット解除を行うと下図のように、列名をレコード(4月と5月)として並び替えます。

具体的なやり方は？

上述の例でご説明します。

例示の表を売り上げテーブルとしてPower Queryで取り込んだ状態を前提とします。

１．売り上げテーブルの4月と5月のカラムを選択します。

2.選択カラムにポインタを置いた状態で右クリックして、”列のピボット解除”をクリック

以上で操作は終わりです。4月と5月という列のピボットが解錠されます。