【PowerQuery】○○を含む行を抽出、含まない行を抽出を複数の条件でやりたい

Excelで関数やVBAは使っているものの、会社のパソコンのエクセルのバージョンは古いので、世間で騒がれている（？）新関数やパワークエリとやらはつかえないだろう、と思い手を出していなかった。

しかし、キャリア採用でやってきたパワークエリの伝道師A氏が「Excel2016でも使える」ということを教えてくれたので、使い始めた。

しかーし、今までやってきた関数などと感覚が違って、なにをどうすればいいのかわからない。関数ならこうやったこうやればすぐできるのに、ということもパワークエリでやるにはどうしたらいいのかさっぱり手も足も出ない。3カ月くらいジタバタしてある日急に「なんかわかった気がする」気がした。それ以来おもしろくなってあれこれやっている。でもやっぱり根本的に何か理解しきっていないような気がするので、あれこれ悩んだ勉強記録をここに記すことにしました。

題して「パワークエリ１年生の苦悩」シリーズ．．．

１．はじめに
２．なにをやるか
３．部分一致・含む行を抽出
４．関数を作ってみる
５．SelectRows内で関数を直接使用する
５．関数は使用せず、SelectRowsに直接入力する
７．最後に
８.参考文献、記事

１．はじめに

今回は、○○を含む行を抽出（または含まない行を抽出）というのをやってみます。

「氏名に、この語句を含む対象は除外する」という案件があり、「この語句」というのが10個くらいあった。それをリストにして抽出対象の氏名のテーブルとぶつけて除外って、パワークエリでできるのでは？と思ってやってみたら、案外難しくて苦悩したのでその記録を記します。

２．なにをやるか

商品テーブルから、対象リストの文字を含まない行だけを抽出したい。

３．部分一致・含む行を抽出

前項の「何をやるか」では含まない行を…といいましたが、話が難しくなるのでまずは「含む」からやっていきたいと思います。

（１）部分一致で含む行を抽出

まずは、マウスクリックでやる方法から

商品テーブルと対象リストテーブルをパワークエリに読み込んでおきます。

読み込みするテーブルを選択した状態で、リボン「データ」＞「テーブルまたは範囲から」

（２）フィルタ操作

パワークエリエディタを開いた状態です。

商品テーブルを選択します。（今回は対象リストは使用しません）

商品名の▼をクリックし、「テキストフィルタ」＞「指定の値を含む」を選択します。

二つしか指定する欄がありませんが「詳細設定」をクリックすると、さらに複数の条件を使いできるようになります。

「商品名」「指定の値を含む」「リンゴ」
「または」を選択し「商品名」「指定の値を含む」「バナナ」
「句の追加」ボタンを句リクして指定欄を増やし
「または」を選択し「商品名」「指定の値を含む」「メロン」

「OK」ボタンをクリックします。

（３）結果

「リンゴ」「バナナ」「メロン」を含む行が抽出されました。

（４）フィルターした時の数式

フィルタした時の数式を確認してみましょう。

= Table.SelectRows(変更された型, each Text.Contains([商品名], "リンゴ") or Text.Contains([商品名], "バナナ") or Text.Contains([商品名], "メロン"))

ふむふむ。行選択が Table.SelectRows で「含む」が Text.Contains ですね。　

（５）含まない行を抽出

数式 Text.Contains の前に not をつけて、or を and に変更すると「リンゴ、バナナ、メロン」を含まない行を抽出します。

= Table.SelectRows(変更された型, each not Text.Contains([商品名], "リンゴ") and not Text.Contains([商品名], "バナナ") and not Text.Contains([商品名], "メロン"))

４．関数を作ってみる

○○を含む行（含まない行）を抽出する方法はわかった。だけど、検査する対象が多い場合、ひとつずつ入力していくのはつらいしなんかかっこ悪い。リストかなんかにして、それとぶっつけてなんちゃら、という風にしたい。

文字列を含んでいたらTURE、含んでいなければFALSEを返す関数を作れば、関数をかけた後にTRUEのみフィルタと化すれば行けるのでは、と思いいろいろ調べてみたところ、この記事にたどり着いた。こちらを読んで勉強させていただきました。なんかできそうな気がする。ありがとうございます。
https://nandemo-xl.hatenablog.com/entry/2022/07/19/170951

（１）作りたい関数

①検査対象文字列を引数に取る
②文字列リストを関数内に用意しておく
③検査対象文字列に文字列リスト（一つずつ）を含むか評価
④含んでいる場合、Newリストに追加
⑤Newリストの要素が1以上 → Tureを返す
　Newリストの要素が0個 → Falseを返す

（２）使えそうな部品（M関数）

● Text.Contains
テキストに文字列が含まれているかどうか。含まれている場合Trueを返す。
例：Text.Contains(“リンゴ飴”,”リンゴ”)　→　true

