セルの値でクエリを変化させる(日付)
Power Queryには「パラメータクエリ」という仕組みがあります。これは、クエリを更新するとき、あらかじめ定めたセルに入力されている値を、そのクエリ内で使用して、クエリの結果をセルによって変化させようという仕組みです。たとえば、セルに"田中"と入力しておけば"田中"で絞り込まれた結果になり、セルが"鈴木"だったら"鈴木"で絞り込まれた結果になる、みたいな感じです。もちろん、そのように変化させられる値は単純な文字列だけでなく、Power Queryで取得するファイル名やフォルダのパスだったり、データの変換や加工などで使う数値など、いろいろな用途に使えます。今回は、そうしたパラメータクエリの中で、セルに入力した日付データを使う方法を解説します。なお、パラメータクエリとはどんな仕組みかという基本的な話は、下記のコンテンツで解説していますので、ぜひご覧ください。
パラメータを別のクエリとして作る
Excelの場合、パラメータクエリを実装するには、大きく二つの考え方があります。詳しくは「セルの値でクエリを変化させる(パラメータクエリの基本)」をご覧ください。さて、まずは「パラメータを別クエリとして作る」方法から解説します。
今回は、上図のようなCSVファイルを扱います。まずは、このCSVファイルをPower Queryで取得します。
[日付]列を"2022/9/2"で絞り込んで、Excelのワークシートに読み込みます。
E列に、変化させる値(パラメータ)の入力欄を作りました。このパラメータを別のクエリとしてPower Queryに取得するので、テーブル形式に変換します。
テーブルの名前を「Target」に変更しました。単なる日付ですから「Date」という名前にしようかと思いましたが、これから"Date"という単語はたくさん登場するでしょうから、区別する目的で「Target」としました。Targetに深い意味はありません。さて、このテーブル「Target」をPower Queryエディタに取得します。
セルに入力されている日付(シリアル値)をPower Queryエディタで取得すると、データ型は最初「日付/時刻」に設定されます。今回時刻は使用しませんので、Power Queryエディタの「データ型」で"日付"に変更します。変更すると[列タイプの変更]画面が表示されますので、[現在のものを置換]ボタンをクリックしてください。ちなみに、この「データ型は最初「日付/時刻」に設定される」というのは、けっこう重要です。この件、後でまた登場しますので、覚えておいてください。
テーブルのデータ部分(ここでは"2022/9/2")を右クリックして、表示されるメニューから[ドリルダウン]をクリックします。
クエリ「Target」が値「2022/9/2」に変換されました。このクエリ名「Target」を、CSVを取得したクエリ「Sample」内で使用します。クエリ「Sample」を選択して「詳細エディタ」を開きます。
「フィルターされた行」の"#date(2022, 9, 2)"部分を"Target"に修正します。[完了]ボタンをクリックすれば完了です。Power Queryエディタ[ホーム]タブの左端にある[閉じて読み込む]のメニューから[閉じて次に読み込む...]を実行します。いま取得したクエリ「Target」は、すでにワークシート上に存在しているのですから、あらためて別のテーブルとしてワークシートに読み込む必要はありません。なので[接続の作成のみ]を選択して[OK]ボタンをクリックします。このへんの手順も、詳しくは「セルの値でクエリを変化させる(パラメータクエリの基本)」をご覧ください。
セルE2の日付を変更してクエリを更新すると、セルE2の日付が、CSVを読み込むクエリ内で使用されて、結果が変化します。
パラメータをM言語で取得する
変化させる日付(パラメータ)を、別のクエリとして取得するのではなく、直接メインのクエリ(ここではSample)内で取得する方法もあります。やってみましょう。なお、CSVを取得して日付のフィルタリングをするクエリ「Sample」は、すでに作成済みだとします。
変化させる日付をセルE1に入力するとします。このセルE1に「Target」という名前を定義しました。Power Queryエディタを起動して、クエリ「Sample」の詳細エディタを開きます。
ここからの手順なども、詳しくは「セルの値でクエリを変化させる(パラメータクエリの基本)」で解説をしていますので、よく分からない方は、まずそちらをご覧ください。「フィルターされた行」の前に1行を挿入して、次のテキストを入力します。
取得した日付 = Excel.CurrentWorkbook(){[Name="Target"]}[Content]{0}[Column1],
作業終了です。[完了]ボタンをクリックします。
失敗です。記述したコードに間違いはありません。「セルの値でクエリを変化させる(パラメータクエリの基本)」でやった"田中"などは成功したのに、なぜか今回は上手くいきませんでした。いったい、なぜでしょう。
失敗した原因は、先に言った
セルに入力されている日付(シリアル値)をPower Queryエディタで取得すると、 データ型は最初「日付/時刻」に設定されます。
です。確認してみましょう。Power Queryエディタ[ホーム]タブ右上にある[その他のソース]からの[空のクエリ]をクリックします。実行すると、新しく空のクエリが挿入されます。
詳細エディターを開いて、先の
Excel.CurrentWorkbook(){[Name="Target"]}[Content]{0}[Column1]
を、入力してみます。最後のカンマ(,)は不要です。入力したら[完了]ボタンをクリックしてください。
取得された結果のデータ型は「日付/時刻」になっています。Power Queryの"データ型"とは、Excelの"表示形式"とは異なる仕組みです。"表示形式"は、セルに入力されているデータの見せ方を決める機能ですが、Power Queryは表計算ではなくデータベースです。データの"見せ方"などという概念はありません。Power Queryの"データ型"は、そこに格納できるデータの型(種類)を定めています。「日付/時刻」のデータ型には「日付/時刻」のデータしか入りません。先に、パラメータを別クエリとして取得したときに"データ型"を変更したのは、"見せ方"を変えたのではなく、入っているデータそのものを変更したようなものです。今回も同じことをやらなくてはなりません。
では、データ型を変更しましょう…って、できませんｗ
[ホーム]タブの[データ型：すべて▼]はクリックできない状態です。これ、テーブルではなく値なので、データ型を変更できないんです。では、どーするか。[ホーム]タブではなく、日付時刻ツールの[変換]タブを使います。ここに[日付]というメニューがあります。開くと[日付のみ]というコマンドがありますので、これをクリックします。
実行すると、[日付/時刻]型が[日付]型に変更されます。
M言語的な記述方法は、詳細エディタで確認します。
これを、クエリ「Sample」に組み込みます。
Excelのワークシートに戻って確認してみます。
成功です。パラメータクエリで、日付データ(Excelでのシリアル値)をPower Queryで扱うときは、データ型に注意してください。
月末日を指定したい
先日のセミナーに参加された土屋さんから、この手の「セル内の日付を変えたら、それでクエリの結果を変更したいんです」という質問を受けました。ここまで解説した方法で実現可能です。ご質問のケースは、指定した日付「と等しい」で絞り込むのではなく「より小さい」とか「より大きい」のように考えればいいです。せっかくですから、質問されたケースの解説もしましょうか。
質問は、こういうことでした。まず「データ内に、たくさんの、ランダムな日付がある」と。そうしたデータが日々増えていくそうです。そんなデータ内から「先月末までの日付だけを抽出したい」とのことでした。こんな感じでしょうか。
サンプルとして、こんなCSVファイル(C:\Work\Data.csv)を作ってみました。
Power Queryエディタで取得して[日付]列を昇順で並べ替えました。
とりあえず便宜的に「2022/7/31」までの日付で絞り込んでみます。
う～む、[日付フィルター]に、「次の値より前」はありますけど、これだと「今月の1日」を指定しなければなりません。まぁ、どちらでも同じなのですが、この後の処理を考えると「先月末の日」を指定できた方が楽です。その指定はできないのでしょうか。
ありました。ですよね～こういう指定ができないようなポンコツじゃないですよね、Power Queryは。とりあえず便宜的な日付で絞り込めましたので、この状態でワークシートに読み込んでみます。
上図では、パラメータとして渡すセルも作りました。今回のポイントはここです。今日の日付によって、自動的に"先月の末日"を取得したいです。この手の処理って、M言語の関数でもできそうな気がします。ちなみに、末日を取得する関数は見当たりませんでした。あるのかな？それほど本気で探していません。だって、これって、Excelだったら簡単ですから。私のセミナーでも解説していますが、何でもPower Queryだけでやろうとしないでください。私たちは、Power Queryのスペシャリストを目指しているのではありません。Excelという道具を使って、業務を遂行しているんです。だったら、Excelで簡単にできることはExcelでやるという気持ちを忘れないでくださいね。ちなみに、本原稿を執筆しているのは2022年9月某日です。したがって、セルE2には「2022/8/31」が入ります。ここをテーブル形式に変換しました。テーブルの名前は「先月末日」です。このテーブルをPower Queryエディタで取得します。
このへんの手順は、上記をご覧ください。さて、Excelのワークシートに戻ります。
実際には本日の日付が10月にならないとセルE2は変化しないのですが、そこは裏でゴニョゴニョしてｗ 10月になったとします。すると、"先月"ですから9月の月末日になります。クエリを更新すると、次のようになります。
