日付をまたぐ時間間隔も暦日ごとに時間を集計するなど

11369 ワード
PowerBI PowerQuery PowerBI テキストリンク

ですよねーと思っていたし、本来自分のためなんだけど、誰かのためになるかもと私もそう思ったので。アイデアは表に出していかないと別のアイデアが浮かばないのである。
[その3] Power BI で社内のリース車の利用状況を可視化してみた - @KodamaJn を読んでいました。

どういうことか

やりたいことはそうややこしいことではない。開始日時 終了日時 から 時間の間隔(Duration)を集計する。

さっそく

いくつかの方法があるので試しながらのイメージで。

ソースデータ

Events
let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "dY1BDoAgDAS/0nCWpAsi4A2/Qfj/N0SjgYImPc1mOjmrpBZlGFGDtWXiuDPXkxT+oWXJ6ug3EMxgQLNpfy4j9ZulexiMtVHZQM18GTDSEA1HCHPDE/4avBHcbASCfY1yAg==", 
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let 
            _t = ((type text) meta [Serialized.Text = true])
        in
            type table [Asset = _t, Start = _t, End = _t]
    )
in
    Source

日付カレンダーは必須

日付カレンダーは必須なので作ります。ここでは実証できればよいので ソースデータから作成。自動で組み込まれる日付テーブルは無効にした方がよい。

Dates
Dates = 
VAR StartDate = MIN( 'Events'[Start] )
VAR EndDate = MAX( 'Events'[End] )
RETURN
    ADDCOLUMNS(
        CALENDAR( StartDate, EndDate ),
        "Year", YEAR( [Date] ),
        "Month", MONTH( [Date] ),
        "Day", DAY( [Date] ),
        "WeekdayName", FORMAT( [Date], "aaa" ), // Locale = ja
        "WeekdayNum", WEEKDAY( [Date] )
    )


ね、簡単でしょ。

Plan A メジャーだけで解決

メジャーを記述すれば解決できる。結果を得るためのアイデアのうちのひとつでしかなから、本当にそれでよいかは考慮が必要。

Measure
# Hours v1 = 
VAR CurrentPeriodStart = MIN( Dates[Date] )
VAR NextPeriodStart = MAX( Dates[Date] ) + 1
VAR FilteredEvents =
    FILTER(
        'Events',
        NOT(
            'Events'[Start] >= NextPeriodStart
            || 'Events'[End] <= CurrentPeriodStart
        )
    )
VAR SummarizedTable =
    ADDCOLUMNS(
        ADDCOLUMNS(
            FilteredEvents,
            "@Start", MAX( 'Events'[Start], CurrentPeriodStart ),
            "@End", MIN( 'Events'[End], NextPeriodStart )
        ),
        "@Minutes", DATEDIFF( [@Start], [@End], MINUTE )
    )
VAR Result =
    SUMX(
        SummarizedTable,
        [@Minutes] / 60
    )
RETURN
    Result

日付テーブルとのリレーションシップに利用できる列がないから、自力で絞り込みと集計を盛り込む。
イメージとしては、テーブルビジュアルで 日付( 'Dates'[Date] ) または 年( 'Dates'[Year] ), 月( 'Dates'[Month] ) が 行見出しになっているときそれぞれの行でどのような集計が必要か考える。
集計粒度は暦日ごとだけではないから、それぞれ取りうる範囲のために Variable で用意しておく。

FilteredEvents
集計すべき対象のみを残すフィルターを 'Events' テーブルに適用
SummarizedTable
フィルタが適用された 'Events' 各行ごとに 時間間隔を計算し、列として追加
MIN 関数 - DAX / MAX 関数 - DAX は 引数(スカラ値)を2つ渡したとき、それぞれ 小さい値 / 大きい値を返すので IF 関数 - DAXじゃなくても事足りる。で、集計する期間を交差する 'Events' の行では、集計期間の開始終了に置き換える算段。
Result
各行、分単位で計算された結果の列 [＠Minutes] の和を求める

ね、簡単でしょ。

Plan B ソースデータを暦日に分解してから集計する

集計したい内容をできるだけ網羅できるようモデリングをしておいた方がよさそうである。

まず Events クエリ

ディメンジョンテーブルとして、これを Power Query エディター で調整する。

