この記事はMicrosoft Power BI Advent Calendar 2024に参加しています。
使用しているPower BIは2024/12/22時点の最新バージョンです。
IT系の仕事をしていると国際的なイベントが多く開催され、時差やタイムゾーンに触れる機会も多いと思います。
今回の記事ではそんな時差やタイムゾーンがPower BIやPower Queryでどのように扱われるかを書いていきます。
Power Queryにおけるタイムゾーン
Power Queryでdatetimezone型の値を作成するには二種類の方法があります。
- 自分で作成する
- Text型からデータ型の検出でdatetimezone型に変換する
datetimezone型の作成
#datetimezoneでdatetimezone型の値を作成できます。
#datetimezone(2010, 12, 31, 12, 26, 2, 8, 0)
引数は年、月、日、時間、分、秒、タイムゾーンオフセット時間、タイムゾーンオフセット分の順で並んでいます。
#datetimezoneを記述する場合の注意
#datetimezoneを記述する際はUTCマイナスの扱いに注意しましょう。
例として、以下の式でUTC-6:00のdatetimezone型の値を作成できます。
=#datetimezone(2024, 12, 31, 12, 26, 0, -6, 0)
しかしUTC-6:30のdatetimezone型の値を作成する場合はどうでしょうか？
=#datetimezone(2024, 12, 31, 12, 26, 0, -6, 30)
このように記述した場合、UTC-5:30のdatetimezone型の値が作成されます。
正しい記述は以下の通りです。
=#datetimezone(2024, 12, 31, 12, 26, 0, -6, -30)
#datetimezoneを記述する際は、UTCプラスなら正の値、UTCマイナスなら負の値でタイムゾーンオフセット時間、タイムゾーンオフセット分の引数を揃える必要があります。
Text型からデータ型の検出でdatetimezone型に変換する
タイムゾーンオフセット時間、タイムゾーンオフセット分を別途用意しておくと、datetime型からdatetimezone型に変換することは容易です。
まず、datetime型をtext型に変換します。
次に割り当てたいタイムゾーンオフセット時間、タイムゾーンオフセット分を数値で用意します。
この二列を:を区切り記号として結合します。
先程用意したtext型に変換したdatatimeと を区切り記号として結合します。
最後にデータ型の変更でdatetimezoneに変更すると書式を自動的に識別して良い感じにdatetimezone型へ変換してくれます。
データ型の検出でdatetimezone型に変換する場合の注意点
ここでもやはりUTCマイナスの扱いが注意点になります。
先程の#datetimezoneを記述する場合の注意では正の値と負の値を揃えましたが、データ型の変更の場合はどうでしょうか？
同じようにUTC-6:30のdatetimezone型の値を作成してみましょう。
まず、タイムゾーンオフセット時間とタイムゾーンオフセット分を負の値で揃えた場合です。
これはデータ型の変更でエラーになります。
次はタイムゾーンオフセット時間を負の値、タイムゾーンオフセット分を正の値にした場合です。
正しくUTC-6:30であるdatetimezone型の値に変換されました。
データの変更でdatetimezone型の値に変換する場合、タイムゾーンオフセット分は必ず正の値である必要があります。
番外編:データソース側でdatetimezone型にしておく
datetimezone型に対応しているデータソースであれば、そのままPower Queryにdatetimezone型として取り込むことが出来ます。
Excelなどのdatetimezone型が扱えないデータソースではtext型のデータとして入力してPower Queryで変換する事になります。
Power BIにおけるタイムゾーン
Power BIでdatetimezone型は扱えません。
Power Query上でdatetimezone型として扱った列はPower BIに取り込んだ時点で自動的にdatetime型に変換され、タイムゾーン情報は無くなります。
そのため、Power BIでタイムゾーンの計算をしたい場合は事前にUTC0に揃えたdatetime列を作成しておくなどの工夫が必要になります。
Power QueryでdatetimezoneをUTCに変換する
Power Query上にdatetimezone型の値があるならDateTimeZone.ToUtc関数を使うことでUTCに変換できます。
=DateTimeZone.ToUtc([現地開始時刻タイムゾーン])
UTC-6:00が+6:00され、UTCに変換されました。
タイムゾーンを扱う為のディメンションテーブルを用意する
time型はマイナスの値を持つ事は出来ません。
そのため、UTC±のフラグを持ったtime型のデータを用意すると計算が楽になります。
フラグを作成する際は、UTC-0:30というタイムゾーンは存在しないためタイムゾーンオフセット時間が負の値であるかを条件にすれば問題無く作成できます。
Power BI レポート上でタイムゾーンを計算する
Microsoft Igniteのような海外のイベントや、Microsoft AI tourのような多国籍イベントを扱う場合、タイムゾーン計算の為のUTC日時、開催地の現地日時、そしてどの国から見るかで変わる視聴地日時の三つが必要になります。
データモデル
今回使用するデータモデルは以下の通りです。
d_タイムゾーンマスタテーブルはフィルターで値を使用するのでリレーションシップは設定しません。
UTC日時から視聴地日時を計算する。
先程作成したUTC±Flag列とIF文を組み合わせて加算と減算を制御します。
datetime型とtime型の値は直接加算減算できるので、+と-を入れ替えるだけでどちらの計算もできます。
更にMAXX関数を使うことで確実にファクトテーブル内だけで評価させます。
DAX式は以下の通りです。
視聴地時刻 =
IF(
SELECTEDVALUE(
'd_タイムゾーンマスタ'[UTC±Flag]
)=TRUE(),
MAXX(
'f_セッションリスト',
[UTC開始時刻]
+
SELECTEDVALUE(
'd_タイムゾーンマスタ'[タイムゾーン]
)
),
MAXX(
'f_セッションリスト',
[UTC開始時刻]
-
SELECTEDVALUE(
'd_タイムゾーンマスタ'[タイムゾーン]
)
)
)
ビジュアルはd_タイムゾーンマスタテーブルの視聴国をスライサーとして配置します。
これにより動的に視聴地時刻で使用されているタイムゾーンを制御できます。
以下はUTC+9の日本とUTC-6のアメリカ(シアトル)をスライサーで切り替えている例です。
番外編:スライサーは非表示でも機能する
スライサービジュアルは非表示にしても、選択された内容は他のビジュアルに作用します。
d_タイムゾーンマスタテーブルはスライサーを選んでいない状態ではアメリカと日本の2行です。
ここに日本を選択したスライサーを追加して非表示にしても、選択されたフィルターは有効なままです。
これを使うことで最初に選んだ視聴国の選択を、ページを跨いでも一貫して有効化することが出来ます。
まとめ
Power BIとPower Queryで使えるデータ型はイコールではない
datetimezone型以外にもPower Query内でしか使えないデータ型はいくつかある。
Power BIのレポートに落とし込む前に、Power BIで扱えるデータ型になっているか確認しよう
データ型の検出による自動変換はそれなりに融通が利く
完全な書式ではなくとも保管してくれる場合が多い。
知っていれば入力の手間が軽減されるかも。
DAX式だけではなく、ビジュアルも計算に組み込める
テーブルを一つ用意してビジュアルに組み込むことで動的な計算がとても楽になる事もある。
インタラクティブな操作が出来るPower BIの強みを生かそう。
Power Queryでdate型やdatetime型をdatetimezone型に変換すると、システムロケールに基づいたTimezone情報が自動的に付与される挙動ってどうにか変更できないのかな。