コース: データ分析 入門１：基本

Power Queryでデータモデリングを行う

コース: データ分析 入門１：基本

Power Queryでデータモデリングを行う

“

常にデータベースへ アクセスできるとは限りません。 そのような場合には、 Power Query を使うことができます。 Power Query には、 各種データへ接続し、 取り込み、 マージや結合する機能があります。 ここでは複数のデータを 結合する操作を学習していきます。 06_06 フォルダー内の Excel ファイル、 ModelDatawithPowerQuery を 開いています。 賃金データと郵便番号データを取得し、 マージや結合をしていきます。 「データ」タブの 「データの取得」をクリックします。 「ファイルから」＞「ブックから」 の順に選択します。 06_06 フォルダー内の PostalCodeData.xlsx を選択し、 「インポート」をクリックします。 PostalCode を選択します。 表示されたデータは 地名データベースサイトから 検索された、 アメリカ合衆国の 都市の郵便番号情報です。 実際の郵便番号は５桁ですが、 ３桁で表示されています。 データを変換し、 原因を発見しましょう。 「データの変換」をクリックします。 PowerQuery エディター画面が 表示されます。 「郵便番号テキスト」列は、 データ型が変換されています。 Power Query はデータを取得した際に データの羅列から 自動的にデータ型を判定し、 設定してくれます。 「変更された型」 ステップを削除します。 すると「郵便番号テキスト」列 のデータには、 先頭の０が表示された状態となりました。 「郵便番号テキスト」列のデータ型を テキストへ変換しておきます。 テキスト型にすることで 先頭の０は維持されます。 「ZIP コード」列は必要ありませんから 右クリックし、 「削除」を選択します。 「ホーム」タブの 「閉じて読み込む」 をクリックします。 これで郵便番号データが シートに取り込まれました。 42,264 レコードのデータです。 「郵便番号テキスト」列のデータは 先頭の０が 維持されていることが確認できます。 次のデータセットを取得しましょう。 「データ」タブの 「データの取得」を選択します。 そして「ファイルから」＞「ブックから」 の順に選択し、 WageSurvey.xlsx ファイルを選んでから 「インポート」をクリックします。 Survey を選択し、 「データの変換」をクリックします。 画面左下を見ると、 賃金調査データには、 343 件の回答データが あることがわかります。 画面を右へスクロールし、 郵便番号列を表示します。 郵便番号は 先頭列へ移動しましょう。 列名で右クリックし、 「移動」 「先頭に移動」を選択します。 先頭へ移動した理由は、 この後マージという処理を行う時に 確認しやすいからです。 データ型をテキストに変更します。 データ同士をマージや 結合するには、 対象となる列を 同じデータ型にしておく必要が あるからです。 ２つのデータセットが 準備できました。 次に２つのデータセットを マージしていきます。 「ホーム」タブの 「クエリのマージ」 を選択します。 ここで「クエリのマージ」を選択すると、 ２つのデータセットが 単純にひとつにマージされます。 今回は別々のデータセットとして 使いたいため、 「新規としてクエリをマージ」 を選択します。 マージの設定画面が表示されます。 上部に賃金調査データが 表示されています。 下部のリストから、 PostalCode 郵便番号データを選択します。 それぞれのデータから 「郵便番号」 「郵便番号テキスト」列をクリックし、 マージに使用するフィールドを 指定します。 結合タイプは 「左外部（最初の行すべて、 および２番目のうち一致するもの）」 を選択します。 左外部結合にすることで、 賃金調査データは、 すべて表示され、 PostalCode データからは 郵便番号が一致するデータのみ 表示されるようになります。 画面左下には、 343 行中、 337 行のデータが 一致していることが表示されています。 一致していない行は 郵便番号が間違っているか、 欠けている可能性があることが 想像できます。 OK をクリックします。 クエリ名も わかりやすいものに 変更しておきましょう。 左側「マージ１」を右クリックし、 「名前の変更」を選択します。 「郵便番号調査」とします。 次にアラバマ州と 67 の群の賃金調査データだけを 分析してみます。 別のクエリを作成しましょう。 再び「ホーム」タブの 「クエリのマージ」 「新規としてクエリをマージ」 を選択します。 先ほどと同様に Survey と PostalCode、 データを指定し、 それぞれ「郵便番号」と 「郵便番号テキスト」を選択します。 ここでも結合の種類は 「左外部」としますが、 Power Query の画面では 左右ではなく上下 に表示されます。 Survey が左になります。 OK をクリックします。 クエリ名を右クリックし、 「名前の変更」から 「アラバマ州調査結果」に変更します。 画面を右へスクロールします。 一番右側に PostalCode テーブルがあります。 フィールド名の右側のアイコンを クリックし、 表示するフィールド名を選択します。 ここでは「主要都市」 「州」「群」 指定します。 OK をクリックします。 「主要都市」「州」 「群」が表示されました。 しかし後から 緯度と経度が必要になるかもしれません。 ステップ「展開された PostalCode」 の右側にある 歯車のアイコンをクリックします。 「緯度」「経度」を選択し、 OK をクリックします。 「緯度」「経度」のフィールドが 表示されました。 先ほど作成した郵便番号調査クエリの 表示列を設定しましょう。 「郵便番号調査」をクリックします。 画面を右端へスクロールします。 ここでは 「郵便番号テキスト」と 「タイムゾーン」 フィールド以外となるように、 チェックマークを外し、 OK をクリックします。 フィールドが展開されました。 それぞれのフィールド名のタイトルにある PostalCode が不要であれば 非表示に設定することができます。 ステップ「展開された PostalCode」 の歯車���イコンをクリックし、 「既定の列名のプレフィックス」欄を 空白にして、 OK をクリックします。 同様に「アラバマ州調査結果」 のクエリの列名も変更します。 最後に「アラバマ州調査結果」クエリの データから、 「アラバマ州」をフィルタリングします。 画面を右へスクロールし、 「州」フィールドの フィルターアイコンをクリックし、 アラバマ州を表す AL のみを選択し、 OK をクリックします。 続いて「ホーム」タブの 「閉じて読み込む」をクリックします。 「アラバマ州調査結果」 というシート名になっています。 PowerQuery で作成したクエリ名が シート名になっています。 Survey シートは シート名を「オリジナルデータ」 と変更します。 PostalCode シートはシート名を 「郵便番号データ」と変更します。 「空白のシート１」は削除します。 右側のクエリ名 「アラバマ州調査結果」 をクリックすると、 該当するシートへ移動できます。 ここまでの作業を保存しましょう。 データのモデリング作業は、 PowerQuery の登場などにより、 ここ数年で とても簡単になりました。 データのモデリングは より良いレポート作成や、 さらなるデータプリミングのために、 有効な方法です。

目次