列内の改行コード等の印刷できない文字を一括で除去する方法(クリーン)[Power Query(パワークエリ)基礎]
AさんAさん

データ内の改行部分だけ邪魔なので、取り除きたいですが、これもPower Query(パワークエリ)で対応できるものですか?

森田森田

はい、対応できますよ!
その場合、Power Queryエディター上の「クリーン」というコマンドが有効ですよ!

具体的な使い方について解説していきますね。

解説動画:【パワークエリ#6】データクレンジング基本テクニック13選 - 元データを綺麗にするための不備修正の作業を自動化する

この記事の内容は下記の動画でも解説しています。
コメント欄の「クリーン」の時間の部分をクリックするとクリーンの解説へジャンプできますよ!

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

  • データ内の改行コードの除去作業を自動化したい方
  • データ整形・加工の工程が複数あり、かつその作業が定期的に発生する方
  • Power Query(パワークエリ)の使い方を習得したい方

前提条件

この記事のテクニックを使うためには、以下の条件を満たす必要があります。

  • ExcelのバージョンはExcel2010以降(Microsoft365含む)が必須
  • Excel2010/2013ユーザーの場合、事前にMicrosoft社公式HPよりPower Query(パワークエリ)のダウンロードが必要(Excel2016以降は標準機能)
  • Power Query(パワークエリ)の概要を理解していること
    →まず、以下の記事で概要を把握することをおすすめします。

元データ内に不要な改行コードがある場合に「クリーン」が有効

クリーンとは、改行コード等の印刷できない文字を取り除くことを指します。

ちなみに、改行コードとは、改行の指示を出しているコード(文字)のことです。

このコードは目には見えませんが、このコードの次の文字列が改行されます。

実際に、改行されているデータは、次のようなイメージです。

こうしたケースは、人の誤入力で改行コードが残ってしまうことが多いですが、他システムでエクスポートしたデータの仕様上、改行コードが入ってしまうこともあり得ます。

こうした場合、Excelワークシート上で値のクリーン作業を行う際は、CLEAN関数の利用が一般的です。

ただし、他のデータ整形作業とセットで行うなら、Power Query(パワークエリ)で行った方が、一連の作業手順を記録できて自動化できますよ!

では、Power Query(パワークエリ)でのクリーンの手順を確認していきましょう。

Power Query(パワークエリ)でのクリーン手順

今回の前提として、クリーンを行う対象テーブルはブック内にあり、事前にPower Queryエディターに取り込んでいる状態からスタートするとします。(ブック内のデータ取得について復習したい方は下記記事を参照)

まず、クリーン対象のデータが含まれる列を選択(①)しましょう。

これは、通常のワークシート上の操作と同じ感覚で問題ありません。

次に、リボン「変換」タブをクリック(②)し、「書式」をクリック(③)してください。

最後に、「クリーン」をクリック(④)します。

「プレビュー」ウィンドウ上で改行コードが除去されたことを確認できたらOK

「プレビュー」ウィンドウを見ると、無事「氏名」の全データの改行コードが除去されていますね!

あとは、その他の処理を行い、任意の方法でデータを出力すれば完了です。

【参考】クリーン内容を変更したい場合は

もし、あとでクリーンした内容や対象データを変更したい場合は、「クエリの設定」ウィンドウの該当のステップを一旦「×」で削除し、改めてクリーン処理を行えばOKです。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_PowerQueryデータ整形_クリーン.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ファイルを開いたら、クリーンを行う前に、Power Queryエディターを起動させてくださいね。
ブック起動時、「セキュリティの警告」メッセージが表示された場合は「コンテンツの有効化」をクリックしてください。

  1. リボン「データ」タブをクリック
  2. 「クエリと接続」をクリック
  3. 「社員マスタ(クリーン)」クエリをダブルクリック

ここまで準備ができたら、次の手順を実施してください。(今までの解説のまとめです)

  1. クリーン対象のデータが含まれる列を選択
  2. リボン「変換」タブをクリック
  3. 「書式」をクリック
  4. 「クリーン」をクリック
  5. OK」ボタンをクリック

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

クリーンはデータ整形作業の中でもオーソドックスな処理のひとつです。

特に、エクスポートするシステムの仕様上、改行コードが入ってしまう元データを扱う機会がある方は習得しておくと便利でしょう。

なお、Power Query(パワークエリ)の各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。


ご参考になれば幸いですm(_ _)m

森田森田

改行コードの取り扱いは、人によって頻度は変わりますが、前回のトリミングとセットで覚えておくと応用の範囲が広がります。

処理自体は難しくないので、この機会にしっかり覚えておくことをおすすめします。