ई-कॉमर्स एक्सपोर्ट डेटा ज्यादातर समय गड़बड़ होता है। एक फाइल में कई SKU विविधताएं, असंगत ग्राहक जानकारी, संयुक्त पता फ़ील्ड, डुप्लिकेट या नल मान, और बिखरे हुए मूल्य विवरण हो सकते हैं। अगर आपने कभी ऐसे डेटा को Excel या Power BI में विश्लेषण करने की कोशिश की है, तो आप जानते हैं कि चीजें कितनी जल्दी जटिल हो जाती हैं। यहीं Power Query अविश्वसनीय रूप से उपयोगी हो जाता है। चाहे आप किसी भी टूल का उपयोग कर रहे हों—Excel या Power BI—Power Query जटिल सूत्र लिखे बिना कच्चे ई-कॉमर्स डेटा को साफ और रीशेप करने में मदद करता है।
इस ट्यूटोरियल में, हम ई-कॉमर्स बिक्री डेटा के लिए पांच आवश्यक Power Query ट्रांसफॉर्मेशन सीखेंगे जो ऑनलाइन स्टोर डेटासेट के लिए विशेष रूप से उपयोगी हैं।
SKU विविधता कॉलम को अनपिवट करना
कई ई-कॉमर्स एक्सपोर्ट ऑर्डर किए गए उत्पादों को कई कॉलम में स्टोर करते हैं, जैसे Stock_S, SKU 1, SKU 2, SKU 3, Variant 1, Variant 2, और Variant 3, जिनमें से प्रत्येक में एक मात्रा होती है। यह संरचना विश्लेषण के लिए कठिन है क्योंकि प्रत्येक ऑर्डर उत्पाद विवरण को कई कॉलम में फैलाता है। पिवट टेबल, DAX माप, और SUM सूत्र इस वाइड फॉर्मेट में प्रभावी ढंग से एकत्रित नहीं कर सकते। अनपिवटिंग एक लंबी, सामान्यीकृत फैक्ट टेबल बनाता है जो विश्लेषण के लिए आदर्श है।
चरण:
- अपने डेटा को Power Query में लोड करें
- SKU कॉलम चुनें (Stock S, M, L, XL)
- Transform टैब पर जाएं >> Unpivot Columns चुनें
- Power Query इन SKU कॉलम को दो नए कॉलम में बदल देता है और उनका नाम बदलता है:
- Attribute: Size
- Value: Quantity
इस ट्रांसफॉर्मेशन का उपयोग तब करें जब आपका एक्सपोर्ट कई ऑर्डर किए गए उत्पादों को अलग-अलग कॉलम के बजाय अलग-अलग पंक्तियों में स्टोर करता है।
शिपिंग एड्रेस स्ट्रिंग्स को कई फ़ील्ड में विभाजित करना
लगभग 90% कच्ची ई-कॉमर्स फाइलें पूरे शिपिंग पते को एक ही स्ट्रिंग में स्टोर करती हैं। इस फ़ील्ड को विभाजित करने से राज्य-स्तरीय टैक्स रिपोर्टिंग, शिपिंग लागत अनुकूलन, क्षेत्रीय प्रदर्शन विश्लेषण, और मैपिंग सक्षम होती है।
चरण:
- Shipping Address कॉलम चुनें
- Home टैब पर जाएं >> Split Column चुनें >> By Delimiter चुनें
- अपने डेटा के आधार पर, निम्न जैसे डिलीमीटर का उपयोग करें:
- कॉमा
- हाइफ़न
- लाइन ब्रेक
- कस्टम सेपरेटर
- मौजूदा डेटा के लिए, सेपरेटर के रूप में Comma चुनें
- OK पर क्लिक करें
- परिणामी कॉलम को सार्थक लेबल में नाम बदलें: Street, City, Postcode, और Country
एक बार पते विभाजित हो जाने पर, आप शहर के अनुसार ऑर्डर का विश्लेषण कर सकते हैं, ज़ोन के अनुसार डिलीवरी प्रदर्शन, क्षेत्र के अनुसार बिक्री, और विशिष्ट स्थानों से दोहराने वाले ग्राहक। यह स्थानीय डिलीवरी व्यवसायों और क्षेत्र-आधारित बिक्री रिपोर्टिंग के लिए विशेष रूप से उपयोगी है।
उन्नत नोट: सभी पते एक ही प्रारूप का पालन नहीं करते। कुछ में अतिरिक्त भाग, लापता भाग, या अपार्टमेंट विवरण हो सकते हैं। इन मामलों में, आप कर सकते हैं:
- विभाजन के बाद स्पेस ट्रिम करें
- चुने हुए भागों को वापस एक साथ मर्ज करें
- Extract Text Before/After Delimiter का उपयोग करें
- अपवाद प्रबंधन के लिए कस्टम कॉलम बनाएं
ट्रिमिंग, सफाई, और डेटा प्रकारों को मानकीकृत करना
बड़े ई-कॉमर्स डेटासेट में, ग्राहक नाम, SKU, ईमेल पते, और उत्पाद श्रेणियों में अक्सर अग्रणी या अनुगामी स्पेस, गैर-प्रिंट करने योग्य वर्ण, और असंगत कैपिटलाइज़ेशन होते हैं। डेटा प्रकार भी बेमेल हो सकते हैं, जैसे टेक्स्ट के रूप में संग्रहीत संख्यात्मक मान।
ये छोटी समस्याएं महत्वपूर्ण समस्याएं पैदा कर सकती हैं। एक ही डेटा अलग-अलग मानों के रूप में दिखाई दे सकता है, मर्ज ऑपरेशन में विफल हो सकता है, या डुप्लिकेट समूह बना सकता है।
डेटा साफ करना:
- स्पेस ट्रिम करें:
- टेक्स्ट कॉलम चुनें
- Transform >> Format >> Trim चुनें
- यह शुरुआत और अंत से अतिरिक्त स्पेस हटा देता है
- छिपे हुए वर्ण साफ करें:
- उसी कॉलम चुने हुए,
- Format >> Clean चुनें
- यह गैर-प्रिंट करने योग्य वर्णों को हटा देता है
- कैसिंग मानकीकृत करें:
- Transform >> Format चुनें >> चुनें:
- Capitalize Each Word नामों के लिए
- Lowercase ईमेल के लिए यदि आवश्यक हो
डेटा प्रकार मानकीकृत करना:
- दिनांक कॉलम चुनें
- Home टैब पर जाएं >> Data Type चुनें >> Date चुनें
- यदि आवश्यक हो, तो एक कॉलम जोड़ें:
- Add Column टैब पर जाएं >> Date का विस्तार करें
- Year, Month, Day, Quarter, Day of Week चुनें
- Quantity और Price जैसे संख्यात्मक कॉलम चुनें
- कॉलम हेडर आइकन का विस्तार करें >> Decimal Number चुनें
- या Transform टैब पर जाएं >> Data Type चुनें >> Decimal Number या Whole Number चुनें
यह ट्रांसफॉर्मेशन आवश्यक है क्योंकि यह ग्रुपिंग, मर्जिंग, फ़िल्टरिंग, और लुकअप ऑपरेशन को कहीं अधिक विश्वसनीय बनाता है।
डुप्लिकेट हटाना और नल मानों को संभालना
ई-कॉमर्स डेटा में नल आम हैं। कुछ हानिरहित हैं, जबकि अन्य गणनाओं को तोड़ते हैं। पेमेंट गेटवे और सिंक प्रोसेस डुप्लिकेट OrderID बना सकते हैं, और नल मात्रा या कीमतें योग और विज़ुअल को तोड़ सकती हैं। अगर नल को सही ढंग से नहीं संभाला गया, तो परिणाम भ्रामक हो सकते हैं।
डुप्लिकेट हटाना:
- OrderID और OrderDate को कुंजी के रूप में चुनें (यह वैध समान-दिन के दोहराए गए ऑर्डर को हटाने से रोकता है)
- Home टैब पर जाएं >> Remove Duplicates चुनें
- केवल वास्तव में टूटी हुई पंक्तियों को हटाएं:
- Home टैब पर जाएं >> Remove Blank Rows चुनें (पहले OrderID नल वाली पंक्तियों को फ़िल्टर करें)
मान बदलना:
- अगर खाली छूट का मतलब कोई छूट नहीं है, तो नल को 0 से बदलें
- सभी Quantity और Price कॉलम चुनें
- Transform टैब पर जाएं >> Replace Values चुनें:
- खोजने का मान: null
- बदलें: 0
टेक्स्ट मान साफ करना:
- अनावश्यक प्रीफिक्स जैसे Stock_ हटाएं
- Size कॉलम चुनें
- Transform टैब पर जाएं >> Replace Values चुनें:
- खोजने का मान: Stock_
- बदलें: (खाली छोड़ दें)
अब Size मान साफ और पढ़ने योग्य दिखाई देते हैं।
दोहराए गए मानों को नीचे भरना:
- कभी-कभी किसी ऑर्डर की केवल पहली पंक्ति में OrderID या ग्राहक नाम होता है
- कॉलम चुनें
- Transform >> Fill >> Down चुनें
महत्वपूर्ण सावधानी: हर नल को आंख बंद करके न बदलें। एक लापता मान "लागू नहीं", "अज्ञात", या डेटा समस्या दर्शा सकता है। मान बदलने से पहले हमेशा व्यावसायिक अर्थ समझें।
गणना किए गए कस्टम कॉलम बनाना
ई-कॉमर्स डेटा से, आप राजस्व या मार्जिन कॉलम प्राप्त कर सकते हैं। हालांकि यह Excel सूत्रों का उपयोग करके किया जा सकता है, वे रीफ्रेश पर टूट जाते हैं। Power Query कस्टम कॉलम स्वचालित रूप से पुनर्गणना करते हैं और आपके ETL पाइपलाइन का हिस्सा बने रहते हैं।
Net_Revenue:
- Add Column टैब पर जाएं >> Custom Column चुनें
- एक नाम दर्ज करें
- निम्न सूत्र डालें
[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 को Currency, Margin_Pct को Percentage
महत्वपूर्ण: हमेशा डिवीजन सूत्रों की रक्षा करें। हर में शून्य Power Query में पूरे कॉलम में त्रुटियां पैदा कर सकता है और लोड के दौरान पंक्तियों को छोड़ सकता है। किसी भी गणना अनुपात के लिए पैटर्न if [X] = 0 then null else … का उपयोग करें।
उच्च-प्रदर्शन सारांश टेबल के लिए ग्रुपिंग और एकत्रीकरण
लाखों पंक्तियों वाली कच्ची ई-कॉमर्स फाइलें रिपोर्ट को धीमा कर सकती हैं। ग्रुपिंग ड्रिल-थ्रू विश्लेषण के लिए विस्तृत क्वेरी को संरक्षित करते हुए डैशबोर्ड के लिए कुशल समग्र टेबल बनाता है।
चरण:
- Transform >> Group By चुनें
- Category चुनें >> Add aggregation पर क्लिक करें:
- Net_Revenue का योग
- Margin_Pct का औसत
- Order_ID की गिनती (पंक्तियाँ)
- नई क्वेरी का नाम Sales_Summary रखें और मूल को ड्रिल-थ्रू के लिए Sales_Detail के रूप में रखें
प्रो टिप: Power BI में, डिटेल क्वेरी पर "Enable load" अक्षम करें और प्रदर्शन सुधारने के लिए केवल सारांश और आयाम टेबल लोड करें।
अंतिम चरण:
- Home टैब पर जाएं >> Close & Apply चुनें डेटा लोड करने के लिए
निष्कर्ष
अब आप ई-कॉमर्स बिक्री डेटा पर इन पांच आवश्यक Power Query ट्रांसफॉर्मेशन को लागू कर सकते हैं। Power Query ई-कॉमर्स डेटासेट को साफ करने के लिए सबसे प्रभावी टूल में से एक है क्योंकि यह सरल और जटिल दोनों ट्रांसफॉर्मेशन को दोहराने योग्य तरीके से संभालता है। यह डेटा तैयारी समय को काफी कम कर सकता है। ये सामान्य सफाई चरण नहीं हैं—ये सीधे वास्तविक दुनिया की ई-कॉमर्स रिपोर्टिंग चुनौतियों का समाधान करते हैं और डाउनस्ट्रीम विश्लेषण को बहुत अधिक कुशल बनाते हैं। एक बार जब आप इन ट्रांसफॉर्मेशन के साथ सहज हो जाते हैं, तो आप हर नए मार्केटप्लेस एक्सपोर्ट के लिए एक पुन: प्रयोज्य Power Query टेम्पलेट बना सकते हैं।
मुफ़्त उन्नत Excel अभ्यास समाधान के साथ प्राप्त करें!
