PowerQuery 日付データの計算方法 |引き算と足し算(加算)

PowerQuery(パワークエリ)で日付・時間の計算方法を紹介します。日時データの差の計算方法(減算)と日時データに期間を足し合わせる方法(加算)を解説しています。
テストデータを用いて経過日数や年齢などの計算方法も解説していますので参考にしてみてください。

目次

日付・時刻を表すデータ型

日付や時刻を表すデータ型は、よく使われる「日付」や「日付/時刻」を含めて5種類あります。これらの加算、減算方法について後述で解説します。

データ型データ例データ例の関数表記
日付2023/04/01#date(2023,4,1)
日付/時刻2023/04/01 11:12:13#datetime(2023,4,1,11,12,13)
時刻11:12:13#time(11,12,13)
日付/時刻/タイムゾーン2023/01/02 10:11:40 +02:20#datetimezone(2023,1,2,10,11,40,2,20)
期間14.10:11:40#duration(14,10,11,40)
日付・時刻を表すデータ型

「期間」は日時データの計算によく使用するデータ型であり、それぞれの数値は#duration(日数,時間,分,秒)を表しています。日時データの足し算 や 日時データの引き算の結果などで使うデータ形式です。

日時データの差を計算する(減算・引き算)

ボタン操作での計算方法

日時データ同士の差を計算するには、列の追加タブ内にある以下のコマンドを使用します。

  • 日数の減算: 「日付」の「日数の減算」
  • 時間の減算: 「時刻」の「減算」

「時刻」の「減算」を使用した場合は計算結果は期間データとなります。期間データを日数や年数に変換する方法は後述のカスタム列での計算方法で解説しています。

【計算手順】

  1. 減算で引かれる側の日時データ(遅い方の日時)を選択する。
  2. Ctrlキーを押しながら減算で引く側の日時データ(早い方の日時)を選択する。
  3. 列の追加タブ内にある「日付」の「日数の減算」を選択する。または「時刻」の「減算」を選択する。

選択する順番で計算の結果が変わるため、データ選択の順番に気を付けてください。

【使用例】経過日数を算出する

計算手順
STEP
データ準備

下図のデータから日時データを引き算して経過日数を算出します。

STEP
データの選択
  1. 引き算の引かれる側の[終了日]を選択する。
  2. 引き算の引く側の[開始日]を選択する。

この順番でデータを選択することにより [終了日] – [開始日] の計算ができるようになります。

STEP
日付の減算

列の追加タブにある「日付」の「日数の減算」を選択する。

STEP
経過日数の計算完了

以上で経過日数の計算完了です。減算の列に経過日数が算出されています。

カスタム列での計算方法

カスタム列で日時データの差を計算するにはマイナス記号(-)を使った計算式を作成し、結果の期間データを変換してほしい単位に求めます。

【計算手順】

  1. 列の追加タブの「カスタム列」を選択する。
  2. 日時データとマイナス記号(-)で計算式を作成して、引き算をする。
  3. 計算結果の期間データを変換タブの「期間」で変換したい単位に変換する。

経過した日時を算出したい場合は合計年数や合計日数などの合計〇〇を選択してください。

【使用例】経過時間を計算する

計算手順
STEP
データ準備

下図のデータから日時データを引き算して経過時間を算出します。

STEP
カスタム列での計算
  1. 列の追加タブの「カスタム列」を選択する。
  2. カスタム列の式に下記の計算式を入力してOKをクリックする。※列名は任意で設定。
[終了日]-[開始日]
STEP
期間データの変換
  1. 計算結果の期間データを選択する。
  2. 変換タブの「期間」で「合計時間数」を選択して、期間データを経過時間に変換する。
STEP
経過時間の計算完了

以上で経過時間の計算完了です。減算の列に経過時間が算出されています。

Sponsored link

日時データに一定期間を足す・引く(加算・足し算)

関数での計算方法

日時データにある一定期間を足し引きして〇〇日後や〇〇日前などの計算をする場合は、下記の関数をカスタム列で利用します。〇〇後ならば日時データにプラスの値を追加し、〇〇前ならばマイナスの値を追加します。

加算期間関数
Date.AddDays ( 日時データ, 追加日数)
Date.AddWeeks ( 日時データ, 追加週数)
Date.AddMonths ( 日時データ, 追加月数)
四半期Date.AddQuarters ( 日時データ, 追加四半期数)
Date.AddYears ( 日時データ, 追加年数)
日時加算の関数

この計算方法は日~年の単位の計算に適しており、時間を加算したい場合は後述のカスタム列と期間データを用いた方法を参考にしてください。

【計算手順】

  1. 列の追加タブの「カスタム列」を選択する。
  2. 期間を追加する関数(Date.Add〇〇)を使った計算式を作成する。

関数の日時データ部分には「日付」「日付/時刻」「日付/時刻/タイムゾーン」のデータ型が使用できます。上図のように[列名]で列を指定することも可能です。

【使用例】3か月後の日時を計算する

計算手順
STEP
データ準備

