訪日外客数データで Power BI レポート作成（データクレンジング編）

はじめに

日本政府観光局の訪日外客数データを使って Power BI Desktop でレポートを作成していく方法を「データクレンジング 編」と「データ可視化 基本編」「データ可視化 中級編」の３つに分けて記事を書きます
本記事は、「データクレンジング 編」です

本記事の「データクレンジング編」前半と「データ可視化 基本編」前半のハンズオン実施動画 ( @dahatake さん) が YouTube に公開されましたので、こちらの動画を見ながら進めるとわかりやすいと思います

訪日外客数データについて

訪日外客数データは、日本政府観光局が公開しているオープンデータで、以下サイトから取得できます
今回は、国/月別の訪日外客数データを使います

このデータが Power BI Desktop のレポート作成体験の Workshop に向いている理由は、以下です
・オープンデータであること
・訪日外客数という誰でも理解しやすいデータであること
・時系列かつ各国の人数構成比等、色々なビジュアル（グラフ）を使い甲斐のあるデータであること
・可視化するとそれなりのインサイトが得られて面白いこと
・業務現場にありがちな 年毎シート分け、ある年からレイアウト変更あり、そのままだとレイアウトが崩れるというほど良い課題を持ったデータであること

実際の業務現場データはデータ活用（データ分析）の視点では汚く課題だらけです
キレイなサンプルデータをもとにPower BI Desktop のレポート作成を体験するよりも、このような少々課題ありのデータを扱えるようになる方が現場で使えるスキルが向上します

データクレンジングの完成イメージ

年毎シートに分かれて月が横並びしている Excel 表を、 [年月] [国名] [人数] 項目の縦持ち（全期間を1つのテーブル）のデータにしたい

基本的な手順

① Web に公開されている Excel データを Power BI Desktop に取り込む
② 年毎分かれているデータを1つの表に取り込む
③ 不要な行・列を削除する
④ 項目名を変更する
⑤ [年] と [国名] を軸にピボット解除する
⑥ [年] と [月] 項目から [年月] 項目 (YYYY/MM/01) を作る
⑦ 項目名、型を整え、並べ替えする

このデータの一番の課題は、2019 年以前と 2020 年以降でレイアウトが変わってしまっていること
2020 年以降は赤枠の中東地域の出現により、1月の人数が B 列から C 列にスライドしてしまい、国名も A 列と B 列にまたがってしまっている
そのため取込データとしては [2003-2019], [2020~] と分けて取り込み、最後にくっつける作戦

[2003-2019] データの取り込みとクレンジング

① Web に公開されている Excel データを Power BI Desktop に取り込む

・訪日外客数(Excel) のリンクをコピーしてURLを取得

・データを取得 > Web > URL に取得したURLを指定
https://www.jnto.go.jp/statistics/data/since2003_visitor_arrivals_June_2024.xlsx
(注意：ファイル名に年や月の情報が入るようになってしまったのでリンクのコピーで最新URLを取得しましょう！）

② 年毎分かれているデータを1つの表に取り込む

・複数シートに分かれたデータを1つの表に取り込む手順については、以下サイトがわかりやすく説明している

・まずは [2003-2019] を作っていく
・[2003-2019] の代表年シートとして、[2019] を選択し、[データの変換] を選択

・プロパティ名を [2003-2019] に変更
・適用したステップの一番上の[ソース] 以外のステップは一旦消す（ステップ左の×をクリック）

・不要なシートデータは削除する
・行の削除 > 上位の行の削除 > 行数: 5

・行の保持 > 上位の行の保持 > 行数: 17

・[Data] 列の展開ボタンをクリック

③ 不要な行・列を削除する

・上位3行を削除

・1行目をヘッダとして使用を選択

・列の選択で年と国名と各月の列のみ選択
※実数値があれば、伸率や累計はレポート側でいくらでも計算できるので、それらの項目はデータとしては含めない

・国名の入っている列 [Column2] の右の下矢印で国名の行だけにフィルターする
・チェックを外すテキストは以下
　・(null)
　・yyyy年 訪日外客数（総数）
　・総数
　・（国名）計
　・注や*で始まる注釈

④ 項目名を変更する

・列のヘッダで右クリック > 名前の変更 で [年] [国名] に名前を変更

⑤ [年] と [国名] を軸にピボット解除する

・1月~12月の列を選択し、変換 > 列のピボット解除 を選択
※Shift キーで選択の開始列から終了列まで一括選択可能

⑥ [年] と [月] 項目から [年月] 項目（YYYY/MM/01)を作る

