PowerQuery【01】PowerQuery(パワークエリー)で出来ること

PowerQueryについてご案内します。

Excel2016(※)から標準搭載されました。取得と変換(Get & Transform Data)機能の一つです。
便利です。簡単です(VBAのスキルも不要です)。
Excelの「データ」タブからインポートファイルを指定して、その後、PowerQueryエディタが起動するので、書式や並び替え等を設定します。
とはいえ設定は簡単なので、皆さんに是非ぜひお勧めしたいです。
(※Excel2016以前でもアドインでセットすることが可能です。)

そしてVLOOKUPの代替と思っている方。違います。もっともっと色んなことが出来ます。
今後しばらくの間、PowerQueryについて連載していきますので、是非おためしください。
(私もまだ、ほんの少ししか触っていないのですが、「これ無しではいられなくなる」予感が大いにしています。)

【今回】

1.PowerQueryで出来ること
2.PowerQueryで少し不便なところ
3.PowerQueryで出来ること【サンプル1-8】
【次回以降】手順・活用事例等

↓順次連載したもののリンクを、以下の表に追記しています。

1.PowerQueryで出来ること

PowerQueryで出来ること
(【サンプル】は「3.PowerQueryで出来ること【サンプル1-8】」に記載
PowerQuery【02】PowerQuery(パワークエリー)の動かし方←次のページにジャンプ
0 PowerPivot(パワーピボット)の入口(データモデルの設定)に使う 2021/3/22追加リンクしました
PowerPivot【01】PowerPivot(パワーピボット)で出来ること
PowerPivot【02】PowerPivot(パワーピボット)の作成方法【設定編】
1 データベース※や外部ソースと接続し、行列ともに絞り込み・並び替えができる
(※CSV・Excel・Accessも含む)
PowerQuery【03】CSVファイルのインポート
PowerQuery【04】Excelファイルのインポート
2 Excelでは読み込めないような大きなデータを読み込み分析できる
・「接続の作成のみ」にすると、ファイルサイズも大きくならない
3 複数のデータを結合できる
PowerQuery【06】2つのテーブルを結合する
・複数のキーで結合出来る
・行列の並び替えができる【サンプル1】
・条件絞り込みが出来る
・・キーの組み合わせに条件式を適用できる(例:販売日によって異なる価格にする)【サンプル2】
PowerQuery【09】キー重複の場合に適切な行のデータを取り出す
・・不一致分のみ表示できる(→名寄せができる)【サンプル3】
PowerQuery【12】二つのファイルの不一致分のみ表示する(名寄せ_簡易バージョン)
PowerQuery【13】二つのファイルの不一致分のみ表示する(名寄せ_本格バージョン)
4 同じ種類のファイル(CSV・Excel)を一度に【複数】取り込み、一つのファイルに出来る
【サンプル4】
PowerQuery【05】フォルダから読み込み(Excel・CSV等の複数ファイルを同時にインポートする)
5 Excelデータを整形できる 
・横展開したリスト(展開クロス集計表・ピボットテーブル等)縦リストに展開【サンプル5】
PowerQuery【10】Pivotテーブルを縦展開する
・ブランクセルは上と同じに埋める(フィル機能)【サンプル6】
PowerQuery【08】フィル(Fill)機能(空白を埋める)
・重複行を削除できる
PowerQuery【11】重複行の削除(上の行/下の行)
6 「全件データ」を作るのが容易なので、RPAの処理データ作成に活用できる
・RPA用の全件データを作る
・横のマトリックス表をRPA用に縦データにする
PowerQuery【14】PowerQueryでRPA用データを作る
7 プロセスの自動化
・一度登録すると「データの読み込み・結合・抽出・整形」が一気にできる
・繰り返し作業は「クエリ」に登録され、「更新ボタン」押下で、早く正確に処理できる
8 ヘッダーが整っていなくてもテーブルにできる
・1行目がブランクでも、セル結合していてもインポートできるため、
「PowerQueryで取込後、Accessへインポートする」運用ができる【サンプル7】
9 Excelをデータベースとして使う事が容易になる
・従来Excelをデータベースとして使用する場合、
データが増えると、行間にデータを挿入したり、最終行にデータを入れてソートしたりしていた。
・PowerQueryで「本体データのレプリカシート」を作り、ソートすることで
「データベース=データの蓄積場所(最終行にデータを入れたまま)」という管理が出来る【サンプル8】
10 ピボットテーブルの値に文字を表示できる
・Excelのピボットテーブルは便利だが、値欄は数値のみ(含む日付データ)しかできなかったが、文字を表示できる。
・Accessのクロス集計クエリと同様の処理ができる。
PowerQuery【07】ピボットテーブルの「値」覧に文字を表示する(マトリックス表を作るときに便利)
11 これらがVBA等のプログラム言語なしで誰でも出来る
「処理ステップ毎」に登録されるため、手順と実行結果が確認しやすく、ブラックボックスになりにくい。

活用事例も記載しました→ PowerQuery【15】活用事例

 

2.PowerQueryで少し不便なところ

今のところ、以下に少し不便を感じています。今後実際に使っていく内に解決方法もわかっていくかもしれません。
その場合、随時付記していきます。

①式がExcelの関数とは異なる

列を追加して、フラグ立てのような条件式を書く場合、「M言語」を使っており、Excel・Access・VBAとは異なるため、慣れるまではGoogle検索しながら式を書く必要がある。
->PowerQueryでデータテーブルに変換した後に、式を書いた方がラクな場面もある。
式の例:IF文 「if Text.PositionOf([所属名],”営業課”)>0 then “営業課” else null 」
(所属名に「営業課」という文字が含まれていたら「営業課」と表示して、それ以外はNull)

②慣れるまで少し時間がかかる

Accessを使い慣れている方なら30分~1時間・VLOOKUP関数を使っている【のみ】なら1時間以上はかかるように思います。
->はじめは簡単な処理から初めて便利さを実感して、ちょっと複雑な手順は、「習熟者が作り、各ユーザが実行するだけ」という使い方が良いかも。

③テーブルの後処理

出来上がった結合表は罫線ありのテーブルデータなので、単独のExcelリストに切り離すのに慣れが必要。
->単独のExcelリストに後1列項目を足す「だけ」なら、VLOOKUP関数の方が手軽かも。

【Access使用目線から】
④3つ以上のテーブルの結合は一度にはできない

->テーブルの結合は「2つ」までなので、3つ以上のテーブルを結合させるなら、何回かに分けて「結合を繰り返す」していく必要がある。

⑤列の選択

Accessのクエリ画面は、「選択項目名が縦に並ぶ」ので選択しやすいが、同様の設定をするPowerQueryエディタは項目が横に並ぶ(=列)ので、少し扱いにくい。
->上記よりPowerQueryエディタで列削除により、始めに項目を絞り込んだ方が扱いやすくなる。

3.PowerQueryで出来ること【サンプル1-8】

【サンプル1】行列の並び替えができる

【サンプル2】キーの組み合わせに条件式を適用できる

(例:販売日によって異なる価格にする)
(↓クリックすると拡大します)

【サンプル3】不一致分のみ表示できる(→名寄せができる)

(↓クリックすると拡大します)

【サンプル4】同じ種類のファイル(CSV・Excel)を一度に【複数】取り込み、一つのファイルに出来る

【サンプル5】横展開したリストを縦リストに展開

(↓クリックすると拡大します)

【サンプル6】フィル機能でブランクセルは上と同じに埋める

(↓クリックすると拡大します)

【サンプル7】「PowerQueryで取込後、Accessへインポートする」運用ができる

(↓クリックすると拡大します)

【サンプル8】「データベース=データの蓄積場所」という管理が出来る

(↓クリックすると拡大します)

それでは、次回は実際のPowerQueryの設定手順についてご案内します。
(↑クリックすると次回へ)