- MS365tips
- PowerAutomate
Power Automateフィルタークエリの書き方(列名・日時・タイムゾーンに注意)
![](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/powerautomate-commentary.png)
Power Automateにおける、フィルタークエリの使い方について解説します。
フィルタークエリとは、SharePointやExcelに対して、データを絞り込む際に使う記述方法です。
フィルタークエリを使う際は、列名の指定方法と、日時・タイムゾーンの取り扱いに注意が必要です。
- フィルタークエリ一覧と使用例
- 日時に対するクエリ記述方法
- 内部名の確認方法
- タイムゾーンの扱い
について、解説します。
この記事を読めば、スムーズに意図通りのデータの取得ができるようになるはずです。
ぜひ最後までご覧ください。
—この記事の監修者—
![](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/03/image-3-180x180.png)
ヒョウノモトハル
- 1979年神奈川県生まれ
- 大学卒業後、モラトリアム期間を経て会社員となる
- 経理・総務・管理といったバックオフィスでの経験を積み、2016年から会社の情報システム部門へ転属
- その後2021年にキャリアの経験を活かし、Microsoft365関連の講座でUdemy講師としてデビュー
- 2023年現在、受講生5万人を抱えるベストセラー講師となる
Contents
フィルタークエリ一覧と使用例
以下、フィルタークエリで利用できる演算子の一覧です。
種類 | オペレーター | 説明 |
---|---|---|
文字列・数値比較 | 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 | 指定した文字列を含む |
以下のテーブルを基に、各フィルタークエリの実際の使用例を示します。
ID | Name | Age | Status | JoinDate |
---|---|---|---|---|
1 | 佐藤 | 25 | Active | 2023/01/15 |
2 | 鈴木 | 30 | Inactive | 2022/12/20 |
3 | 高橋 | 35 | Active | 2021/05/10 |
4 | 田中 | 40 | Inactive | 2020/07/22 |
5 | 渡辺 | 45 | Active | 2019/03/30 |
等しい: eq (Equal)
・行いたいこと: StatusがActiveの行のみ取得
・クエリ:
Status eq ‘Active’
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
佐藤 | 25 | Active | 2023/01/15 |
高橋 | 35 | Active | 2021/05/10 |
渡辺 | 45 | Active | 2019/03/30 |
等しくない: ne (Not Equal)
・行いたいこと: Ageが30以外の行を取得
・クエリ:
Age ne 30
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
佐藤 | 25 | Active | 2023/01/15 |
高橋 | 35 | Active | 2021/05/10 |
田中 | 40 | Inactive | 2020/07/22 |
渡辺 | 45 | Active | 2019/03/30 |
より大きい: gt (Greater Than)
・行いたいこと: Ageが30より大きい行を取得
・クエリ:
Age gt 30
・結果:
Name | Age | Status | JoinDate |
高橋 | 35 | Active | 2021/05/10 |
田中 | 40 | Inactive | 2020/07/22 |
渡辺 | 45 | Active | 2019/03/30 |
以上: ge (Greater Than or Equal)
・行いたいこと: Ageが35以上の行を取得
・クエリ:
Age ge 35
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
高橋 | 35 | Active | 2021/05/10 |
田中 | 40 | Inactive | 2020/07/22 |
渡辺 | 45 | Active | 2019/03/30 |
より小さい: lt (Less Than)
・行いたいこと: Ageが40未満の行を取得
・クエリ:
Age lt 40
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
佐藤 | 25 | Active | 2023/01/15 |
鈴木 | 30 | Inactive | 2022/12/20 |
高橋 | 35 | Active | 2021/05/10 |
以下: le (Less Than or Equal)
・行いたいこと: Ageが40以下の行を取得
・クエリ:
Age le 40
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
佐藤 | 25 | Active | 2023/01/15 |
鈴木 | 30 | Inactive | 2022/12/20 |
高橋 | 35 | Active | 2021/05/10 |
田中 | 40 | Inactive | 2020/07/22 |
論理積: and
・行いたいこと: StatusがActiveかつAgeが30より大きい行を取得
・クエリ:
Status eq ‘Active’ and Age gt 30
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
高橋 | 35 | Active | 2021/05/10 |
渡辺 | 45 | Active | 2019/03/30 |
論理和: or
・行いたいこと: StatusがActiveまたはAgeが30以下の行を取得
・クエリ:
Status eq ‘Active’ or Age le 30
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
佐藤 | 25 | Active | 2023/01/15 |
鈴木 | 30 | Inactive | 2022/12/20 |
高橋 | 35 | Active | 2021/05/10 |
渡辺 | 45 | Active | 2019/03/30 |
指定した文字列で始まる: startswith (Starts With)
・行いたいこと: Nameが「佐」で始まる行を取得
・クエリ:
startswith(Name, ‘佐’)
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
佐藤 | 25 | Active | 2023/01/15 |
指定した文字列を含む: substringof
・行いたいこと: Nameに「田」が含まれる行を取得
・クエリ:
substringof(‘田’, Name)
・結果:
Name | Age | Status | JoinDate |
---|---|---|---|
田中 | 40 | Inactive | 2020/07/22 |
日時に対するフィルタークエリの指定方法
日時に対する指定方法を、具体例と共に紹介します。
日時は、未来ほど値が大きくなります。
そのため、過去はlt・le(より前、以前)で、未来はgt・ge(より後、以降)で指定します。
また、eq・ne(等しい・等しくない)は使い方に注意が必要です。
以下のテーブルを例に、解説します。
JoinDateが日付だけの項目、Modifiedが時刻も含めた項目です。
ID | Name | Age | Status | JoinDate | Modified |
---|---|---|---|---|---|
1 | 佐藤 | 25 | Active | 2023-01-15 | 2024/06/01 8:00 |
2 | 鈴木 | 30 | Inactive | 2022-12-20 | 2024/04/20 11:45 |
3 | 高橋 | 35 | Active | 2021-05-10 | 2023/12/10 8:00 |
4 | 田中 | 40 | Inactive | 2020-07-22 | 2023/06/22 16:30 |
5 | 渡辺 | 45 | Active | 2019-03-30 | 2023/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’
・結果:
Name | Age | Status | JoinDate | Modified |
---|---|---|---|---|
高橋 | 35 | Active | 2021-05-10 | 2023/12/10 8:00 |
・行いたいこと: JoinDateが2021/05/10ではない行を取得
・クエリ:
JoinDate ne ‘2021-05-10’
・結果:
Name | Age | Status | JoinDate | Modified |
---|---|---|---|---|
佐藤 | 25 | Active | 2023-01-15 | 2024/06/01 8:00 |
鈴木 | 30 | Inactive | 2022-12-20 | 2024/04/20 11:45 |
田中 | 40 | Inactive | 2020-07-22 | 2023/06/22 16:30 |
渡辺 | 45 | Active | 2019-03-30 | 2023/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より前」としています。
結果:
Name | Age | Status | JoinDate | Modified |
---|---|---|---|---|
佐藤 | 25 | Active | 2023-01-15 | 2024/06/01 8:00 |
lt・le(より前、以前)
行いたいこと: JoinDateが2021/5/10より前の行を取得
クエリ:
JoinDate lt ‘2021-5-10’
結果:
ID | Name | Age | Status | JoinDate | Modified |
---|---|---|---|---|---|
4 | 田中 | 40 | Inactive | 2020-07-22 | 2023/06/22 16:30 |
5 | 渡辺 | 45 | Active | 2019-03-30 | 2023/01/15 10:15 |
gt・ge(より後、以降)
行いたいこと: Modifiedが2023/12/01 0時0分0秒 以降の行を取得
クエリ:
Modified ge ‘2023-12-01T00:00:00’
結果:
Name | Age | Status | JoinDate | Modified |
---|---|---|---|---|
佐藤 | 25 | Active | 2023-01-15 | 2024/06/01 8:00 |
鈴木 | 30 | Inactive | 2022-12-20 | 2024/04/20 11:45 |
高橋 | 35 | Active | 2021-05-10 | 2023/12/10 8:00 |
SharePointの列には、表示名と内部名の2種類があります。
フィルタークエリで指定するのは内部名です。
アルファベットのみの列名であれば、表示名=内部名となります。
記号を含む場合や日本語の列名の場合、表示名と内部名は異なるため注意しましょう。
種類 | 表示名 | 内部名 | 表示名=内部名 |
---|---|---|---|
アルファベットのみ | Name | Name | ○ |
日本語 | 名前 | x540d__x524d_ | × |
数字から始まる | 1Name | _x0031_Name | × |
スペースあり | Na me | Name | × |
内部名の確認方法
内部名の確認方法は、以下です。
1.対象のリストを開いている状態で〈設定〉から〈リストの設定〉を開く
![内部名の確認手順1:対象のリストを開いている状態で〈設定〉から〈リストの設定〉を開く](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_1.webp)
2.内部名を調べたい列名をクリック
![内部名の確認手順2:内部名を調べたい列名をクリック](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_2-1024x845.webp)
3.開かれたページのURLのField=の後ろが内部名
![内部名の確認手順3:開かれたページのURLのField=の後ろが内部名](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_3-1024x396.webp)
画像の場合、「_x72b6__x614b_」が内部名
列作成時にアルファベットのみでわかりやすい内部名に
SharePointの列作成時に、名前をアルファベットのみで指定すると、わかりやすい内部名にできます。
最初に作成した列名を元に内部名が決まり、その後は変更しても変わりません。
日本語の列名にしたい場合であっても、一度アルファベットのみで作成してから日本語に変更すると、フィルタークエリの記述が楽になり、可読性も上がります。
注意2:タイムゾーンを理解する
日時を扱う際は、タイムゾーンに注意しましょう。
以下、シーンごとにどのタイムゾーンが使われるかの一覧です。
シーンによって、「UTC(協定世界時)」か「サイトの設定に従う」かが異なるので、非常に混乱しやすいです。
シーン | フィルタークエリ 日時の末尾 | タイムゾーン |
---|---|---|
SharePoint | – | サイトの設定に従う |
Power Automate | – | UTC |
フィルタークエリ | Z付与 | UTC |
Z未付与 | サイトの設定に従う |
フィルタークエリを使う際に認識しておくべきことは、以下の4点。
- SharePointのタイムゾーンが日本時間でない場合、見た目上の値は実際の時刻と一致しない
- Power Automate上、日時の列の取得結果は、UTC時刻に変換される
- 手入力で日時を指定する場合、末尾に「Z」はつけない
- 「過去(未来)の時間の取得」アクションの結果は、フィルタークエリでそのまま利用可能
それぞれのタイムゾーンの扱いと、認識しておくべきことについて、詳しく解説します。
SharePointの「更新日時」と「登録日時」に表示されるのは、サイトで設定されているタイムゾーンの時刻です。タイムゾーンが日本時間になっていれば問題ありませんが、そうでない場合は、日本時間とズレがあることをおさえましょう。
例えば、タイムゾーンが〈東部標準時〉(以降、ESTと表記)の場合、
登録日時・更新日時もESTの時刻で更新されます。
(例)
日本時間(以降、JST) 2024/05/28 13:36に、データを更新した場合。
更新日時は、ESTの2024/05/28 0:36で更新される。
![登録日時・更新日時の表示時刻はサイトのタイムゾーン設定で変わる](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_4.webp)
タイムゾーンの確認・変更方法
1.対象のリストを開いている状態で、〈設定〉の〈サイト情報〉をクリックします。
![タイムゾーンの確認・変更手順1:対象のリストを開いている状態で、〈設定〉の〈サイト情報〉をクリック](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_5.webp)
2.〈すべてのサイト設定を表示〉をクリックします。
![タイムゾーンの確認・変更手順2:〈すべてのサイト設定を表示〉をクリック](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_6-516x1024.webp)
3.〈地域の設定〉をクリックします。
![タイムゾーンの確認・変更手順3:〈地域の設定〉をクリック](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_7-1024x903.webp)
4.タイムゾーンの値を確認します。
![タイムゾーンの確認・変更手順4:タイムゾーンの値を確認](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_8-1024x127.webp)
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時として対応してしまった」なんてことになりかねません。
タイムゾーン変更前に、「登録日時」「更新日時」のように自動更新されず、日本時間ベースで更新している日時項目がないかを必ず確認しましょう。
日本時間ベースで更新している日時項目がある場合、以下の手順で、正しい日時を保ったままタイムゾーンを変更できます。
- 稼働していない時間帯を利用して、「日本時間ベースで更新している日時項目」をすべて、タイムゾーンに合わせて変換する
- サイトのタイムゾーンを、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…(略)」となります。
![例1:「過去の時間の取得」アクションの実行結果の図](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_9-841x1024.webp)
例2:SharePointから取得したデータの日時の項目
SharePoint上「2024/06/01 8:00」で登録されているデータは、PowerAutomate上ではUTCで取得されます。
(SharePoint上のデータ)
![例2:SharePointから取得したデータの日時の項目の図1](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_10-1.webp)
(PowerAutomateでの出力結果)
![例2:SharePointから取得したデータの日時の項目の図2](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_11-1024x306.webp)
フィルタークエリ:Z付与でUTC、未付与でサイトのタイムゾーン
最後にフィルタークエリで日時を指定した場合の、タイムゾーンについて解説します。
指定した日時の末尾に「Z」を付与した場合はUTC、付与しなかった場合はサイトのタイムゾーンとして処理されます。
具体例で解説します。
SharePointのリストに、以下のデータを用意しました。
![SharePointのリストに用意したデータ](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_12-2.webp)
サイトのタイムゾーンは日本時間に設定してあります。
上記データに対して、
2024-05-31T23:00 以降
2024-05-31T23:00Z 以降
それぞれでフィルタークエリを実行してみます。
Zを付与しなかった場合(サイトのタイムゾーン時刻に対して絞り込まれる)
フィルタークエリでZを付与しなかった場合、出力結果は「0531」と「0601」の2件でした。
Zを付与しなかった場合、サイトのタイムゾーン時刻でのJoinDate、つまりSharePoint上の表示そのままの日時に対して絞り込みが行われます。
Name | JoinDate(日本時間) | 2024-05-31T23:00以降 |
---|---|---|
0601 | 2024/06/01 8:00 | ○ |
0531 | 2024/05/31 23:00 | ○ |
Zを付与した場合(UTC時刻に対して絞り込まれる)
フィルタークエリで2024-05-31T23:00Z 以降と指定した場合、出力結果は「0601」の1件でした。
![フィルタークエリでの指定内容](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_13-1024x111.webp)
SharePointのUTC時刻でのJoinDateに対して絞り込みが行われたためです。
Name | JoinDate(日本時間) | (JoinDateのUTC時間) | 2024-05-31T23:00Z以降 |
---|---|---|---|
0601 | 2024/06/01 8:00 | 2024-05-31 23:00 | ○ |
0531 | 2024/05/31 23:00 | 2024-05-31 14:00 | × |
このように、Zを付与すると、SharePointで表示されている日時とは異なる値(UTC時刻)に対して絞り込みが行われます。
これが、手入力で日時を指定する場合に、Zを付与しない理由です。
なお、サイトのタイムゾーンがJSTでない場合、Zを付与せずに、日本時間を基準に日時を指定しても、意図どおりの絞り込みができません。
- 日本時間を基準にした日時を、タイムゾーンの設定に合わせて変換した上で、フィルタークエリで利用する
- サイトのタイムゾーンをJSTにする(※確認必須)
上記いずれかで対応すると良いでしょう。
「過去(未来)の時間の取得」アクションの結果は、フィルタークエリでそのまま利用可能
「過去(未来)の時間の取得」アクション等、PowerAutomateのアクションの結果はUTC時刻ですが、変換しなくてもフィルタークエリでそのまま利用可能です。
アクションの結果の日時には末尾にZが付与されており、フィルタークエリにおいてUTC時刻同士で比較することになるからです。
まず、「過去(未来)の時間の取得」アクションの結果は、UTC時刻です。末尾にZもついています。
![「過去(未来)の時間の取得」アクション結果の様子](https://www.unite-365.co.jp/unite365/wp-content/uploads/2024/05/power-automate-filterquery_14-1.webp)
この結果をそのままフィルタークエリ内で使用した場合、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:00 | 2024-05-28 0:00 | 2024-05-28 15:00 |
更新日時 | 2024-05-28 0:00 | 2024-05-28 15:00 |
どちらも同じUTC時刻に対して比較するので、結果として意図通りの絞り込みが行えます。
Microsoft365の運用にお困りではありませんか?
株式会社uniteでは、Microsoft365のすべてのアプリを対象に、法人様向けの運用サポートを行っています。
- 社内メール⇒teamsへの円滑な移行と利用率UP施策
- SharePointのドキュメントライブラリを活用したファイル管理
- ExcelやAccessで作成された既存の社内ツールをPowerAppsへリニューアル
- 手作業で行っている業務をPowerAutomateで自動化
等、MS365を利用した業務改善のお手伝いをさせて頂いております。
ご予算、サポート範囲については完全オーダーメードでサポート対応をしておりますので、お気軽にご相談ください。