本講座では、パワークエリで時間の計算をするため「時間を表すデータ型の学習」から実際に「関数を使った様々な時間の計算」を行います。
時間を表すデータ型の確認
パワークエリで時間を扱う場合には、5種類のデータ型を利用します。それぞれの性質を理解して時間に関する計算や、変換を習得していきましょう。
datetime型 ; 日付/時刻
書式 | #datetime(年,月,日,時,分,秒) |
関数定義の詳細説明 | マイクロソフト公式サイトへのリンク |
実例 | #datetime(2023,1,2,10,11,40) |
実例の結果 | 2023/01/02 10:11:40 |
date型 ; 日付
書式 | #date(年,月,日) |
関数定義の詳細説明 | マイクロソフト公式サイトへのリンク |
実例 | #date(2023,1,2) |
実例の結果 | 2023/01/02 |
time型 ; 時刻
書式 | #time(時,分,秒) |
関数定義の詳細説明 | マイクロソフト公式サイトへのリンク |
実例 | #time(10,11,40) |
実例の結果 | 10:11:40 |
datetimezone型 ; 日付/時刻/タイムゾーン
書式 | #datetimezone(年,月,日,時,分,秒,+時間,+分) |
関数定義の詳細説明 | マイクロソフト公式サイトへのリンク |
実例 | #datetimezone(2023,1,2,10,11,40,2,20) |
実例の結果 | 2023/01/02 10:11:40 +02:20 |
duration型 ; 期間
duration型は、時間の長さを表しています。実際の使い方としては、ある日時から一定時間経過した日時を出す場合に、ある日時にduration型の値を加算するような使い方をします。
書式 | #duration(日数,時間,分,秒) |
関数定義の詳細説明 | マイクロソフト公式サイトへのリンク |
実例 | #duration(14,10,11,40) |
実例の結果 | 14.10:11:40 |
日付情報を取得する
日付の利用の仕方は、2023年という「年」のみの情報で良かったり、四半期の情報であったり、はたまた週の通過日、等々と多岐に渡ります。
日付情報の取得方法としては、以下の通り二つあります。両方使えるようにしておくと自由度が広がるのでお勧めです。
- Date関数を直接入力して日付情報を取得する方法
- パワークエリエディタの画面から日付情報を取得する方法
日付情報を取得できるデータ型は以下の3つです。
- datetime型 ; 日付/時刻
- date型 ; 日付
- datetimezone型 ; 日付/時刻/タイムゾーン
Date関数を直接入力して日付情報を取得する方法
Date関数を直接入力することで日付情報を取得する方法を説明します。後ほど説明するGUIでも可能ですが、関数を使っている概念を知っておくと今後応用の幅が広がります。
Date関数は複数あり下記のマイクロソフト公式サイトで確認することができます。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

- 対象の列を選択して[列の追加]を選択する
- カスタム列をクリックする。

カスタム列の式に、日付情報が取り出せるDate関数を入力します。[Date.]と入力するとそれに続く関数が候補として出てきます。今回は、Date.DayというDateTime 値の日付を返す関数を入力します。

Date.Dayと入力

Date.Dayの引数として、[日時]列を入力します。使用できる列で[日時]列を選択して「挿入」ボタンを押下すると自動的に入力されます。

以下のようになれば入力完了です。

Date.Day関数で指定した通り、[日時]列の日付け情報のみが表示される

画面操作のみで日付情報を取得する方法
続いては、関数は使わずに画面操作のみで日付情報を取得する方法を説明します。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

[日時]列から日のみの情報を取り出します。
- 対象の列を選択して[列の追加]を選択します。
- [日付]アイコンを選択します。
- [日]を選択します。

[日時]列の日付け情報のみが表示されていることが確認できます。ポイントは関数が自動的に生成されている点です。関数バーに以下のコードが入力されており、Date.Day関数が使われていることが分かるかと思います。
1 |
= Table.AddColumn(変更された型, "日", each <span class="swl-marker mark_orange">Date.Day([日時])</span>, Int64.Type) |
例えば、月を取り出す項目を選択したらDate.Month([日時])と入力され、選択項目に応じて関数が変わってきます。

