Power Query イベント期間を1日ずつ分割

Power Queryでクロス結合の活用例です。

[その3] Power BI で社内のリース車の利用状況を可視化してみた|@KodamaJn が題材です。

日付をまたぐ時間間隔も暦日ごとに時間を集計するなど|@PowerBIxyz と同じことを
詳細エディタを使わず、画面ポチポチ( ＋if文 ) で作ってみた感じです。
Excelでデータ加工している感覚に近いかも。

#サンプルデータ
###イベントデータ

###カレンダーテーブル
CALENDER関数(DAX式)は使えないので、
Power Queryでカレンダー|@tanuki_phoenix を参考に作ってみました。

日付列のデータ型は 日付/時刻 にしておきます。

#手順

##１．クロス結合
前回の記事を参考に、イベントテーブル に カレンダーテーブル をクロス結合します。

～途中略～

tableの展開まで済ませます。

##２．判定列作成
新しい列を作って、欲しいデータに"1"が入るようにします。
カレンダーの日付がStartとEndの間に含まれてたら、その日付は欲しいデータなので"1"にします。

if [Start] < [日付] + #duration(1, 0, 0, 0) and
    [日付] < [End]
then
    1
else
    0

日付を比較するときに気をつけるのは、
4月1日 は 4月1日 0時00分 のことなので、
Startと比較するときは 日付+1日 してください。

#duration(1, 0, 0, 0) が "1日" を表しています。
この資料によると#duration(日, 時, 分, 秒)ってことらしいです。

##３．欲しいデータのみをフィルタ
欲しいデータは 判定列=1 になっているので フィルタします。

欲しいデータだけになりました。

##４．後片付けとおまけ情報追加
###判定列はもう使わないので削除

###モデリングや集計に必要な情報を追加
見せたい情報によっても変わるので、要件に合わせていろいろ追加してください。
####１日に分割した単位でStart、Endを計算

分割したデータのStart時刻は、Startと日付の大きい方

分割したデータのEnd時刻は、Endと日付+1の小さい方

####分割Startから分割Endまでの時間を計算

###StartとEndを削除
モデリングで、クロス結合前のイベントテーブルとリレーションさせれば、
これらの情報は引っ張ってこれるので、このテーブルからは除きます。

###できあがり

#あとがき
以前、業務でこれを求められたんですが、当時はPower Queryでの加工方法がわからず、
結局 データソース側(Access)SQLで分割してました。。
たまたまデータソースも自分で弄れたから助かったけど、
作り直す機会があったら、Power Queryでやり直したいなぁ。