Power Query और Power Pivot Excel के भीतर दो अलग टूल हैं। Power Query डेटा अंदर लाता है और उसे साफ़ करता है। Power Pivot कई टेबलों को एक साथ रखता है, ताकि आप उन्हें एक ही सारांश में बदल सकें। कौन-सा टूल कब लेना है, यह जानने से बार-बार होने वाले हाथ के काम में घंटों की बचत होती है।
Power Query: एक बार साफ़ करें, हमेशा ताज़ा रखें
आप Data टैब पर Get Data से शुरू करते हैं। Power Query CSV या Excel फ़ाइल, फ़ाइलों का पूरा फ़ोल्डर, डेटाबेस या कोई वेब पेज पढ़ सकता है। डेटा Power Query Editor में खुलता है, जहाँ आप खाली रो हटा सकते हैं, कॉलम बाँट सकते हैं, प्रकार बदल सकते हैं, फ़िल्टर कर सकते हैं और भी बहुत कुछ।
हर कार्य दाईं ओर की सूची में applied step के रूप में दर्ज होता है। किसी स्टेप पर क्लिक करके देखें कि उस समय डेटा कैसा था, या कोई स्टेप जो आप नहीं चाहते उसे हटा दें। जब आप Close & Load चुनते हैं, तो नतीजा वर्कशीट टेबल में या Data Model में आ जाता है।
अगले महीने नया एक्सपोर्ट उसी जगह डालें और Refresh All दबाएँ। वही स्टेप फिर से चलते हैं। आपकी मूल फ़ाइल कभी नहीं बदलती: Power Query उससे केवल पढ़ता है।
जो काम यह अच्छी तरह करता है
- Append: एक जैसे कॉलम वाली बारह मासिक फ़ाइलों को एक टेबल में एक के नीचे एक जोड़ें।
- Merge: एक साझा कॉलम पर दो टेबल मिलाएँ, कुछ-कुछ लुकअप जैसा, पर सारे मिलान एक बार में ले आता है।
- Unpivot: हर महीने के लिए एक कॉलम वाली चौड़ी टेबल को Month और Value कॉलम वाली साफ़ सूची में बदलें, जिसका सारांश बनाना कहीं आसान है।
- Clean text: स्पेस हटाएँ, बड़े-छोटे अक्षर ठीक करें, और "Smith, Ann" को दो कॉलम में बाँटें।
अगर आप एक ही सफ़ाई हाथ से दो बार से ज़्यादा करते हैं, तो शायद वह Power Query का काम है।
Power Pivot: कई टेबल, एक सारांश
कल्पना कीजिए एक Orders टेबल है जिसमें CustomerID कॉलम है, और एक अलग Customers टेबल है जिसमें हर ग्राहक का क्षेत्र है। Power Pivot के बिना आप लुकअप से क्षेत्र को Orders में कॉपी करते। उसके साथ आप दोनों टेबल Data Model में जोड़ते हैं और दोनों CustomerID कॉलम के बीच एक relationship बनाते हैं। इसके बाद एक PivotTable सीधे क्षेत्र के अनुसार ऑर्डर समूहित कर सकता है।
Data Model डेटा को संपीड़ित रूप में भी सेव करता है, इसलिए वह वर्कशीट की 1,048,576 रो की सीमा से कहीं ज़्यादा रो रख सकता है। आप DAX नाम की फ़ॉर्मूला भाषा में अपने खुद के measures लिख सकते हैं, जैसे ऐसा कुल जो रद्द किए गए ऑर्डर को छोड़ दे।
चुनाव, और कुछ सावधानियाँ
- गड़बड़ या बदलते इनपुट: Power Query।
- कई संबंधित टेबल, बहुत बड़ा डेटा, दोबारा इस्तेमाल होने वाले measures: Power Pivot (Data Model)।
- छोटा, साफ़-सुथरा, एक बार का डेटा: साधारण फ़ॉर्मूला या सामान्य PivotTable काफ़ी हैं।
- सुविधाएँ Excel के संस्करण और प्लेटफ़ॉर्म के हिसाब से अलग होती हैं। Power Pivot विंडो हर संस्करण में नहीं होती, और Excel for Mac में Excel for Windows से कम विकल्प हैं।
दोनों एक साथ अच्छी तरह काम करते हैं: Power Query से साफ़ करें, Data Model में लोड करें, फिर संबंध बनाएँ और सारांश दें।