時間情報を取得する
時間情報の取得方法としては、以下の通り二つあります。両方使えるようにしておくと自由度が広がるのでお勧めです。
- Time関数を直接入力して時間情報を取得する方法
- パワークエリエディタの画面から時間情報を取得する方法
時間情報を取得できるデータ型は以下の3つです。
- datetime型 ; 日付/時刻
- date型 ; 日付
- datetimezone型 ; 日付/時刻/タイムゾーン
Time関数を直接入力して日付情報を取得する方法
Time関数を直接入力することで時間情報を取得する方法を説明します。後ほど説明するGUIでも可能ですが、関数を使っている概念を知っておくと今後応用の幅が広がります。
Time関数は複数あり下記のマイクロソフト公式サイトで確認することができます。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

- 対象の列を選択して[列の追加]を選択する
- カスタム列をクリックする。

カスタム列の式に、時間情報が取り出せるTime関数を入力します。[Ti.]と入力するとそれに続く関数が候補として出てきます。今回は、Date.HourというDateTime 値の時(h)を返す関数を入力します。

Date.Hourと入力

Date.Hourの引数として、[日時]列を入力します。使用できる列で[日時]列を選択して「挿入」ボタンを押下すると自動的に入力されます。

以下のようになれば入力完了です。

Date.Hour関数で指定した通り、[日時]列の時(h)情報のみが表示される

画面操作のみで日付情報を取得する方法
続いては、関数は使わずに画面操作のみで日付情報を取得する方法を説明します。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

[日時]列から時(h)のみの情報を取り出します。
- 対象の列を選択して[列の追加]を選択します。
- [時刻]アイコンを選択します。
- [時]を選択します。

[日時]列の時(h)情報のみが表示されていることが確認できます。ポイントは関数が自動的に生成されている点です。関数バーに以下のコードが入力されており、Time.Hour関数が使われていることが分かるかと思います。
1 |
= Table.AddColumn(変更された型, "時", each<span class="swl-marker mark_orange"> Time.Hour([日時])</span>, Int64.Type) |
例えば、[秒]情報を取り出す項目を選択したらTime.Second([日時])と入力され、選択項目に応じて関数が変わってきます。

二つの日付の減算を求める
二つの日付を減算して、日数と時間を取得します。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

二つの日付けを減算して日数を算出します。選択する順番が減算する順番になっている点が独特となっています。以下の例では、[終了日-開始日]の計算を行うために、以下の手順で二つの列を選択します。
- [終了日列]を選択します。
- Ctrlキーを押下しながら、[開始日列]を選択します。

二つの日付を減算して日数を算出します。
- [列の追加]を選択します。
- [日付]アイコンを選択します。
- [日数の減算]を選択します。

二つの日付を減算して時間を算出します。
- [列の追加]を選択します。
- [日付]アイコンを選択します。
- [日数の減算]を選択します。

減算の結果である、[日数]と[時間]の列が追加されました。

最も早い日付、最も遅い日付を求める
複数の日付から最も早い日付、最も遅い日付を求める方法を説明します。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

複数の列の中で最も早い日付を求めます。以下の方法で複数の列を選択してください
- [第1日付]を選択します。
- Shiftキーを押下しながら、[第4日付]を選択します。
これで、全ての列が選択されました。

最も早い日付を新しい列に追加します。
- [列の追加]を選択します。
- [日付]アイコンを選択します。
- [最も早い]を選択します。

List.Min関数を使い日付の最小値を求めています。
1 |
= Table.AddColumn(変更された型, "最も早い", each <span class="swl-marker mark_orange">List.Min</span>({[第1日付], [第2日付], [第3日付], [第4日付]}), type datetime) |
最も遅い日付を新しい列に追加します。
- [列の追加]を選択します。
- [日付]アイコンを選択します。
- [最も遅い]を選択します。

List.Max関数を使い日付の最大値を求めています。
1 |
= Table.AddColumn(最も早い日時を挿入しました, "最も遅い", each <span class="swl-marker mark_orange">List.Max</span>({[第1日付], [第2日付], [第3日付], [第4日付]}), type datetime) |
最も早い日付及び最も遅い日付の新しい列が追加されました。

最も早い時刻、最も遅い時刻を求める
続いて、複数の日付から最も早い時刻、最も遅い時刻を求める方法を説明します。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

複数の列の中で最も早い時刻を求めます。以下の方法で複数の列を選択してください
- [第1日付]を選択します。
- Shiftキーを押下しながら、[第4日付]を選択します。
これで、全ての列が選択されました。

最も早い時刻を新しい列に追加します。
- [列の追加]を選択します。
- [時刻]アイコンを選択します。
- [最も早い]を選択します。

