複数ファイルを読み込んで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つのテーブルでも販売台数の可視化はできます。ただ、前年比などを計算するメジャーを作成するなら、タイムインテリジェンス関数が使えるように「日付テーブル」を用意したり、メーカーをグループ別に見たりするために「メーカー」テーブルを用意したりと、分析の切り口となるデータを別テーブルに分けて、スタースキーマにするのがお勧めです。