Power QueryでEC販売データをクリーンアップ！押さえておきたい5つの必須テクニック

ECサイトから書き出したデータは、ほとんどの場合が「汚い」状態です。1つのファイルの中に、同じ商品の複数SKUが別々の列に散らばっていたり、顧客情報の表記がバラバラだったり、住所が1つのセルにまとめられていたり、重複や空欄、価格情報の不備などが混在しています。こうしたデータをExcelやPower BIで分析しようとすると、あっという間に作業が複雑になってしまいます。そこで活躍するのがPower Queryです。ExcelでもPower BIでも同じ操作で、複雑な数式を書かずに生データを整形・変換できます。

このチュートリアルでは、EC販売データの分析に特に役立つ、Power Queryの必須変換テクニックを5つ紹介します。

1. SKUバリエーション列のピボット解除（Unpivot）

多くのECエクスポートファイルでは、注文された商品が「Stock_S」「SKU 1」「SKU 2」「Variant 1」のように複数の列に分けて格納され、それぞれに数量が入っています。この横持ち形式は分析に非常に不便です。各注文の商品情報が複数列にまたがるため、ピボットテーブルやDAXメジャー、SUM関数では正しく集計できません。ピボット解除を行うことで、縦持ちの正規化されたファクトテーブルに変換でき、分析に最適な形になります。

手順：

• データをPower Queryに読み込む

• SKU列（Stock S、M、L、XL）を選択する
• 変換タブ >> 列のピボット解除を選択

• Power QueryがSKU列を2つの新しい列に変換するので、名前を変更します：
  • 属性： サイズ（Size）
  • 値： 数量（Quantity）

注文商品が行ではなく列ごとに格納されているエクスポートデータを扱うときは、この変換を使いましょう。

2. 配送先住所の文字列を複数フィールドに分割

生のECデータの約90%は、配送先住所全体を1つの文字列として保存しています。このフィールドを分割することで、都道府県・州単位の税務レポート、配送コストの最適化、地域別の売上分析、地図へのプロットなどが可能になります。

手順：

• 配送先住所（Shipping Address）列を選択
• ホームタブ >> 列の分割 >> 区切り記号ごとを選択

• データに応じて、次のような区切り記号を使用します：
  • カンマ
  • ハイフン
  • 改行
  • カスタム区切り文字
• 今回のサンプルデータではカンマを選択
• OKをクリック

• 生成された列に、わかりやすい名前を付けます：番地（Street）、市区町村（City）、郵便番号（Postcode）、国（Country）

住所を分割すれば、都市別の注文分析、ゾーン別の配送パフォーマンス、地域別の売上、特定エリアからのリピーター顧客などを把握できるようになります。地域密着型の配送業態や、エリア別の売上レポート作成に特に有効です。

上級者向けポイント： すべての住所が同じ形式とは限りません。余計な項目が入っていたり、一部が欠けていたり、マンション名などの詳細が含まれる場合もあります。そのようなケースでは以下の方法が役立ちます：

• 分割後に余分なスペースをトリムする
• 必要な部分だけを結合し直す
• 区切り記号の前/後のテキスト抽出を使う
• 例外処理用のカスタム列を作成する

3. データのトリム・クリーン・データ型の標準化

大規模なECデータセットでは、顧客名、SKU、メールアドレス、商品カテゴリなどに、前後の余分な空白、印刷できない不可視文字、大文字小文字の不統一がよく見られます。また、数値がテキストとして保存されるなど、データ型の不一致も発生しがちです。

こうした小さな問題は重大なトラブルの原因になります。同じデータが別々の値として認識され、マージ操作が失敗したり、重複したグループが作られたりします。

データのクリーニング：

• 余分な空白のトリム：
  • テキスト列を選択
  • 変換 >> 形式 >> トリムを選択
  • 先頭と末尾の余分なスペースが削除されます

• 不可視文字の除去：
  • 同じ列を選択したまま、
  • 形式 >> クリーンを選択
  • 印刷できない文字が削除されます
• 大文字小文字の統一：
  • 変換 >> 形式 >> 以下から選択：
  • 氏名には各単語の先頭だけ大文字にする
  • 必要に応じてメールアドレスには小文字

データ型の標準化：

• 日付列を選択
• ホームタブ >> データ型 >> 日付を選択

• 必要であれば、日付要素の列を追加できます：
  • 列の追加タブ >> 日付を展開
  • 年、月、日、四半期、曜日などを選択

• 数量や価格などの数値列を選択
• 列ヘッダーのアイコンを展開 >> 10進数を選択
• または、変換タブ >> データ型 >> 10進数または整数を選択

