PowerQuery 1行上のセルを参照する(前月比・差分計算)

パワークエリで1行上(1行前)のセルに入力されている値を参照して計算する方法を紹介します。前年比・前月比・前日比や前データからの差分や差額の計算をする際にぜひ参考にしてください。

目次

1行上のセルを参照する方法

1行上(1行前)のセルに入力されている値を参照して計算する方法は以下の3ステップです。

手順
  1. インデックス列を追加する。
  2. インデックス列を参照することで参照したい列を1行ズラした列を作成する。
  3. 2つのデータ列の値を用いて計算する。

【画像付き解説】1行上のセルの値を参照する

下記のデータを用いて「売上」列の1行前のデータを参照して前月比を作成してみます。

STEP
データ準備

今回は下図の「年月」と「売上」データから1行前のデータを参照して前月比を作成します。

STEP
インデックス列の追加

「列の追加」タブ内の「インデックス列」を選択して列を追加する。ここでインデックス番号は0から始める。

STEP
新しいステップを追加する

クエリの設定内の「適用したステップ」にあるステップを右クリックして「後にステップを挿入」を選択する。

STEP
前行のデータを参照する新しい列を作成する
  1. 「列の追加」タブ内にある「カスタム列」をクリックして新しい列を追加する。
  2. 列名と行番号を使って参照セルを指定する下記式を入力する。

 「 対象テーブル名 [列名] {行番号}」

ここでの入力式は「= 追加されたインデックス [売上] { [インデックス] -1 }」です。
※行番号は表の1行目が「0」、2行目が「1」と表と1つズレているので注意してください。

設定値
  • 対象テーブル名:追加されたインデックス (※適用したステップにある名称を入力)
  • 列名:売上
  • 行番号:[インデックス] – 1  (インデックス列の番号からマイナス1した行番号 → 1行上の行番号)

 

追加したカスタム列を確認すると下図のように売上データが一つズレたデータ列が作成できています。
※Errorを回避する方法は後述していますので気になる方は確認してみてください。

STEP
1行上の値を用いて前月比を計算する

「売上」列と「前月売上」列のデータを用いて「[売上]/[前月売上]」の計算式を入力します。

STEP
計算完了

最後に前月比のデータ型を「%」に変更して計算完了です。

行をズラしたカスタム列を作らなくてもOK

前述しているようにわざわざカスタム列「前年売上」を作成しなくても下記のようにひとつの計算式にまとめて「前月比」を算出してもOKです。

ステップ数も少なくすることができるので慣れてきたらこちらの方法をオススメします。

Error処理(例外処理)

インデックスを用いてひとつ上の行を参照すると一番上の行はインデックス番号が「-1」となり下図のように「Error」が発生します。Errorをなくしたい場合は例外処理を計算式に追加しましょう。

例外処理

try 通常の処理 otherwise エラー時の処理

例外処理の使用例

前述の「前年売上」のカスタム列を作成する計算式に例外処理を追加してみます。エラーが発生した場合は「null」となるように記載しています。otherwiseの後ろに計算式や文字列を記入することで任意の処理を設定できます。

= try 追加されたインデックス[売上]{[インデックス]-1} otherwise null

結果、エラーはなくなり1行目は「null」となっています。

Power Queryのおすすめ書籍

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

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

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

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

この記事を書いた人

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

コメント

コメントする

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

CAPTCHA

目次