PowerQuery 置換リストにより複数の文字列を一括置換する

PowerQuery（パワークエリ）で置換リストを用いて複数の文字列を一括置換する方法ついて紹介します。部分一致による置換と完全一致による置換の2種類の方法を解説しています。

複数文字列の置換（完全一致）

複数の文字列を完全一致で置換する方法を解説します。概要手順と利用する関数式は以下の通りです（利用する関数式以外の手順は部分一致の場合と同じです）

【概要手順】

1. 複数文字列の置換用テーブルを作成する。
2. 置換用テーブルから置換リストを作成する。
3. 置換を行うテーブルにインデックス列を追加する。
4. カスタム列でList.ReplaceMatchingItems関数を用いた式を入力して置換後の列を作成する。

【置換式（部分一致）】

List.ReplaceMatchingItems(テーブル名[列名],置換リスト){[インデックス列名]})

※テーブル名、列名、インデックス列名、置換リストは状況に応じて異なる値が入ります。

【使用列】

STEP

データ準備

置換リストによる複数文字列の置換の例として、下図の「A」「B」「C」を「あ」「い」「う」に置換します。今回は完全一致による置換でセル内にA,B,Cのみが入力されている場合にのみ置換を行います。

STEP

置換用のテーブルを作成する

Excel上に置換前の文字列と置換後の文字列をまとめたテーブルを作成して、PowerQueryエディターに読み込みます。

STEP

置換用のリストを作成する

変換用テーブルから変換用リストを作成します。

1. カスタム列をクリックして新しい列を追加する。
2. 下記式を入力して置換用リストの列を作成する。

={[置換前の列], [置換後の列]}

3. 作成したデータ列をクリックして選択する。
4. 変換タブにある「リストに変換」を選択して置換リストを作成する。

これで下図のように列名変換用が作成されます。

Listの中身

セルのListの中身は下図のように変更前と変更後のデータのリストデータが格納されています。

STEP

インデックス列を追加する

置換を行うテーブルを開いて、列の追加タブの「インデックス列」をクリックして列を追加する。インデックスは０始まりとします。

STEP

置換する列を作成する

1. カスタム列をクリックして新しい列を追加する。
2. 下記式を入力して置換リストの複数文字列を置換した列を作成する。

List.ReplaceMatchingItems(追加されたインデックス[列1],置換リスト){[インデックス]}

STEP

複数文字列の置換完了

以上で複数文字列の置換の完了です。下図のように値が「A」「B」「C」のみのセルが「あ」「い」「う」に置換されています。

Sponsored link

複数の1文字の置換

置換リストを用いて文字列中の複数の文字を一括置換する方法を解説します。概要手順と利用する関数式は以下の通りです。

【概要手順】

1. 複数文字の置換用テーブルを作成する。
2. 置換用テーブルから置換リストを作成する。
3. 置換を行うテーブルにインデックス列を追加する。
4. カスタム列でList.ReplaceMatchingItems関数を用いた式を入力して置換後の列を作成する。

【置換式（部分一致）】

Text.Combine(List.ReplaceMatchingItems(Text.ToList(テーブル名[列名]{[インデックス列名]}),置換リスト)))

※テーブル名、列名、インデックス列名、置換リストは状況に応じて異なる値が入ります。

【使用列】

STEP

データ準備

置換リストによる複数文字の置換の例として、下図の「A」「B」「C」をそれぞれ「あ」「い」「う」に置換します。

STEP

置換用のテーブルを作成する

Excel上に置換前の文字列と置換後の文字列をまとめたテーブルを作成して、PowerQueryエディターに読み込みます。

STEP

置換用のリストを作成する

変換用テーブルから変換用リストを作成します。

1. カスタム列をクリックして新しい列を追加する。
2. 下記式を入力して置換用リストの列を作成する。

={[置換前の列], [置換後の列]}

3. 作成したデータ列をクリックして選択する。
4. 変換タブにある「リストに変換」を選択して置換リストを作成する。

これで下図のように列名変換用が作成されます。

Listの中身

セルのListの中身は下図のように変更前と変更後のデータのリストデータが格納されています。

STEP

インデックス列を追加する

置換を行うテーブルを開いて、列の追加タブの「インデックス列」をクリックして列を追加する。インデックスは０始まりとします。

STEP

置換する列を作成する

1. カスタム列をクリックして新しい列を追加する。
2. 下記式を入力して置換リストの複数文字を置換した列を作成する。

Text.Combine(List.ReplaceMatchingItems(Text.ToList(追加されたインデックス[列1]{[インデックス]}),置換リスト))

STEP

複数文字列の置換完了

以上で複数文字の置換の完了です。下図のように「A」「B」「C」が「あ」「い」「う」に置換されています。

Sponsored link

置換文字列を動的に変更する

上述の2つの方法で文字列置換を行った場合、置換リストを変更することで置換文字列を後で変更することができます。
下記の手順で変更および更新を行います。

1. 置換用リストの元テーブルのデータを変更する。
2. 置換をしているテーブルのクエリを更新する。

例として置換リストのデータを「a」「b」「c」と変更すると下図のように変わります。

Sponsored link

まとめ

今回は置換リストを用いて複数の文字列を一括置換する方法ついて紹介しました。概要手順と利用する式は以下の通りです。

1. 複数文字列の置換用テーブルを作成する。
2. 置換用テーブルから置換リストを作成する。
3. 置換を行うテーブルにインデックス列を追加する。
4. カスタム列でList.ReplaceMatchingItems関数を用いた式を入力して置換後の列を作成する。

Text.Combine(List.ReplaceMatchingItems(Text.ToList(テーブル名[列名]{[インデックス列名]}),置換リスト)))

List.ReplaceMatchingItems(テーブル名[列名],置換リスト){[インデックス列名]})