● List.Select
リストから、条件がTrueになる要素を選択し、作成したリストを返す。
例：List.Select({1,2,3,4,5}, each _ > 2) → {3,4,5}

なお、List.Contains （リストに含まれているかどうか）が使えないか？と思ったのですが、部分一致はできないみたいなので、ダメか．．．と。

（３）関数を作成する

①対象リストをリストに変換しておきます。
「対象リスト」を選択し、リボン「変換」＞「リストに変換」をクリック
名前を「検索リスト」に変更しました。

②空のクエリを作成

リボン「ホーム」＞新しいソース＞空のクエリ　で新しいクエリを作成
「詳細エディター」をクリックして、コードを入力する画面を立ち上げます

③コード

以下のコードを入力しました。

(テキスト as text) =>
let
    新リスト = List.Select(検索リスト, each Text.Contains(テキスト, _ )),
    有無 = List.Count(新リスト) > 0 
in
    有無

(検索テキスト as text) =>
引数のテキストを受けとる部分です。

新リスト = List.Select(検索リスト, each Text.Contains(テキスト, _ )),

分解していくと、検索リストには先に作ったリストを指定していますので、以下の内容と同じことになります。
List.Select({“リンゴ”,”バナナ”,”メロン”}, each Text.Contains(テキスト, _ ))

リストから一つずつ取り出して、それぞれに（each）Text.Countains で引数の「テキスト」に含むかどうか検査しています。アンダーバーがなんじゃ？って感じですが、リストから取り出した要素を受けたもの、だそうです。

たとえば引数の文字列が「リンゴ飴」だったとすると
Text.Contains(“リンゴ飴”, “リンゴ” ) → 含む(ture)ので新リストに追加 → 新リスト{“リンゴ”}
Text.Contains(“リンゴ飴”, “バナナ” ) → 含まない(false)ので新リストに追加しない
Text.Contains(“リンゴ飴”, “メロン” ) → 含まない(false)ので新リストに追加しない
で、出来上がった新リストは {“リンゴ”}

有無 = List.Count(新リスト) > 0
List.Countで新リストの要素数を出す。新リストは{“リンゴ”}なので、この場合「１」を返す。
１ > 0 の判定は TREU なので有無には TRUE が入る。

で、TRUEを返すわけですな。

・・・たとえば引数の文字列が「イチゴ飴」だったとすると
Text.Contains(“イチゴ飴”, “リンゴ” ) → 含まない(false)ので新リストに追加しない
Text.Contains(“イチゴ飴”, “バナナ” ) → 含まない(false)ので新リストに追加しない
Text.Contains(“イチゴ飴”, “メロン” ) → 含まない(false)ので新リストに追加しない
で、出来上がった新リストは {}　空っぽ。

有無 = List.Count(新リスト) > 0
新リストは空っぽなので List.Count は 0 。
0 > 0 の判定は FALSE なので有無には FALSE が入る。

⑤関数名を「有無検査」としました。

⑥関数を使ってみましょう。
引数に「リンゴとバナナ」と入力します → TRUE
引数に「あいうえお」と入力します → FALSE

（４）関数を使って抽出する

「商品テーブル」のクエリを選択します。
ここから作成した関数を使ってリストにある文字列を含む行を抽出していきます。

①「列の追加」タブ＞「カスタム関数の呼び出し」
新しい列名は適当に入力
関数クエリは作成した関数「有無検査」を指定
テキスト（引数の部分）は「列名」を指定し「商品名」を指定
「OK」をクリック

②追加された列にTRUE、FALSEが出力されています。
含む行を抽出したいので「TRUE」でフィルタします。

リストの文字列を「含まない」行を抽出したい場合は「FALSE」でフィルタします。

５．SelectRows内で関数を直接使用する

４では列を追加し、カスタム関数を使用しましたが、SelectRowsの行で関数を使用してみたらどうでしょう。

「商品テーブル」のクエリの詳細エディターを開きます。以下のコードとなっています。

let
    ソース = Excel.CurrentWorkbook(){[Name="商品テーブル"]}[Content],
    呼び出されたカスタム関数 = Table.AddColumn(ソース, "有無検査", each 有無検査([商品名])),
    フィルターされた行 = Table.SelectRows(呼び出されたカスタム関数, each ([有無検査] = true))

in
    フィルターされた行

これを書き換えます。

let
    ソース = Excel.CurrentWorkbook(){[Name="商品テーブル"]}[Content],
    フィルターされた行 = Table.SelectRows(ソース, each 有無検査([商品名]))
in
    フィルターされた行

