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でやり直したいなぁ。