この隠れた Excel ツールは、データを自動的に迅速にクリーンアップします。

Posted on 10 4月 12:33 am

M は、Power Query をバックグラウンドで実行する強力な数式言語です。複雑なコードのように見えるかもしれませんが、M の一般的なタスクの多くは驚くほど初心者に優しく、データのクリーンアップを迅速に自動化できます。基本的な Excel 関数を作成できる場合は、最も重いデータセットを処理するための M の学習を開始できます。

多くの人はリボン上のボタンを使用して Power Query エディターを開始しますが、それは問題ありません。ただし、この方法では長いクリックマラソンを強いられます。 M を使用すると、複数の変換を 1 つのステップに結合できるため、管理、再利用、保守が容易になり、煩雑な作業を省くことができます。

M 言語: 始め方

Power Query インターフェイスについてよく理解する

Excel テーブルの列 A には乱雑な注文 ID、列 B には一貫性のない大文字の名前、C 列には 1 つのセルが空白の領域が含まれています。

上記のデータセットが送信されたところだと想像してください。混乱を招く注文 ID、一貫性のない大文字の名前、不完全な領域が混在しています。

このガイドを読み進めるには、例で使用されている Excel ワークブックの無料コピーをダウンロードしてください。リンクをクリックすると、画面の右上隅にダウンロードボタンが表示されます。

M を使用して問題を修正するには、まず環境を準備する必要があります。データセットがすでにという名前の Excel テーブルとしてフォーマットされていると仮定します。 T_販売データ、次の手順に従います。

