• MS365tips
  • PowerAutomate

Power Automateフィルタークエリの書き方(列名・日時・タイムゾーンに注意)

Power Automateにおける、フィルタークエリの使い方について解説します。
フィルタークエリとは、SharePointやExcelに対して、データを絞り込む際に使う記述方法です。
フィルタークエリを使う際は、列名の指定方法と、日時・タイムゾーンの取り扱いに注意が必要です。

  • フィルタークエリ一覧と使用例
  • 日時に対するクエリ記述方法
  • 内部名の確認方法
  • タイムゾーンの扱い

について、解説します。
この記事を読めば、スムーズに意図通りのデータの取得ができるようになるはずです。
ぜひ最後までご覧ください。

—この記事の監修者—

ヒョウノモトハル

  • 1979年神奈川県生まれ
  • 大学卒業後、モラトリアム期間を経て会社員となる
  • 経理・総務・管理といったバックオフィスでの経験を積み、2016年から会社の情報システム部門へ転属
  • その後2021年にキャリアの経験を活かし、Microsoft365関連の講座でUdemy講師としてデビュー
  • 2023年現在、受講生5万人を抱えるベストセラー講師となる

フィルタークエリ一覧と使用例

以下、フィルタークエリで利用できる演算子の一覧です。

種類オペレーター説明
文字列・数値比較eq等しい(Equal)
ne等しくない(Not Equal)
数値比較gtより大きい(Greater Than)
ge以上(Greater Than or Equal)
ltより小さい(Less Than)
le以下(Less Than or Equal)
論理演算and論理積
or論理和
文字列比較startswith指定した文字列で始まる
substringof指定した文字列を含む

参考:
https://learn.microsoft.com/ja/jp/sharepoint/dev/business/apps/power/automate/guidance/working/with/get/items/and/get/files

以下のテーブルを基に、各フィルタークエリの実際の使用例を示します。

IDNameAgeStatusJoinDate
1佐藤25Active2023/01/15
2鈴木30Inactive2022/12/20
3高橋35Active2021/05/10
4田中40Inactive2020/07/22
5渡辺45Active2019/03/30

等しい: eq (Equal)

・行いたいこと: StatusがActiveの行のみ取得

・クエリ: 

Status eq ‘Active’

・結果:

NameAgeStatusJoinDate
佐藤25Active2023/01/15
高橋35Active2021/05/10
渡辺45Active2019/03/30

等しくない: ne (Not Equal)

・行いたいこと: Ageが30以外の行を取得

・クエリ: 

Age ne 30

・結果:

NameAgeStatusJoinDate
佐藤25Active2023/01/15
高橋35Active2021/05/10
田中40Inactive2020/07/22
渡辺45Active2019/03/30

より大きい: gt (Greater Than)

・行いたいこと: Ageが30より大きい行を取得

・クエリ:

Age gt 30

・結果:

NameAgeStatusJoinDate
高橋35Active2021/05/10
田中40Inactive2020/07/22
渡辺45Active2019/03/30

以上: ge (Greater Than or Equal)

・行いたいこと: Ageが35以上の行を取得

・クエリ:

Age ge 35

・結果:

NameAgeStatusJoinDate
高橋35Active2021/05/10
田中40Inactive2020/07/22
渡辺45Active2019/03/30

より小さい: lt (Less Than)

・行いたいこと: Ageが40未満の行を取得

・クエリ:

Age lt 40

・結果:

NameAgeStatusJoinDate
佐藤25Active2023/01/15
鈴木30Inactive2022/12/20
高橋35Active2021/05/10

以下: le (Less Than or Equal)

・行いたいこと: Ageが40以下の行を取得

・クエリ:

Age le 40

・結果:

NameAgeStatusJoinDate
佐藤25Active2023/01/15
鈴木30Inactive2022/12/20
高橋35Active2021/05/10
田中40Inactive2020/07/22

論理積: and

・行いたいこと: StatusがActiveかつAgeが30より大きい行を取得

・クエリ:

Status eq ‘Active’ and Age gt 30

・結果:

NameAgeStatusJoinDate
高橋35Active2021/05/10
渡辺45Active2019/03/30

論理和: or

・行いたいこと: StatusがActiveまたはAgeが30以下の行を取得

・クエリ:

Status eq ‘Active’ or Age le 30

・結果:

NameAgeStatusJoinDate
佐藤25Active2023/01/15
鈴木30Inactive2022/12/20
高橋35Active2021/05/10
渡辺45Active2019/03/30

指定した文字列で始まる: startswith (Starts With)

・行いたいこと: Nameが「佐」で始まる行を取得

・クエリ:

startswith(Name, ‘佐’)

・結果:

NameAgeStatusJoinDate

佐藤
25Active2023/01/15

