Power Query を使って絞り込んだデータを取得する － 日付編

SharePoint リストなどからデータを取得し、Excel で加工、集計をする場合、おおよそ全件データを取得し Excel のテーブルにデータを展開してから、ピボットテーブルを使ったり、ワークシート関数を使うことが多いと思う。現在 Excel は百万行を超えるレコード（行）を扱うことができるため、実務上はほぼ問題ないが、場合によっては、絞り込んだデータのみを扱いたいときがある。

過去のデータは一切関係なく、たとえばセミナー申込みの管理などで今日（または指定日）以降に実施される予定コースの登録者アイテムのみが欲しい、といったケースだ。後工程でデータの加工、集計をするのだが、そこで絞り込みを行うのではなく、必要なデータ「だけ」がテーブルに存在していたほうが都合がよい場合だ。

SharePoint リスト接続は全件データをテーブルもしくはピボットキャッシュに取り込む。Office データ接続は上記に加えてデータ モデルに全件データを読み込んでから、Excel 側での処理が行われる。

OLE DB クエリ接続もしくはモデル OLE DB クエリ接続の Power Query の場合も、基本的には全件データを Power Query に取り込んでいる。しかし、Power Query の場合は、Power Query エディターを使って、データの絞り込みや、並べ替え、カスタム列の追加を行ってから Excel に渡すことができる。

(注) SharePoint リスト接続、Office データ接続、OLE DB クエリ接続、モデル OLE DB クエリ接続については、こちらの投稿を参考にしていただきたい。

Power Query のクエリ エディターの画面で絞り込みを行っているところ

絞り込んだデータをどうしても欲しい場合は、Office データ接続によるデータ取得ではなく、Power Query を使わざるを得ない。2015年1月の段階では、Power Query のモデル OLE DB クエリ接続は Excel Online でのデータ更新ができない制限があるが、この制限の実務上の問題がなければ、Power Query を使うことになる。

日付による絞り込み

絞り込みの条件はさまざまあるが、今回は上述のように「日付」に注目して、その絞り込み方法を紹介したい。
とはいえ、Excel ユーザーのとっては有難いことに、Excel のオートフィルターを使った絞り込みと同等の操作を行うことで実現が可能だ。

Power Query エディターの「日付/時刻フィルター」

Excel オートフィルターの「日付フィルター」

Power Query の場合、このオプションに加えて関数を使って指定することが可能なのが特徴だろう。特に「今日」より～ という指定が可能なことだ。

Excelのオートフィルターでも今日より後、といった「今日」という指定は可能だ。

しかし、この「今日」のボタンを押すと、「2015/1/25」といった今日の文字の値が入力される。Excel のオートフィルターという使い方では問題ないが、データ接続のデータ更新により SharePoint リストなどのデータ ソースからデータを抽出する場合は「今日」は「その日」でなければ困る場合が多い。
Power Query エディターの日付/時刻フィルターの「今日」や「明日」の扱いは、Excel のオートフィルターとは異なる。
「今日」を指定しても Excel のように日時を指定するのではなく、Data.IsInCurrentDay([実施日]) という関数を使って配列（複数件データ）を取得して、それをテーブルに展開するのだ。

Power Query エディターの日付/時刻フィルター オプションの「今日」

「今日」を指定したときの式

この指定であれば、データ更新をすると「その日」のデータを持ってくることができる。

関数を使った指定

上図のように、Power Query エディターで絞り込みなどをすると、その操作はすべて「式」として記録さている。

たとえば、「今日より以降（後）のデータ」を指定するときは、オプションのダイアログから「次の値より後...」で「今日」を入力することができない。

Power Query 行のフィルター選択

 ここで日を指定すると数式は以下のようになる。

今日よりも後を指定したいのであれば、赤い線の部分が「今日」になればよい。
ワークシート関数の Today() や Now() に相当するものが入ればよさそうなことは想像に難くない。
それが、DateTime.LocalNow() 関数だ。

実際に数式を直接書き換えてみた結果が以下だ。

ダイアログからは DateTime.LocalNow() 関数はバリデーションチェックによって入力ができないが、数式バー（のようなもの）では直接入力、修正が可能なのだ。

シリアル値ではない日付形式

Excel における日付や時間(時刻）の扱いは「シリアル値」を使っている。Excel ではないプラットフォームではシリアル値が使われていないため、日付・時間の扱いには注意が必要になることが多い。

Power Query では「DateTime」型が基本である。 DateTime 型は Date 型と Time 型から成り立っている。たとえば既出の DateTime.LocalNow() は DateTime 型を返す。

DateTime.LocalNow()  ->  2015/01/07 11:47:45

ここから日付だけを抜き出したい場合は、Date プロパティを参照する。

DateTime.Date(DateTime.LocalNow())  -> 2015/01/07

時間だけを抜き出したい場合は、Time プロパティを参照する。

DateTime.Time(DateTime.LocalNow())  -> 11:47:45

年や月や日を抜き出す場合、ちょっとしたテクニックが必要になる。
DateTime 型に含まれる Date 型からプロパティ参照する。そして、Year や Monty、Day プロパティを参照すると、その戻り値は「数値」になる。

Date.Year([申込日])  -> 2015
Date.Month([申込日])  -> 1
Date.Day([申込])  -> 7

ゼロパディングしたい場合、1月は 01、7日は 07 の場合はテキストに置き換える必要がある。戻り値は「数値」なので、以下のようにする。

Number.ToText(Date.Day([申込]), "00")  -> 07

もちろん、12 や24 の場合も問題ない。

ならば、DateTime から ToText を使えばよさそうだと思うだろう。
しかし、en-us, ja-jp などのカルチャが関係しそうな記述がヘルプにあるのだが、 フォーマットオプションについては期待する動きを見つけられていないので注意されたい。（現状、私は使用していない）

DateTime.ToText([日付], "yyyy")  -> 2015 (文字列）
DateTime.ToText([日付], "yy")  -> 15 (文字列）

DateTime.ToText([日付], "d")  -> 2015/01/07 (文字列）


最後にシリアル値であれば 1 をプラスすることで1日後となるが、DateTime の場合は、AddDays メソッドを使う。

Date.AddDays([申込日], 2)  ->  2015/1/9 (申込日が 2015/1/7の場合）
Date.AddDays([申込日], -30)  ->  2014/12/8 (申込日が 2015/1/7の場合）

Power Query で利用できる関数は以下のページにある。解説が親切ではないので、いろいろと試して確認するのがいいだろう。

Power Query fomula categories
https://weu-odcsup.office.com/en-SG/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819