選択 任意の細胞 あなたのテーブルにも、 データ タブ、クリック テーブル/範囲から。
一度 パワークエリエディタ データグリッドの上に白いバーが表示されていることを確認します ( 数式バー）が表示されます。そうでない場合は、 ビュー タブを押してチェックを入れる 数式バー。
を見つけます。 FX 数式バーの左側にあるボタン。このアイコンをクリックすると、 カスタムステップ コードを入力または貼り付けることができます。

スニペット 1: 乱雑な名前の大文字と小文字を標準化する

Text.Proper を使用して大文字を適切に修正する

狙い： の名前を修正するには 顧客名 すべて大文字、すべて小文字、またはランダムに混合して各単語の最初の文字のみを大文字にする列。

をクリックします。 FX 数式バーの左側にあるアイコンをクリックして、新しいステップを作成します。次に、すべてを置き換えます 数式バー 次のコードを入力して押します入力:

= Table.TransformColumns(#"Changed Type", {{"CustomerName", each Text.Proper(_), type text}})

Power Query は、ステップに次のような一般的な名前を割り当てます。 カスタム1。この一般的なステップ名を右クリックします。 適用されたステップ ペインをクリックして 名前の変更 次のようなよりわかりやすい名前を付けます フィックスケーシング。

この最初のスニペットについては、以下の表でその完全な構造を詳しく説明します。残りの 3 つのスニペットについては、全体的なパターンは同じであるため、新しい部分または変更された部分のみに焦点を当てます。

要素	説明
Table.TransformColumns(…)	既存の列を変更するために使用される主な関数。続く括弧は関数の引数を「ラップ」します。
#”変更されたタイプ”	[適用されたステップ]リスト内の直前のステップを参照します。各ステップでは、次にどのバージョンのテーブルを処理するかを認識する必要があります。
中括弧: { }	リストを表します。複数の列を一度に変換できるため、M では命令をこれらの中括弧内に含める必要があります。
「顧客名」	変更する特定の列を識別します。二重引用符は名前をリテラルテキストとして定義します。別の名前を入力するだけで、同じ手順を別の列に適用できます。
テキストを入力する	列を特定のデータ型に強制します。これにより、Excel が名前を数値または日付と誤認識するのを防ぎます。
それぞれ	列内の個々の行ごとに次のロジックを繰り返すように Power Query に指示するキーワード。
テキスト.適切	適用されている特定の機能。この場合、テキストをタイトル大文字に変換します。すべて大文字にしたい場合は、これを Text.Upper に置き換えます。
アンダースコア: _	「このセルの現在の値」を表すプレースホルダー。

標準の Excel 数式とは異なり、M 言語では大文字と小文字が区別されます。 Table.TransformColumns の代わりに table.transformcolumns を記述するとエラーが発生するため、大文字と小文字の使用には細心の注意を払ってください。

スニペット 2: 目に見えないフォーマットエラーを排除する

列のレイアウトをクリーンアップする

狙い： 先頭のスペースと改行を削除するには 顧客名 カラム。重要なのは、「jOHN (break) sMITH」が「JohnSmith」ではなく「John Smith」になるように、改行をスペースに置き換えることです。

先ほどと同じように、をクリックします FX、既存のテキストを以下のコードに置き換えて、を押します。入力:

= Table.TransformColumns(FixCasing, {{"CustomerName", each Text.Clean(Text.Trim(Text.Replace(_, "#(lf)", " "))), type text}})

括弧内の最初の要素は、[適用されたステップ]リストの前のステップの名前と常に一致する必要があることに注意してください。

ステップの名前を変更する クリーントリム。

各主要部分の機能は次のとおりです。

#(lf): これは、ラインフィード (Excel で Alt+Enter によって作成される非表示の文字、つまり改行) の省略形 M です。
Text.Replace(_, “#(lf)”, ” “): これにより、改行が検索され、スペースに置き換えられます。
テキスト.トリム: セルの先頭と末尾から余分なスペースを削除します。
テキスト.クリーン: 残りの非印刷制御文字 (非表示の ASCII 文字など) を削除します。

Text.Select による文字のフィルタリング

狙い： 数値以外のすべてを削除するには、 注文ID カラム。

クリック後 FX、以下のコードを貼り付けて押します入力:

= Table.TransformColumns(CleanTrim, {{"OrderID", each Text.Select(_, {"0".."9"}), type text}})

ステップの名前を変更する 数値の抽出。

Power Query で数字を除くすべての文字の ID コードを削除するために使用される M 言語。

ここで導入した新しい要素を見てみましょう。

テキスト選択: M に、リストした特定の文字のみを「選択」し、それ以外はすべて破棄するように指示します。
{“0”..”9″}: これは、許可される文字のリストを定義します。 2 つのピリオド (..) は「およびその間のすべて」を意味し、0 から 9 までのすべての数字が抽出されることを意味します。文字と記号はこのリストに含まれていないため、完全に破棄されます。

スニペット 4: 列を結合して「null」エラーを処理する

クリーンな ID 領域コードの作成

狙い： きれいになったものを組み合わせると、 注文ID そして地域単一のコード (101N など) に変換されます。領域が空 (null) の場合は、「null」という単語を表示せずに ID のみを表示し、元の列を削除してテーブルを無駄のない状態に保ちます。

クリック FXに次のコードを挿入して、を押します。入力:

= Table.SelectColumns(Table.AddColumn(ExtractNumbers, "ID", each (OrderID) & (if (Region) = null then "" else (Region))), {"ID", "CustomerName"})

ステップの名前を変更する MergeIDRegion。

Power Query で ID とリージョンを新しい ID 列にマージするために使用される M 言語。

このコードがどのように機能するかは次のとおりです。

if (地域) = null then “” else (地域): Power Query にリージョンを確認するように指示します。 null を検出した場合は、代わりに空の文字列 (“”) として処理します。これにより、103 と null 値を組み合わせると、結果は 103null ではなく 103 になります。
(注文ID) &…: スニペット 3 からクリーンな数値 ID を取得し、アンパサンドは領域テキストをその末尾に直接添付するように Power Query に指示します。
「ID」: 新しい結合列のヘッダー名を定義します。
{“ID”, “顧客名”}: ステートメントの最後にこれら 2 つの列のみをリストすることで、元の OrderID 列と Regional 列を破棄するように Power Query に指示することになります。

M 言語が強力な公式の集合体にすぎないことがわかると、恐怖要素は消えます。これら 4 つのスニペットは、合理化された管理しやすいコードを使用して、効率と精度を高め、クリック疲れを軽減するための最初のステップです。開発者のように考え始めたら、日常業務にさらに強力な機能を提供するツールである Excel での Python の使用に移行してみてはいかがでしょうか。