0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Vlookupからの脱却!Power Queryの1クエリ内で2つのテーブルをマージしてシートができるのを防ぐ

Last updated at Posted at 2024-07-27

こんな人に読んでほしい

この投稿では、とある実績表と、そこに登場するユーザーの部署名が記録されている別のCSVファイルをマージさせます。

たぶんExcelではVLOOKUP関数を使ってやっている方が多いと思いますが、Power Queryにチャレンジしてみたい方にお勧めです。

Power QueryをすでにExcelで使っている方ならば経験があると思いますが、クエリがそのまま1つのシートになってしまいます。これを避けるために、2つのテーブルを1つのクエリの中に収める方法もこの記事では解説します。

基本的にはPower BIでも同じなので、クエリをまとめる方法は応用が利きます。すでにPower Queryの基本的な使い方はわかっているので、クエリ内だけの処理を知りたい方は途中を読み飛ばして、こちらからどうぞ。

テストデータを用意する

ユーザーの一覧を用意するために、おすすめのサービス。個人情報テストジェネレーター を紹介します。

使い方はとても簡単。ほしい列を指定して「生成」ボタンをクリックするだけです。これで、ダミーユーザーの一覧が簡単に作成できます。

image.png

出力されたCSVファイルをExcelで開いて、部署名列だけ適当に追加しました。

image.png

今回は、研修参加者の一覧にメールアドレスしかなく、別のuser.csvからメールアドレスにマッチする氏名と部署名をB列、C列に加えたいというシチュエーションを例にしてみます。結構こういう地味な作業ありますよね?

image.png

Power Queryを利用する

