Power Query の カスタム列の追加 を使わずに 日付テーブル を生成

Power BI や PowerPivot for Excel などで 日付テーブル を作ることはよくあるのだけど、Power Query で生成することが多い。簡単だしそこそこ複雑なことができるから。で、思っていたのが [カスタム列の追加]がちょっと面倒くさい。シンプルな操作ではあるけれどもステップが増えていく一方なので考え方を変えてみた。

日付テーブルとは

日付テーブルを例にするけれども、似たような構成が必要になったときでもよそうそうな気がする。

日付テーブル
日付列を持ち、その日付は日単位で連続していること。この日付テーブルとリレーションシップにより タイム インテリジェンス関数 (DAX) が機能する。なので必須でもあるディメンジョンテーブル。

日付	年	月	日
...	...	...	...
2018-05-01	2018	5	1
2018-05-02	2018	5	2
...	...	...	...
2018-12-31	2018	12	31
2019-01-01	2019	1	1
...	...	...	...

Power Query

連続データの生成

連続データの生成にはいくつか方法があって、必要な連続データに合わせてどれかを使えばよい。

.. 演算子

list(リスト)は、"{" "}"(波かっこ)を用いて定義できるが、連続する値リストになる定義として .. 演算子が利用できる。

連続する値をもつlist

{-3 .. 3} = {3, 2, 1, 0, 1, 2, 3} // true

{"あ" .. "お"} = {"あ", "ぃ", "い", "ぅ", "う", "ぇ", "え", "ぉ", "お"} // true

number(数値)は Int32.Type(32bit整数型)、text(文字列)は 長さ1のtextをサポートする。
なので、連続した日付を持つ list をいきなり出力することができない。

整数に変換してから、..演算子

