PowerBI クエリで複数要素が該当するかどうか判定

Question

実現したいこと

アクセスリストというテーブルに”コンテンツの場所”という列があり、これまでにアクセスしたSharepointOnlineのURLが記載されています。除外ワードリストというテーブルには除外ワードという列があり、除外したいキーワードが記載されています。”コンテンツの場所”の列にある要素で除外ワードを一部でも含むかどうかの判定列の作成方法を教えてください。

発生している問題・分からないこと

if List.AnyTrue(List.Transform(除外ワードリスト[除外ワード], each Text.Contains([コンテンツの場所],(_)))) then "除外" else "許可"
カスタム列としては上記を作成しましたが、
Expression.Error: フィールド アクセスを型 Text に適用できません。
詳細:
Value=siteassets
Key=コンテンツの場所
というエラーが表示され、siteassetsは除外ワードの要素でありText型になっていて、解決方法が見つかっていません。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

List.AnyTrue、List.Transform、Text.Containsのそれぞれの意味は理解できたが、組み合わせて場合の使い方が正しいのかが分からなかった。

補足

コンテンツの場所ではなく、111siteassetsという１つの要素の場合は以下の式で試したところ問題なしでした。
if List.AnyTrue(List.Transform(除外ワードリスト[除外ワード], each Text.Contains("111siteassets", _))) then "含まれる" else "含まれない"

Answer 1

アクセスリストというテーブルに”コンテンツの場所”という列があり

除外ワードリストというテーブルには除外ワードという列があり

”コンテンツの場所”の列にある要素で除外ワードを一部でも含むかどうかの判定列の作成方法

let
    //除外ワードを含むか否かを判定する関数を作る
    IsExcluded = (Expression as text) => 
        let
            //テーブル[除外ワードリスト]の[除外ワード]列をドリルダウンしてリスト化
            ExcludedWords = 除外ワードリスト[除外ワード], 
            //引数 Expression に渡されたテキストと部分一致する要素をリストから抽出
            MatchedWords = List.Select(ExcludedWords, each Text.Contains(Expression, _)),
            //抽出後のリストの要素の数が 0 を超える場合は true を、超えない場合は false を返す
            Judgement = if List.Count(MatchedWords) > 0 then true else false 
        in
            Judgement,
    //評価対象となるテーブルを参照
    Source = アクセスリスト,
    //それぞれのレコードの[コンテンツの場所]の値を上記の関数によって評価した結果を新たな列として追加する
    AddedConditionalColumn = Table.AddColumn(Source, "いずれかの除外ワードを含んでいる", each IsExcluded([コンテンツの場所]))
in
    AddedConditionalColumn

IsExcluded の各ステップに関しては、List.MatchesAny 関数を用いて 1 つにまとめてもよいでしょう。

let
    //除外ワードを含むか否かを判定する関数を作る
    IsExcluded = (Expression as text) => 
        let
            //テーブル[除外ワードリスト]の[除外ワード]列に、引数 Expression に渡されたテキストと部分一致する要素が存在する場合は true を、存在しない場合は false を返す
            Judgement = List.MatchesAny(除外ワードリスト[除外ワード], each Text.Contains(Expression,_)) 
        in
            Judgement,
    //評価対象となるテーブルを参照
    Source = アクセスリスト,
    //それぞれのレコードの[コンテンツの場所]の値を上記の関数によって評価した結果を新たな列として追加する
    AddedConditionalColumn = Table.AddColumn(Source, "いずれかの除外ワードを含んでいる", each IsExcluded([コンテンツの場所]))
in
    AddedConditionalColumn

2024.06.27 14:59 追記

詳細エディターは以下のようになっています。

現在のクエリに合わせるなら次のようになさればよいでしょう。

let
    //除外ワードを含むか否かを判定する関数を作る
    IsExcluded = (Expression as text) => 
        let
            //テーブル[除外ワードリスト]の[除外ワード]列に、引数 Expression に渡されたテキストと部分一致する要素が存在する場合は true を、存在しない場合は false を返す
            Judgement = List.MatchesAny(除外ワードリスト[除外ワード], each Text.Contains(Expression,_)) 
        in
            Judgement,
    //ワークブックの参照
    ソース = Excel.Workbook(File.Contents("C:\Users\My_Data\アクセス履歴\Test\新しいフォルダー\全ユーザー結果_01.xlsm"), null, true),
    //ワークシートの参照
    データ_Sheet = ソース{[Item="データ",Kind="Sheet"]}[Data],
    //1行目をヘッダー行として使用
    昇格されたヘッダー数 = Table.PromoteHeaders(データ_Sheet, [PromoteAllScalars=true]),
    //各列のデータ型を変換
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"アクセス日時", type datetime}, {"ユーザー", type text}, {"コンテンツの場所", type text}}), 
    //それぞれのレコードの[コンテンツの場所]の値を上記の IsExcluded 関数によって評価した結果を新たなカスタム列として追加する
    追加されたカスタム = Table.AddColumn(変更された型, "いずれかの除外ワードを含んでいる", each IsExcluded([コンテンツの場所])) 