この変換は非常に重要です。データ型が適切に揃っていれば、グループ化、マージ、フィルター、参照操作の信頼性が大幅に向上します。

4. 重複の削除とNull値の処理

ECデータではNull（空欄）は日常的に発生します。無害なものもあれば、計算を壊してしまうものもあります。決済ゲートウェイや同期処理によってOrderIDが重複することもあり、数量や価格のNullは合計値やビジュアルを破損させます。Nullを適切に処理しないと、結果が誤ったものになる恐れがあります。

重複の削除：

• OrderIDとOrderDateをキーとして選択（これにより、同日の正当な再注文が誤って削除されるのを防げます）
• ホームタブ >> 重複の削除を選択

• 本当に壊れた行だけを削除する場合：
  • ホームタブ >> 空白行の削除を選択（事前にOrderIDがNullの行をフィルターしておくのがおすすめ）

値の置換：

• 割引額が空欄なら「割引なし」を意味する場合は、Nullを0に置き換えます
• 数量と価格のすべての列を選択
• 変換タブ >> 値の置換を選択：
  • 検索する値：null
  • 置換後の値：0

テキスト値のクリーニング：

• Stock_のような不要な接頭辞を削除します
• サイズ（Size）列を選択
• 変換タブ >> 値の置換を選択：
  • 検索する値：Stock_
  • 置換後の値：（空欄のまま）

これでサイズの値がすっきりと読みやすくなりました。

繰り返し値の下方向へのフィル：

• 注文IDや顧客名が、注文の最初の行にしか入力されていないことがあります
• 該当する列を選択
• 変換 >> フィル >> 下を選択

重要な注意点： すべてのNullを盲目的に置き換えてはいけません。欠損値は「該当なし」「不明」、あるいはデータ自体の問題を示している場合があります。値を置き換える前に、必ずビジネス上の意味を理解しましょう。

5. 計算カスタム列の作成

ECデータからは、売上や利益率の列を派生させることができます。Excelの数式でも計算できますが、リフレッシュすると壊れてしまうのが難点です。一方、Power Queryのカスタム列は自動的に再計算され、ETLパイプラインの一部として組み込まれます。

純売上（Net_Revenue）：

• 列の追加タブ >> カスタム列を選択
• 列名を入力
• 次の数式を挿入

[Unit_Price] * [Qty] * (1 - [Discount_Pct])

粗利益（Gross_Margin）：

[Net_Revenue] - ([Cost_Per_Unit] * [Qty])

利益率（Margin_Pct）：%

if [Net_Revenue] = 0 then 0 else [Gross_Margin] / [Net_Revenue]

• データ型を明示的に設定します：Net_RevenueとGross_Marginは通貨、Margin_Pctはパーセンテージ

重要： 除算を含む数式には必ずガード（保護処理）を入れましょう。分母がゼロになると、Power Queryでは列全体にエラーが波及し、読み込み時に行が欠落する原因にもなります。計算比率にはif [X] = 0 then null else … のパターンを使いましょう。

6. グループ化と集計による高パフォーマンスなサマリーテーブル

数百万行ある生のECファイルは、レポートの動作を遅くします。グループ化して効率的な集計テーブルを作れば、ダッシュボードを軽量化しつつ、詳細クエリをドリルスルー分析用に残せます。

手順：

• 変換 >> グループ化を選択
• Categoryを選択 >> 集計の追加をクリック：
  • Net_Revenueの合計
  • Margin_Pctの平均
  • Order_IDの件数（行数）

• 新しいクエリにSales_Summaryと名前を付け、元のクエリはドリルスルー用にSales_Detailとして保持します

プロのコツ： Power BIでは、詳細クエリの「読み込みを有効にする」をオフにし、サマリーテーブルとディメンションテーブルだけを読み込むと、パフォーマンスが向上します。

最後のステップ：

• ホームタブ >> 閉じて適用するを選択してデータを読み込みます

まとめ

以上、EC販売データにすぐ使える5つの必須Power Query変換テクニックを紹介しました。Power Queryは、シンプルな変換から複雑な変換までを再現可能な形で処理できるため、ECデータのクレンジングに最も効果的なツールのひとつです。データ準備にかかる時間を大幅に短縮できます。ここで紹介したのは汎用的なお掃用手順ではなく、実際のECレポーティングの課題に直接対応する実践的なテクニックです。これらの変換に慣れてきたら、新しいマーケットプレイスのエクスポートファイルごとに使い回せる、再利用可能なPower Queryテンプレートを構築してみてください。