[Power BI] カレンダーテーブルをPower Query で記述していくなかで、最初に読んでおけばいいなと思ったこと３つ

はじめに

以前上記のような記事を書きました。
DAXでカレンダーテーブルを記述し、カレンダーテーブルの再利用をしやすくしています。
しかし、再利用のことを突き詰めると、カレンダーテーブルはデータフローとして組織共通のものを作成しておくことがいいのかなと思い、同じことをPower Queryで実施していきたいと思います。

Power BI データフローではDAXを使用したテーブルは記述できない。

Goal

Power BI サービスの「データフロー作成 > 空のクエリ」にPower Queryを記述して、カレンダーテーブルのデータフローを作成する。

もとの雛形（カレンダーテーブル：DAXバージョン）

再掲。

日付テーブル = 
VAR StartDate=DATE(2000,01,01)
VAR EndDate=DATE(2030,12,31)
VAR Today=UTCTODAY()+9/24
VAR ThisFY=if(MONTH(Today)>3,YEAR(Today),YEAR(Today)-1)
VAR BaseCalendar =CALENDAR (StartDate,EndDate)
RETURN

ADDCOLUMNS(
    BaseCalendar,
    "年_数値", YEAR ( [Date] ),
    "年_yyyy年",FORMAT([Date],"yyyy年"),
    "月_数値", MONTH ([Date]),
    "月_MM月", FORMAT ([Date], "MM月" ),
    "日_数値", DAY ([Date]),
    "日_DD日", FORMAT([Date],"DD日"),
    "年月_数値", VALUE(FORMAT([Date], "yyyyMM" )),
    "年月_yy年MM月", FORMAT ( [Date], "yy年MM月" ),
    "年度_数値",if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),
    "年度_0年度",FORMAT(if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),"0年度"),
    "年度四半期_数値", if(MONTH([Date])<4,4,if(MONTH([Date])<7,1,if(MONTH([Date])<10,2,3))),
    "年度四半期_0Q", if(MONTH([Date])<4,"4Q",if(MONTH([Date])<7,"1Q",if(MONTH([Date])<10,"2Q","3Q"))),
    "週_数値", WEEKNUM ( [Date], 1 ),
    "週_第00週", FORMAT ( WEEKNUM ( [Date], 1 ), "第00週" ),
    "曜日番号月曜から_数値", WEEKDAY ( [Date], 2 ),
    "曜日_aaa", FORMAT([Date],"aaa"),
    "日と曜日", FORMAT ( [Date], "DDaaa" ),
    "相対日付",DATEDIFF(Today,[Date],DAY),
    "相対月",DATEDIFF(Today,[Date],MONTH),
    "相対年度",if(MONTH([Date])>3,YEAR([Date])-ThisFY,YEAR([Date])-1-ThisFY),
    "会計期",YEAR(EDATE([Date],-5)) - 1940,
    "相対会計期",YEAR(EDATE([Date],-5)) - YEAR(EDATE(today,-5))
    )

カレンダーテーブル Power Query バージョン

試行錯誤しながら作成してみました。
間に参考にしたドキュメントも差し込んであります。
勉強したいというかたはぜひ参考にしてください。

また、どーしても相対月の算出の仕方がわからなかったので誰か優しい人教えて下さい・・・。

2022年1月28日 @tenmyo さんのコメントを元に、相対月も追加することができました！ありがとうございます！

2022年1月29日 @yugoes1021 さんよりご指摘いただき、today の定義を見直しました。 `DateTime.LocalNow()` の動きがPower BI Desktop 上とPower BI Service 上で変わるので、 UTCをとってきてから日本時間に変換するやり方に変更。 相対月の求める式も、そのtodayを利用しています。

このクエリを空のクエリに貼り付ければカレンダーテーブルをデータフローに実装できます。
実運用のときは StartDate や EndDate を変数で定義してあげるのがいいのかなと思っています。