in
    追加されたカスタム

これだと２つのクエリに分かれてしまうので、１つのクエリの中で判定できるようにしたいです。

他のクエリ上でも同様の処理を実行する可能性があるならば、別途 IsExcluded で行なっている処理を切り出してカスタム関数として作成した上、任意のクエリ上から呼び出すようにされた方が使い勝手が良いと思います。

Comments

[Emizo1]

ありがとうございます。
さっそく上記をカスタム列の式として入れてみたところカスタム列には”Table”が各行に表示され
Expression.Error: 評価中に循環参照が検出されました。
というエラーが出ました。
対応方法のアドバイスを頂けますか。

[sk.exe]

既に[アクセスリスト]または[除外ワードリスト]という名前のクエリが作成済みであり、そのどちらかに記述されたからではないでしょうか。

とりあえず、編集されたクエリの式を元の状態に戻した上、それらのクエリとは別に空のクエリを新規作成し、詳細エディターを開いて例示した式を記述してみて下さい。

[Emizo1]

ご確認ありがとうございます。
空のクエリを作成し、詳細エディターを開いた上記の式を入れたところ、判定できるようになりました。
ただ、これだと２つのクエリに分かれてしまうので、１つのクエリの中で判定できるようにしたいです。

列の追加でカスタム列の作成を行うと
Expression.Error: 評価中に循環参照が検出されました。
というエラーが表示され、詳細エディターは以下のようになっています。
let
ソース = Excel.Workbook(File.Contents("C:\Users\My_Data\アクセス履歴\Test\新しいフォルダー\全ユーザー結果_01.xlsm"), null, true),
データ_Sheet = ソース{[Item="データ",Kind="Sheet"]}[Data],
昇格されたヘッダー数 = Table.PromoteHeaders(データ_Sheet, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"アクセス日時", type datetime}, {"ユーザー", type text}, {"コンテンツの場所", type text}, {"拡張子", type text}}),
追加されたカスタム = Table.AddColumn(変更された型, "カスタム", each let
//除外ワードを含むか否かを判定する関数を作る
IsExcluded = (Expression as text) =>
let
//テーブル[除外ワードリスト]の[除外ワード]列に、引数 Expression に渡されたテキストと部分一致する要素が存在する場合は true を、存在しない場合は false を返す
Judgement = List.MatchesAny(除外ワードリスト[除外ワード], each Text.Contains(Expression,_))
in
Judgement,
//評価対象となるテーブルを参照
Source = アクセスリスト,
//それぞれのレコードの[コンテンツの場所]の値を上記の関数によって評価した結果を新たな列として追加する
AddedConditionalColumn = Table.AddColumn(Source, "いずれかの除外ワードを含んでいる", each IsExcluded([コンテンツの場所]))
in
AddedConditionalColumn)
in
追加されたカスタム

エラーの原因を解消するアドバイスを頂けますか。
よろしくお願いいたします。

[Emizo1]

回答ありがとうございます。
試したところ問題が解決しました！
ベストアンサーに選ばせていただきました。

[Emizo1]

すみませさん、最後のコメントを見落としていました。実際にアクセスリストの他の列に関して別のテーブルの列の要素を判定しようと考えています。 IsExcluded で行なっている処理を切り出してカスタム関数とするやり方を教えて頂けますか。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
他のクエリ上でも同様の処理を実行する可能性があるならば、別途 IsExcluded で行なっている処理を切り出してカスタム関数として作成した上、任意のクエリ上から呼び出すようにされた方が使い勝手が良いと思います。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
よろしくお願いいたします。

[sk.exe]

> 実際にアクセスリストの他の列に関して別のテーブルの列の要素を判定しようと考えています。

それは既に本件とは別の質問になっていますので、出来れば本スレッドを解決済みにして新たにスレッドを作成し、テンプレートに則って「実現したいこと」や「発生している問題・分からないこと」を一から明記されることをお奨めします。

1つのスレッドの中で異なる複数の問題を取り上げ続けては収拾がつきませんので。