①呼び出しされたカスタム関数の行を削除します。
②フィルターされた行のステップの基とするステップ名「呼び出しされたカスタム関数」を「ソース」に書き換えます。
③Table.SelectRows の each ([有無検査] = true) 有無検査列がTRUEの場合、というところに有無検査の関数に書き換えます。
each ([有無検査] = true) → each 有無検査([商品名])

Table.SelectRows は true の場合、行を残し、false の場合行をセレクトしません。
なので、有無検査([商品名]) の結果が true の場合、行が残り、false の場合行が削除されます。

「含まない」にしたい場合は有無検査([商品名]) の前に not を置きます。
not を前に置くことで、ture false を反転します。関数の戻り値が ture の場合 false となり、false の場合 true になります。

//含まないの場合
let
    ソース = Excel.CurrentWorkbook(){[Name="商品テーブル"]}[Content],
    フィルターされた行 = Table.SelectRows(ソース, each not 有無検査([商品名]))
in
    フィルターされた行

５．関数は使用せず、SelectRowsに直接入力する

関数を作成しない場合

①変更前の関数を使った「商品テーブル」クエリの詳細エディタのコード

let
    ソース = Excel.CurrentWorkbook(){[Name="商品テーブル"]}[Content],
    フィルターされた行 = Table.SelectRows(ソース, each 有無検査([商品名]))
in
    フィルターされた行

②Table.SelectRowsのところに関数のコードを組み込む

let
    ソース = Excel.CurrentWorkbook(){[Name="商品テーブル"]}[Content],
    フィルターされた行 = Table.SelectRows(ソース, each
        let
            テキスト = _[商品名],
            新リスト = List.Select(検索リスト, each Text.Contains(テキスト, _ )),
            有無 = List.Count(新リスト) > 0 
        in
            有無
    )
in
    フィルターされた行

③解説

each 有無検査([商品名])
の関数の部分を書き換えます。

each のあとを見やすいように改行して、カスタム関数の中身をコピペしたものをはりつけして、なんやかんやします。

テキスト = _[商品名],
ソースの中から１行分のデータを借り受けしたものがアンダーバー(_)になります。
_[商品名]　は　ソースのとある１行のデータの[商品名]の列の値と読み替えできます。
つまり各行の商品名の列の値をテキストという変数（というのかな？）に入れています。

新リスト = List.Select(検索リスト, each Text.Contains(テキスト, _ )),
有無 = List.Count(新リスト) > 0
ここは先ほどの関数と同じ内容ですね。

検査リストの文字列を含んでいる場合 TRUE を返します。

結果が TRUE の場合 SelectRow されます。

これで同じ結果が得られます。

④別の書き方。

これらのコードは m.tanaka さんの「なんでもエクセル」ブログの記事を参考とさせていただいたものです。ありがとうございます。

let
    ソース = Excel.CurrentWorkbook(){[Name="商品テーブル"]}[Content],
    フィルターされた行 = Table.SelectRows(ソース, (t) => [
        新リスト = List.Select(検索リスト, each Text.Contains(t[商品名], _ )),
        有無 = List.Count(新リスト) > 0 
    ][有無])
in
    フィルターされた行

「なんでもエクセル」ブログの記事をみながら見よう見まねで書いてみた。むずかしくてよくわからないが、同じ結果が得られた。

⑤含まない行を抽出するには

②のコードを書き換えます。

trueの行が抽出されるわけだから、含まない行を抽出したい場合は
有無 = List.Count(新リスト) = 0
として0の場合をTRUEにする

let
    ソース = Excel.CurrentWorkbook(){[Name="商品テーブル"]}[Content],
    フィルターされた行 = Table.SelectRows(ソース, each
        let
            テキスト = _[商品名],
            新リスト = List.Select(検索リスト, each Text.Contains(テキスト, _ )),
            有無 = List.Count(新リスト) = 0 
        in
            有無
    )
in
    フィルターされた行

⑥検査語句リストが大量にある場合など、List.Buffer を使うと処理が速くなるかも

こちらも以下のブログの記事で勉強させていただいたものです
https://nandemo-xl.hatenablog.com/entry/2022/07/19/170951

let
    ソース = Excel.CurrentWorkbook(){[Name="商品テーブル"]}[Content],
    フィルターされた行 = Table.SelectRows(ソース, each
        let
            リストb = List.Buffer(検索リスト),
            テキスト = _[商品名],
            新リスト = List.Select(リストb, each Text.Contains(テキスト, _ )),
            有無 = List.Count(新リスト) > 0 
        in
            有無
    )
in
    フィルターされた行

対象が多い場合など、List.Bufferしたほうが、処理が速くなるとかならないとか。。。
リストをメモリにバッファーするそうです。

テーブルをリストに変換していない場合、
List.Buffer(テーブル名[列名])
で、指定したテーブルの指定した列がリストになり、さらにメモリにバッファーしてくれるようです。