Events
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY1BDoAgDAS/0nCWpAsi4A2/Qfj/N0SjgYImPc1mOjmrpBZlGFGDtWXiuDPXkxT+oWXJ6ug3EMxgQLNpfy4j9ZulexiMtVHZQM18GTDSEA1HCHPDE/4avBHcbASCfY1yAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Asset = _t, Start = _t, End = _t]),
    AddedIndex = Table.AddIndexColumn(Source, "ID", 1, 1),
    ChangedType = Table.TransformColumnTypes(
        AddedIndex,
        {
            {"Asset", type text},
            {"Start", type datetime},
            {"End", type datetime},
            {"ID", Int64.Type}
        }
    ),
    AddedDays = Table.AddColumn(
        ChangedType,
        "Days",
        each
            Duration.Days( DateTime.Date([End]) - DateTime.Date( [Start] ) ) 
            - Number.From( DateTime.Time([End]) = Time.From( 0 ) )
            + 1,
        Int64.Type
    )
in
    AddedDays

AddedIndex
ソースにしたデータにはユニークなキーがないので追加
ChangedType
データ型を調整
AddedDays
開始終了日時から日数を計算し列として追加。あった方がよさげであり、後で使うことにはなった。

で、こうなる

ね、簡単でしょ。

Events を暦日に分解

調整済み Events を暦日に分解しファクトテーブルに。Events クエリを参照し、Detail クエリとする。

Detail
let
    Source = Events,
    ChangedType1 = Table.TransformColumnTypes(
        Source,
        {
            {"Start", type date},
            {"End", type date}
        }
    ),
    AddedCustom = Table.AddColumn(
        ChangedType1,
        "Dates",
        each Table.FromColumns(
            {
                List.Dates([Start], [Days], Duration.From( 1 )),
                {true} & List.Repeat({false}, [Days] - 1)
            },
            {"Date", "IsPrimary"}
        )
    ),
    ExpandedTable = Table.ExpandTableColumn(
        AddedCustom,
        "Dates",
        {"Date", "IsPrimary"},
        {"Date", "IsPrimary"}
    ),
    ChangedType2 = Table.TransformColumnTypes(
        ExpandedTable,
        {
            {"Date", type date},
            {"IsPrimary", type logical}
        }
    ),
    RemovedOtherColumns = Table.SelectColumns(
        ChangedType2,
        {"ID", "Date", "IsPrimary"}
    )
in
    RemovedOtherColumns

Source
Events クエリを参照
ChangedType1
type datetime から type date に変換することで date のみを
AddedCustom
List.Dates 関数 - Power Query で 連続する日付のリストを生成
先頭行のみを true とするリストを生成
これらを列とする table を Table.FromColumns 関数 - Power Query で生成
得られた 値(type table) を各行にアタッチ
ExpandedTable
値(type table) を展開
ChangedType2
展開した列のデータ型の調整
RemovedOtherColumns
不要な列を除外

ね、簡単でしょ。

ロードしてモデリング

リレーションシップ

角が少ないけど立派なスタースキーマ

計算列の追加

'Detail'テーブルの各行ごとで計算した時間間隔を追加する。たぶん、その段取りの方が集計の要件を追加しやすいのではないかな。

Hours(計算列)
Hours = 
    DATEDIFF(
        MAX( RELATED( 'Events'[Start] ), 'Detail'[Date] ),
        MIN( RELATED( 'Events'[End] ), 'Detail'[Date] + 1 ), 
        MINUTE
    ) / 60

RELATED 関数 - DAX で リレーションシップ 1 側の列を参照できるので、これらを 'Detail'テーブルの [Date] 列と比較。得られた結果から 時間間隔を 分単位で計算

Measure
# Hours v3 = SUM( Detail[Hours] )

ね、超簡単でしょ。
ここでは追加していないけど各行ごとで調整された開始と終了(時刻のみ)を列として持たせておけば、時間帯でもスライスもできそう。

計算列を追加せずメジャーで記述するなら次の通り。

Measure
# Hours v2 = 
SUMX(
    ADDCOLUMNS(
        Detail,
        "@Minutes",
        VAR _Start = MAX( RELATED( Events[Start] ), 'Detail'[Date] )
        VAR _End = MIN( RELATED( Events[End] ), Detail[Date] + 1 )
        RETURN
            DATEDIFF( _Start, _End, MINUTE )
    ),
    [@Minutes]
) / 60

並べてみる

同じになったのでひと安心

思ったこと🙄

同じ結果を得られるにしても必ずいくつかの方法がある。それらの多くをできるだけ知っておくほうが良いとは思う。その中から一番都合がよいものを選択していきたいものである。

アイデアやひらめき次第だから基本的にはどうぞご自由になんだけど、
スタースキーマに整えること
メジャーを必ず記述する
は守っていった方がよいねと。週末は集計から除外するとか、実績と比較するとかいろんなことができるなぁ。
応用していろいろと

その他