List.Min関数を使い時刻の最小値を求めています。
1 |
= Table.AddColumn(変更された型, "最も早い", each List.Min({[第1日付], [第2日付], [第3日付], [第4日付]}), type datetime) |
最も遅い時刻を新しい列に追加します。
- [列の追加]を選択します。
- [日付]アイコンを選択します。
- [最も遅い]を選択します。

List.Max関数を使い時刻の最大値を求めています。
1 |
= Table.AddColumn(最も早い日時を挿入しました, "最も遅い", each List.Max({[第1日付], [第2日付], [第3日付], [第4日付]}), type datetime) |
最も早い時刻及び最も遅い時刻の新しい列が追加されました。

現在日付及び時刻の取得
現在日付を使った計算をする場合に現在日付の取得が必要になってきます。
DateTime.LocalNow関数を使い現在日付及び時刻を取得します。
1 |
= DateTime.LocalNow() |
カスタム列で、以下のコードを入力します。引数は不要です。
1 |
= DateTime.LocalNow() |

以下のように現在日付及び時刻が表示されます。

期間型データの加算減算
任意の日付に期間型データの値を加算減算する方法を説明します。具体的には、2024年8月8日に30日を加算した値を算出します。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

任意の日付に任意の日数を加算減算する場合には、日数を数値型ではなく期間型データとする必要があります。サンプルデータをパワークエリエディタで開くと数値型になっていて日付の計算に利用できません。そこで、下図のように左上の[数値アイコン]をクリックして、[期間]を選択する必要があります。

以下の関数が自動生成され、期間型データ(duration型)に変更されます。
1 2 |
= Table.TransformColumnTypes(ソース,{{"日付", type datetime}, {"期間", type duration}}) |

[列の追加]タブの[カスタム列]を選択して期間を加算する式を入力します。
- [新しい列名]を入力します。
- [カスタム列の式]に[日付]+[期間]と入力します。両方列のことです。

[日付]列に[期間]列の日数が加算された結果列が追加されました。

[日付]列と[期間]列の加算は以下の式となります。
1 2 |
=Table.AddColumn(変更された型, "日付に期間を加算", each [日付]+[期間]) |
〇〇後の日付の算出
任意の日付に指定した月数、週数、日数等を加算した日付の算出方法を説明します。
説明で利用するExcelファイルをダウンロードして実際に操作をしてみましょう。
パワークエリエディタを開きます。
- テーブル内のセルを選択して[データ]タブを選択します。
- [テーブルまたは範囲から]を選択します。

- 対象の列を選択して[列の追加]を選択する
- カスタム列をクリックする。

今回は、450日を[日付]列に加算します。AddDaysという関数を使います。
- [新しい列名]を入力します。
- [カスタム列の式]に以下のように入力します。
1 |
=Date.AddDays([日付],450) |

[日付]列に上記で指定したように450日加算された列が追加されました。

日数の加算以外にも関数が用意されています。
単位 | 関数(リンク先はマイクロソフト公式サイト) |
---|---|
〇〇日後 | Date.AddDays |
〇〇週間後 | Date.AddWeeks |
〇〇月後 | Date.AddMonths |
〇〇四半期後 | Date.AddQuarters |
〇〇年後 | Date.Years |
パワークエリ学習コンテンツ
-
第1回PowerQuery講座|パワークエリとは?何ができる?どう起動する?
-
第2回PowerQuery講座|パワークエリのデータソースの取り込み方法をデータ形式毎に解説
-
第3回PowerQuery講座|パワークエリの『列』操作
-
第4回PowerQuery講座|パワークエリの『行』操作
-
第5回PowerQuery講座|文字を整えて(テキストクレンジング)パワークエリで扱いやすいようにする。
-
第6回PowerQuery講座|パワークエリの数字計算(四則演算、四捨五入、切り上げ、切り捨て)
-
第7回PowerQuery講座|パワークエリの統計関数やグループ化機能を使った列の集計方法
-
第8回PowerQuery講座|パワークエリの時間を表すデータ型と時間を使った計算
-
第9回PowerQuery講座|パワークエリの様々な条件判断
-
第10回PowerQuery講座|パワークエリで行と列を入れ替える(列のピボット解除、列のピボット)
-
第11回PowerQuery講座|パワークエリにおけるクエリの管理方法
コメント