Excelで [データ]>[テーブルまたは範囲から」を選択します。
image.png

情報の範囲をテーブル作成が促されます。すでにテーブル化されている場合ならそれでもOKです。
image.png

Power Queryエディターが開き、先ほどの範囲が「テーブル1」という名前がついたクエリになりました。
image.png

ここに、さらにダミーデータとして作成した「users.csv」を取り込んでみましょう。
左側、クエリの空いているスペース。図では「テーブル1」の下あたりで右クリックすると「新しいクエリ」が選べます。今回はCSVファイルなので、[ファイル]>[テキスト/CSV]を選択してクリックします。

image.png

CSVファイルを選択します。
image.png

文字のエンコードが選べます。表示されているデータが文字化けしていなければ、そのまま[OK]をクリックします。
image.png

usersクエリが追加されました。
image.png

1行目にヘッダーが表示されてしまっています。[変換]>[1行目をヘッダーとして使用]をクリックします。
image.png

1行目だったヘッダーが繰り上がり、氏名、アドレス、部署名がヘッダーになりました。
image.png

そのままマージしてみる

テーブル1に、usersテーブルをマージしてみます。Excelの場合はクエリ名がそのままシート名になるので、「テーブル1」の名前を「マージ済み」に変更しておきました。

[ホーム]>[クエリのマージ]>[クエリのマージ]をクリックします。
image.png

どの列を結びつけるかを指定します。上側に「マージ済み」テーブルが表示されているところに、下側の「users」の一部の列をくっつけます。上が左、下が右と読み替えます。主は「マージ済み」なので結合の種類は「左外部」を選択しています。

今回のキーはメールアドレスなので、上下それぞれメールアドレスが表示されている列を選択して[OK]をクリックします。

image.png

usersの列が「マージ済み」に追加されました。さらにテーブルを展開します。users列ヘッダーの右端にある開くボタンをクリックします。
image.png

メールアドレスは既に表示されているのでチェックを外します。「元の列名をプレフィクスとして使用します」のチェックも外して[OK]をクリックします。
image.png

これで、メールアドレスに紐づく氏名と部署名を一つのテーブルに表示できました。
image.png

Power Queryを確定させると2つのシートができてしまう

ここまでPower Queryで操作ができたら「閉じて読み込む」をクリックしてみましょう。

Excelには3つのシートが表示されています。「マージ済み」シートが今回最終的に作成したいものです。
image.png

問題は「users」シートです。Power QueryでCSVファイルを読み込んだことにより、外部のCSVファイルの中身がすべて表示されてしまっています。このシートは作らせたくないですね。
image.png

2つのテーブルを1つのクエリ内であつかう

いよいよチョイテクです。まずはPower Queryに戻るために作成されたテーブルあたりを選択した状態で[クエリ]>[編集]をクリックします。
image.png

usersクエリ側を選択して右クリック、「詳細エディタ」をクリックします。
image.png

詳細エディタでは、先ほどまでGUIで操作した内容がMクエリという言語で記述されています。letからinの間がクエリの実態です。この部分の文字列をコピーしておきます。
image.png

マージ済みテーブルに、先ほどのusersテーブルの処理が記述されているMクエリを貼り付けます。下図ではわかりやすいように緑色部分のコメントを加えています。よく見ると、各行はいずれも 行名 = 処理 の形で記載されています。usersテーブルの部分と、マージ済みテーブルの部分で同じ処理名が重複しているので、これらを修正してやる必要があります。
image.png

クエリをこのように修正しました。まずは赤い色で囲った部分。usersテーブル部分の先頭の行名には頭にusersの文字を追加してます。4行目だけは加工の最終結果なので「users_table」という名前を付けました。実際にはお好みの名前で構いません。

緑色の資格は、ひとつ上の行の行名が入っています。前の行の処理の結果をつかって次の処理へ引き継いでいるのがわかると思います。

もうひとつ、忘れてはいけないのがusers_table行の一番最後の「,」カンマです。各行の最後には必ずカンマが入ってます。これを追加すると「ソース」行の波線エラーが消えるはずです。

image.png

つぎに、「マージされたクエリ数」の行にある「users」の部分を、「users_table」に書き換えます。

image.png

これは何を意味するかというと、もともとの「users」とは「users」クエリのことでした。これですね。
image.png

もともと「users」クエリの中のMクエリを、「マージ済み」クエリの中に貼り付けて一体化してやったことで、ひとつのクエリの中に収め、処理の結果である「users_table」を「users」クエリの代わりに使ってやることがこれで実現できます。書き換えたら[OK]をクリックします。

修正に成功していれば、エラーが出ずに先ほどと同じ結果が表示されているはずです。画面右側のステップ表示では2種類のソースの読み込みを行っていることが名前からわかりますね。
image.png

「users」クエリはこれで用済みとなったので、削除してやります。
image.png

「users」クエリを削除しても「マージ済み」クエリにエラーは出ません。1つのクエリの中で2つのテーブルを読み込んでマージする処理が完結していることを意味します。「閉じて読み込む」をクリックしてExcelシートに戻ってみます。
image.png

初回にPowerQueryを実行した際の「users」シートが残っていますので、こちらも削除してやりましょう。
image.png

これで、[データ]>[すべて更新] をクリックすることで、自動的に「Sheet1」に記載されたメールアドレスだけから、「マージ済み」シートに氏名と部署名が補完された状態になります。
image.png

まとめ

今回は、研修参加者メールアドレスを「Sheet1」に記載していましたが、users.csvと同じように外部のファイルをPower Queryで読んできて、第3のExcelファイルとしてマージ済み結果だけのファイルにすることも可能です。その場合も手順は全く同じです。

1つのクエリの中で出た結果を、処理を飛ばして違うところで使うというテクニックは、今回のようにテーブルの引き込みに限らずいざと言うときに役立つので、こういうこともできるんだと覚えておいて損はないと思います。

Excelの式でも別のワークブックを指定することは可能なので、関数を使って同じことを実現できますが、Power Queryだと行数が増えても関数を付け足す必要がありません。CSV,Excelにかぎらず、AccessやSQLサーバなどからデータを持ってくることも簡単です。
「すべて更新」ボタンひとつで、最新の情報に更新されるのも魅力です。Power Queryが使えるようになると、Power BIへのチャレンジもずいぶんハードルが下がりますので、ぜひチャレンジしてみてください。

0
1
1

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
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?