पावर क्वेरी में डेटा पथों को पैरामीटरेट करना

यदि आपने Microsoft Excel में पहले से ही मुफ्त Power Query ऐड-इन के टूल का उपयोग करना शुरू कर दिया है, तो बहुत जल्द आप एक अत्यधिक विशिष्ट, लेकिन बहुत बार-बार और परेशान करने वाली समस्या का सामना करेंगे, जो स्रोत डेटा के लगातार टूटने वाले लिंक से जुड़ी है। समस्या का सार यह है कि यदि आपकी क्वेरी में आप बाहरी फ़ाइलों या फ़ोल्डरों का उल्लेख करते हैं, तो Power Query क्वेरी टेक्स्ट में उनके लिए पूर्ण पथ को हार्डकोड करता है। आपके कंप्यूटर पर सब कुछ ठीक काम करता है, लेकिन अगर आप अपने सहयोगियों को अनुरोध के साथ फाइल भेजने का फैसला करते हैं, तो वे निराश होंगे, क्योंकि। उनके पास अपने कंप्यूटर पर स्रोत डेटा के लिए एक अलग पथ है, और हमारी क्वेरी काम नहीं करेगी।

ऐसी स्थिति में क्या करें? आइए इस मामले को निम्नलिखित उदाहरण के साथ और अधिक विस्तार से देखें।

समस्या का निरूपण

मान लीजिए हमारे पास फ़ोल्डर में है E:बिक्री रिपोर्ट फ़ाइल झूठ बोलती है शीर्ष 100 उत्पाद.xls, जो हमारे कॉर्पोरेट डेटाबेस या ERP सिस्टम (1C, SAP, आदि) से एक अपलोड है। इस फ़ाइल में सबसे लोकप्रिय कमोडिटी आइटम के बारे में जानकारी है और यह अंदर की तरह दिखती है:

यह शायद बल्ले से ही स्पष्ट है कि इस रूप में एक्सेल में इसके साथ काम करना लगभग असंभव है: डेटा के साथ एक के माध्यम से खाली पंक्तियाँ, मर्ज किए गए सेल, अतिरिक्त कॉलम, एक बहु-स्तरीय हेडर, आदि हस्तक्षेप करेंगे।

इसलिए, उसी फ़ोल्डर में इस फ़ाइल के आगे, हम एक और नई फ़ाइल बनाते हैं हैंडलर.xlsx, जिसमें हम एक Power Query क्वेरी बनाएंगे जो स्रोत अपलोड फ़ाइल से बदसूरत डेटा लोड करेगी शीर्ष 100 उत्पाद.xls, और उन्हें क्रम में रखें:

किसी बाहरी फ़ाइल के लिए अनुरोध करना

फ़ाइल खोलना हैंडलर.xlsx, टैब पर चयन करें जानकारी आदेश डेटा प्राप्त करें - फ़ाइल से - एक्सेल वर्कबुक से (डेटा - डेटा प्राप्त करें - फ़ाइल से - एक्सेल से), फिर स्रोत फ़ाइल का स्थान और हमें जिस शीट की आवश्यकता है उसे निर्दिष्ट करें। चयनित डेटा Power Query संपादक में लोड किया जाएगा:

आइए उन्हें वापस सामान्य स्थिति में लाएं:

1. के साथ खाली लाइनें हटाएं होम — लाइन्स डिलीट करें — खाली लाइन्स को डिलीट करें (होम - पंक्तियाँ हटाएँ - खाली पंक्तियाँ हटाएँ).
2. के माध्यम से अनावश्यक शीर्ष 4 पंक्तियों को हटाएं होम — पंक्तियाँ हटाएँ — शीर्ष पंक्तियाँ हटाएँ (होम - पंक्तियाँ हटाएँ - शीर्ष पंक्तियाँ हटाएँ).
3. बटन के साथ पहली पंक्ति को टेबल हेडर तक उठाएं हेडर के रूप में पहली पंक्ति का प्रयोग करें टैब होम (होम - हेडर के रूप में पहली पंक्ति का प्रयोग करें).
4. कमांड का उपयोग करके दूसरे कॉलम में उत्पाद के नाम से पांच अंकों के लेख को अलग करें विभाजित स्तंभ टैब परिवर्तन (रूपांतरण - स्प्लिट कॉलम).
5. बेहतर दृश्यता के लिए अनावश्यक कॉलम हटाएं और शेष के शीर्षकों का नाम बदलें।

नतीजतन, हमें निम्नलिखित, बहुत अधिक सुखद तस्वीर मिलनी चाहिए:

यह इस समृद्ध तालिका को वापस हमारी फाइल में शीट पर अपलोड करना बाकी है हैंडलर.xlsx दल बंद करें और डाउनलोड करें (होम - बंद करें और लोड करें) टैब होम:

अनुरोध में फ़ाइल का पथ ढूँढना

अब देखते हैं कि संक्षिप्त नाम "एम" के साथ पावर क्वेरी में निर्मित आंतरिक भाषा में हमारी क्वेरी "हुड के नीचे" कैसी दिखती है। ऐसा करने के लिए, दाएँ फलक में उस पर डबल क्लिक करके हमारी क्वेरी पर वापस जाएँ अनुरोध और कनेक्शन और टैब पर समीक्षा चुनें उन्नत संपादक (देखें - उन्नत संपादक):

