複数ファイルを読み込んで1つのテーブルにしたい
前回の記事で複数ファイルのパスを受け取って、読み込んだ結果をテーブルで返す関数を作りました。今回はこの関数を使って5年、60か月分のデータを1つのテーブルにしていきます。
前回作成した関数のおさらい
Excel ファイルのパスのURLを2つ引数として渡すことで、ファイルの必要部分を抽出して、メーカーと台数を列として持つテーブルにする関数を作りました。
2つ目の引数が yyyymm の形式で年月を渡すようになっています。
関数をどう使うのか？
「yyyymm をリストにして関数に渡して表を作りたい」と考えていたのですが、試行錯誤しているうちに気が付きました。リストを渡すのではなく、渡したい yyyymm を列の値にして、関数を使って追加される Table を展開して、メーカーと台数を追加すれば良い と。ちょっと何言ってるか分からないと思うので、以下の手順をご覧ください。
yyyymm のリストの作り方
別に手入力でも Excel にやらしても良いんですけど、せっかくかっこよく作ろうとしているので、スタートの年月とエンドの年月をパラメーターにして作成することにします。
まず「その他のクエリ」の配下に StartDate と EndDate という名前の日付型のパラメーターを作成します。
日付型で作成することで、日付関数を使ってリストを作成できます。
この2つのパラメーターの範囲の日付を一覧で持つリストを作成します。
空のクエリを作成し、数式エディタにこちらを貼り付けます。
= List.Dates(
StartDate,
Duration.Days(EndDate - StartDate) + 1,
#duration(1,0,0,0)
)
List.Dates は3つの引数を持ち、1つ目は最初の日付、2つ目は作成する日付の数、3つ目は追加する日付の間隔です。
2つ目の引数で、Duration.Days を使って追加する日数を計算しています。
3つ目の引数で一日単位であることを指定しています。
#duration の4つの引数は、日、時間、分、秒を表しています。
リストをテーブルに変換
リストができたら [テーブルへの変換] をします。
テーブルに変換したら、データ型を日付型に変更します。
この方法を使って、日付テーブルの土台を作ることができます。
ほしいのは年月の値なので、列の追加で [例からの列] を使って yyyymm の列を作ります。「カスタム」の欄に手入力でいくつか値を入れると「こういうことがしたいんでしょ？」と、変換する式の候補が表示されます。1/1とか2/2みたいに月と日が同じではない日付の行を入力すると、余計な法則性を掴まないので手っ取り早いです。
変換の式がこのようになっていれば [OK] です。
Text.Combine({Date.ToText([Column1], "yyyy"), Date.ToText([Column1], "MM")})
「カスタム」という列が作られるので、重複を削除します。また、データ型が「テキスト」であることを確認します。
関数を使って列を追加
[列の追加] → [カスタム関数の呼び出し] します。
[関数クエリ] で前回作成した関数を選択します。
1つ目のパラメーター ExcelPath の値は、一旦 [テキスト] で「ExcelPath」とします。本当はパラメーターを指定したいですが、テキストか列名しか選択できないので、テキストにしています。
2つ目のパラメーター yyyymm の値は、[カスタム] 列の値を渡したいので、[列名] で「カスタム」を選択します。
データのプライバシーに関する情報が必要です。[続行] と表示されるので、[続行] してプライバシーレベルを選択します。今回は Web 上に公開されているデータなので「パブリック」を選択します。
数式エディタに次のように表示されてるはずなので、「"ExcelPath"」のダブルクォーテーションを削除します。
= Table.AddColumn(削除された重複, "カスタム.1", each メーカー別月別新車販売台数("ExcelPath", [カスタム]))
ダブルクォーテーションを削除することで、テキストではなく、
パラメーターの ExcelPath を示すことになります。
「カスタム.1」という列が追加され、値はすべて「Table」となっています。列名の右端にある矢印ボタン [↰↱] をクリックして、Table を展開します。
決して行内の「Table」をクリックしないでください。
その行だけ展開されることになります。
[元の列名をプレフィックスとして使用します] のチェックを外して [OK] します。
「メーカー」と「台数」という列が追加されるので、それぞれ「テキスト」と「整数」にデータ型を変更します。その他の列名を適宜変更し、クエリの名前も分かりやすいものに変更します。
最後に、関数の基となったクエリ「Sheet1」を右クリックして [読み込みを有効にする] のチェックを外します。
[読み込みを有効にする] のチェックを外すことで、このクエリのデータをセマンティック モデルに読み込むことを防ぎます。
⇒ストレージ、メモリの節約になります。
まとめ
これで2019年1月から2013年12月まで丸5年60か月分、13のメーカーの新車販売台数データ、合計780行のテーブルが出来上がりました。
このまま1つのテーブルでも販売台数の可視化はできます。ただ、前年比などを計算するメジャーを作成するなら、タイム インテリジェンス関数が使えるように「日付テーブル」を用意したり、メーカーをグループ別に見たりするために「メーカー」テーブルを用意したりと、分析の切り口となるデータを別テーブルに分けて、スタースキーマにするのがお勧めです。