More than 1 year has passed since last update.

Power Query へそのゴマ Advent Calendar 2024

@spumoni(spumoni)

Power Query へそのゴマ　第１５章　日付、時刻、期間

Last updated at 2024-12-14Posted at 2024-12-14

15.1. 日付データの基本操作

15.1.1. 日付データの型

Power Queryでは、日付データは以下の3種類の型で表現されます。

date: 年月日のみ（例: 2023-12-07）
time: 時刻のみ（例: 10:30:00）
datetime: 年月日と時刻（例: 2023-12-07T10:30:00）
datetimezone: 年月日、時刻、タイムゾーン（例: 2023-12-07T10:30:00+09:00）
**duration: 期間

ExcelやPower BIのDAXでは、日付を数値として扱います。

Excelでは、1は 1900/1/1 を表しますが、Power BIでは、1は 1899/12/31 を表します。しかし、2024/12/25 はどちらも 45,651 となります。

この違いの原因は、Excelの初期バージョンが、当時人気のあった表計算ソフト Lotus 1-2-3 のうるう年バグとの互換性を持たせたことにあります。この互換性が現在まで引き継がれている一方で、Power BIとPower Queryではその互換性を採用していないため、1日のズレが生じます。

また、それ以前の日付は負の数値で表記されます。ただし、数値と日付が連携して扱える範囲は、-657,434（100/1/1）から 2,958,465（9999/12/31）の間に限定されます。この仕様により、M言語では 0001/01/01 から 9999/12/31 までの日付を扱うことが可能ですが、Power BIと連携できるのは 100/1/1 から 9999/12/31 までとなります。

15.1.2 `culture` と日付データのフォーマット

日付けや数値の表示方法は、各国によって違いがあります。日本の日付けの表示では 2024/12/25 となっていますが、アメリカの表示形式では 12/25/2024 となります。また、イギリスの表示形式では 25/12/2024 です。Power BI Desktopでは、ユーザーのマシンの地域設定によって制御しています。つまり、環境が変われば表示形式が変わってしまうことになります。

M言語では、culture を指定することで、表示形式を個別に制御することができます。

Date.From 関数で 14/2/2024 を変換しようとすると通常エラーになりますが、第2引数に culture を設定することで、日付を変換することができます。

Date.From("14/2/2024", "en-GB")

Power Queryにおける culture は、文字列データを日付や数値型に変換に使用される ロケール 情報を指します。cultureは、地域や言語に応じた日付や数値フォーマットの違いを反映します。

例:
- アメリカ英語（en-US）: MM/dd/yyyy (例: 12/07/2023)
- イギリス英語（en-GB）：dd/MM/yyyy （例: 07/12/2023）
- 日本語（ja-JP）: yyyy/MM/dd (例: 2023/12/07)

Date.FromTextやDateTime.FromText関数では、Cultureを指定して文字列から日付に変換できます。

let
    Table =
        Table.FromRecords(
            {
                [
                    アメリカ = "12/07/2024",
                    イギリス = "07/12/2024", 
                    日本 = "2023/12/07"
                ]
            },
            type table [アメリカ = text, イギリス = text, 日本 = text]
        ),
    ToDate =
        Table.TransformColumns(
            Table,
            {
                {"アメリカ", each Date.FromText(_, [Culture = "en-US"]), type date },
                {"イギリス", each Date.FromText(_, [Culture = "en-GB"]), type date },
                {"日本",     each Date.FromText(_, [Culture = "ja-JP"]), type date }
            }
        )
in
    ToDate

複数の国で使用されたり、複数のカルチャを使い分ける必要がない限り、コードを使用してカルチャを指定する必要はありません。

15.1.3 日付の演算

更に、M言語では、日付を単純な加算や減算を利用して計算することはできません。以下の式はエラーになります。

#date(2024,1,1) + 1

足し算を行うには、一度数値型に変換するか、Date.AddDays, Date.AddWeeks, Date.AddMonths, Date.AddQuarters, Date.AddYears などの関数を使用します。