指定した文字列を含む: substringof 

・行いたいこと: Nameに「田」が含まれる行を取得

・クエリ:

substringof(‘田’, Name)

・結果:

NameAgeStatusJoinDate
田中40Inactive2020/07/22

日時に対するフィルタークエリの指定方法

日時に対する指定方法を、具体例と共に紹介します。

日時は、未来ほど値が大きくなります。
そのため、過去はlt・le(より前、以前)で、未来はgt・ge(より後、以降)で指定します。
また、eq・ne(等しい・等しくない)は使い方に注意が必要です。

以下のテーブルを例に、解説します。
JoinDateが日付だけの項目、Modifiedが時刻も含めた項目です。

IDNameAgeStatusJoinDateModified
1佐藤25Active2023-01-152024/06/01 8:00
2鈴木30Inactive2022-12-202024/04/20 11:45
3高橋35Active2021-05-102023/12/10 8:00
4田中40Inactive2020-07-222023/06/22 16:30
5渡辺45Active2019-03-302023/01/15 10:15

末尾にZはつけない

前提として、手入力で日時を指定する場合、末尾にZを付けないようにしましょう。

ZはUTC時刻を意味し、SharePointの表示通りの日時が取得できなくなってしまいます。
(詳しくは「注意2:タイムゾーンを理解する」で解説します。)

NG例:

 Modified ge ‘2023-12-01T00:00:00Z’

OK例:

 Modified ge ‘2023-12-01T00:00:00’

eq・ne(等しい・等しくない)

日付の項目に対して日付指定で使う場合、特に問題なく絞り込みが行えます。
これは、日付の項目は内部的には0:00を保持しているためと推測します。
フィルタークエリの条件で時刻を省略した場合も、0:00が補完されて処理されます。

・行いたいこと: JoinDateが2021/05/10の日付の行を取得

・クエリ: 

JoinDate eq ‘2021-05-10’

・結果:

NameAgeStatusJoinDateModified
高橋35Active2021-05-102023/12/10 8:00

・行いたいこと: JoinDateが2021/05/10ではない行を取得

・クエリ: 

JoinDate ne ‘2021-05-10’

・結果:

NameAgeStatusJoinDateModified
佐藤25Active2023-01-152024/06/01 8:00
鈴木30Inactive2022-12-202024/04/20 11:45
田中40Inactive2020-07-222023/06/22 16:30
渡辺45Active2019-03-302023/01/15 10:15

時刻も含む項目に対して、日付で絞りたい場合は、「0:00以降~翌日0:00より前」で指定すると良いでしょう。

・行いたいこと: Modifiedが2024/6/1の日付の行を取得

・クエリ:

Modified ge ‘2024-06-01T00:00:00’ and Modified lt ‘2024-06-02T00:00:00’

※00:00:00~23:59:59 でも問題無いと思われますが、ミリ秒の扱いが気になる(もし内部的にミリ秒も保持している場合、23:59:59.001等のデータが漏れるのでは・・)ので、「翌日00:00:00より前」としています。

結果:

NameAgeStatusJoinDateModified
佐藤25Active2023-01-152024/06/01 8:00

lt・le(より前、以前)

行いたいこと: JoinDateが2021/5/10より前の行を取得

クエリ:

JoinDate lt ‘2021-5-10’

結果:

IDNameAgeStatusJoinDateModified
4田中40Inactive2020-07-222023/06/22 16:30
5渡辺45Active2019-03-302023/01/15 10:15

gt・ge(より後、以降)

行いたいこと: Modifiedが2023/12/01 0時0分0秒 以降の行を取得

クエリ:

Modified ge ‘2023-12-01T00:00:00’

結果:

NameAgeStatusJoinDateModified
佐藤25Active2023-01-152024/06/01 8:00
鈴木30Inactive2022-12-202024/04/20 11:45
高橋35Active2021-05-102023/12/10 8:00

注意1:SharePointの列名は内部名を使用する

SharePointの列には、表示名と内部名の2種類があります。
フィルタークエリで指定するのは内部名です。

アルファベットのみの列名であれば、表示名=内部名となります。
記号を含む場合や日本語の列名の場合、表示名と内部名は異なるため注意しましょう。

種類表示名内部名表示名=内部名
アルファベットのみNameName
日本語名前x540d__x524d_×
数字から始まる1Name_x0031_Name×
スペースありNa meName×

内部名の確認方法

内部名の確認方法は、以下です。

1.対象のリストを開いている状態で〈設定〉から〈リストの設定〉を開く

2.内部名を調べたい列名をクリック

3.開かれたページのURLのField=の後ろが内部名

画像の場合、「_x72b6__x614b_」が内部名

列作成時にアルファベットのみでわかりやすい内部名に

