E コマース販売データをクリーンアップするための 5 つの主要なパワー クエリ テクニックをマスターする

 

電子商取引の輸出データは、ほとんどの場合、混乱しています。 1 つのファイルには、複数の SKU バリエーション、一貫性のない顧客情報、結合された住所フィールド、重複または NULL 値、散在する価格設定の詳細が含まれる場合があります。 Excel や Power BI でそのようなデータを分析しようとしたことがあるなら、物事がいかに急速に複雑になるかをご存知でしょう。ここで Power Query が非常に役立ちます。 Excel や Power BI など、どのツールを使用しているとしても、Power Query を使用すると、複雑な数式を記述することなく、生の e コマース データをクリーンアップして再形成することができます。

このチュートリアルでは、特にオンライン ストアのデータセットに役立つ、e コマース販売データに対する 5 つの重要な Power Query 変換について学習します。

SKU バリエーション列のピボット解除

多くの電子商取引エクスポートでは、注文された商品が、Stock_S、SKU 1、SKU 2、SKU 3、Variant 1、Variant 2、Variant 3 などの複数の列にまたがって保存され、それぞれに数量が保持されます。各注文では商品の詳細が複数の列にまたがっているため、この構造を分析するのは困難です。ピボット テーブル、DAX メジャー、および SUM 式は、この幅広い形式にわたって効果的に集計できません。アンピボットにより、分析に最適な長く正規化されたファクト テーブルが作成されます。

手順:

• データを Power Query にロードする

• SKU 列を選択します (在庫 S、M、L、XL)
• 変換に移動します。 タブ>> 列のアンピボットを選択します

• Power Query は、これらの SKU 列を 2 つの新しい列に変換し、名前を変更します。
  • 属性: サイズ
  • 値: 数量

エクスポートで複数の注文商品を個別の行ではなく個別の列に保存する場合は、この変換を使用します。

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

未加工の電子商取引ファイルの約 90% には、配送先住所全体が 1 つの文字列で保存されています。このフィールドを分割すると、州レベルの税レポート、配送コストの最適化、地域のパフォーマンス分析、マッピングが可能になります。

手順:

• 配送先住所を選択します 列
• ホームに移動します タブ>> 列の分割を選択します。>>区切り文字によるを選択します

• データに基づいて、次のような区切り文字を使用します。
  • カンマ
  • ハイフン
  • 改行
  • カスタム区切り文字
• 既存のデータの場合は、カンマを選択します。 区切り文字として
• [OK] をクリックします。

• 結果の列の名前を意味のあるラベル（番地、都市、郵便番号、国）に変更します

住所を分割すると、都市ごとの注文、ゾーンごとの配送実績、地域ごとの売上、特定の場所からのリピート顧客を分析できます。これは、地元の配送業者や地域ベースの販売レポートに特に役立ちます。

事前の注意事項: すべてのアドレスが同じ形式に従っているわけではありません。一部には余分な部品、不足している部品、またはアパートの詳細がある場合があります。このような場合、次のことが可能です。

• 分割後にスペースをトリミングする
• 選択したパーツを結合して元に戻す
• 区切り文字の前後のテキストを抽出を使用する
• 例外処理用のカスタム列を作成する

データ型のトリミング、クリーニング、標準化

大規模な e コマース データセットでは、顧客名、SKU、電子メール アドレス、製品カテゴリに先頭または末尾のスペース、印刷不可能な文字、一貫性のない大文字が含まれることがよくあります。テキストとして保存された数値など、データ型が一致しない場合もあります。

これらの小さな問題は重大な問題を引き起こす可能性があります。同じデータが異なる値として表示されたり、マージ操作が失敗したり、重複したグループが作成されたりする可能性があります。

データのクリーニング:

• スペースをトリミングする:
  • テキスト列を選択します
  • 変換に移動します。>> 形式を選択します>>トリムを選択します
  • これにより、先頭と末尾から余分なスペースが削除されます

• 隠し文字を削除する:
  • 同じ列を選択した状態で
  • 形式を選択します>>クリーンを選択します
  • これにより、印刷不可能な文字が削除されます
• 大文字と小文字を標準化する:
  • 変換に移動します。>>フォーマットを選択します>> 選択してください:
  • 各単語を大文字にします 名前用
  • 小文字 必要に応じて電子メールを送信する

データ型の標準化:

• 日付列を選択
• ホームに移動します タブ>> データタイプを選択します>> 日付を選択してください

