複数ファイルから1つのテーブルを作成

# 複数ファイルを読み込んで1つのテーブルにしたい
[前回の記事](https://qiita.com/ayumito/items/3a3da237b273be8b24a7)で複数ファイルのパスを受け取って、読み込んだ結果をテーブルで返す関数を作りました。今回はこの関数を使って5年、60か月分のデータを1つのテーブルにしていきます。

https://qiita.com/ayumito/items/3a3da237b273be8b24a7

## 前回作成した関数のおさらい
Excel ファイルのパスのURLを2つ引数として渡すことで、ファイルの必要部分を抽出して、メーカーと台数を列として持つテーブルにする関数を作りました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/576be4b2-7e24-d8bc-76df-7d9d2a90da65.png)

2つ目の引数が yyyymm の形式で年月を渡すようになっています。

## 関数をどう使うのか？ 
「yyyymm をリストにして関数に渡して表を作りたい」と考えていたのですが、試行錯誤しているうちに気が付きました。リストを渡すのではなく、**渡したい yyyymm を列の値にして、関数を使って追加される Table を展開して、メーカーと台数を追加すれば良い** と。ちょっと何言ってるか分からないと思うので、以下の手順をご覧ください。

### yyyymm のリストの作り方
別に手入力でも Excel にやらしても良いんですけど、せっかくかっこよく作ろうとしているので、スタートの年月とエンドの年月をパラメーターにして作成することにします。

まず「その他のクエリ」の配下に StartDate と EndDate という名前の**日付型**のパラメーターを作成します。

:::note info
日付型で作成することで、日付関数を使ってリストを作成できます。
:::

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/1627585c-e84a-58a6-28dd-757c74f4c74b.png)

この2つのパラメーターの範囲の日付を一覧で持つリストを作成します。
空のクエリを作成し、数式エディタにこちらを貼り付けます。

```
= List.Dates(
        StartDate, 
        Duration.Days(EndDate - StartDate) + 1,
        #duration(1,0,0,0)
    )
```

List.Dates は3つの引数を持ち、1つ目は最初の日付、2つ目は作成する日付の数、3つ目は追加する日付の間隔です。

https://learn.microsoft.com/ja-jp/powerquery-m/list-dates

2つ目の引数で、Duration.Days を使って追加する日数を計算しています。

https://learn.microsoft.com/ja-jp/powerquery-m/duration-days

3つ目の引数で一日単位であることを指定しています。
#duration の4つの引数は、日、時間、分、秒を表しています。

https://learn.microsoft.com/ja-jp/powerquery-m/sharpduration

### リストをテーブルに変換
リストができたら [テーブルへの変換] をします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/b4836332-a067-fd96-479e-6f521548705a.png)

テーブルに変換したら、データ型を日付型に変更します。

:::note info
この方法を使って、日付テーブルの土台を作ることができます。
:::

ほしいのは年月の値なので、列の追加で [例からの列] を使って yyyymm の列を作ります。「カスタム」の欄に手入力でいくつか値を入れると「こういうことがしたいんでしょ？」と、変換する式の候補が表示されます。1/1とか2/2みたいに月と日が同じ**ではない**日付の行を入力すると、余計な法則性を掴まないので手っ取り早いです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/47a14088-c4f2-25fa-e941-a4455b3152ea.png)

変換の式がこのようになっていれば [OK] です。

_Text.Combine({Date.ToText([Column1], "yyyy"), Date.ToText([Column1], "MM")})_

「カスタム」という列が作られるので、重複を削除します。また、データ型が「テキスト」であることを確認します。

### 関数を使って列を追加

[列の追加] → [カスタム関数の呼び出し] します。

[関数クエリ] で前回作成した関数を選択します。
1つ目のパラメーター ExcelPath の値は、一旦 [テキスト] で「ExcelPath」とします。本当はパラメーターを指定したいですが、テキストか列名しか選択できないので、テキストにしています。
2つ目のパラメーター yyyymm の値は、[カスタム] 列の値を渡したいので、[列名] で「カスタム」を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/335614bb-d27d-afe0-b940-7f06df4972ff.png)

データのプライバシーに関する情報が必要です。[続行] と表示されるので、[続行] してプライバシーレベルを選択します。今回は Web 上に公開されているデータなので「パブリック」を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/125c6dd7-ef19-119f-7c58-1c289d4a1f18.png)

数式エディタに次のように表示されてるはずなので、「"ExcelPath"」のダブルクォーテーションを削除します。

```
= Table.AddColumn(削除された重複, "カスタム.1", each メーカー別月別新車販売台数("ExcelPath", [カスタム]))
```

:::note info
ダブルクォーテーションを削除することで、テキストではなく、
**パラメーターの ExcelPath** を示すことになります。
:::

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/18019b8a-3777-1b9a-a1ad-d674163c68ad.png)

「カスタム.1」という列が追加され、値はすべて「Table」となっています。**列名の右端にある矢印ボタン [↰↱] をクリック**して、Table を展開します。

:::note alert
決して行内の「Table」をクリックしないでください。
その行だけ展開されることになります。
:::

[元の列名をプレフィックスとして使用します] のチェックを外して [OK] します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/4359bb0f-44e2-bec6-b427-8f62b190be26.png)

「メーカー」と「台数」という列が追加されるので、それぞれ「テキスト」と「整数」にデータ型を変更します。その他の列名を適宜変更し、クエリの名前も分かりやすいものに変更します。

最後に、関数の基となったクエリ「Sheet1」を右クリックして **[読み込みを有効にする] のチェックを外します。**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/c1219c92-5ffc-e781-738d-d3d3b7300610.png)

:::note info
[読み込みを有効にする] のチェックを外すことで、このクエリのデータをセマンティック モデルに読み込むことを防ぎます。
⇒ストレージ、メモリの節約になります。
:::

https://learn.microsoft.com/ja-jp/power-bi/connect-data/refresh-include-in-report-refresh

# まとめ
これで2019年1月から2013年12月まで丸5年60か月分、13のメーカーの新車販売台数データ、合計780行のテーブルが出来上がりました。
このまま1つのテーブルでも販売台数の可視化はできます。ただ、前年比などを計算するメジャーを作成するなら、タイム インテリジェンス関数が使えるように「日付テーブル」を用意したり、メーカーをグループ別に見たりするために「メーカー」テーブルを用意したりと、**分析の切り口となるデータを別テーブルに分けて、スタースキーマにする**のがお勧めです。

https://learn.microsoft.com/ja-jp/power-bi/guidance/star-schema

https://learn.microsoft.com/ja-jp/training/modules/design-model-power-bi/