{ Number.From( #date( 2018, 5, 1 ) ) .. Number.From( #date( 2018, 5, 4 ) ) }

で、あとから date(日付)に変換するなど。

List.Dates 関数

連続した日付を持つ list(リスト)を出力する List.Dates 関数 (Power Query)

構文：List.Dates

List.Dates(
    start as date,
    count as number,
    step as duration
) as list

連続した日付のstart(開始日付) に step(duration)を加算しながら、count(リストアイテム数)の list(リスト)を出力する。

List.Dates( #date( 2018, 5, 1 ), 3, #duration( 1, 0, 0, 0 ) )
    = { #date( 2018, 5, 1 ), #date( 2018, 5, 2 ), #date( 2018, 5, 3 ) } // true

List.Dates( #date( 2018, 5, 1 ), 3, #duration( 2, 0, 0, 0 ) )
    = { #date( 2018, 5, 1 ), #date( 2018, 5, 3 ), #date( 2018, 5, 5 ) } // true

List.Dates( #date( 2018, 5, 1 ), 3, #duration( 0, 12, 0, 0 ) )
    = { #date( 2018, 5, 1 ), #date( 2018, 5, 1 ), #date( 2018, 5, 2 ) } // true 

step(duration)に "日"単位以外を指定してもdate(日付)のlist(リスト)に。
必要な日付値のアイテム数を指定するのには便利なのかも。開始と終了から日付リストを得る場合、アイテム数を計算して求める感じになる。

開始と終了の日付から

let
    StartDate = #date( 2018, 5, 1 ), EndDate = #date( 2018, 5, 3 ),
    Dates = List.Dates(
        StartDate,
        Duration.Days( EndDate - StartDate ) + 1,
        #duration( 1, 0, 0, 0 )
    )
in
    Dates

List.Generate 関数

List.Generate 関数 (Power Query)

構文：List.Generate

List.Generate(
    initial as function,
    condition as function,
    next as function,
    optional selector as nullable function
) as list

initial を初期値として扱い、condition が true になるまで next を繰り返す。selector で出力されるリストアイテムを変換する。最初はとっつきにくいけど慣れるととても便利。

List.Generate( () => #date( 2018, 5, 1 ), each _ <= #date( 2018, 5, 3 ), each _ + #duration( 1, 0, 0, 0 ) )
    = { #date( 2018, 5, 1 ), #date( 2018, 5, 2 ), #date( 2018, 5, 3 ) } // true

List.Generate 推し

カスタム列の追加のような処理も List.Generate (Power Query) 関数 に任せる方針にする

引数はスカラ値じゃなくてもよい

サンプルでは 日付を表す #date( 2018, 5, 1 ) を使ったけれども、スカラ値でなくてもよいので record を使用。
List.Generate の next の処理で record を繰り返し list にする算段

let
    StartDate = #date( 2018, 5, 1 ), EndDate = #date( 2018, 5, 7 ),
    Dates = List.Generate(
        () => [日付 = StartDate],
        each [日付] <= EndDate,
        each [日付 = [日付] + #duration( 1, 0, 0, 0 )]
    ),
    TableFromRecords = Table.FromRecords( Dates )
in
    TableFromRecords

record なので[カスタム列の追加]に相当する複数の Field を用意

let
    StartDate = #date( 2018, 5, 1 ), EndDate = #date( 2018, 5, 7 ),
    Dates = List.Generate(
        () => [
                  日付 = StartDate,
                  年 = Date.Year( 日付 ),
                  月 = Date.Month( 日付 ),
                  日 = Date.Day( 日付 )
              ],
        each [日付] <= EndDate,
        each
            [
                日付 = [日付] + #duration( 1, 0, 0, 0 ),
                年 = Date.Year( 日付 ),
                月 = Date.Month( 日付 ),
                日 = Date.Day( 日付 )
            ]
    ),
    TableFromRecords = Table.FromRecords( Dates )
in
    TableFromRecords

initial と next に使用した record の定義は同じする必要もあるので関数化
[カスタム列の追加]に相当する Field を record に追加しておくだけ。

let
    StartDate = #date( 2018, 5, 1 ), EndDate = #date( 2018, 5, 7 ),
    DateRecord = 
        ( date as date ) as record =>
            [   // このrecord に field を追加していけばよい
                日付 = date,
                年 = Date.Year( date ),
                月 = Date.Month( date ),
                日 = Date.Day( date ),
                IsWeekEnd = List.Contains( 
                    { Day.Sunday, Day.Saturday},
                    Date.DayOfWeek( date )
                ) 
            ],
    Dates = List.Generate(
        () => DateRecord( StartDate ),
        each [日付] <= EndDate,
        each DateRecord( [日付] + #duration( 1, 0, 0, 0 ) )
    ),
    TableFromRecords = Table.FromRecords( Dates )
in
    TableFromRecords

ステップを増やさず列の追加ができてよいかなと考えてはいる。ただ、データ型の判定と指定はそこそこ面倒なので手作業でよいかと。

データ型を予め定義することも

列のデータ型を予め定義したり、Loop の判定に使用する field名 に依存しないこととか。

列のデータ型を予め定義したクエリ

let
    StartDate = #date( 2018, 1, 1 ), EndDate = #date( 2019, 12, 31 ),
    DateRecord = 
        ( date as date ) as record =>
            [
                _date = date,
                日付 = date,
                年 = Date.Year( date ),
                月 = Date.Month( date ),
                日 = Date.Day( date ),
                IsWeekEnd = List.Contains( 
                    { Day.Sunday, Day.Saturday},
                    Date.DayOfWeek( date )
                )
            ],
    ColumnsType =
        type table
            [
                日付 = Date.Type,
                年 = Int64.Type,
                月 = Int64.Type,
                日 = Int64.Type,
                IsWeekEnd = Logical.Type
            ],
    DateFieldValue = 
        (record as record) as date => record[_date] ,
    Dates = List.Generate(
        () => DateRecord( StartDate ),
        each DateFieldValue( _ ) <= EndDate,
        each DateRecord( DateFieldValue( _ ) + #duration( 1, 0, 0, 0 ) )
    ),
    TableFromRecords = Table.FromRecords( Dates, ColumnsType )
in
    TableFromRecords

その他

• Power BI 勉強会 - connpass
• Power BI Desktop を使い始めるには
• Excel で Power Query を使い始めるには