Power Queryの操作が一通りわかってきたので、簡単なお題で一通りの操作をしてみます。ネタとしては、以前投稿したTableau Prepの記事を活用します。
活用じゃなくて流用でしょ
いやいや…（図星
Tableau Prepとは、Power Query同様のデータクリーニングツールで、BIツールとして利用者が多いTableau社から出ている製品です。無償での利用はできず、有償ライセンスを購入した場合に使えるソフトの一つとなっています。
そう、無償では使えないの。一番、高いライセンスが必要よ。
大抵の会社に導入されているエクセルだと、追加費用なしでPower Queryが使えます。
これらクリーニングツールが使えると、データ前準備作業として分析しやすい形式にデータを整えることができます。
従来では、データ前準備作業は複雑な関数、場合によってはVBAマクロが使われてきました。しかしながら、こういったやり方は作った人に依存してしまう傾向があります。
データクリーニングツールでは、インターフェースがGUIとなりより直感的な操作となるため、いわゆる野良エクセルがはびこるのを防ぎます。
しかも、更新作業はボタン一発！
今回の記事では、国連の人口データを元に分析前の整形作業をハンズオン形式でご紹介します。
紹介する書籍の中で翔泳社出版のものは、紙媒体やKindleのほかに公式サイト「SEshop」でPDF書籍が購入できます。オフィスで画面に移しながら手を動かせるので使い勝手抜群！！
全国送料無料！IT書、ビジネス書、資格書が豊富なSEshop
「SEShop」だとクーポン利用可、PDF版での入手となり扱いやすいです。現在、10%オフとなるクーポンが配布されていました。アマゾンとかで買うよりもお得です。
Power Queryの有効性がわかるハンズオン記事
前提条件
この記事はベーシックな内容で投稿します。
元データのファイルもこのページからエクセルファイルでダウンロード可能です。ツールに興味がある方は、真似しながら手を動かしてみてください。
ひとつの記事でPower Queryの大まかな作業の流れ、整形処理でどんなことができるのか広く浅く触れていきます。
初心者が理解できるように包括的に説明されているコンテンツは動画でUdemyから出ています。興味のある方は活用してみるとよいと思います。
参考 Udemyで学べる「Excel Power Query」
ハンズオンで行う主な操作
高度なことはしません。また実践の場でパフォーマンスを意識したような内容ではありません。
クリーニング処理の効果がどの程度かイメージしていただければと思います。同じことを従来のエクセルでやろうとすれば手間が発生するのは伝わると思います。
また、完全に同じことを自動でやろうとすればVBAマクロを使うことは避けて通れません。
- データの取得（エクセルファイル）
- ステップ操作
- 不要な行・重複行の排除
- 使う行・列のみの絞り込み
- ピボット
- ユニオン
- 結合
- 型の変換・列名称の変更
- データモデルへの取り込み
テストデータと完成系
国連の人口データを使います。そのうち、今回は2つのファイルをデータソースとして使い、パワークエリで一つの表にまとめます。
元がエクセルのデータ
下記のページにデータがあります。
以下の2つのエクセルファイルをデータソースとします。サイトにファイルはありますが、この後の画像と合わせるため、当ブログ内にファイルを格納しています。
- TOTAL_POPULATION_MALE （男性人口が含まれる）
- TOTAL_POPULATION_FEMALE （女性人口が含まれる）
▲ データを開くとたくさんのシートがあります。それぞれのファイルで使うシートは「ESTIMATES」「MEDIUM VARIANT」の2つです。
- 「ESTIMATE」シート：1950～2020年のデータ
- 「MEDIUM VARIANT」シート：2020～2100年のデータ
- 2020年が重複している
2つのファイル、4つのシートをPowerQueryで1つのテーブルに整形・統合します。
完成系
最終的に2つの非数値フィールド、4つの数値フィールドに絞り込みます。
元ファイルをご覧いただければわかると思いますが、手作業だと結構骨が折れると思います。
- 非数値フィールド（ディメンション）
- 国名
- 国コード
- 年
- 数値フィールド（メジャー）
- 女性人口
- 男性人口
それではハンズオン
- ステップ1データの取得
- ステップ2データを俯瞰、不要な行・列を削除
- ステップ3ピボット処理
- ステップ4列名称と型を整える
- ステップ5ユニオン処理
- ステップ6結合処理
- ステップ7データモデルへの取り込み・分析への活用
参考 Udemyで学べる「Excel Power Query」
ステップ1 データの取得
エクセルを新規作成として開き、リボンメニュー「データ」から「データの取得」⇒「ファイルから(O)」⇒「ブックから(W)」の順でデータが入ったエクセルをインポートします。
▲ ナビゲーターが開き、複数シートが表示される状態になります。
ファイル内の「ESTIMATES」「MEDIUM VARIANT」を使います。
ここでは「ESTIMATES」を選びます。ステップ4までの作業は「MEDIUM VARIANT」でも同じ要領で行います。
▲ Power Queryエディターが開きます。データクリーニングはこのエディタを使って行います。
最初にクエリ名称を付けておきます。
選んだシートの「ESTIMATES」は1950年から2020年の人口なので「MALE-1950-2020」としておきました。
| ファイル名 | シート名 | クエリ名 |
|---|---|---|
| TOTAL_POPULATION_MALE | ESTIMATES | MALE-1950-2020 |
| MEDIUM VARIANT | MALE-2020-2100 | |
| TOTAL_POPULATION_FEMALE | ESTIMATES | FEMALE-1950-2020 |
| MEDIUM VARIANT | FEMALE-2020-2100 |
地味に、この名称付けが重要です！
どのようなデータクエリかひと目でわかる名称が好ましいわ。
▲ このエクセルは冒頭の16行目まではデータではなく、ファイルに対する説明が記載されています。分析するうえで不要なので消します。
「ホーム」タブの「行の削除」⇒「上位の行の削除」を選びます。この機能で、先頭から指定する行数を削除することができます。ここでは「16」と指定します。
▲ 先頭から16行目までが削除されました。
Power Queryエディターで行った操作はステップごとに記録されています。
全ての変更履歴が記録されているので、整形までのアプローチが可視化されます。
また、操作誤りがあってもカンタンに戻ることができます。
複雑なステップを作る場合は、ステップ名を変更するとメンテナンスしやすくなるわよ。
▲ 不要な行が削除されましたが、まだ行ヘッダーが正しく認識されていません。「ホーム」タブの「1行目をヘッダーとして使用」を押し、ヘッダーを認識させます。
ステップ2 データを俯瞰、不要な行・列を削除
ここからデータを俯瞰します。
▲ データを見て明らかに使うことがない列は消してしまいましょう。「Index」「Variant」「Notes」「Parent Code」とはさようなら
このエクセルは国別、地域別が一緒になっています。地域というのは北アメリカ地方とか、極東地方とか。
今回は地方的な情報は外して国別にしたいのでこれらをフィルター（行の絞り込み）して除外します。
「Type」列の右の「▼」を押下げすると、オートフィルターのように表示する項目を選べます。ここでは「Country/Area」のみを表示対象とします。
▲ フィルター後はこの「Type」もいらないので列を削除します。前の段階の絞り込みは、該当ステップを消さない限りは列が消えても有効です。
ステップ3 ピボット処理
▲ 「1950」より右側は、年単位で大量の列ヘッダーとなっています。
一般的には、「日付」を示すフィールドを用意してそこに各年を格納したいところです。そのほうが分析するにも扱いやすいですよね。
ということで現在、横持ちになっているデータを縦持ちのデータ構造に変換します。
パワークエリなら一瞬よ
▲ 「1950」から右端まで、年を示すフィールド全体を選択して「変換」タブ⇒「列のピボットを解除」を押下げします。
本ケースで「列のピボットを解除」を行う場合、列を一つずつ処理するのではなく、一括処理して行います。列を一つずつピボット解除すると出来上がるデータ構造が別のものになります。
ステップ4 列名称と型を整える
一つ目のシートの作業は概ね終わり、最後に列名称とデータ型を整えます。
| 変更前の列名称 | 変更後の列名称 | データ型 |
|---|---|---|
| Region, subregion, country or area * | 国名 | テキスト |
| Country code | 国コード | テキスト |
| 属性 | 年 | テキスト |
| 値 | 男性人口 | 10進数 |
一通りの編集が終わったら、「閉じて読み込む▼」を展開して「閉じて次に読み込む…」を押します。
ここでは、「接続の作成のみ」でOKを押します。これが最もデータ量が少ないクエリとなります。
パワークエリで編集後のシート読込を指定することができます。
- 閉じて読み込む
- データはエクセルシート内に取り込まれます。
- 文字データがファイル内に残る、つまりデータ量が増えます。
- 閉じて次に読み込む
- オプションで画像のように取り込み方法を指定します。
- 2回目以降はこの「閉じて次に読み込む」オプションはグレーアウトされ、最初に選んだ取り込み方法が踏襲されます。
個人的には、データはシートに取り込まない「接続の作成のみ」を使っています。
パワークエリのデータはピボットテーブルを使えば、どうとでも描画することができるので。シートにデータを取り込まないのでファイル容量は劇的に少なくなります。
整形後の完成データは データモデル に取り込みます。
インメモリでデータ分析ができ、大量データでも高速です。
ここまでで「ESTIMATE」シートの処理は終わり、ステップ1～4を同じエクセルファイルの「MEDIUM VARIANT（2020年から2100年のデータ）」でも行います。
ファイルは2つ、シートは全部で４つ。男性・女性に気をつけて
ステップ5 ユニオン処理（二つのテーブルの連結処理）
「ESTIMATE」シートの処理結果と、「MEDIUM VARIANT」シートの処理結果をユニオンで繋げます。
ユニオンとは、データベース用語で同じデータ系列を持つデータをマージする（縦につなげる）ことです。
▲ 「クエリと接続」からクエリの一つを選び、右クリック⇒「追加」を選択します。
▲ 追加ダイアログが表示されます。ここで2つのテーブルを連結して一つのテーブルにすることができます。「最初のテーブル」「2つ目のテーブル」とそれぞれを指定してOKを押します。
▲ Power Queryエディターが再び起動します。クエリには区別しやすいように「MALE-ALLDATA」と名前をつけます。
「年」フィールドを確認すると1950～2100までのデータが存在することがわかります。
▲ 実は、このデータは2020年が重複しています。重複の削除もパワークエリから行えます。
重複の削除は、ホームタブの「行の削除」→「重複の削除」からおこないます。
▲ ここでは、4つのフィールド全てを選んだ後で「重複の削除」をおこないます。
▲ 少し変な操作になりますが、パワークエリ上で行数をカウントできます。「変換」タブ→「行数のカウント」で行数が出力されます。
このデータは、片方の性別で30,351行数あります。
行数カウントなどの統計機能を使うと、ステップが挿入されます。このステップは不要なので消してから読み込みます。
以上で男性側のファイル（TOTAL_POPULATION_MALE)は完了しました。同じステップ1から5までの処理を女性側のファイル（TOTAL_POPULATION_FEMALE)でもおこないます。
繰り返し触るといやでもおぼえるわ
ステップ6 結合処理
ステップ5まできちんとやると次のステップに進めます。
男性・女性の人口データをひとつにします。結合処理は、データモデル上で行うやり方、パワークエリ上で行うやり方がありますが、今回は後者でおこないます。
ユニオンと混同しやすいけど、こちらは関連するテーブルを横方向につなぐイメージです。
▲ 男性・女性のクエリのいずれかを選択して右クリック→「結合」を選びます。
▲ テーブルを連結させるためにテーブル内でユニークとなるキー指定が必要です。今回は、国や年だけではユニークにならないので、「国コード＋年」の複合キーで連結しました。
結合の種類は、内部や左外部・右外部などありますが、今回のデータでは結果に違いは出ません。データ結合に際して、列の型を合わせておく必要があります。
▲ 読み込むと、主体となるテーブルが読み込まれ、結合される側は「TABLE」という形で読み込まれます。
▲ フィールド名横にある「←→」から読み込む列を指定します。今回は女性人口のフィールドだけあれば十分です。