・月の入っている列[属性]を右クリック > 値の置換　で　検索する値：月、置換後は何もいれずに「OK」クリックし、月という文字列を値から削除する

・一旦、年のデータ型をテキストに変換

・列の追加 > カスタム列で [年] と [月] 項目から [年月] 項目 (YYYY/MM/01) を作る
式： [年] & "/" & [属性] & "/01"

※参考：Power Query のカスタム列等で使用できる式のリファレンスはこちら↓

⑦ 項目名、型を整え、並べ替えする

・[年月] の型を日付に変更

・[値] を [人数] に名前変更し、型を10進数に変更
・[属性] を [月] に名前変更し、型を10進数に変更
・[年] の型を 10進数 に変更
・[年月] [国名] [人数] [年] [月] の順番に並び替え

[2020~]データの取り込みとクレンジング

① Web に公開されている Excel データを Power BI Desktop に取り込む

・Power Query エディター内の ホーム > 新しいソース > Web > URL 指定
(注意：ファイル名に年や月の情報が入るようになってしまったのでリンクのコピーで最新URLを取得しましょう！）

② 年毎分かれているデータを1つの表に取り込む

・[2020~] の代表年シートとして、[2020] を選択し、[OK] をクリック

・プロパティ名を [2020~] に変更
・適用したステップの一番上の [ソース] 以外のステップは一旦消す（ステップ左の×をクリック）
・必要なシートデータのみ残す
・行の保持 > 上位の行の保持 > 行数: 5

・[Data] 列の展開ボタンをクリック

③ 不要な行・列を削除する

・上位3行を削除
・1行目をヘッダとして使用を選択

★ 2列に分かれている国名を1列にマージする

・国名が入っている2列 [Column2] [Column3] を選択し、変換 > 列のマージ を選択
・区切り記号：なし、新しい列名：国名 を入力

・[国名] 列を選択し、変換 > 書式 > トリミング を選択し、前後の空白を除去

・あとは、[2003-2019] の時同様に不要な列や行を削除
・列の選択で年と国名と各月の列のみ選択
・国名の入っている列 [Column2] の右の下矢印で国名の行だけにフィルターする
・チェックを外すテキストは以下
　・(null)
　・yyyy年 訪日外客数（総数）
　・総数
　・（国名）計
　・注や*で始まる注釈
　・中東地域

以降は④-⑦は [2003-2019] の手順と全く同じなので上記手順参照して作業

[2003-2019] と [2020~] データを縦結合

・ホーム > クエリの追加 > クエリを新規クエリとして追加 を選択し
最初のテーブルに [2003-2019]、2つ目のテーブルに [2020~] を選択して [OK] クリック

・プロパティで名前を [訪日外客数] に変更する

[訪日外客数] データを適用

・[2003-2019] と [2020~] データは元データのため、[レポートの読み込みを有効にする] のチェックを外す
・クエリ > 対象のクエリを選択し右クリック > 読み込みを有効にする の左チェックを外す
こうすることで [訪日外客数] データのみを Power BI Desktop に取り込むことができる

・ホーム > 閉じて適用 を選択

（自動的にPower BI Desktop 画面に遷移する）
・フィールドを展開し、クレンジングしたテーブルや項目が表示されていることを確認

・データビューを選択し、データを確認する

Power BI Desktop で 「データ可視化 編」へ