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

[Power Queryでクロス結合](https://qiita.com/hrkasno/items/2b928d2e4713720cf6ce)の活用例です。

[[その3] Power BI で社内のリース車の利用状況を可視化してみた](https://qiita.com/KodamaJn/items/b938f8935e0447130870)|@KodamaJn が題材です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/f18849af-2a9f-3767-9c14-92ed25cfe235.png)

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


#サンプルデータ
###イベントデータ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/a6f85641-e7ed-5e77-2422-8b4b53a06eba.png)

###カレンダーテーブル
CALENDER関数(DAX式)は使えないので、
[Power Queryでカレンダー](https://qiita.com/tanuki_phoenix/items/7c2e3a60fbfcc43022c6)|@tanuki_phoenix を参考に作ってみました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/2268e94d-268a-f784-babc-3b99e6b5f084.png)
`日付`列のデータ型は 日付/時刻 にしておきます。

#手順

##１．クロス結合
[前回の記事](https://qiita.com/hrkasno/items/2b928d2e4713720cf6ce)を参考に、イベントテーブル に カレンダーテーブル をクロス結合します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/4dfd4209-d045-47d7-96e6-07704b19b3ab.png)
*～途中略～*
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/6997089f-95d0-8f57-0e25-822c0e3bf08a.png)
tableの展開まで済ませます。

##２．判定列作成
新しい列を作って、欲しいデータに"1"が入るようにします。
カレンダーの`日付`が`Start`と`End`の間に含まれてたら、その日付は欲しいデータなので"1"にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/e91ba286-a448-fe6a-f993-fae328deb9cd.png)


```
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日" を表しています。
[この資料](https://docs.microsoft.com/en-us/powerquery-m/m-spec-values#duration)によると\#duration(日, 時, 分, 秒)ってことらしいです。

##３．欲しいデータのみをフィルタ
欲しいデータは `判定`列=1 になっているので フィルタします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/2f974111-9c89-2473-cd1f-39fb103aabcb.png)
欲しいデータだけになりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/a139122f-4932-cd3c-6bcc-b79a15d1cd12.png)

##４．後片付けとおまけ情報追加
###判定列はもう使わないので削除
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/ee1adb1b-4fd9-dad2-8477-edc47296e2bc.png)
###モデリングや集計に必要な情報を追加
見せたい情報によっても変わるので、要件に合わせていろいろ追加してください。
####１日に分割した単位でStart、Endを計算

分割したデータのStart時刻は、`Start`と`日付`の大きい方
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/7ada4e3b-18d2-d14c-918e-1ec393a65bb3.png)

分割したデータのEnd時刻は、`End`と`日付+1`の小さい方
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/1379d2dd-d303-92a9-4382-37f1b2a3c755.png)

####`分割Start`から`分割End`までの時間を計算
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/c6aa7c8b-8532-a359-e28c-c4d05c57912b.png)

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

###できあがり
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239983/5ddc8782-d807-adff-1467-8960ae2e421c.png)

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