PowerQueryでワイルドカードを利用した置換の代替方法を紹介します。
Excelでよく利用されるアスタリスク(*)や半角疑問符(?)などのワイルドカードはパワークエリでは利用できません。
そこで、Text.StartWith関数やText.EndWith関数などを用いてワイルドカード置換と同様の処理ができる方法を紹介します。
ワイルドカード(*)置換の代替方法
ワイルドカードであるアスタリスク(*)を用いた置換は下記の4つのパターンで使用されます。
- A*：Aから始まる文字列
- *A：Aで終わる文字列
- A：Aが含まれる文字列
- A*A：Aで始まりAで終わる文字列
ドノ例えば、「*本」とした場合は絵本、漫画本、日本、参考本などが対象となります。
PowerQueryで上記のそれぞれのパターンを実行できる方法を解説します。
特定の文字から始まる
まずは特定の文字から始まる文字列の置換についてです。
特定文字から始まる文字列の置換はText.StartsWith関数を使った下記の式を利用します。
Table.ReplaceValue(
テーブル名,
each if Text.StartsWith([列名], “特定文字列“) then [列名] else false,
each “置換後の文字列“,
Replacer.ReplaceText,
{“列名“})
オレンジ色の部分を自由に変更して利用してください。
テーブル名：参照テーブル名列名：置換を行いたいデータ列名特定文字列：始まりの指定文字置換後の文字列：置換後の文字
置換手順
それでは例として下図のデータの「Aから始まる文字列」を「Aグループ」と置換します。
ステップを右クリックして「後にステップの挿入」を選択して、新しいステップを追加する。
新しいステップで数式バーに下記の式を入力する。
テーブル名は変更された型、列名はText、特定文字列はA、置換後の文字列はAグループとしています。
Table.ReplaceValue(
変更された型,
each if Text.StartsWith([Text], "A") then [Text] else false,
each "Aグループ",
Replacer.ReplaceText,{"Text"})
下図のようにAで始まる文字列が置換されています。
置換後のデータを新しい列として取得したい場合はカスタム列で下記の式を利用してください。
if Text.StartsWith([列名], “特定文字列“) then “置換後の文字列” else [列名]
列名には置換を行いたいデータ列、特定文字列には始まりの指定文字、置換後の文字列は置換後の文字をそれぞれ設定してください
特定の文字で終わる
次は特定の文字で終わる文字列の置換についてです。
特定文字で終わる文字列の置換はText.EndsWith関数を使った下記の式を利用します。
Table.ReplaceValue(
テーブル名,
each if Text.EndsWith([列名], “特定文字列“) then [列名] else false,
each “置換後の文字列“,
Replacer.ReplaceText,
{“列名“})
オレンジ色の部分を自由に変更して利用してください。
テーブル名：参照テーブル名列名：置換を行いたいデータ列名特定文字列：終わりの指定文字置換後の文字列：置換後の文字
置換手順
それでは例として下図のデータの「Dで終わる文字列」を「Dグループ」と置換します。
ステップを右クリックして「後にステップの挿入」を選択して、新しいステップを追加する。
新しいステップで数式バーに下記の式を入力する。
テーブル名は変更された型、列名はText、特定文字列はD、置換後の文字列はDグループとしています。
Table.ReplaceValue(
変更された型,
each if Text.EndsWith([Text], "D") then [Text] else false,
each "Dグループ",
Replacer.ReplaceText,{"Text"})
下図のようにDで終わる文字列が置換されています。
置換後のデータを新しい列として取得したい場合はカスタム列で下記の式を利用してください。
if Text.EndsWith([列名], “特定文字列“) then “置換後の文字列” else [列名]
列名には置換を行いたいデータ列、特定文字列に終わりの指定文字、置換後の文字列は置換後の文字をそれぞれ設定してください
特定の文字を含む
次は特定の文字を含む文字列の置換についてです。
特定文字を含む文字列の置換はText.Contains関数を使った下記の式を利用します。
Table.ReplaceValue(
テーブル名,
each if Text.Contains([列名], “特定文字列“) then [列名] else false,
each “置換後の文字列“,
Replacer.ReplaceText,
{“列名“})
オレンジ色の部分を自由に変更して利用してください。
テーブル名：参照テーブル名列名：置換を行いたいデータ列名特定文字列：置換したい文字列に含まれる文字置換後の文字列：置換後の文字
置換手順
それでは例として下図のデータの「BCが含まれる文字列」を「BCグループ」と置換します。
ステップを右クリックして「後にステップの挿入」を選択して、新しいステップを追加する。
新しいステップで数式バーに下記の式を入力する。
テーブル名は変更された型、列名はText、特定文字列はBC、置換後の文字列はBCグループとしています。
Table.ReplaceValue(
変更された型,
each if Text.Contains([Text], "BC") then [Text] else false,
each "BCグループ",
Replacer.ReplaceText,{"Text"})
下図のようにBCが含まれる文字列が置換されています。
置換後のデータを新しい列として取得したい場合はカスタム列で下記の式を利用してください。
if Text.Contains([列名], “特定文字列“) then “置換後の文字列” else [列名]
列名には置換を行いたいデータ列、特定文字列に含まれる指定文字、置換後の文字列は置換後の文字をそれぞれ設定してください
特定の文字で始まり特定の文字で終わる
次は特定の文字で始まり、特定の文字で終わる文字列の置換についてです。
特定の文字で始まり特定の文字で終わる文字列の置換はText.StartsWith関数とText.EndsWith関数を組み合わせた下記の式を利用します。
Table.ReplaceValue(
テーブル名,
each if Text.StartsWith([列名], “始まり文字“) and Text.EndsWith([列名], “終わり文字“) then [列名] else false,
each “置換後の文字列“,
Replacer.ReplaceText,
{“列名“})
オレンジ色の部分を自由に変更して利用してください。
テーブル名：参照テーブル名列名：置換を行いたいデータ列名始まり文字：始まりの指定文字終わり文字：終わりの指定文字置換後の文字列：置換後の文字
置換手順
それでは例として下図のデータの「Aで始まりDで終わる文字列」を「ADグループ」と置換します。
ステップを右クリックして「後にステップの挿入」を選択して、新しいステップを追加する。
新しいステップで数式バーに下記の式を入力する。
テーブル名は変更された型、列名はText、始まり文字はA、終わり文字はD、置換後の文字列は
ADグループとしています。
Table.ReplaceValue(
変更された型,
each if Text.StartsWith([Text], "A") and Text.EndsWith([Text], "D") then [Text] else false,
each "ADグループ",
Replacer.ReplaceText,{"Text"})
下図のようにAで始まりDで終わる文字列が置換されています。
置換後のデータを新しい列として取得したい場合はカスタム列で下記の式を利用してください。
if Text.StartsWith([列名], “始まり文字“) and Text.EndsWith([列名], “終わり文字“)then “置換後の文字列” else [列名]
列名には置換を行いたいデータ列、始まり文字に始まりの指定文字、終わり文字に終わりの指定文字、置換後の文字列は置換後の文字をそれぞれ設定してください
ワイルドカード(?)置換の代替方法
Excelのワイルドカードの半角疑問符（?）は1つに対し1文字の代わりとして機能します。
アスタリスク(*)が複数文字にも対応していたのに対して半角疑問符(?)は1文字対応なので、?を使うことで文字数を指定した置換を行うことができます。
処理方法は簡単で、上記で解説してきたワイルドカード置換式に文字数の条件を追加すればOKです。
ドノ例えば、A???というAから始まる4文字の単語を置換したい場合は「Aから始まる」という条件と「文字数は4文字」という条件を組み合わせることで処理を再現できます。
利用する式
それぞれの条件で利用する式を紹介します。
特定の文字から始まる+文字数指定
Text.StartsWith関数とText.Length関数を使った下記の式を利用します。オレンジ色の部分を自由に変更して利用してください。
Table.ReplaceValue(
テーブル名,
each if Text.StartsWith([列名], “特定文字列“) and Text.Length([列名])=文字数 then [列名] else false,
each “置換後の文字列“,
Replacer.ReplaceText,
{“列名“})
テーブル名：参照テーブル名列名：置換を行いたいデータ列名特定文字列：始まりの指定文字文字数：置換したい文字列の文字数置換後の文字列：置換後の文字
特定の文字で終わる＋文字数指定
Text.EndsWith関数とText.Length関数を使った下記の式を利用します。オレンジ色の部分を自由に変更して利用してください。
Table.ReplaceValue(
テーブル名,
each if Text.EndsWith([列名], “特定文字列“) and Text.Length([列名])=文字数 then [列名] else false,
each “置換後の文字列“,
Replacer.ReplaceText,
{“列名“})
テーブル名：参照テーブル名列名：置換を行いたいデータ列名特定文字列：終わりの指定文字文字数：置換したい文字列の文字数置換後の文字列：置換後の文字
特定の文字を含む＋文字数指定
Text.Contains関数とText.Length関数を使った下記の式を利用します。オレンジ色の部分を自由に変更して利用してください。
Table.ReplaceValue(
テーブル名,
each if Text.Contains([列名], “特定文字列“) and Text.Length([列名])=文字数 then [列名] else false,
each “置換後の文字列“,
Replacer.ReplaceText,
{“列名“})
テーブル名：参照テーブル名列名：置換を行いたいデータ列名特定文字列：置換したい文字列に含まれる文字文字数：置換したい文字列の文字数置換後の文字列：置換後の文字
特定文字で始まり特定文字で終わる＋文字数指定
Text.StartsWith関数とText.EndsWith関数とText.Length関数を使った下記の式を利用します。オレンジ色の部分を自由に変更して利用してください。
Table.ReplaceValue(
テーブル名,
each if Text.StartsWith([列名], “始まり文字“) and Text.EndsWith([列名], “終わり文字“) and Text.Length([列名])=文字数 then [列名] else false,
each “置換後の文字列“,
Replacer.ReplaceText,
{“列名“})
テーブル名：参照テーブル名列名：置換を行いたいデータ列名始まり文字：始まりの指定文字終わり文字：終わりの指定文字文字数：置換したい文字列の文字数置換後の文字列：置換後の文字
置換手順
それでは置換手順の説明例として下図のデータの「Aから始まる4文字の文字列（A???）」を「Aグループ」と置換します。
ステップを右クリックして「後にステップの挿入」を選択して、新しいステップを追加する。
新しいステップで数式バーに下記の式を入力する。
テーブル名は変更された型、列名はText、特定文字列はA、文字数は4、置換後の文字列はAグループとしています。
Table.ReplaceValue(
変更された型,
each if Text.StartsWith([Text], "A") and Text.Length([Text])=4 then [Text] else false,
each "Aグループ",
Replacer.ReplaceText,{"Text"})
下図のようにAから始まる4文字の文字列が置換されています。
パワークエリの学習本についてはコチラの記事でまとめてますので参考にしてみてください。
・初心者向けおすすめ学習本
・初中級者向けおすすめ学習本
コメント