このたび就いた新しい仕事では大量のデータの集計、分析を担当しています。そこで使ったExcelのPowerQueryの機能のすごさに驚きました。まさに名前の通りPowerQuery。かっこいい名前ですね。Excel2016からは「取得と変換」となり標準搭載されました。テーブル機能と組み合わせることで大きなパワーを発揮します。
特に複数のデータを寄せ集めて分析する場合、またその作業を繰り返す場合に便利です。型枠のようなものを作っておけばデータを差し替えるだけで再利用できるところは、Accessのクエリみたいな感じです。
なごみExcelの標準機能やVBAを使ってやっていた面倒な作業が、大幅に削減されました。
Accessで扱いにくいデータがきっかけ
PowerQueryを使い始めたのはAccessで扱えないほど大きなデータを処理しないといけなかったからです。
システムからエクスポートされたCSVを使って分析するツールを作成する仕事に就きました。データは約100万件、カラム（列）数は400以上、エクスポートされたCSVファイルは2Gバイトを超えます。前任者はAccessでツールを作成していて、ファイルサイズ1.8Gバイトと2Gバイトの上限ぎりぎりのところで引き継ぎました。Accessで扱えるカラム数は255が上限のため、400カラムから100カラムだけ選んでAccessデータベースにインポートされてキープされ、差分ダウンロードのみされていました。元データは毎日更新され、そこから必要なデータ10万件程抽出して配布するという流れです。Accessで扱うには、データが大きすぎると困っていました。
「ユーザーはExcelに慣れていて出来ればExcel形式で配布して欲しい、出来れば全カラムで」という要望がありました。セキュリティのため規則が厳しくデータベースシステムやソフトをインストールすることができず、あるもの（Microsoft Office）しか使えないという状況です。（結構こういうところは多いのではと思います。）
PowerQueryなら扱える
こんなサイズのデータを扱い慣れていなかったので、どうしたものかと思っていたのですが、PowerQueryで解決することができました。2.2ギガのCSVファイルでも時間は多少かかりますが、データ抽出ができるんですよね。カラム数も心配ないぐらい豊富です。助かった。
Excelに搭載されたPowerQueryエディターを使ってエクスポートしてきたCSVをソースに指定し、100万件のデータから必要なデータを抽出して、Excelで扱えるデータ量にすることにしました。本当は10万レコードぐらいをひとつにしたいところでしたが、各ユーザーのPCのメモリは大抵4Gバイト程度のため、パソコンのスペックを考慮して2〜5万件程度に分割して（ちょうど区切りの良い項目があったので）、3つぐらいのファイルにして配布しています。
PowerQueryの良いところは、データを保持したままファイルの受け渡しができることです。データの取得はリンクのように働くのですが、自動接続をオフにしておいて必要な時だけ手動でデータを更新することができます。リンク切れエラーのような煩わしさがありません。そのため配布もしやすいのです。
PowerQueryでの抽出はAccessのクエリに似た感じで、型の変換や計算、集計ができたり、テーブルの結合（LEFT JOIN、INNNER JOIN、UNION）ができます。
またリレーションシップも設定することができます。参照してデータを処理する部分に関してはAccessみたいに使えそうだなと思っています。取得してきたデータを参照して集計する部分では、Accessでできない部分を補ってくれそうなパワフルなツールだと感じました。
書き込みや単票表示（フォーム）やレポート作成の部分では、Accessがいいかな。Accessではフォームの単票表示みたいにExcelでも簡単にできたら良いのですが・・。データの書き込みには、データベース専門のツールの方が良さそうです。PowerQueryは参照して集計、分析するのに適しているのだろうなぁと思っています。このあたり私も検討中です。
学習できるサイト
Udemyで受講中のコースをご紹介します。PowerQueryは以前のオフィスではアドオンとして提供されていて、デフォルトでは入っておらず自分で入れる必要がありました。Office2016からは取得と変換という形でExcelに標準搭載されました。PowerBIというMicrosoftが提供している強力な分析ツールが別であるのですが、こちらは個人で使う分には無料で使えます。この一部に取得と変換と同じ機能が組み込まれています。
当初はPowerQueryという名前だったため、「取得と変換」というキーワードで探すよりも「PowerQuery」で検索する方が情報が多く得られます。ほぼ同じと見てよいでしょう。さらにUdemyで検索する際にも、PowerQueryで探すよりもPowerBIで探す方が情報が多いです。PowerBIもこれまたすごいです。ビッグデータから得られた情報を簡単にグラフなどにできます。例えばつまみを動かすだけでグラフを変化させたりできます。（表現するの難しい。初めての方はぜひ動画で見てください。プレビューだけだと無料で見られます。ここまで出来るのかと驚きますよ。）
PowerQueryも搭載されてから数年しか経っていないこともあり、まだ書籍や初心者向けの情報が少ないです。機能追加も毎月されているらしくどんどん進化してます。Excel得意な方はぜひ早めに触れてみて、慣れるのが良いと思います。これからはビッグデータの時代ですから、今までのように手入力されたデータだけではなく、システムからエクスポートされる大量データを分析するデータサイエンティストとしての能力が求められることが多くなります。その時には役立つことでしょう。
日本語のサイト
Microsoft
公式サイトですが、英語からの翻訳のせいか少し分かりづらいところがあります。
Qiita
役立つTIPSが豊富です。詳しい方が書かれている記事が多く、内容も専門的。初心者だと難しいかもしれません。
PowerBIとデータ分析の学びサイト
初心者の方にも分かりやすい記事が多い。
Udemy（英語のコースだと激安で勉強できます）
初心者向けの情報が少ない中、初心者向けの動画を見る方法があります。ただし英語です。PowerQueryの日本語の本はあまり無いのですが、英語だと動画で見られます。
Udemyで「PowerBI」と検索すると多数の講座があります。ほとんどのプレビュー（概要説明）を見た上で、まずはこの2つを選びました。日本語のコースも少しありますが、まだ少ないし比較的受講料が高めです。英語だと初心者向けのコースが充実しています。英語に抵抗が無いならおすすめです。セール中なら10時間以上のコースが１コース千円台で受講できます。
コンピュータの講座の場合は動きが見られるので、ある程度雰囲気で聞き取れるレベルでも結構わかるんじゃないかな。プレビューで結構見られるので、興味のある方は確認してからチャレンジしてみられるのをおすすめします。
私は下の英語の２コースを購入しました。Excelも英語もかなり得意な方には、分かりやすくてオススメです。
Microsoft Power BI – Up & Running With Power BI Desktop
Microsoft Power BI – Up & Running With Power BI Desktop | Udemy
Microsoft Power BI – A Complete Introduction
Microsoft Power BI – A Complete Introduction | Udemy
iPhoneやiPadではアプリを使って見ることが出来ます。アプリの方がレクチャーが小分けされていて、見たい場所にジャンプできるので扱いやすいです。
Udemy（日本語）
日本語のコースが出てきました。英語が苦手な方にはオススメです。
Microsoft Excel – Power Pivot, Power Query, DAX 入門講座 | Udemy
【続】Microsoft Power BI Desktop – 入門講座 ～Power Query Editor編～ | Udemy
Power BI を使用したデータ分析入門講座 ｜Udemy
YouTube
実際に見て良かった動画を「再生リスト」にお気に入りとして入れていっています。よろしければご参考にしてください。PowerQueryはPowerBIの機能の一部ですので、PowerBIの動画でその部分だけ見るのもおすすめです。
普段の動画視聴のついでに、このリストは随時更新していっています。
Power Queryの本
どちらも良心的な価格です。しかもKindle Unlimited会員だと無料で読めます。
入門
Excel Power Query入門 Kindle版｜Amazon
中・上級者向け
ワークシートからパラメータ（抽出キーワードなど）設定ができるようになると便利です。例えばユーザーにはワークシートから条件を入力してもうことができるようになり、パワークエリの画面を触る必要がなく敷居がさがります。私はこれを利用してツールとしてユーザーに提供しています。
パワークエリの画面は慣れると難しくはないのですが、ユーザーから見ると複雑でとっつきにくいものです。また不用意に壊してしまったりということもなくなり安心です。