Mから始めよう #4 〜Power BIのタイムゾーンについて〜

#タイムゾーンとは
　タイムゾーンとは、協定世界時 (UTC)を元にして、各国の標準時間とのずれを表示したものです。日本標準時はJSTと呼ばれ、UTCに9時間プラス(UTC+9:00)します。
　表記の仕方は、日時の表記が各国で異なりますが、通常、日時表記の後にUTCとのずれの時間を表記し、**2021/03/17 10:00:00 +09:00**などと書きます。
　表示形式を統一するために、**ISO8601**の**YYYY-MM-DDThh：mm：ss.sTZD**形式を採用するAPIもあります。TZDは、タイムゾーンオフセットを表し、通常は+09:00などとなりますが、UTCの場合は特別に Z の1文字で表記されます。つまり、YouTubeなどのAPIから取得するデータの日時は、**2021-03-07T01:46:44Z**のような表記になります。この形式のデータは、Power Queryで「日付／時刻／タイムゾーン」型に変換することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c2f99292-40b0-69a6-077d-cf6305f463ae.png)

#Power Queryのタイムゾーンの扱い
　Power QueryのM式の日時の関数は、Date、DateTime、DateTimeZoneの3段階で操作することができます。Date関数は年月日、DateTimeは年月日時分秒、DateTimeZoneは年月日時分秒に加えてオフセット時分を記述します。
　以下のような、UTCとJSTデータを用意しました。このテストを行っているのが3月17日9時過ぎなので、4番目のデータ（5行目）と6番目のデータ（7行目）の日時が、現時点から大きい値になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/015d87d6-50f2-2add-84f5-1a3e299ad21f.png)
　これをPower BIのPower Queryで読み込ませ、Power Queryに用意された関数、**DateTimeZone.LocalNow**と**DateTimeZone.UtcNow**との比較をさせてみました。（※ 「日時」の値が今の時間より大きければ True、小さければ Falseとなる）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a8d1114f-a691-4552-dd1d-5bef3ecfc6a1.png)
　タイムゾーンを判断して、UTC、JSTいずれも4番目、6番目のデータが True となり、同じ結果になることがわかります。
　このままレポートに表示させても、比較はPower Queryで行っているので、上記と同じ結果となります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3fbe1a46-0440-4668-d368-9e76cfcd7a21.png)

　ただし、これを**Power BIサービス**に発行し、データセットを更新してからレポートを見ると、以下のように**DateTimeZone.LocalNow**の値がUTCと同じ値になってしまいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/1f2497a7-3bbb-3451-4afe-cdbbcc5dce25.png)
　**DateTimeZone.LocalNow**関数と**DateTime.LocalNow**関数は、Power BI DesktopではPCの日時を返しますが、Power BIサービスに発行した後はUTCの日時を返すようになります。失敗しないために、Power Queryの中で現在の日時を使って計算する場合は、**DateTimeZone.UtcNow()**で取得し、他のデータの日時と同じタイムゾーンに変換して使うのが良いと思います。

#DAXのタイムゾーンの扱い

　一方で、Power Queryエディターで見ると、以下の通り、タイムゾーンを含んで表示されていますが、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/05a6722a-841a-a9d2-04a3-5bbedc52063c.png)
　Power BIのデータビューでは、以下の通りタイムゾーン情報が消えてしまいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/223bc4e3-94b8-cce8-acf0-498ad06e7a70.png)
　Power Query以降の処理では、タイムゾーンを扱うデータ型、関数いずれも今のところありませんので、タイムゾーンの情報は削除され、「日付と時刻」の型に変換されてしまうものと思われます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a0598413-c049-ded2-ffc1-fe89603fea21.png)
　このまま、DAXのUTCNOW関数(NOW関数でも同じ)と比較を行うと、以下のようにUTC同士の比較は正しくできますが、JSTとの比較ではタイムゾーンは考慮してもらえず、正しい結果となりません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ccd00c93-895f-cf8f-4ef3-a481c1e9ad84.png)

　**つまり、Power BIで日時データにタイムゾーンが含まれているときは、Power Queryの時点でタイムゾーンを意識して統一しておいた方がいいでしょう。**

# Power Queryでタイムゾーンを変更する
　Power Queryでタイムゾーンを変更するには、**DateTimeZone.SwitchZone**を使用します。Power BIのレポートでTimeZone情報は消えてしまうのですが、潔癖症な方は意図的に**DateTimeZone.RemoveZone**を使ってTimeZone情報を消してDateTime型にしておきます。以下、UTCからJSTへ変更するサンプルです。

```
    発行日の型を日時に変更 = 
        Table.TransformColumns(
            #"展開された default",
            {
                {
                    "publishedAt", 
                    each DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.From(_), 9)), 
                    type datetime
                }
            }
        ),
```

# DAXでUTCを使うには
　データの日時を日本標準時(JST)にしておけば、レポートにも日本標準時(JST)で表示されます。これで困るのは、**UTCNOW**を使って、今時点の日時とデータの日時を比較したいときです。UTCNOW、NOWいずれもUTC時間を取得しますので、そのままだとJSTと比較できません。
　そこで、比較の前にUTCNOWに**TIMEVALUE**関数を使って9時間プラスしてやる作業が必要です。以下、サンプルです。

```
UTC NOWに9時間加算との比較 = [日時] > UTCNOW() + TIMEVALUE("9:00")
```

　これで、以下ようにUTCNOWに9時間を足して比較すれば、JSTの日時と正しく比較できるようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/bbe4ed41-b555-266b-be5d-408475150ad8.png)


# まとめ
- APIで取得するデータは日時がUTCになっている場合があるので、**タイムゾーン情報**には注意して、Power Queryで統一しておきましょう。
- Power Queryでタイムゾーンを変更するには、**DateTimeZone.SwitchZone**を使用しましょう。
- Power Queryで取得する**DateTimeZone.LocalNow**と**DateTime.LocalNow**は、Power Query Desktopでの値はPCの日時ですが、発行してデータセットの更新をするとUTCの日時になるので注意しましょう。
- DAXで**UTCNOW**を使用する場合は、**TIMEVALUE**でデータの日時のタイムゾーンに合わせましょう。

以上