SharePointの列作成時に、名前をアルファベットのみで指定すると、わかりやすい内部名にできます。
最初に作成した列名を元に内部名が決まり、その後は変更しても変わりません。
日本語の列名にしたい場合であっても、一度アルファベットのみで作成してから日本語に変更すると、フィルタークエリの記述が楽になり、可読性も上がります。

注意2:タイムゾーンを理解する

日時を扱う際は、タイムゾーンに注意しましょう。

以下、シーンごとにどのタイムゾーンが使われるかの一覧です。
シーンによって、「UTC(協定世界時)」か「サイトの設定に従う」かが異なるので、非常に混乱しやすいです。

シーンフィルタークエリ
日時の末尾
タイムゾーン
SharePointサイトの設定に従う
Power AutomateUTC
フィルタークエリZ付与UTC
Z未付与サイトの設定に従う

フィルタークエリを使う際に認識しておくべきことは、以下の4点。

  • SharePointのタイムゾーンが日本時間でない場合、見た目上の値は実際の時刻と一致しない
  • Power Automate上、日時の列の取得結果は、UTC時刻に変換される
  • 手入力で日時を指定する場合、末尾に「Z」はつけない
  • 「過去(未来)の時間の取得」アクションの結果は、フィルタークエリでそのまま利用可能

それぞれのタイムゾーンの扱いと、認識しておくべきことについて、詳しく解説します。

SharePointのタイムゾーンはサイトの設定次第

SharePointの「更新日時」と「登録日時」に表示されるのは、サイトで設定されているタイムゾーンの時刻です。タイムゾーンが日本時間になっていれば問題ありませんが、そうでない場合は、日本時間とズレがあることをおさえましょう。

例えば、タイムゾーンが〈東部標準時〉(以降、ESTと表記)の場合、
登録日時・更新日時もESTの時刻で更新されます。

(例)

日本時間(以降、JST) 2024/05/28 13:36に、データを更新した場合。
更新日時は、ESTの2024/05/28 0:36で更新される。

タイムゾーンの確認・変更方法

1.対象のリストを開いている状態で、〈設定〉の〈サイト情報〉をクリックします。

2.〈すべてのサイト設定を表示〉をクリックします。

3.〈地域の設定〉をクリックします。

4.タイムゾーンの値を確認します。

SharePointのタイムゾーンは安易に変更しない

確認画面から、タイムゾーンをJST(大阪、札幌、東京)に変更できますが、既に運用中のサイトであれば、安易に変更しないことをオススメします。
手入力で登録していた日時も変換されてしまうからです。

具体例で解説します。
まず、更新日時です。元々の値がESTの時刻なので、タイムゾーンを変更することでJSTに変換されます。

更新日時タイムゾーン
2024/05/28 0時EST
(タイムゾーン変更後↓)
2024/05/28 13時JST(実際の登録時間)

「納期」という列を手入力で更新していた場合。
元々の値が既にJST基準で登録されているのに、タイムゾーンの変更により更に変換されてしまいます。

納期タイムゾーン
5/28 7時JST(実際の納期)
(タイムゾーン変更後↓)
5/28 20時JSTに更に+13時間

この変換に気づかずに運用を続けた場合、「本来7時の納期なのに、20時として対応してしまった」なんてことになりかねません。

タイムゾーン変更前に、「登録日時」「更新日時」のように自動更新されず、日本時間ベースで更新している日時項目がないかを必ず確認しましょう。

日本時間ベースで更新している日時項目がある場合、以下の手順で、正しい日時を保ったままタイムゾーンを変更できます。

  1. 稼働していない時間帯を利用して、「日本時間ベースで更新している日時項目」をすべて、タイムゾーンに合わせて変換する
  2. サイトのタイムゾーンを、JSTに変更する

内部的にはUTCの時刻を持っている

SharePointは、内部的にはUTC(協定世界時)のデータを保持しています。
仕組みとしては、タイムゾーンの設定に従い、変換した上で表示しているだけです。
内部的にはUTCの時刻を持っていることを理解しておくと、フィルタークエリで指定する際に迷わずに済むはずです。

Power AutomateのタイムゾーンはUTC

Power Automateのタイムゾーンは、UTC(協定世界時)です。

  • テストを実行した際の開始時刻や終了時刻
  • 「過去の時間の取得」アクションの実行結果
  • SharePointから取得したデータの日時の項目

などは、いずれもUTCで表示、出力されます。

特に、フィルタークエリを使って取得した「SharePoint上の日時のデータ」も、サイトのタイムゾーンに関係無く、「UTC(協定世界時)ベースでの日時」となる点に注意しましょう。
メールの本文に載せるなど、日本時間として利用する場合は、変換が必要です。

例1:「過去の時間の取得」アクションの実行結果