数値に変換して加算

let
    Source = #date(2024,1,1),
    AddDays = Number.From(Source) + 4,
    NewDate = Date.From(AddDays)
in
    NewDate

Date.AddDaysで連続した日付を生成

let
    // 2024年1月1日から12月31日までの連続した日付を作成
    Source = 
        List.Generate( 
            () => #date(2024, 1, 1), 
            each _ <= #date(2024, 12, 31), 
            each Date.AddDays( _, 1)
        ),
    // テーブルに変換
    Table = 
        Table.FromList(
            Source,
            Splitter.SplitByNothing(),
            type table [Date = date],
            ExtraValues.Error
        )
in
    Table

日付け同士の差を求めることができます。

let
    Date1 = #date(2024,12,1),
    Date2 = #date(2024,12,25),
    Diff = Date2 - Date1
in
    Diff

この計算の結果は、24.00:00:00 と表示されます。つまり差は２４日となります。

また、以下の様にdate型とtime型に演算子+を使用することはできません。

#date(2024, 2, 14) + #time(17, 0, 0)  // Error

しかし、演算子&を使うことができます。

#date(2024, 2, 14) & #time(17, 0, 0)

15.1.4 年、月、日の抽出

以下のコードは、Date.Year、Date.Month、および Date.Day 関数を使用して、日付のそれぞれのコンポーネントを抽出しています。

