こんな人に読んでほしい
この投稿では、とある実績表と、そこに登場するユーザーの部署名が記録されている別のCSVファイルをマージさせます。
たぶんExcelではVLOOKUP関数を使ってやっている方が多いと思いますが、Power Queryにチャレンジしてみたい方にお勧めです。
Power QueryをすでにExcelで使っている方ならば経験があると思いますが、クエリがそのまま１つのシートになってしまいます。これを避けるために、２つのテーブルを１つのクエリの中に収める方法もこの記事では解説します。
基本的にはPower BIでも同じなので、クエリをまとめる方法は応用が利きます。すでにPower Queryの基本的な使い方はわかっているので、クエリ内だけの処理を知りたい方は途中を読み飛ばして、こちらからどうぞ。
テストデータを用意する
ユーザーの一覧を用意するために、おすすめのサービス。個人情報テストジェネレーター を紹介します。
使い方はとても簡単。ほしい列を指定して「生成」ボタンをクリックするだけです。これで、ダミーユーザーの一覧が簡単に作成できます。
出力されたCSVファイルをExcelで開いて、部署名列だけ適当に追加しました。
今回は、研修参加者の一覧にメールアドレスしかなく、別のuser.csvからメールアドレスにマッチする氏名と部署名をB列、C列に加えたいというシチュエーションを例にしてみます。結構こういう地味な作業ありますよね？
Power Queryを利用する
Excelで [データ]>[テーブルまたは範囲から」を選択します。
情報の範囲をテーブル作成が促されます。すでにテーブル化されている場合ならそれでもOKです。
Power Queryエディターが開き、先ほどの範囲が「テーブル1」という名前がついたクエリになりました。
ここに、さらにダミーデータとして作成した「users.csv」を取り込んでみましょう。
左側、クエリの空いているスペース。図では「テーブル1」の下あたりで右クリックすると「新しいクエリ」が選べます。今回はCSVファイルなので、[ファイル]>[テキスト/CSV]を選択してクリックします。
CSVファイルを選択します。
文字のエンコードが選べます。表示されているデータが文字化けしていなければ、そのまま[OK]をクリックします。
usersクエリが追加されました。
1行目にヘッダーが表示されてしまっています。[変換]>[1行目をヘッダーとして使用]をクリックします。
１行目だったヘッダーが繰り上がり、氏名、アドレス、部署名がヘッダーになりました。
そのままマージしてみる
テーブル1に、usersテーブルをマージしてみます。Excelの場合はクエリ名がそのままシート名になるので、「テーブル１」の名前を「マージ済み」に変更しておきました。
[ホーム]>[クエリのマージ]>[クエリのマージ]をクリックします。
どの列を結びつけるかを指定します。上側に「マージ済み」テーブルが表示されているところに、下側の「users」の一部の列をくっつけます。上が左、下が右と読み替えます。主は「マージ済み」なので結合の種類は「左外部」を選択しています。
今回のキーはメールアドレスなので、上下それぞれメールアドレスが表示されている列を選択して[OK]をクリックします。
usersの列が「マージ済み」に追加されました。さらにテーブルを展開します。users列ヘッダーの右端にある開くボタンをクリックします。
メールアドレスは既に表示されているのでチェックを外します。「元の列名をプレフィクスとして使用します」のチェックも外して[OK]をクリックします。
これで、メールアドレスに紐づく氏名と部署名を一つのテーブルに表示できました。
Power Queryを確定させると２つのシートができてしまう
ここまでPower Queryで操作ができたら「閉じて読み込む」をクリックしてみましょう。
Excelには3つのシートが表示されています。「マージ済み」シートが今回最終的に作成したいものです。
問題は「users」シートです。Power QueryでCSVファイルを読み込んだことにより、外部のCSVファイルの中身がすべて表示されてしまっています。このシートは作らせたくないですね。
２つのテーブルを１つのクエリ内であつかう
いよいよチョイテクです。まずはPower Queryに戻るために作成されたテーブルあたりを選択した状態で[クエリ]>[編集]をクリックします。
usersクエリ側を選択して右クリック、「詳細エディタ」をクリックします。
詳細エディタでは、先ほどまでGUIで操作した内容がMクエリという言語で記述されています。letからinの間がクエリの実態です。この部分の文字列をコピーしておきます。
マージ済みテーブルに、先ほどのusersテーブルの処理が記述されているMクエリを貼り付けます。下図ではわかりやすいように緑色部分のコメントを加えています。よく見ると、各行はいずれも 行名 = 処理 の形で記載されています。usersテーブルの部分と、マージ済みテーブルの部分で同じ処理名が重複しているので、これらを修正してやる必要があります。
クエリをこのように修正しました。まずは赤い色で囲った部分。usersテーブル部分の先頭の行名には頭にusersの文字を追加してます。4行目だけは加工の最終結果なので「users_table」という名前を付けました。実際にはお好みの名前で構いません。
緑色の資格は、ひとつ上の行の行名が入っています。前の行の処理の結果をつかって次の処理へ引き継いでいるのがわかると思います。
もうひとつ、忘れてはいけないのがusers_table行の一番最後の「,」カンマです。各行の最後には必ずカンマが入ってます。これを追加すると「ソース」行の波線エラーが消えるはずです。
つぎに、「マージされたクエリ数」の行にある「users」の部分を、「users_table」に書き換えます。
これは何を意味するかというと、もともとの「users」とは「users」クエリのことでした。これですね。
もともと「users」クエリの中のMクエリを、「マージ済み」クエリの中に貼り付けて一体化してやったことで、ひとつのクエリの中に収め、処理の結果である「users_table」を「users」クエリの代わりに使ってやることがこれで実現できます。書き換えたら[OK]をクリックします。
修正に成功していれば、エラーが出ずに先ほどと同じ結果が表示されているはずです。画面右側のステップ表示では２種類のソースの読み込みを行っていることが名前からわかりますね。
「users」クエリはこれで用済みとなったので、削除してやります。
「users」クエリを削除しても「マージ済み」クエリにエラーは出ません。１つのクエリの中で２つのテーブルを読み込んでマージする処理が完結していることを意味します。「閉じて読み込む」をクリックしてExcelシートに戻ってみます。
初回にPowerQueryを実行した際の「users」シートが残っていますので、こちらも削除してやりましょう。
これで、[データ]>[すべて更新] をクリックすることで、自動的に「Sheet1」に記載されたメールアドレスだけから、「マージ済み」シートに氏名と部署名が補完された状態になります。
まとめ
今回は、研修参加者メールアドレスを「Sheet1」に記載していましたが、users.csvと同じように外部のファイルをPower Queryで読んできて、第３のExcelファイルとしてマージ済み結果だけのファイルにすることも可能です。その場合も手順は全く同じです。
１つのクエリの中で出た結果を、処理を飛ばして違うところで使うというテクニックは、今回のようにテーブルの引き込みに限らずいざと言うときに役立つので、こういうこともできるんだと覚えておいて損はないと思います。
Excelの式でも別のワークブックを指定することは可能なので、関数を使って同じことを実現できますが、Power Queryだと行数が増えても関数を付け足す必要がありません。CSV,Excelにかぎらず、AccessやSQLサーバなどからデータを持ってくることも簡単です。
「すべて更新」ボタンひとつで、最新の情報に更新されるのも魅力です。Power Queryが使えるようになると、Power BIへのチャレンジもずいぶんハードルが下がりますので、ぜひチャレンジしてみてください。