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

# 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 
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/021d391c-55f5-d00d-f1e1-4e8ee51e4ae5.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/6099e889-2ffd-e2eb-1c2b-23ee78aebffb.png)

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

## 15.1.3 日付の演算


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

```
#date(2024,1,1) + 1
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2082b463-b651-bcbb-9f68-57fe21d6287e.png)

足し算を行うには、一度数値型に変換するか、`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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/4d4103d9-9433-7487-15cc-16e358fbc4a6.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9733e815-0ed9-7257-8812-5e5169090879.png)

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f297b6cc-21c4-ad27-677e-ab47d6090116.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/5f1c5afe-ed62-4820-34c1-aa2b764cfb7a.png)


## 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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/e486e672-2682-ea97-6e18-9e1a2a9443d9.png)

日付のコンポーネントを抽出するもう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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ecc9d45b-ec43-d5ee-6156-e1c76c81645d.png)

## 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
    ]
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b2401105-2991-fbed-a86f-f4372a7be025.png)

`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
    ]
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/e0c95dc6-b557-df36-be4c-8c0eb5c477e3.png)

## 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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/40956197-783e-893c-be6d-fe30aba266ef.png)

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"
    ]
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c5a5d6ac-cb96-0c21-96e3-355e9ac201f7.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f7f8c05b-ef42-5340-b4d0-d3dcb726834f.png)

## 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"
    ]
)
```

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

https://powerquery.how/date-totext/

---
# 参考文献
1. **Microsoft Learn**: [Date functions (Power Query)](https://learn.microsoft.com/en-us/powerquery-m/date-functions)

3. **The Definitive Guide to Power Query** by Reza Rad and Leila Etaati
4. **Collect, Combine, and Transform Data Using Power Query in Excel and Power BI** by Gil Raviv