• 必要に応じて、列を追加します。
  • 列の追加に移動します。 タブ>> 日付を展開します
  • 年を選択してください 、月 、日 、四半期 、曜日

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

この変換により、グループ化、マージ、フィルタリング、検索操作の信頼性が大幅に向上するため、この変換は不可欠です。

重複の削除と Null 値の処理

電子商取引データではヌルがよく見られます。無害なものもあれば、計算を壊すものもあります。支払いゲートウェイと同期プロセスでは重複した OrderID が作成される可能性があり、数量や価格がヌルであると合計やビジュアルが壊れる可能性があります。 null が正しく処理されないと、結果が誤解を招く可能性があります。

重複の削除:

• 注文IDを選択します と注文日 キーとして使用 (これにより、正当な同日リピート注文が削除されなくなります)
• ホームに移動します タブ>> 重複の削除を選択します

• 本当に壊れた行のみを削除します:
  • ホームに移動します タブ>> 空白行の削除を選択します。 (最初に OrderID が null の行を除外します)

値の置換:

• 空白の割引が割引がないことを意味する場合は、null を 0 に置き換えます
• 数量と価格の列をすべて選択します
• 変換に移動します。 タブ>> 値の置換を選択します :
  • 検索する値:null
  • 次のように置き換えます:0

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

• Stock_ などの不要な接頭辞を削除します。
• サイズ列を選択します
• 変換に移動します。 タブ>> 値の置換を選択します :
  • 検索する値:Stock_
  • 次のように置き換えます:(空白のままにします)

これで、サイズ値がきれいに表示され、読みやすくなりました。

繰り返される値を埋める:

• 注文の最初の行にのみ OrderID または顧客名が含まれる場合があります
• 列を選択します
• 変換に移動します。>>塗りつぶしを選択します>>下を選択します

重要な注意: すべての null を盲目的に置き換えないでください。値が欠落している場合は、「該当なし」、「不明」、またはデータの問題を示している可能性があります。値を置き換える前に、常にビジネスの意味を理解してください。

カスタム計算列の作成

電子商取引データから、収益または利益の列を取得できます。これは Excel の数式を使用して実行できますが、更新時に壊れます。 Power Query のカスタム列は自動的に再計算され、ETL パイプラインの一部として残ります。

Net_Revenue:

• 列の追加に移動します。 タブ>> カスタム列を選択します
• 名前を入力してください
• 次の数式を挿入します

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

売上総利益:

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

Margin_Pct (%):

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

• データ型を明示的に設定します:Net_Revenue と Gross_Margin を Currency に設定します。 、Margin_Pct から パーセント

重要: 常に除算式を守ってください。分母にゼロがあると、Power Query の列全体でエラーが発生する可能性があり、読み込み中に行が削除される可能性があります。 if [X] =0 then null else … パターンを使用します。 計算された比率については。

高パフォーマンスの要約テーブルのためのグループ化と集計

数百万行の生の e コマース ファイルを使用すると、レポートの速度が遅くなる可能性があります。グループ化により、ドリルスルー分析用の詳細なクエリを保持しながら、ダッシュボード用の効率的な集計テーブルが作成されます。

手順:

• 変換に移動します。>> グループ化を選択します
• カテゴリを選択します>> [集計の追加] をクリックします。 :
  • Net_Revenue の合計
  • Margin_Pct の平均
  • Order_ID の数 (行)

• 新しいクエリに「Sales_summary」という名前を付けます。 オリジナルをSales_Detailとして保持します。 ドリルスルー用

プロのヒント: Power BI では、パフォーマンスを向上させるために、詳細クエリの「読み込みの有効化」を無効にし、サマリー テーブルとディメンション テーブルのみを読み込みます。

最終ステップ:

• ホームに移動します タブ>> 閉じて適用を選択します データをロードする

結論

これら 5 つの重要な Power Query 変換を e コマース販売データに適用できるようになりました。 Power Query は、単純な変換と複雑な変換の両方を反復可能な方法で処理するため、電子商取引データセットをクリーニングするための最も効果的なツールの 1 つです。データの準備時間を大幅に短縮できます。これらは一般的なクリーンアップ手順ではなく、実際の電子商取引レポートの課題に直接対処し、下流の分析をより効率的にします。これらの変換に慣れたら、新しいマーケットプレイス エクスポートごとに再利用可能な Power Query テンプレートを構築できます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!