置換リストの作成

パワークエリを使って、表の中の複数の名称を置換する方法を紹介します。

置換するためには、List.Accumlate関数を使用します。

次の例では、「取引先マスタ」にある株式会社の略名を全て「株式会社」に置換します。

変換テーブルの作成
クエリの作成
パフォーマンスの向上

変換テーブルの作成

まずは、置換に必要な変換テーブルを作成します。

テーブル名は「変換テーブル」として、パワークエリエディタに取り込みます。

クエリの作成

新しいクエリで「空のクエリ」を作成します。

クエリに「取引先マスタ」と名前を付けて詳細エディタを開きます。

詳細エディタ内に以下の文を記入し、「完了」をクリックします。

let    
    ソース = Excel.CurrentWorkbook(){[Name="取引先マスタ"]}[Content],
    追加されたカスタム = Table.AddColumn(ソース, "取引先名（変換後）",
     each List.Accumulate(Table.ToRows(変換テーブル),[取引先名],
     (accumulater, current)=>Text.Replace(accumulater,current{0},current{1})))
in
    追加されたカスタム

エディターを確認すると、取引先名（変換後）という列が追加されて、全て「株式会社」に変換されました。

解説

①Table.AddColumn(テーブル,列名,処理)
新しい列を追加する関数です。

② List.Accumulate(リスト,処理対象の列,処理）
リストに対して、繰り返し（ループ）処理を行います。参照先がテーブル型の場合は、リスト型に変換する必要があります。
例では、「変換テーブル」に対してTable.ToRows関数を使い、リスト型に変換してます。

③Text.Replace（テキスト,置換したい文字,置換後の文字）
文字を置換します。カスタム関数を使用して、変換テーブルから値を参照しています。

つまり、
③で該当した行のテキストを置換する。
②でその処理を全行で繰り返す。
①でその結果を新しい列で表示する。

といった動きになっています。

【参考】Table.ToRows関数の中身

パフォーマンスの向上

List.Accumulate関数で置換表を作成して、そのデータ量が多い場合、処理速度がとても遅くなることがあります。

この問題を解決するためには、「 Table.Buffer 」関数を使用します。

先ほどの式に対して、次のように作り変えると、パフォーマンスが向上します。

let
    テーブルBF　= Table.Buffer(変換テーブル),
    
    ソース = Excel.CurrentWorkbook(){[Name="取引先マスタ"]}[Content],
    追加されたカスタム = Table.AddColumn(ソース, "取引先名（変換後）",
        each List.Accumulate(Table.ToRows(テーブルBF),[取引先名],
        (accumulater, current)=>Text.Replace(accumulater,current{0},current{1})))
in
    追加されたカスタム