結構使えるかもしれないPowerQuery関数① 常に最新年のみにフィルターする

常に最新年のデータのみに表示を絞り込みたいレポートで
毎年手動でフィルターを最新年に指定しなおすやり方では
今年2017年のところ、来年は2018年に選択し直さないといけませんね。

しかし
いちいち手動でフィルターで一つの値を指定せずとも、PowerQuery関数を使えば
常に最新年だけを自動で表示するフィルターが作成できます。
また、それを利用して常に最新の「前年比」も自動で出すことができます。

データはゲートウェイで自動更新なのに年度フィルターは毎年手動で変更…
というのは、忘れる可能性もありますし、なんかスマートではないですね。
当然すべて自動化したいところです。

通常は下図のようなフィルターをしますが、これを
関数 DateTime.LocalNow(システムの現在の日時を返します)を使用して
無理やり書き換えてやります

f:id:frogwell_powerBI_blog:20170824104336p:plain
※元の式
= Table.SelectRows(変更された型, each ([年] = “2017”))
単純に2017年を指定してます

これを・・・

①”2017″で指定しているところを、現在のシステム日時に置き換えます

= Table.SelectRows(変更された型, each [年] = DateTime.LocalNow())
 (※「変更された型」の部分は直前のステップ名を入れてください)

★DateTime.LocalNow():システムの現在の日時を返します

②得られたシステム日時から、年のみを抜き出します(Date.Year)
= Table.SelectRows(変更された型, each [年] = Date.Year(DateTime.LocalNow()))

   ★Date.Year([日時データ]):指定した日付の年の値を返します

 

③取り出された年は、データ型が値扱いになっています。
年をテキスト型で持っている場合は、計算結果もテキストにする必要があります
= Table.SelectRows(変更された型, each [年] = Text.From(Date.Year(DateTime.LocalNow())))

★Text.From ([値型データ]):指定した値を文字列に変換します

出来ました。

f:id:frogwell_powerBI_blog:20170824110906p:plain

これなら常に現時点のシステム日付から最新年を取り出してくれるので、
手動でフィルターを変更する必要がありません。
この記事の作成時は2017年なので、上の図では2017年にフィルターされていますが
来年になったら2018年のフィルターに勝手に変わってくれるわけです。

また、これの発展形で「●●年前」とかの指定もできます。
Date.Yearの値から1を引くと去年、
2を引くと一昨年。。。というようにいくらでも指定ができます

こんな感じです

f:id:frogwell_powerBI_blog:20170824111511p:plain

Date.Year値の呼び出し直後に “-1” を追加したので、2016年にフィルターされました。

これを利用すれば常に最新の前年比を見るカラムも作成できます

(参照・複製などで新しく2017年のみの商品別売上をつくって)

f:id:frogwell_powerBI_blog:20170824113206p:plain
(同じように2016年のみの商品別売上もつくって)

f:id:frogwell_powerBI_blog:20170824113125p:plain

(新規クエリとして商品番号でマージ )

f:id:frogwell_powerBI_blog:20170824113952p:plain

(前年比のカスタム列を作ったら)

f:id:frogwell_powerBI_blog:20170824114151p:plain

出来上がり。
常に最新年と昨年の売上対比が自動算出できます。便利。

f:id:frogwell_powerBI_blog:20170824114456p:plain

PowerQueryの式について 公式の詳細はこちら