日本時間「2024/5/29 08:56:57」に「過去の時間の取得」するフローを実行した場合、
実行時間は、UTCの「2024/5/28 23:56:57」です。

「過去の時間の取得」で1時間前を取得した結果も、UTC時間の1時間前である「2024-05-28T22:56:57…(略)」となります。

例2:SharePointから取得したデータの日時の項目

SharePoint上「2024/06/01 8:00」で登録されているデータは、PowerAutomate上ではUTCで取得されます。

(SharePoint上のデータ)

(PowerAutomateでの出力結果)

フィルタークエリ:Z付与でUTC、未付与でサイトのタイムゾーン

最後にフィルタークエリで日時を指定した場合の、タイムゾーンについて解説します。
指定した日時の末尾に「Z」を付与した場合はUTC、付与しなかった場合はサイトのタイムゾーンとして処理されます。

具体例で解説します。

SharePointのリストに、以下のデータを用意しました。

サイトのタイムゾーンは日本時間に設定してあります。

上記データに対して、
2024-05-31T23:00 以降
2024-05-31T23:00Z 以降
それぞれでフィルタークエリを実行してみます。

Zを付与しなかった場合(サイトのタイムゾーン時刻に対して絞り込まれる)

フィルタークエリでZを付与しなかった場合、出力結果は「0531」と「0601」の2件でした。

Zを付与しなかった場合、サイトのタイムゾーン時刻でのJoinDate、つまりSharePoint上の表示そのままの日時に対して絞り込みが行われます。

NameJoinDate(日本時間)2024-05-31T23:00以降
06012024/06/01 8:00
05312024/05/31 23:00

Zを付与した場合(UTC時刻に対して絞り込まれる)

フィルタークエリで2024-05-31T23:00Z 以降と指定した場合、出力結果は「0601」の1件でした。

SharePointのUTC時刻でのJoinDateに対して絞り込みが行われたためです。

NameJoinDate(日本時間)(JoinDateのUTC時間)2024-05-31T23:00Z以降
06012024/06/01 8:002024-05-31 23:00
05312024/05/31 23:002024-05-31 14:00×

このように、Zを付与すると、SharePointで表示されている日時とは異なる値(UTC時刻)に対して絞り込みが行われます。
これが、手入力で日時を指定する場合に、Zを付与しない理由です。

なお、サイトのタイムゾーンがJSTでない場合、Zを付与せずに、日本時間を基準に日時を指定しても、意図どおりの絞り込みができません。

  • 日本時間を基準にした日時を、タイムゾーンの設定に合わせて変換した上で、フィルタークエリで利用する
  • サイトのタイムゾーンをJSTにする(※確認必須)

上記いずれかで対応すると良いでしょう。

「過去(未来)の時間の取得」アクションの結果は、フィルタークエリでそのまま利用可能

「過去(未来)の時間の取得」アクション等、PowerAutomateのアクションの結果はUTC時刻ですが、変換しなくてもフィルタークエリでそのまま利用可能です。

アクションの結果の日時には末尾にZが付与されており、フィルタークエリにおいてUTC時刻同士で比較することになるからです。

まず、「過去(未来)の時間の取得」アクションの結果は、UTC時刻です。末尾にZもついています。

この結果をそのままフィルタークエリ内で使用した場合、UTC時刻に対して、絞り込みが行われます。

例えば、1日前に更新したデータを削除したい場合。
日本時間5/29 0:00であれば1日前は5/28 0:00ですが、「過去の時間の取得」の結果は、UTC時間「2024-05-28 15:00Z」となります。

この出力結果「2024-05-28 15:00Z」をフィルタークエリで指定した場合、SharePoint内のデータもUTC時刻ベースで絞り込みが行われます。
更新日時が日本時間で「5/28 0:00」のデータは、UTC時刻で「2024-05-28 15:00」です。

日本時間1日前UTC時間
過去の時間の取得2024-05-29 0:002024-05-28 0:002024-05-28 15:00
更新日時2024-05-28 0:002024-05-28 15:00

どちらも同じUTC時刻に対して比較するので、結果として意図通りの絞り込みが行えます。

Microsoft365の運用にお困りではありませんか?

株式会社uniteでは、Microsoft365のすべてのアプリを対象に、法人様向けの運用サポートを行っています。

  • 社内メール⇒teamsへの円滑な移行と利用率UP施策
  • SharePointのドキュメントライブラリを活用したファイル管理
  • ExcelやAccessで作成された既存の社内ツールをPowerAppsへリニューアル
  • 手作業で行っている業務をPowerAutomateで自動化

等、MS365を利用した業務改善のお手伝いをさせて頂いております。
ご予算、サポート範囲については完全オーダーメードでサポート対応をしておりますので、お気軽にご相談ください。