PowerQuery【15】活用事例
PowerQueryへのリンク
PowerQuery全体：PowerQuery【01】PowerQuery（パワークエリー）で出来ること
PowerQueryの手順：PowerQuery【02】PowerQuery（パワークエリー）の動かし方
「PowerQuery【01】PowerQuery（パワークエリー）で出来ること」に【02】～【14】まで各手順へのリンクを埋め込んできました。
さて、今回は、業務で実際にPowerQueryを使った中での活用事例と留意事項について書いていきます。
※まだ3ヶ月ほどしか使っていないので、今後は事例等も増えてくると思いますので、ここに書き足していきます。
本題に入る前に
1．「結合」と「追加」について
「データの結合」といったときは、以下2つのイメージがあります。
私の周りでは「B」もマージというケースがあるのですが、
PowerQueryの用語に合わせ、「結合」「追加」を使っていきます。
↓PowerQueryではアイコンもついているので分かりやすいです。
活用事例
1．元のExcel表を残しつつ、行の並び順を変えたり、列構成（並び順や非表示列）を変える
(1)行の並び順を変える
一番手軽に使えるんではないでしょうか？
コード順に並んでいる元表はそのままに、順位で並び替える表も作っておくというケースで使っています。
なお、PowerQuery特有の黄緑の列表示は、以下で解除できます。
クエリやテーブル上でクリックする
→「テーブルデザイン」タブ／テーブルスタイル／▽をクリックして／クリア
※ただし、色がついている方が、「PowerQueryのデータなんだな、更新できるんだな」と分かるので便利だと思いますが。
(2)列構成（並び順や非表示列）を変える
他のExcelの表に追加したり、雛形に貼り付けるなどの際に、Excelの列を並び替えるのって結構面倒です。PowerQueryだと簡単に対応できます。
↓以下のような例です（クリックすると拡大します）
以下の様な事例に対応できます。
・列項目の並び順が異なる
・項目名が一部異なる（所属部／所属部名）←クエリ読み込み前に修正要
・表それぞれで項目構成が異なる（例：大阪営業所には、東京営業所には無い項目がある）
以下の手順で「追加」を実施します。
①項目名を揃える
追加すべき項目名で、異なるものがあるときは（例：所属部／所属部名）、項目名を揃えるよう修正が必要です。
②それぞれのテーブルを一旦PowerQueryエディタに読み込む
③クエリタブ／追加／追加先テーブル
④PowerQueryエディタ上でテーブルが追加されるので、不要な列を削除した上で「閉じて読み込む」
2．RPA処理の基礎データを作る
これ地味に便利です。
RPA処理に際して、ロボット用データを作るときには、全件データを準備する必要があります。
ロボットへの依頼： 全件『A01、A02、A03、B01、…D03』の処理を全て用意
人への依頼： 『A～D』までの範囲それぞれで、『01～03』の処理を口頭で依頼
RPAでは、人への依頼とは違い、処理すべき行を全て用意する必要があるのです。人への依頼では「人間の頭」で補ってくれている部分です。
この全件データを作るというのは結構めんどくさくて、従来はAccessで作っていたのですが、
いったん作ると何度も使い回せる点も含めてPowerQueryは便利です。
実際に作っている事例をお見せします。
作業手順は結構長くなるので以下に書きましたので PowerQuery【14】PowerQueryでRPA用データを作る
をご参照ください。
(1)RPA用に全件展開したデータを作る
↓こんな風に出来上がります。
(2)横のマトリックス表を縦にする
これもRPAの事例です。
以下は権限を設定するRPAを動かす前の処理ですが、
入力シート（マトリックス表）を作り、まず人の手で「誰に何の権限をつけるか」を、フラグ立てします。
この入力シートを、縦型の「RPA入力用シート」に変換します。（列のピボット解除を使います。）
3．同じフォーマットの複数ファイルを一つにする
今までならマクロを書いていたのが、1分で出来るので、かなり便利に活用しています。
4．連続技での使用
CSVを取り込み、別のExcelファイルと結合させ、必要な項目だけをリストにする、なんていうときは便利です。
5．VLOOKUPの代わりに結合する
これが皆さんがイメージするPowerQueryの一般的な使い方だと思います。
VLOOKUPに比べ、複数のキーを持てる。PowerQueryを複数つなげることで、複数のファイルを結合できる。
上記の処理をプロセス化出来るので、繰り返し処理に活用できるといった利点があります。
ただし、件数が多いときは、PCのスペックによっては、考慮が必要です。
ここは不便。こうやって対応
1．遅い
件数が多くて（3万件以上）、PCのスペックが悪いと、PowerQueryエディタに展開するのに、すごく時間がかかることがありました。（5分以上）
多分、3万行程度でも、列が多いと読み込みに時間がかかるようです。
Webで検索すると、それほどでも無い事例もあるので、PCのスペックが悪いときだけかもしれません。
このときは、複数のテーブルを呼び出すので耐えられず、結局Accessで対応しました。（CSVインポート・複数のクエリ処理・クエリエクスポート程度の簡単な処理です。）
2．3つ以上のファイルを結合する際は、クエリを複数作らなくてはいけない
Accessは複数のテーブルを結合させるのは簡単ですし、SQL文も副問い合わせを使える（※）のと違い、2つしか結合出来ないのが、地味にウザいです。
対応としては、シンプルな（2～3件位）のマスタテーブルなら結合させるのでは無く、IF文で書いてしまった方がラクだと思います。
ファイルの大きさや、その後の処理を勘案して、Accessで対応する選択肢もあると思います。
※SQLの副問い合わせ：私はあんまり得意では無いので「WITH」を使って書いてしまうんですが。
(参考)IF文の書き方
PowerQueryのIF文は、ExcelのIF文と若干異なるのですが、慣れればそんなに難しくはありません。
慣れない内は、以下のウィザードを使うと便利です。
列の追加タブ／条件列
ウィザードにより、式が出来上がり、「サンプル」列が追加されています。
= Table.AddColumn(ピボット解除された列, “サンプル”, each if [値] = 1 then “追加” else if [値] = 2 then “削除” else if [値] = 9 then “全削除” else null)
また気づいたことがあれば、随時書き足していきます。
