Power Query：日時を表す数値を適切な型に変換する

日付形式が 8桁の数値（いわゆる「yyyymmdd形式」）になっていたり、時刻が「hhmmss」になっていたりするデータは時々ある。これを日付型や時刻型に変換してみよう。

「yyyyMMdd」形式の 8桁の数値を日付型に変換するのは簡単。「テーブル1」に「日付」列があるものとして。

    日付変換 = (num as number)=>
        Date.From(Number.ToText(num, "0-00-00")),
    列の編集 = Table.TransformColumns(
        テーブル1, {{"日付", 日付変換, Date.Type}}
    )

これでいい。

「hmm」形式の 3～4桁の数値を時刻型に変換する場合、上の「Date.From」を「Time.From」にすれば一応変換はできる。ただし数値が「2400」を超える場合、「Time.From」だとエラーを起こす。

後で計算に使うことや、深夜の入退室を考慮するなら期間型にすべきで、その場合は日時を分けて変換する。

    時刻変換 = (num as number)=> [
        日=Number.IntegerDivide(num, 2400)*1E4,
        時=Number.Mod(num, 2400),
        文字列変換=Number.ToText(日+時, "0\.00\:00"),
        期間変換=Duration.From(文字列変換)
    ][期間変換],
    列の編集2 = Table.TransformColumns(
        テーブル1, {
            {"入室", 時刻変換, Duration.Type},
            {"退室", 時刻変換, Duration.Type}
        }
    )

こういう形。

    変換 = (num as number)=> [
        日付 = Date.From(Number.ToText(num, "0-00-00")),
        日時 = [
            日=Number.IntegerDivide(num, 2400)*1E4,
            時=Number.Mod(num, 2400),
            文字列変換=Number.ToText(日+時, "0\.00\:00"),
            期間変換=Duration.From(文字列変換)
        ][期間変換]
    ],
    列の編集 = Table.TransformColumns(
        テーブル1, {
            {"日付", each 変換(_)[日付], Date.Type},
            {"入室", each 変換(_)[日時], Duration.Type},
            {"退室", each 変換(_)[日時], Duration.Type}
        }
    )

２つをまとめるならこんな感じ。

似たような形で「yyyyMMddhhmm」形式の 12桁の数値を日時に変換する場合。

この場合は DateTime型に変換する。「テーブル2」から計算するものとして。

    日時変換 = (num as number)=> [ 
        文字列=Number.ToText(num, "0\/00\/00 00\:00"),
        日時=DateTime.From(文字列)
    ][日時],
    列の編集 = Table.TransformColumns(
        テーブル2, {
            {"入室", 日時変換, type datetime},
            {"退室", 日時変換, type datetime}
        }
    ),
    滞在時間列の追加 = Table.AddColumn(
        列の編集, "滞在時間", each [退室]-[入室], type duration
    )

むしろ簡単。