Power Queryでデータを抽出するとき、フィルター条件をセルで指定できると便利です。
Excelのセルであれば、PowerQueryのフィルター条件(指定値)をPower Queryエディターを起動せずに変更が可能です。また、数式を用いた動的な日付指定なども可能で自動化の幅も広がります。
今回は、日付を含むデータを事例に、セルの値をもとにデータ抽出する方法です。
内容:
確認したExcelは「Microsoft® Excel® for Microsoft 365 MSO」(バージョン2409)です。
アウトプットイメージ
下図上側のデータA列(日付)について、下図下側のセルの日付以降を抽出します。
セルの値を変更すると、PowerQueryの結果を更新できるようにします。
実施手順
1.基本的な手順で「元になるクエリ」を作成(抽出条件は固定)
セルの値での変更を設定する前に、基本的な手順で「元になるクエリ」を作成し、データ抽出します。
例えば、この事例では以下のようにデータ抽出するクエリを一旦作成しています。
(1)PowerQueryに読み込み
下図は、PowerQueryエディターで元データを読み込み、データ型の変更までが完了している状態。
(2)フィルターの処理
指定した日付(適当な固定値)でデータを抽出する。
下図は、「次の値より後」で日付を指定している。
上記の結果、次のように固定の指定値で、データ抽出が完了する。
2.セルの値を変数とするクエリに修正する
(1)設定用のテーブルを作成する
セルの値を変数とするため、指定値を含む次のようなテーブルを作成する。
(セルE1:E2でテーブルを作成)。
この事例で、テーブルの設定情報は次のとおり。
- テーブル名(下図①): 設定情報
- テーブルのタイトル部分(下図②): 指定日付
- テーブルの値:条件に使用する日付データ
なお、①テーブル名、②テーブルのタイトルの値は、後の数式で使用するのでメモしておく。
(2)PowerQueryエディターで数式を修正する
作成したテーブルの情報をもとに、「手順1(2)フィルタのー処理」の数式を変更する。
事例では、
元の数式:
= Table.SelectRows(変更された型, each [日付] > #datetime(2020, 12, 24, 0, 0, 0))
となっている。
この赤字部分をセルの値で変更したいので、
= Table.SelectRows(変更された型, each [日付] > Excel.CurrentWorkbook(){[Name="設定情報"]}[Content][指定日付]{0})
とする。
ここで、”設定情報”と”指定日付”は、2(1)で作成したテーブルの情報に従う。
なお、日付データの型により、エラーが発生することがあるので、適時、次のようにデータ関数を組み合わせる。
日付のデータ型の場合:
= Table.SelectRows(変更された型, each [日付] > Date.From(Excel.CurrentWorkbook(){[Name="設定情報"]}[Content][指定日付]{0}))
日付/時刻のデータ型の場合:
= Table.SelectRows(変更された型, each [日付] > DateTime.From(Excel.CurrentWorkbook(){[Name="設定情報"]}[Content][指定日付]{0}))
設定のアウトプットイメージは次のとおり。
これで、セルの値をもとに可変のPowerQueryの設定が完了です。
セルの値をExcelの数式で動的に変更するなどして、PowerQueryの結果を更新することも可能となります。
補足説明:データ範囲をテーブルに変換について
データ範囲をテーブルに変換する方法について、次の記事の「データ範囲をテーブルに変換」でも説明していますので、不明な場合は以下も参考にしてください。
以上、Power Queryで、セルの値をもとにデータ抽出する方法でした。