下図の開始日データから3か月後の日時データを作成する。

STEP
カスタム列での計算
  1. 列の追加タブの「カスタム列」を選択する。
  2. カスタム列の式に下記の計算式を入力してOKをクリックする。3か月後のあためDate.AddMonths関数を用いて追加月数に3を設定します。※列名は任意で設定。
Date.AddMonths([開始日],3)
STEP
3か月後の日時データの計算完了

以上で3か月後の日時データの算出は完了です。

カスタム列+期間データでの計算方法

カスタム列と期間データを用いて計算をすることも可能です。足し引きしたい期間を「期間」データのフォーマットを用いて作成し、それをカスタム列で加算(減算)することで計算します。

期間データのフォーマット:#duration(日数,時間,分,秒)

この計算方法は日、時間、分、秒の単位の計算に適しており、月の加算したい場合は前述の関数を用いた方法を参考にしてください。

【計算手順】

  1. 列の追加タブの「カスタム列」を選択する。
  2. 期間データ(#duration(日数,時間,分,秒))を使用した計算式を作成する。

〇〇時間前や〇〇日前などを計算したい場合は引き算すればOKです。

【使用例】10時間前の日時を計算する

計算手順
STEP
データ準備

下図の開始日データから10時間前の日時データを作成する。

STEP
カスタム列での計算
  1. 列の追加タブの「カスタム列」を選択する。
  2. カスタム列の式に下記の計算式を入力してOKをクリックする。10時間前ため #duration(0,10,0,0) を開始日から引き算します。※列名は任意で設定。
[開始日] - #duration(0,10,0,0)
STEP
10時間前の日時データの計算完了

以上で10時間前の日時データの算出は完了です。

Sponsored link

使用例: 経過月数の計算

STEP
データ準備

下図のデータから日時データを引き算して経過時間を算出します。

STEP
カスタム列での計算
  1. 列の追加タブの「カスタム列」を選択する。
  2. カスタム列の式に下記の計算式を入力してOKをクリックする。Duration.TotalDaysは期間データを日数に変換する関数であり、それにより算出された日数を1ヶ月の平均日数30.416で割ります。
Duration.TotalDays([終了日]-[開始日])/30.416
STEP
小数点を切り捨てる
  1. 計算結果のデータを選択する。
  2. 変換タブの「丸め」で「切り捨て」を選択する。
STEP
経過月数の計算完了

以上で経過月数の計算完了です。

Sponsored link

使用例:年齢の計算

STEP
データ準備

下図の生年月日データから本日時点での年齢を算出します。

STEP
カスタム列での計算
  1. 列の追加タブの「カスタム列」を選択する。
  2. カスタム列の式に下記の計算式を入力してOKをクリックする。今日の日付を表す関数から生年月日を引いて経過期間を計算する。
    • 今日の日付を表す関数:Date.From(DateTime.LocalNow())
Date.From(DateTime.LocalNow())-
STEP
期間データの変換
  1. 計算結果の期間データを選択する。
  2. 変換タブの「期間」で「合計年数」を選択して、期間データを経過年数に変換する。
STEP
小数点を切り捨てる
  1. 計算結果のデータを選択する。
  2. 変換タブの「丸め」で「切り捨て」を選択する。
STEP
年齢の計算完了

以上で今日時点の年齢の計算完了です。(下図は2023/8/31時点の計算結果です)

Sponsored link

使用例:工程毎の作業時間の計算

STEP
データ準備

下図の作業データから工程A,B,Cそれぞれの合計作業時間(分)を算出します。

STEP
日時データの差を計算
  1. 日時が遅い[作業終了]を選択する。
  2. 日時が早い[作業開始]をCtrlキーを押しながら選択する。
  3. 列の追加タブの「時刻」の「減算」を選択して、2つの日時データの差を計算する。
STEP
期間データの変換
  1. 計算結果の期間データを選択する。
  2. 変換タブの「期間」で「合計分数」を選択して、期間データを経過分数に変換する。
STEP
グループ化でデータを集計する
  1. 任意の列を選択する。
  2. 変換タブの「グループ化」を選択する。
  3. グループ化する列に[工程]、操作に[合計]、集計される列に[減算]を選択して工程ごとで経過分数を集計する。

グループ化については下記記事で紹介してますので参考にしてみてください。

STEP
小数点を切り捨てる
  1. 集計データを選択する。
  2. 変換タブの「丸め」で「切り捨て」を選択する。
STEP
作業時間の集計完了

以上で工程ごとの作業時間(分)の集計は完了です。

Sponsored link

Power Queryのおすすめ書籍

パワークエリの学習本についてはコチラの記事でまとめてますので参考にしてみてください。

初心者向けおすすめ学習本

初中級者向けおすすめ学習本

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

仕事、お金、旅行のことなどメモしてます。
仕事ではTableauを勉強中!
初心者にもわかりやすいように学んだことを発信していきます。

コメント

コメントする

コメントは日本語で入力してください。(スパム対策)

CAPTCHA

目次