意識高い系のコワーキングスペースが月額2,000円〜 全国規模で店舗も多くてテレワークが捗る!(詳細はリンク先にて)

Power Query で行うデータ整形ハンズオン♪ Tableau Prepと同じことができるのかを検証。

モダンExcel
スポンサーリンク

Power Queryの操作が一通りわかってきたので、簡単なお題で一通りの操作をしてみます。ネタとしては、以前投稿したTableau Prepの記事を活用します。

ジャル
ジャル

活用じゃなくて流用でしょ

アレコレメモ
アレコレメモ

いやいや…(図星

Tableau Prepとは、Power Query同様のデータクリーニングツールで、BIツールとして利用者が多いTableau社から出ている製品です。無償での利用はできず、有償ライセンスを購入した場合に使えるソフトの一つとなっています。

ジャル
ジャル

そう、無償では使えないの。一番、高いライセンスが必要よ。

アレコレメモ
アレコレメモ

大抵の会社に導入されているエクセルだと、追加費用なしでPower Queryが使えます。

これらクリーニングツールが使えると、データ前準備作業として分析しやすい形式にデータを整えることができます。

従来では、データ前準備作業は複雑な関数、場合によってはVBAマクロが使われてきました。しかしながら、こういったやり方は作った人に依存してしまう傾向があります。

データクリーニングツールでは、インターフェースがGUIとなりより直感的な操作となるため、いわゆる野良エクセルがはびこるのを防ぎます。

しかも、更新作業はボタン一発!

今回の記事では、国連の人口データを元に分析前の整形作業をハンズオン形式でご紹介します。

スポンサーリンク

Power Queryの有効性がわかるハンズオン記事

前提条件

この記事はベーシックな内容で投稿します。

元データのファイルもこのページからエクセルファイルでダウンロード可能です。ツールに興味がある方は、真似しながら手を動かしてみてください。

ひとつの記事でPower Queryの大まかな作業の流れ、整形処理でどんなことができるのか広く浅く触れていきます。

初心者が理解できるように包括的に説明されているコンテンツは動画でUdemyから出ています。興味のある方は活用してみるとよいと思います。

参考 Udemyでのパワークエリ検索結果

ハンズオンで行う主な操作

高度なことはしません。また実践の場でパフォーマンスを意識したような内容ではありません。

クリーニング処理の効果がどの程度かイメージしていただければと思います。同じことを従来のエクセルでやろうとすれば手間が発生するのは伝わると思います。

また、完全に同じことを自動でやろうとすればVBAマクロを使うことは避けて通れません。

この記事で行う主な操作
  • データの取得(エクセルファイル)
  • ステップ操作
  • 不要な行・重複行の排除
  • 使う行・列のみの絞り込み
  • ピボット
  • ユニオン
  • 結合
  • 型の変換・列名称の変更
  • データモデルへの取り込み

テストデータと完成系

国連の人口データを使います。そのうち、今回は2つのファイルをデータソースとして使い、パワークエリで一つの表にまとめます。

元がエクセルのデータ

下記のページにデータがあります。

以下の2つのエクセルファイルをデータソースとします。サイトにファイルはありますが、この後の画像と合わせるため、当ブログ内にファイルを格納しています。

今回使うデータはこちら
Tableau Prepでデータ準備にするのはなんと国連データ

▲ データを開くとたくさんのシートがあります。それぞれのファイルで使うシートは「ESTIMATES」「MEDIUM VARIANT」の2つです。

  • 「ESTIMATE」シート:1950~2020年のデータ
  • 「MEDIUM VARIANT」シート:2020~2100年のデータ
    • 2020年が重複している

2つのファイル、4つのシートをPowerQueryで1つのテーブルに整形・統合します。

Power Queryの有効性がわかるようにハンズオン(2つのファイル、4つのシートをPowerQueryで1つのテーブルに整形・統合します。)

完成系

最終的に2つの非数値フィールド、4つの数値フィールドに絞り込みます。

元ファイルをご覧いただければわかると思いますが、手作業だと結構骨が折れると思います。

出来上がるディメンションとメジャー
  • 非数値フィールド(ディメンション)
    • 国名
    • 国コード
  • 数値フィールド(メジャー)
    • 女性人口
    • 男性人口

それではハンズオン

整形作業の流れ
  • ステップ1
    データの取得

  • ステップ2
    データを俯瞰、不要な行・列を削除
  • ステップ3
    ピボット処理
  • ステップ4
    列名称と型を整える

  • ステップ5
    ユニオン処理

  • ステップ6
    結合処理
  • ステップ7
    データモデルへの取り込み・分析への活用

ステップ1 データの取得

Power Queryの有効性がわかるようにハンズオン(データの取得からエクセルを読み込む)

エクセルを新規作成として開き、リボンメニュー「データ」から「データの取得」⇒「ファイルから(O)」⇒「ブックから(W)」の順でデータが入ったエクセルをインポートします。

Power Queryの有効性がわかるようにハンズオン(データの取得からエクセルを読み込む)

▲ ナビゲーターが開き、複数シートが表示される状態になります。

ファイル内の「ESTIMATES」「MEDIUM VARIANT」を使います。

ここでは「ESTIMATES」を選びます。ステップ4までの作業は「MEDIUM VARIANT」でも同じ要領で行います。

Power Queryの有効性がわかるようにハンズオン(パワークエリに読み込まれたエクセルシート)
パワークエリに読み込まれたエクセルシート

▲ Power Queryエディターが開きます。データクリーニングはこのエディタを使って行います。

整形作業を進めていきますが、実体ファイルを更新するわけではないので読込側だけの作業となります。これはデータベースなどでも同様です。

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
アレコレメモ
アレコレメモ

地味に、この名称付けが重要です!

ジャル
ジャル

どのようなデータクエリかひと目でわかる名称が好ましいわ。

クエリ名称をつけておくと、ファイル内でクエリが増えた時に用途がわかります。

また、データを連結するデータモデル、DAX関数から新たなフィールドを作る時などにも非常に便利なのでパワークエリで整形作業を始める時の最初の動作にしておきましょう。

Power Queryの有効性がわかるようにハンズオン(シート上部はヘッダー的な情報で分析に不要)

▲ このエクセルは冒頭の16行目まではデータではなく、ファイルに対する説明が記載されています。分析するうえで不要なので消します。

Power Queryの有効性がわかるようにハンズオン(行の削除から上位行を削除)
Power Queryの有効性がわかるようにハンズオン(行の削除から上位行を削除)

「ホーム」タブの「行の削除」⇒「上位の行の削除」を選びます。この機能で、先頭から指定する行数を削除することができます。ここでは「16」と指定します。

Power Queryの有効性がわかるようにハンズオン(行の削除から上位行を削除)

▲ 先頭から16行目までが削除されました。

すべての作業は記録されている
Power Queryの有効性がわかるようにハンズオン(操作はステップ単位で記録されている)
操作はステップ単位で記録されている

Power Queryエディターで行った操作はステップごとに記録されています。

全ての変更履歴が記録されているので、整形までのアプローチが可視化されます。

また、操作誤りがあってもカンタンに戻ることができます。

ジャル
ジャル

複雑なステップを作る場合は、ステップ名を変更するとメンテナンスしやすくなるわよ。

Power Queryの有効性がわかるようにハンズオン(シートの一行目をヘッダーとして指定する)

▲ 不要な行が削除されましたが、まだ行ヘッダーが正しく認識されていません。「ホーム」タブの「1行目をヘッダーとして使用」を押し、ヘッダーを認識させます。

ステップ2 データを俯瞰、不要な行・列を削除

ここからデータを俯瞰します。

Power Queryの有効性がわかるようにハンズオン(使わない行は削除しておく)

▲ データを見て明らかに使うことがない列は消してしまいましょう。「Index」「Variant」「Notes」「Parent Code」とはさようなら

Power Queryの有効性がわかるようにハンズオン(列データから絞り込み)
列データから絞り込み

このエクセルは国別、地域別が一緒になっています。地域というのは北アメリカ地方とか、極東地方とか。

今回は地方的な情報は外して国別にしたいのでこれらをフィルター(行の絞り込み)して除外します。

「Type」列の右の「▼」を押下げすると、オートフィルターのように表示する項目を選べます。ここでは「Country/Area」のみを表示対象とします。

Power Queryの有効性がわかるようにハンズオン(フィルターかけた列も消せる)

▲ フィルター後はこの「Type」もいらないので列を削除します。前の段階の絞り込みは、該当ステップを消さない限りは列が消えても有効です。

ステップ3 ピボット処理

Power Queryの有効性がわかるようにハンズオン(年が横に大量に広がるデータ)

▲ 「1950」より右側は、年単位で大量の列ヘッダーとなっています。

アレコレメモ
アレコレメモ

一般的には、「日付」を示すフィールドを用意してそこに各年を格納したいところです。そのほうが分析するにも扱いやすいですよね。

ということで現在、横持ちになっているデータを縦持ちのデータ構造に変換します。

ジャル
ジャル

パワークエリなら一瞬よ

▲ 「1950」から右端まで、年を示すフィールド全体を選択して「変換」タブ⇒「列のピボットを解除」を押下げします。

Power Queryの有効性がわかるようにハンズオン(ピボットをかけて横持ちから縦持ちデータへ)
すごいですよね、一瞬。
ピボット処理は一括で

本ケースで「列のピボットを解除」を行う場合、列を一つずつ処理するのではなく、一括処理して行います。列を一つずつピボット解除すると出来上がるデータ構造が別のものになります。

ステップ4 列名称と型を整える

一つ目のシートの作業は概ね終わり、最後に列名称とデータ型を整えます。

変更前の列名称 変更後の列名称 データ型
Region, subregion, country or area * 国名 テキスト
Country code 国コード テキスト
属性 テキスト
男性人口 10進数
Power Queryの有効性がわかるようにハンズオン(列名称を整える)

「男性人口」の部分はファイルごとに以下のように読み替えます。

  • ファイル「TOTAL_POPULATION_FEMALE」:「値」⇒「女性人口」
Power Queryの有効性がわかるようにハンズオン(閉じて読み込む)

一通りの編集が終わったら、「閉じて読み込む▼」を展開して「閉じて次に読み込む…」を押します。

Power Queryの有効性がわかるようにハンズオン(データをシートに取り込まないクエリ作成)
データをシートに取り込まないクエリ作成

ここでは、「接続の作成のみ」でOKを押します。これが最もデータ量が少ないクエリとなります。

データをシートに読み込むか、クエリだけ作るか

パワークエリで編集後のシート読込を指定することができます。

  • 閉じて読み込む
    • データはエクセルシート内に取り込まれます。
    • 文字データがファイル内に残る、つまりデータ量が増えます。
  • 閉じて次に読み込む
    • オプションで画像のように取り込み方法を指定します。
    • 2回目以降はこの「閉じて次に読み込む」オプションはグレーアウトされ、最初に選んだ取り込み方法が踏襲されます。
Power Queryの有効性がわかるようにハンズオン(クエリの取込み方オプション)

個人的には、データはシートに取り込まない「接続の作成のみ」を使っています。

パワークエリのデータはピボットテーブルを使えば、どうとでも描画することができるので。シートにデータを取り込まないのでファイル容量は劇的に少なくなります。

整形後の完成データは データモデル に取り込みます。

インメモリでデータ分析ができ、大量データでも高速です。

ここまでで「ESTIMATE」シートの処理は終わり、ステップ1~4を同じエクセルファイルの「MEDIUM VARIANT(2020年から2100年のデータ)」でも行います。

Power Queryの有効性がわかるようにハンズオン(一つ目のシートが完了)
ジャル
ジャル

ファイルは2つ、シートは全部で4つ。男性・女性に気をつけて

ステップ5 ユニオン処理(二つのテーブルの連結処理)

Power Queryの有効性がわかるようにハンズオン(二つのシートをユニオンでつなげる)

「ESTIMATE」シートの処理結果と、「MEDIUM VARIANT」シートの処理結果をユニオンで繋げます。

アレコレメモ
アレコレメモ

ユニオンとは、データベース用語で同じデータ系列を持つデータをマージする(縦につなげる)ことです。

Power Queryの有効性がわかるようにハンズオン(クエリの追加処理)

▲ 「クエリと接続」からクエリの一つを選び、右クリック⇒「追加」を選択します。

Power Queryの有効性がわかるようにハンズオン(クエリの追加処理)

▲ 追加ダイアログが表示されます。ここで2つのテーブルを連結して一つのテーブルにすることができます。「最初のテーブル」「2つ目のテーブル」とそれぞれを指定してOKを押します。

Power Queryの有効性がわかるようにハンズオン(新しいクエリはすぐに名前をつける)
新しいクエリはすぐに名前をつける

▲ Power Queryエディターが再び起動します。クエリには区別しやすいように「MALE-ALLDATA」と名前をつけます。

Power Queryの有効性がわかるようにハンズオン(ユニオン処理の確認)

「年」フィールドを確認すると1950~2100までのデータが存在することがわかります。

Power Queryの有効性がわかるようにハンズオン(2020年のみデータが重複している)

▲ 実は、このデータは2020年が重複しています。重複の削除もパワークエリから行えます。

Power Queryの有効性がわかるようにハンズオン(行の削除から重複の削除)

重複の削除は、ホームタブの「行の削除」→「重複の削除」からおこないます。

年フィールドのみの指定だと想定より多くの行が削除されます。

ほかの列との組み合わせることで重複パターンを厳密に指定できます。

Power Queryの有効性がわかるようにハンズオン(複数列を指定してからの重複の削除)

▲ ここでは、4つのフィールド全てを選んだ後で「重複の削除」をおこないます。

Power Queryの有効性がわかるようにハンズオン(パワークエリ上で行数をカウントできる)

▲ 少し変な操作になりますが、パワークエリ上で行数をカウントできます。「変換」タブ→「行数のカウント」で行数が出力されます。

Power Queryの有効性がわかるようにハンズオン(3万行のデータ)
Power Queryの有効性がわかるようにハンズオン(カウントすると不要なステップができるので消す)

このデータは、片方の性別で30,351行数あります。

行数カウントなどの統計機能を使うと、ステップが挿入されます。このステップは不要なので消してから読み込みます。

以上で男性側のファイル(TOTAL_POPULATION_MALE)は完了しました。同じステップ1から5までの処理を女性側のファイル(TOTAL_POPULATION_FEMALE)でもおこないます。

Power Queryの有効性がわかるようにハンズオン(一つ目のファイルがおわり、もう片方も同様にやる)
ジャル
ジャル

繰り返し触るといやでもおぼえるわ

ステップ6 結合処理

ステップ5まできちんとやると次のステップに進めます。

男性・女性の人口データをひとつにします。結合処理は、データモデル上で行うやり方、パワークエリ上で行うやり方がありますが、今回は後者でおこないます。

アレコレメモ
アレコレメモ

ユニオンと混同しやすいけど、こちらは関連するテーブルを横方向につなぐイメージです。

Power Queryの有効性がわかるようにハンズオン(二つのクエリを結合する)
二つのクエリを結合する

▲ 男性・女性のクエリのいずれかを選択して右クリック→「結合」を選びます。

Power Queryの有効性がわかるようにハンズオン(複合キーで結合する)

▲ テーブルを連結させるためにテーブル内でユニークとなるキー指定が必要です。今回は、国や年だけではユニークにならないので、「国コード+年」の複合キーで連結しました。

結合の種類は、内部や左外部・右外部などありますが、今回のデータでは結果に違いは出ません。データ結合に際して、列の型を合わせておく必要があります。

Power Queryの有効性がわかるようにハンズオン(二つめのクエリはテーブルとし取り込まれる)

▲ 読み込むと、主体となるテーブルが読み込まれ、結合される側は「TABLE」という形で読み込まれます。

Power Queryの有効性がわかるようにハンズオン(女性人口の列のみを取り込む)

▲ フィールド名横にある「←→」から読み込む列を指定します。今回は女性人口のフィールドだけあれば十分です。

Power Queryの有効性がわかるようにハンズオン(新しいクエリにはすぐに名称をつける)
Power Queryの有効性がわかるようにハンズオン(必要なもののみデータモデルへ取り込む)

完成したクエリには名称をつけて保存します。

今回は、出来上がりなのでデータモデルに取り込みます。

Power Queryの有効性がわかるようにハンズオン(データモデルを使うとピボットテーブルの挿入でいつでも呼び出せる)

▲ シート上にデータがなくてもいつでもピボットテーブル挿入でデータを呼び出せます。

Power Queryの有効性がわかるようにハンズオン(人口データで作るピボットテーブル)
人口データで作るピボットテーブル

▲ ピボットテーブルで表現する人口ピラミッド的な… ここまでで疲れたので、グラフは割愛。

まとめ

今回はここまで。パワークエリでのデータクリーニング作業の様子を記事にしてみました。

整形作業のみでDAXなどの高度関数には触れていませんが、データを整形する機能のみに着目してもかなり便利なツールです。

少しずつ記事を投稿する予定です。

PowerQueryやPowerBIなどUdemyでの動画コンテンツが豊富です。私も海外のものをよく見ています。

超強力なオンラインマーケットプレイス「Udemy」

Udemy(ユーデミー)の特徴

  • コロナ禍でもオンラインで手軽に学べる
  • 趣味にビジネスに豊富なジャンル
  • 動画は買い切り・繰り返し視聴可能
  • PCでもスマホでも見れる
  • コンテンツのクオリティが高い
\ 動画は買い切り!繰り返し視聴可能! /
タイトルとURLをコピーしました