前回の記事では「区切り記号が1種類」の基本的な分割方法をご紹介しました。しかし、実務で扱うデータはそう綺麗にはいきません。
- ある行はカンマ区切り、別の行はセミコロン区切り
- 『/』や『-』が混在している
- 全角スペースと半角スペースがバラバラに入っている
このように区切り記号が複数種類混在している場合、標準の「列の分割」ボタンでは一度に処理できず、置換を繰り返すなどの手間が発生します。
そこで今回は、Power Queryの関数Text.SplitAnyを使用した、複数の区切り記号を一発で分割するテクニックを解説します。あわせて、不要な文字の除去や空白データの処理など、より実践的なクリーニング手法もご紹介します。
サンプルデータ
今回は、入力ルールが統一されておらず、様々な記号で区切られた「タグ」列を持つデータを例にします。
【データの課題】
- 区切り記号がバラバラ: カンマ(
,)、セミコロン(;)、スラッシュ(/)、ハイフン(-)が混在しています。 - 連続する記号: ID3にはカンマが2つ連続(
,,)しており、分割すると間に「空のデータ」が発生します。 - スペースの混入: ID3の「有線, ゲーミング」のように、記号の後にスペースが含まれています。
これらを綺麗に分割し、1行1個の形式に整形します。
サンプルデータはこちらからダウンロードしてください。
実際の手順：カスタム列でText.SplitAnyを使う
標準機能の「列の分割」では1種類の記号しか指定できません。そのため、今回はカスタム列を作成し、M言語の関数を使って処理を行います。
手順1：カスタム列の追加
Power Query エディターを開き、「列の追加」タブー「カスタム列」をクリックします。
「カスタム列」の設定画面が表示されます。次の設定を行います。
- 新しい列名:「分割後」を入力します。※分かりやすい名前であればなんでもOKです
- カスタム列の式：後述の数式を入力します。
- 設定後、「OK」をクリックします。
数式は次の通りです。
Text.SplitAny([タグ情報], ",;/ -")【関数の解説】
- Text.SplitAny(テキスト, “区切り文字群”)第2引数に指定したダブルクォーテーション内のどの1文字にヒットしても分割を行います。上記の例では、「カンマ」「セミコロン」「スラッシュ」「ハイフン」「スペース」のいずれかが来たら分割する、という指示になります。
新しい列が追加されました。新しい列には「List」という文字が表示されます。
1つのセルに複数の値が含まれているリスト形式になっているため、次の手順でリストを展開します。
手順2：リストの展開
列ヘッダーの右側にある矢印アイコンをクリックし、「新しい行に展開する」をクリックします。
これで、複数の記号で区切られたデータが縦方向（行）に展開されました。
手順3：空要素の除去（連続する区切り記号への対処）
サンプルデータのID3「ゲーミング,, 光る」のように記号が連続している場合、その間にある「何もない文字」も1つのデータとして分割されてしまいます。
展開後の列に空白（空の文字列）が含まれているはずです。空要素を削除しましょう。
展開後の列のフィルターボタン「▼」をクリックし、「空の削除」をクリックします。
空要素が削除されました。
手順4：データ加工の結果確認
データ加工した結果を確認します。「ホーム」タブー「閉じて読み込む」をクリックします。
区切り文字ごとに縦に分割されたExcelの表が出力されていれば完了です。
補足と注意点：データのクレンジング
Text.SplitAnyは強力ですが、強力すぎるがゆえに「不要な空白」などを生み出しやすい性質があります。ここからは、実務で必須となるクレンジング処理を解説します。
数式を使った空要素の除去
フィルターボタン「▼」の「空の削除」を使って、空要素を削除する手順を紹介しました。
より高度な方法として、カスタム列の段階で空要素を除去することも可能です。その場合はList.Select関数を組み合わせます。
List.Select(
Text.SplitAny([タグ情報], ",;/ -"),
each _ <> ""
)不要文字の除去（Text.Remove）
分割する前に、データにとってノイズとなる文字（例えばカッコ[]や、意味のない記号）を消しておきたい場合は、Text.Remove関数が便利です。
例えば、分割処理の前に特定の記号を消したい場合、以下のように記述できます。
Text.Remove([タグ情報], {"[", "]", "*"})これを応用し、「まずノイズを除去してから、分割する」という処理を1つの式で書くと以下のようになります。
Text.SplitAny(
Text.Remove([タグ情報], {"[", "]"}),
",;/ -"
)全角と半角は別の文字として認識される
全角と半角の区別Power Queryは全角と半角を別の文字として扱います。区切り文字群には、必要に応じて全角記号（，や；）も追加してください。
Text.SplitAny([タグ情報], ",;/ -，；")「スペース」を区切り記号に入れる場合の注意点
Text.SplitAnyの区切り文字群に「スペース」を含めると、氏名「佐藤 太郎」のようなデータも「佐藤」と「太郎」に分割されてしまいます。
意味のあるスペースまで分割したくない場合は、区切り文字群からスペースを外し、分割後に「トリミング」を行うのが安全です。
まとめ
区切り記号が複数混在するデータは、標準ボタンだけでは太刀打ちできませんが、Text.SplitAnyを知っていれば一瞬で解決できます。
Text.SplitAny: 複数の区切り文字を指定してリスト化する。Text.Remove: 不要なノイズ文字を事前に削除する。List.Select: 分割によって生じた空のデータをリスト段階で除去する。- リストの展開: 縦（行）方向にデータを正規化する。
これらのM関数を組み合わせることで、どんなに汚いデータでも、分析可能な美しいテーブルに整形することができます。