खुलने वाली विंडो में, दूसरी पंक्ति तुरंत हमारी मूल अपलोड फ़ाइल के लिए एक हार्ड-कोडेड पथ दिखाती है। यदि हम इस टेक्स्ट स्ट्रिंग को पैरामीटर, वेरिएबल, या एक्सेल शीट सेल के लिंक से बदल सकते हैं जहां यह पथ पूर्व-लिखित है, तो हम इसे बाद में आसानी से बदल सकते हैं।

फ़ाइल पथ के साथ एक स्मार्ट तालिका जोड़ें

चलिए अभी के लिए Power Query को बंद करते हैं और अपनी फाइल पर वापस आते हैं हैंडलर.xlsx. आइए एक नई खाली शीट जोड़ें और उस पर एक छोटी "स्मार्ट" तालिका बनाएं, जिसके एकमात्र सेल में हमारी स्रोत डेटा फ़ाइल का पूरा पथ लिखा जाएगा:

नियमित श्रेणी से स्मार्ट तालिका बनाने के लिए, आप कीबोर्ड शॉर्टकट का उपयोग कर सकते हैं कंट्रोल+T या बटन तालिका के रूप में प्रारूपित करें टैब होम (होम - तालिका के रूप में प्रारूपित करें). कॉलम हेडिंग (सेल A1) बिल्कुल कुछ भी हो सकता है। यह भी ध्यान दें कि स्पष्टता के लिए मैंने तालिका को एक नाम दिया है पैरामीटर्स टैब निर्माता (डिज़ाइन).

एक्सप्लोरर से किसी पथ की प्रतिलिपि बनाना या यहां तक ​​कि इसे मैन्युअल रूप से दर्ज करना, निश्चित रूप से, विशेष रूप से कठिन नहीं है, लेकिन मानव कारक को कम से कम करना और यदि संभव हो तो स्वचालित रूप से पथ निर्धारित करना सबसे अच्छा है। इसे मानक एक्सेल वर्कशीट फ़ंक्शन का उपयोग करके कार्यान्वित किया जा सकता है सेल (कक्ष), जो एक तर्क के रूप में निर्दिष्ट सेल के बारे में उपयोगी जानकारी का एक गुच्छा दे सकता है - जिसमें वर्तमान फ़ाइल का पथ भी शामिल है:

यदि हम मानते हैं कि स्रोत डेटा फ़ाइल हमेशा हमारे प्रोसेसर के समान फ़ोल्डर में होती है, तो हमें जिस पथ की आवश्यकता होती है वह निम्न सूत्र द्वारा बनाई जा सकती है:

=LEFT(CELL("filename");FIND("[";CELL("filename"))-1)&"top 100 products.xls"

या अंग्रेजी संस्करण में:

=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»

... समारोह कहाँ है लेवसिमव (बाएं) पूर्ण लिंक से ओपनिंग स्क्वायर ब्रैकेट (यानी वर्तमान फ़ोल्डर का पथ) तक टेक्स्ट का एक टुकड़ा लेता है, और फिर हमारी स्रोत डेटा फ़ाइल का नाम और एक्सटेंशन उससे चिपक जाता है।

क्वेरी में पथ को पैरामीटर करें

अंतिम और सबसे महत्वपूर्ण स्पर्श रहता है - अनुरोध में स्रोत फ़ाइल का पथ लिखने के लिए शीर्ष 100 उत्पाद.xls, हमारी बनाई गई "स्मार्ट" तालिका के सेल A2 का जिक्र करते हुए पैरामीटर्स.

ऐसा करने के लिए, आइए Power Query क्वेरी पर वापस जाएं और इसे फिर से खोलें उन्नत संपादक टैब समीक्षा (देखें - उन्नत संपादक). उद्धरणों में टेक्स्ट स्ट्रिंग-पथ के बजाय "ई: बिक्री रिपोर्टशीर्ष 100 उत्पाद। xlsx" आइए निम्नलिखित संरचना का परिचय दें:

एक्सेल। करंट वर्कबुक (){[नाम = "सेटिंग"]} [सामग्री]0 {}[स्रोत डेटा का पथ]

आइए देखें कि इसमें क्या शामिल है:

• एक्सेल। करंट वर्कबुक () वर्तमान फ़ाइल की सामग्री तक पहुँचने के लिए एम भाषा का एक कार्य है
• {[नाम = "सेटिंग"]} [सामग्री] - यह पिछले फ़ंक्शन के लिए एक परिशोधन पैरामीटर है, यह दर्शाता है कि हम "स्मार्ट" तालिका की सामग्री प्राप्त करना चाहते हैं पैरामीटर्स
• [स्रोत डेटा का पथ] तालिका में कॉलम का नाम है पैरामीटर्सजिसका हम उल्लेख करते हैं
  
• 0 {} तालिका में पंक्ति संख्या है पैरामीटर्सजिससे हम डेटा लेना चाहते हैं। टोपी की गिनती नहीं होती है और नंबरिंग शून्य से शुरू होती है, एक से नहीं।

वास्तव में, बस इतना ही।

क्लिक करना बाकी है अंत और जांचें कि हमारा अनुरोध कैसे काम करता है। अब, दोनों फाइलों के साथ पूरे फ़ोल्डर को दूसरे पीसी में भेजते समय, अनुरोध चालू रहेगा और स्वचालित रूप से डेटा का पथ निर्धारित करेगा।

• Power Query क्या है और Microsoft Excel में कार्य करते समय इसकी आवश्यकता क्यों पड़ती है?
• Power Query में फ़्लोटिंग टेक्स्ट स्निपेट कैसे आयात करें
• XNUMXD क्रॉसस्टैब को पावर क्वेरी के साथ एक फ्लैट टेबल पर फिर से डिजाइन करना