こんな人むけの記事です
ログデータなどを日付範囲でフィルタリングすることは多いと思います。Power Queryでは先週、今秋などの範囲で絞れる便利な機能があったりはするのですが、○日前以降本日までのような指定を動的にするのがちょっとGUIだけでは難しかったりします。
そこで、日付をあらわすQueryを新たに作って、その日付の値をフィルタの比較できる値として使ってしまおうという試みです。
たとえば日付が並んだテーブル
テスト用のテーブルを用意しました。
適当な日付以降のフィルタを設定してやります。
自動で式が記述されました。2月4日よりも大きい日付なので2月5日以降にフィルタリングされましたね。
クエリでフィルタ用の日付を用意する
ここからが本題です。クエリの中で日付をつくってやります。例えば、今日から3日前の日付を作ってみます。
クエリの何もないところを右クリックして[新しいクエリ]>[空のクエリ]を開きます。
作製された新しいクエリの上で右クリックし[詳細エディタ]で開きます。
クエリの中に以下の文字列を貼り付けて[完了]をクリックしてみましょう。
let
Today = DateTime.LocalNow(),
ThreeDaysAgo = Date.AddDays(Date.From(Today), -3)
in
ThreeDaysAgo
今日から３日前の日付が表示されました。「クエリ1」のままだとわかりにくいので、「開始日付」に変更しておきます。
ログテーブルのクエリに戻って、「フィルタされた日付」の比較されている日付の部分を、先ほど作成したクエリの名前に変えてやります。
「開始日付」のクエリには、先ほど見たように「2025/02/13」の値が日付型で入っていましたので、「ログ日付」のフィルタリングするための比較対象として、クエリの名前である「開始日付」がそのまま利用できました！
比較する日付を別クエリにしておくと何がよいのか？
このくらいなら、ログクエリの中でできるといえばそうなのですが、別のクエリにしておくと見通しがよくなります。
たとえば、以下のクエリは３日前の日付を出すものでしたが、応用が利くようにN日というように変更してみました。
let
// Nを指定するパラメーター
N = 7, // ここでNを指定します。例えば7日前の場合は7を指定します。
// 現在の日付を取得
Today = DateTime.LocalNow(),
// N日前の日付を計算
NDaysAgo = Date.AddDays(Date.From(Today), -N)
in
NDaysAgo
7日前にすると2月9日の値が取得できました。ちなみにこの日は日曜日です。
もし取得した値が土曜日なら2日追加、日曜日だったら1日追加して月曜日の値を取りたければこのようにしてみてはどうでしょう？
クエリはこんな感じになります。
let
// Nを指定するパラメーター
N = 3, // ここでNを指定します。例えば7日前の場合は7を指定します。
// 現在の日付を取得
Today = DateTime.LocalNow(),
// N日前の日付を計算
NDaysAgo = Date.AddDays(Date.From(Today), -N),
// 曜日を取得
DayOfWeek = Date.DayOfWeek(NDaysAgo),
// 土曜日の場合は2日追加、日曜日の場合は1日追加
AdjustedDate = if DayOfWeek = 6 then Date.AddDays(NDaysAgo, 2) else if DayOfWeek = 0 then Date.AddDays(NDaysAgo, 1) else NDaysAgo
in
AdjustedDate
2月13日の7日前は2月9日ですが、プラス1日して2月10日の日付が取得できました！
開始日付を基準に、終了日付もクエリで指定しよう
クエリを外に出しておくと、ちょっと複雑な日付指定が行えます。
開始日付＋３日間の指定を、「開始日付」を参照コピーして追加してみます。
取得できた開始日付と終了日付の両方をつかって、日付範囲を指定してログテーブルをフィルタリングしてみます。and条件で終了日付を加えるだけです。
= Table.SelectRows(変更された型, each [ログ日付] > 開始日付 and [ログ日付] < 終了日付)
N日について、パラメータの値をつかう
Power BI デスクトップをつかって、手元のPCで操作している場合には、Power Queryの画面を開いて編集すればいいのですが、たとえばN日の値をPower BI サービスに発行したあとで変更したいような場合に、再度Power BI デスクトップでMクエリを編集して発行しなおすのは大変です。
パラメータという機能を使えばPower BI サービスに発行した後でも値を変更することができるようになります。
まず、[ホーム]>[パラメーターの管理]を開きます。
パラメータの管理画面が開きますので、「新規作成」をクリックして、適当な名前と、種類を10進数に変えてから「現在の値」として7を入力しておきました。
「OK」をクリックすると、「Nの値」というように先ほど名前をつけたパラメータ名と、その中に設定されている値7が表示されました。
開始日付、終了日付 のようなクエリと同じように、このパラメータの値は式の中で変数のように使用することができます。
「開始日付」をふたたび詳細詳細エディタで開き、Nへの代入する値として指定してやりました。たったこれだけで可変の値にすることができます。
適当に表示をさせて、Power BI サービスのマイワークスペースに発行してみます。
Power BI サービスのマイワークスペースに表示されました。
セマンティックモデルの三点リーダーから「設定」を開きます。「パラメーター」の項目を開くと、先ほど名前をつけたパラメーター名と、その値が表示されているのがわかります。
もし、後からこの値を変更したければ、ブラウザ上で値を変更して「適用」をクリックするだけです。ためしに、7から3に変更してみます。
パラメーターの値を変更したあと、セマンティックモデルを更新してやると、開始日付、終了日付の値画変わり、それに従ってフィルタリングされるログ日付も変化しました！
今回は開始日付のNの値だけを変化させましたが、もうひとつ終了日付は開始日付から何日間かを指定することも別のパラメーター名をつくれば対応できそうですね。試してみてください。
まとめ
今回は、「開始日付」のMクエリの中で「DateTime.LocalNow」関数を使い、今日の日付を基準に開始日付、終了日付を求めましたが、ログや他のテーブルから、たとえば発注日付などの日付を基準にして計算する場合にも、フィルタする基準日を別のクエリに分けておくと見通しが良くなるのでお勧めです。
また、パラメーターを使って、Power BI サービスから直接変数を操作できる方法を紹介しました。いろいろと応用が利くとおもうので、ご紹介してみました。
こんな人が書いてます
職場でPower BIを使ったり困っている人のサポートをしたりしてます。Qiitaの記事 や 個人的なブログ ではPower AutomateクラウドフローについてのTIPSのようなものを作ったりしているので、良かったら読んだり「いいね！」していただけると励みになります。
ところで、Copilot君便利ですね。
誤字がないかしら？ と思ってCopilot君に尋ねてみると、誤字チェックとまとめをつくってくれました。
文章には誤字は見当たりませんでした。内容も非常にわかりやすく書かれています。素晴らしい記事ですね！
この記事は、Power BI の M クエリを使って、ログデータなどを日付範囲でフィルタリングする方法について説明しています。特に、○日前以降本日までのような動的な日付指定を行う方法に焦点を当てています。
主な内容
-
日付範囲でのフィルタリング:
- Power Query で先週や今週などの範囲で絞り込む機能の紹介。
- GUI だけでは難しい動的な日付指定の方法。
-
クエリで日付を作成:
- 今日から3日前の日付を取得するクエリの作成方法。
- 取得した日付をフィルタの比較値として使用する方法。
-
N日前の日付を取得:
- N日前の日付を動的に取得するクエリの作成方法。
- 取得した日付が土曜日や日曜日の場合に調整する方法。
-
パラメーターの使用:
- Power BI サービスでパラメーターを使って動的に日付を変更する方法。
- パラメーターを使うことで、Power BI サービス上で値を変更し、再発行する手間を省く方法。
-
応用例:
- 開始日付と終了日付をクエリで指定し、日付範囲をフィルタリングする方法。
- パラメーターを使って、Power BI サービスから直接変数を操作する方法。
この記事は、Power BI を使って日付範囲でデータをフィルタリングする際に役立つ具体的な手順と応用例を提供しています。特に、動的な日付指定やパラメーターの使用方法について詳しく説明しています。