let
  // こういうふうに変数を定義しても良い。
  // (StartDate as date, EndDate as date) =>
  // 変数も使用できます。
  today = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 9)),
  ThisFY = if Date.Month(today) > 3 then Date.Year(today) else Date.Year(today)-1,
  // https://docs.microsoft.com/ja-jp/powerquery-m/sharpdate
  // https://docs.microsoft.com/ja-jp/powerquery-m/sharpduration
  // 変数を設定した場合は下記のようにする感じかと。
  // List.Dates(StartDate, Duration.Days(EndDate - StartDate), #duration(1, 0, 0, 0))
  source = List.Dates(#date(2020, 12, 31), 1000, #duration(1, 0, 0, 0)),
  // https://docs.microsoft.com/ja-jp/powerquery-m/table-fromlist
  // https://docs.microsoft.com/ja-jp/powerquery-m/splitter-splitbynothing
  convert_table = Table.FromList(source, Splitter.SplitByNothing(),{"Date"},ExtraValues.Error),
  Changed_Type = Table.TransformColumnTypes(convert_table, {{"Date", type date}}),
  add_year_column = Table.AddColumn(Changed_Type, "年", each Date.Year([Date]), Int64.Type),
  add_month_column = Table.AddColumn(add_year_column, "月", each Date.Month([Date]), Int64.Type),
  add_day_column = Table.AddColumn(add_month_column, "日", each Date.Day([Date]), Int64.Type),
  // https://docs.microsoft.com/ja-jp/powerquery-m/expressions-values-and-let-expression
  add_年度_column = Table.AddColumn(add_day_column, "年度", each if Date.Month([Date]) > 3
        then Date.Year([Date])
        else Date.Year([Date])-1, Int64.Type),
  add_年度四半期_数値_column = Table.AddColumn(add_年度_column, "年度四半期", each if Date.Month([Date]) < 4
        then 4
        else if Date.Month([Date]) < 7
        then 1
        else if Date.Month([Date]) < 10
        then 2
        else 3, Int64.Type),
  add_年度四半期_0Q_column = Table.AddColumn(add_年度四半期_数値_column, "年度四半期_0Q", each if Date.Month([Date]) < 4
        then "4Q"
        else if Date.Month([Date]) < 7
        then "1Q"
        else if Date.Month([Date]) < 10
        then "2Q"
        else "3Q", type text),
  // https://docs.microsoft.com/ja-jp/powerquery-m/date-weekofyear
  add_週_数値_column = Table.AddColumn(add_年度四半期_0Q_column, "週", each Date.WeekOfYear([Date], 1), Int64.Type),
  // https://docs.microsoft.com/ja-jp/powerquery-m/date-dayofweek
  // 月曜日=> 0 , 火曜日=> 1...
  add_曜日_数値_column = Table.AddColumn(add_週_数値_column, "曜日", each Date.DayOfWeek([Date], 1), Int64.Type),
  add_曜日名_column = Table.AddColumn(add_曜日_数値_column, "曜日名", each Date.DayOfWeekName([Date], "ja-JP"), type text),
  add_月名_column = Table.AddColumn(add_曜日名_column, "月名", each Date.MonthName([Date]), type text),
  add_年月_column = Table.TransformColumnTypes(Table.AddColumn(add_月名_column, "add_年月_column", each Text.Combine({Text.From([年], "ja"), "年", [月名]})), {{"add_年月_column", type text}}),
  // https://docs.microsoft.com/ja-jp/powerquery-m/datetime-date
  // https://docs.microsoft.com/ja-jp/powerquery-m/duration-days
  add_相対日付_column = Table.AddColumn(add_年月_column, "相対日付", each Duration.Days(
            [Date] - today
        ), Int64.Type),
  // https://community.powerbi.com/t5/Power-Query/Date-difference-in-Months-Power-Query/td-p/1993216
  add_相対月_column = Table.AddColumn(add_相対日付_column, "相対月", each 12 * ( [年] - Date.Year(today) ) + [月] - Date.Month(today), Int64.Type),
  add_相対年度_column = Table.AddColumn(add_相対月_column, "相対年度", each [年度] - ThisFY, Int64.Type),
  add_相対年_column = Table.AddColumn(add_相対年度_column, "相対年", each [年] - Date.Year(today), Int64.Type),
  add_会計期_column = Table.AddColumn(add_相対年_column, "会計期", each Date.Year(Date.AddMonths([Date],-5)) - 1940, Int64.Type),
  add_相対会計期_column = Table.AddColumn(add_会計期_column, "相対会計期", each Date.Year(Date.AddMonths([Date],-5)) - Date.Year(Date.AddMonths(today,-5)), Int64.Type)
in
  add_相対会計期_column

先に軽く理解しておくとよさそうなところ３つ

Power Query の勉強を始めたときに、まず最初に読んでおくといいなと思ったところを３つ紹介します。

言語仕様

もちろん全部理解できるわけではないけれど、最初にざーっと目を通しておくと、その後なにかに躓いたとき、先人たちの記事を読んだときに、「ああ、あれってもしかしてここのこと？」と戻ってこれるかもしれない。一回読んで、勉強して、実践して、また戻ってくるところなんだろうなぁと。

2022/01/28現在：このページみたいに日本語だと翻訳がバグっているところがあるので、読んで違和感があるところは英語版も参照するのがいいです。

# を使用した表現

Power Query には、DateやDateTime、期間（Duration）などを表すときに先頭に # をつけた特殊な書き方があります。M関数のドキュメント内の、関数使用例に頻出するので、まずはこの # を使った表現のドキュメントを読むと、他の関数ドキュメントを理解しやすいです。

_ と each 文

簡略化された宣言each-expression は、 (アンダースコア) という名前の 1 つの仮パラメーターを受け取る型指定されていない関数を宣言するための構文の省略形です。簡略化された宣言は、高階関数呼び出しの読みやすさを向上させるためによく使用されます。たとえば、次の宣言のペアは意味的には同等です。

each _ + 1 
(_) => _ + 1  
each [A] 
(_) => _[A] 

Table.SelectRows( aTable, each [Weight] > 12 ) 
Table.SelectRows( aTable, (_) => _[Weight] > 12 ) 

こちらも、上記のカレンダーテーブルには出てこないのですが、ドキュメントや他の人のコードを読むと頻出なので、最初に「こういう書き方もある」と理解しておいたほうがいいと感じました。
pythonなどでいうfor文のイテレーターのような理解をしましたが、下記の記事を参照するとさらによくわかります。

まとめ

DAX、Power Query、データウェアハウス、モデリング、アーキテクチャ？
BI は関連技術や考えなければいけないことがが多くてよくわからなくなるけど、ひとつひとつ紐解いていく感じが楽しい。

2022年1月29日 追記
本記事を書くにあたり、LocalTimeの考え方で学びがあったので、そちらも記事にしました。