Power BI（Power Query（M言語））でExcelの日付データのタイムゾーンを変更する

4330 ワード
PowerBI PowerApps PowerQuery PowerBI テキストリンク

概要

Power Appsでアプリを作ってデータをExcelに出力する際に、日付は標準時間(UTC)で出力されるため、Power BIでそのまま読むと日本時間にならないので変換する必要がありました。
ちなみに、Power Appsは自動的に日本時間に変換します。
おまけに、Power Automateは日付のシリアル値を取るというちぐはぐな仕様になっています。

やりたい事

単純に日本時間にずらすだけなら、時間に9を加算すれば良いのですが、日付が変わらないのでタイムゾーンを変更する方法をとることにしました。

目指す結果

Excelデータを読み込んで、Power BI側で日本時間に変換する。

Excelデータ（標準時間） Power BIデータ（日本時間）
2020/8/7 15:46 2020/8/8 00:46
2020/8/6 23:58 2020/8/7 08:58

Excelデータ

Excelで以下のように登録されています。

変換までの手順

ここからStep By Stepで変換までの手順を紹介します。

Power BIで読み込み

  1. Power BIを起動して、《データを取得》をクリックします。

  2. Excelコネクタを選択し、《接続》をクリックします。

  3. 対象のExcelファイルを選択します。

  4. 対象のデータを選択し、《データの変換》をクリックします。

  5. 今は何もしていないのでそのままExcelデータが読み込まれています。

  6. 《詳細エディター》をクリックして詳細エディターを開きます。

  7. コードを以下の通りに書き換え、《完了》をクリックする。

書き換え前
let
    ソース = Excel.Workbook(File.Contents("C:\タイムゾーン変換.xlsx"), null, true),
    テーブル1_Table = ソース{[Item="テーブル1",Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(テーブル1_Table,{{"timeRec", type datetime}})
in
    変更された型
書き換え後
let
    ソース = Excel.Workbook(File.Contents("C:\タイムゾーン変換.xlsx"), null, true),
    テーブル1_Table = ソース{[Item="テーブル1",Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(テーブル1_Table,{{"timeRec", type datetime}}),
    変更された型２ = Table.TransformColumns(変更された型, {{"timeRec", each DateTime.AddZone(_, 0, 0)}}),
    変更された型３ = Table.TransformColumns(変更された型２, {{"timeRec", each DateTimeZone.SwitchZone(_, 9), type datetimezone}}),
    変更された型４ = Table.TransformColumns(変更された型３, {{"timeRec", each DateTimeZone.RemoveZone(_), type datetime}})
in
    変更された型４
  • コード説明
    • 変更された型２
      • Excelから読み取った日付にはタイムゾーンが付いていないので、DateTime.AddZone(_,0,0)で標準時間のタイムゾーンを付加する。
    • 変更された型３
      • タイムゾーンを標準時間からDateTimeZone.SwitchZone(_,9)で日本時間に変換する。
    • 変更された型４
      • タイムゾーンはPower BIでは必要ないのでDateTimeZone.RemoveZone(_)でタイムゾーンを削除する。

結果確認

日本時間に変換されました。