![見出し画像](https://assets.st-note.com/production/uploads/images/36555818/rectangle_large_type_2_1dd0abd1661fcb122ec2c6fcafb9a520.jpg?width=1200)
Power BI - クエリエディター上でM言語を使ってカレンダー(日付)テーブルを作るには?
Power BI DesktopのクエリエディターやExcel のPower Queryで、
DAX関数ではなく、
M言語を使ってカレンダーテーブル(連続した日付のテーブル)を作る方法について書きます。
Power BI desktopでは、
特にクエリエディター上で日付テーブルを作らなければいけなくなるケースはあまりないと思うのですが、
Power BIのクエリエディタと同じ位置づけの、
Power Query(Excel)では、クエリエディタ上で作成したくなる場合が多々あると思います。(理由は、データモデル上で、日付テーブルを自動生成すると、固定的なカレンダーが自動生成されてしまい、後々不便が生じるからです)
ステップ1 「空のクエリ」を追加する
今回はPower BIのクエリエディタではなく、
Power Queryを使いながら説明を書きます。
(※Power Query と Power BI desktopのクエリエディタは基本的に同じです)
”データ”タブ>”データの取得”>”その他のデータソースから”>”空のクエリ”
を選択します。
既にクエリエディタ上で何らかの作業をしている場合は、
クエリエディタ上でも「空のクエリ」は追加できます。
左のサイドバーの空白部分で右クリックし、
「新しいクエリ」>「その他のソース」>「空のクエリ」をクリックするだけです。
ステップ2 クエリの名前を変更
追加した空のクエリの名前をわかりやすくしておきましょう。
私は「Period」にしておきます。
ステップ3 日付リストを作成する
空のクエリが出来たら、この中に日付のリストを作成します。
次の式を数式バーの中に書きます。
= List.Dates
書いたらEnterキーを押してみてください。
次のように、List.Dates関数の説明と、パラメーターの入力ボックスが3つ出現します。
M言語の便利なところは、=(イコール)と関数名を書けば、必要な引数(パラメーター)と、その関数の説明を表示してくれるところです。
(ほかの関数でもわからなければ、このナビゲーション機能を使って、
とりあえずパラメーターを入力することで、理解が早まると思います)
3つのパラメーターへの入力値は次のようになります。
start: 作成したい日付テーブルの開始日
count: stepに入力する期間を何回分発生させるか
step: どれだけの間隔ごとに日付を発生させるか
List.Dates関数では、終了日を入力することはできず、その代わりに、生成するカレンダーの長さを指定することになります。
従って、基本的な連続する日数のカレンダーを作りたい場合は、
stepを1として、countは逆算して入力することが求められます。
今回は一先ず、2019/1/1から2年間の連続する日数を含んだカレンダーを作りたいと思いますので、
入力値は以下のようになります。
3つのパラメータを入力したら、「呼び出し」をクリックします。
できました。
「呼び出された関数」には、次の式が自動で生成されています。
= Period(#date(2019, 1, 1), 731, #duration (1, 0, 0, 0))
補足:自動生成された数式の意味
少しややこしくなってしまいましたが、
上記の自動生成された式は、以下と同じです。
= List.Dates(#date(2019, 1, 1), 731, #duration (1, 0, 0, 0))
上記ステップ3で、「=List.Dates」と入力したので、
Periodというクエリは、List.Dates関数と同義になっているのです。
「じゃぁ最初っからこの式入れればよかったでしょ」
と言われそうですが、その通りです。
でも理解するための説明としては、上記のステップでよかったのではないでしょうか・・(独り言)
最後に、#duration(1, 0, 0, 0)の並びは、
#duration (Day, Hour, Minutes, Second) になります。
ステップ4 リストからテーブルに変換
リストの形式では、1列しかデータを持てないため、
この後作ったカレンダーに対して、Year列やMonth列を追加していくことを想定すると、テーブルに変換しておいた方が良いと思います。
やり方は、説明するまでもなく、、
画面左上に見えている「テーブルへの変換」ボタンを押すだけです!
「テーブルへの変換」というウィンドウが出てきますが、
そのままOKを押せば、カレンダー「テーブル」の出来上がりです。
今回は基本的な、半分手入力が必要なカレンダーの作り方を記載しましたが、お気づきの通り、開始日(start)やカウント日数(count) に変数を持ってくれば、もっと可変的なカレンダーを作ることもできます。
またおいおい、書きます。
この記事が気に入ったらサポートをしてみませんか?