let
    Source = Table.FromRows(
        { { #date(2024,12,25) } },
        type table [Date = date]
    ),
    AddYearColumn =
        Table.AddColumn( Source, "Year", each Date.Year([Date]) ),
    AddMonthColumn =
        Table.AddColumn( AddYearColumn, "Month", each Date.Month([Date]) ),
    AddDayColumn =
        Table.AddColumn( AddMonthColumn, "Day", each Date.Day([Date]) )
in
    AddDayColumn

日付のコンポーネントを抽出するもう1つの方法は、Date.ToRecord 関数を使用することです。

let
    Source = Table.FromRows(
        { { #date(2024,12,25) } },
        type table [Date = date]
    ),
    
    // Record列に年、月、日に分割したレコードを作成
    toRecord = 
        Table.AddColumn(
            Source,
            "Record",
            each Date.ToRecord([Date])
        ),
        
    // Recordを展開
    Expanded = 
        Table.ExpandRecordColumn(
            toRecord, 
            "Record", 
            {"Year", "Month", "Day"}, 
            {"Year", "Month", "Day"}
        )
in
    Expanded

15.1.5 月初日・月末日の取得

Date.StartOf および Date.EndOf ファミリの関数を使用して、年、月、週の開始日、終了日を取得できます。

let
    DateValue = #date(2024, 12, 7),
    StartOfYear  = Date.StartOfYear(DateValue),
    EndOfYear    = Date.EndOfYear(DateValue),
    StartOfMonth = Date.StartOfMonth(DateValue),
    EndOfMonth   = Date.EndOfMonth(DateValue),
    StartOfWeek  = Date.StartOfWeek(DateValue),
    EndOfWeek    = Date.EndOfWeek(DateValue)
in
    [
        StartOfYear = StartOfYear, EndOfYear = EndOfYear,
        StartOfMonth = StartOfMonth, EndOfMonth = EndOfMonth,
        StartOfWeek = StartOfWeek, EndOfWeek = EndOfWeek
    ]

Date.StartOf および Date.EndOf ファミリの関数は、date, datetime, datetimezone のいずれかの値が渡されますが、出力も渡された値の型と同じ型で返されます。

例えば、Date.EndOfMonth に datetime の値を渡した場合、

Date.EndOfMonth(#datetime(2024,12,7,8,35,00))

返される値は、2024-12-31T23:59:59.9999999 になります。

また、datetimezone の値を渡した場合、返される日時は渡された値と同じタイムゾーンで計算されます。

let
    DateValue = DateTimeZone.UtcNow(),
    StartOfYear  = Date.StartOfYear(DateValue),
    EndOfYear    = Date.EndOfYear(DateValue),
    StartOfMonth = Date.StartOfMonth(DateValue),
    EndOfMonth   = Date.EndOfMonth(DateValue),
    StartOfWeek  = Date.StartOfWeek(DateValue),
    EndOfWeek    = Date.EndOfWeek(DateValue)
in

    [
        StartOfYear = StartOfYear, EndOfYear = EndOfYear,
        StartOfMonth = StartOfMonth, EndOfMonth = EndOfMonth,
        StartOfWeek = StartOfWeek, EndOfWeek = EndOfWeek
    ]

15.1.6 `IsInCurrent` `IsInNext` `IsInPrevious`

日付データ型には、IsInCurrent、IsInNext、および IsInPrevious の3つの追加ファミリーの関数があります。これらの各ファミリーには、日、週、月、四半期、年の標準的な時間単位に対応する関数が含まれています。

指定の datetime 値が現在の月に含まれるか

Date.IsInCurrentDay(#date(2024, 12, 8))

指定された datetime 値が次の月に含まれるか

Date.IsInNextMonth(#date(2024, 12, 25))

Date.IsInNextN Date.IsInPreviousN と付いている関数では、2番目のパラメータを指定し、判定の範囲を広げることができます。

2025/2/25 が翌月から3か月後の月の中に含まれるか

Date.IsInNextNMonths(#date(2025, 2, 25), 3)

この例では、今現在が 2024/12/15 とすると、 2025/1/1 から 2025/3/31 までの日付を指定すると true が返されます。

しかし、datetimezone を渡した場合、日付の判定はローカルの日時に変換されて判断が行われます。

let
    Source = [
        UtcNow = DateTimeZone.UtcNow(),
        #"Utc -3h" = DateTimeZone.UtcNow() + #duration(0, -3, 0, 0),
        #"IsInCurrentDay(Utc -3h)" = Date.IsInCurrentDay(#"Utc -3h"),
        #"Utc +12h" = DateTimeZone.UtcNow() + #duration(0, 16, 0, 0),
        #"IsInCurrentDay(Utc +12h)" = Date.IsInCurrentDay(#"Utc +12h")
    ]
in
    Source

UTCで12月9日と12月8日で日付が異なっていても、日本時間では同一日なので判定は TRUE が返されています。また、UTCと同じ日付でも、日本時間で日付が変わっていれば判定は FALSE になります。

15.2 日付のフォーマット

日付データの出力や、テキスト型から日付型への変換では、フォーマットコードを使用することができます。

15.2.1 日付からテキストへの変換

DateTime.ToText(
    #datetime(2024,12,23,3,0, 0), 
    "yyyy/MM/dd"
)

あるいは、

DateTime.ToText(
    #datetime(2024,12,9,8,10,23),
    [
        Format="yyyy/%M/%d (ddd) hh:mm:ss"
    ]
)

calture を指定することもできます。

DateTime.ToText(
    #datetime(2024,12,9,8,10,23),
    [
        Format="yyyy/%M/%d (ddd) hh:mm:ss",
        Culture = "en-US"
    ]
)

15.2.2 テキストから日付けへの変換

テキスト型から日付け型への変換は、以下の様に行います。

Date.FromText("2010-12-31")

2010/12/31 や 2010年12月31日、2010 dec 31、20101231、12/31/2010、12月31日2010年、31日12月2010年 などの書き方も変換してくれます。

Format や Culture を指定して、精度を上げることもできます。

DateTime.FromText(
    "30 Dez 2010 02:04:50.369730", 
    [
        Format="dd MMM yyyy HH:mm:ss.ffffff", 
        Culture="de-DE"
    ]
)

ただし、和暦はサポートされていません。

参考文献

Microsoft Learn: Date functions (Power Query)
The Definitive Guide to Power Query by Reza Rad and Leila Etaati
Collect, Combine, and Transform Data Using Power Query in Excel and Power BI by Gil Raviv

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Power Query へそのゴマ 第１５章 日付、時刻、期間