विषय-सूची
यदि आप नियमित अभिव्यक्तियों से कम से कम परिचित हैं, तो आपको उनका विज्ञापन करने की आवश्यकता नहीं है। यदि आप विषय में काफी नहीं हैं, तो रेगुलर एक्सप्रेशन (रेगुलर एक्सप्रेशन = RegExp = "regexps" = "रेगुलर") एक ऐसी भाषा है, जहां विशेष वर्णों और नियमों का उपयोग करके, टेक्स्ट में आवश्यक सबस्ट्रिंग की खोज की जाती है, उन्हें निकाला जाता है या अन्य पाठ के साथ बदल दिया। यह एक बहुत शक्तिशाली और सुंदर उपकरण है, पाठ के साथ काम करने के अन्य सभी तरीकों से बेहतर परिमाण का एक क्रम।
मैंने पहले ही विस्तार से और जीवन से उदाहरणों के एक समूह के साथ वर्णन किया है कि आप साधारण मैक्रोज़ का उपयोग करके एक्सेल में नियमित अभिव्यक्ति समर्थन कैसे जोड़ सकते हैं - यदि आपने इस लेख को नहीं पढ़ा है, तो मैं अत्यधिक अनुशंसा करता हूं कि आप इसे जारी रखने से पहले पढ़ लें। आप बहुत सी नई चीजों की खोज करेंगे, मैं गारंटी देता हूं
हालाँकि, यह प्रश्न खुला रहता है - Power Query में रेगुलर एक्सप्रेशन का उपयोग करने की क्षमता कैसे जोड़ें? बेशक, पावर क्वेरी अपने आप में अच्छी है और टेक्स्ट (कटिंग, ग्लूइंग, क्लीनिंग आदि) के साथ बहुत कुछ कर सकती है, लेकिन अगर आप इसे रेगुलर एक्सप्रेशन की शक्ति से पार कर सकते हैं, तो यह सिर्फ एक बम होगा।
दुर्भाग्य से, Power Query में RegExps के साथ काम करने के लिए कोई अंतर्निहित कार्य नहीं हैं, और आधिकारिक Microsoft सहायता और तकनीकी सहायता इस प्रश्न का उत्तर नकारात्मक में देती है। हालाँकि, इस सीमा के आसपास एक रास्ता है
विधि का सार
मुख्य विचार अपमान के लिए सरल है।
अंतर्निहित Power Query क्षमताओं की सूची में, एक फ़ंक्शन है वेब पृष्ठ. आधिकारिक Microsoft सहायता साइट पर इस फ़ंक्शन का विवरण अत्यंत संक्षिप्त है:
अनुवादित, यह होगा: "HTML दस्तावेज़ की सामग्री को उसके घटक संरचनाओं में विभाजित कर देता है, साथ ही टैग को हटा दिए जाने के बाद पूरे दस्तावेज़ और उसके शरीर का प्रतिनिधित्व करता है।" तो-तो विवरण, स्पष्ट रूप से।
आमतौर पर इस फ़ंक्शन का उपयोग वेब से डेटा आयात करते समय किया जाता है और स्वचालित रूप से प्रतिस्थापित हो जाता है, उदाहरण के लिए, जब हम टैब पर चयन करते हैं जानकारी आदेश इंटरनेट से (डेटा - वेब से). हम फ़ंक्शन को एक वेब पेज को एक तर्क के रूप में देते हैं, और यह अपनी सामग्री हमें टेबल के रूप में देता है, पहले सभी टैग साफ़ कर देता है।
मदद क्या नहीं कहती है कि HTML मार्कअप भाषा के अलावा समारोह वेब पृष्ठ जावास्क्रिप्ट स्क्रिप्ट का समर्थन करता है, जो अब इंटरनेट पर वेबसाइटों पर सर्वव्यापी है। और जावास्क्रिप्ट, बदले में, हमेशा नियमित अभिव्यक्तियों के साथ काम करने में सक्षम रहा है और इसमें RegExps के लिए अंतर्निहित कार्य हैं! इसलिए Power Query में रेगुलर एक्सप्रेशन लागू करने के लिए, हमें Web.Page फ़ंक्शन को एक छोटे JavaScript प्रोग्राम के तर्क के रूप में फीड करना होगा जो Power Query के लिए सभी कार्य करेगा।
शुद्ध जावास्क्रिप्ट में यह कैसा दिखता है
इंटरनेट पर जावास्क्रिप्ट में नियमित अभिव्यक्तियों के साथ काम करने पर बहुत सारे विस्तृत ट्यूटोरियल हैं (उदाहरण के लिए, एक, दो)।
संक्षेप में और सरलीकृत में, जावास्क्रिप्ट कोड इस तरह दिखेगा:
यहाँ:
- var str = 'सॉसेज के लिए बिल 123 और 789 का भुगतान करें'; - एक वैरिएबल बनाएं str और इसे स्रोत टेक्स्ट असाइन करें जिसका हम विश्लेषण करेंगे।
- वर पैटर्न = /d+/gi; - एक रेगुलर एक्सप्रेशन बनाएं और इसे एक वेरिएबल में रखें पैटर्न.
एक्सप्रेशन एक स्लैश (/) से शुरू होता है।
उदाहरण के लिए, यहाँ अभिव्यक्ति ही है d+ अंकों के किसी भी क्रम के लिए खड़ा है।
अभिव्यक्ति के बाद अंश के माध्यम से, अतिरिक्त खोज पैरामीटर (संशोधक) हैं - उन्हें किसी भी क्रम में निर्दिष्ट किया जा सकता है:
- g - का अर्थ है वैश्विक खोज, यानी एक मैच खोजने के बाद, आपको रुकना नहीं चाहिए, बल्कि पाठ के अंत तक खोज जारी रखनी चाहिए। यदि यह संशोधक सेट नहीं है, तो हमारी स्क्रिप्ट केवल पहला मैच लौटाएगी (123)
- i - पत्रों के मामले की परवाह किए बिना खोजें
- m - बहु-पंक्ति खोज (जब स्रोत पाठ को कई पंक्तियों में विभाजित किया जाता है)
- वर परिणाम = str.match (पैटर्न)। शामिल हों (';'); - स्रोत पाठ में एक खोज करें (str) दी गई नियमित अभिव्यक्ति द्वारा (पैटर्न) और परिणामों को एक चर में रखें परिणाम, कमांड का उपयोग करके उन्हें अर्धविराम से जोड़ना में शामिल होने
- दस्तावेज़.लिखें (परिणाम); - परिणाम चर की सामग्री प्रदर्शित करें
यह भी ध्यान दें कि जावास्क्रिप्ट में टेक्स्ट स्ट्रिंग्स (रेगुलर एक्सप्रेशन को छोड़कर) एपोस्ट्रोफ में संलग्न हैं, न कि उद्धरण जैसे कि वे पावर क्वेरी या वीबीए में हैं।
आउटपुट पर, यह स्क्रिप्ट हमें स्रोत टेक्स्ट में पाए गए सभी नंबरों के परिणामस्वरूप देगी:
123, 789
जावास्क्रिप्ट लघु पाठ्यक्रम समाप्त हो गया है, आप सभी का धन्यवाद। आशा है कि आपको तर्क मिल गया होगा
यह इस निर्माण को Power Query में स्थानांतरित करना बाकी है।
पावर क्वेरी में रेगुलर एक्सप्रेशन द्वारा टेक्स्ट फ़ंक्शन खोजें और निकालें
हम निम्नलिखित करते हैं:
1. एक्सेल खोलें और टैब में एक नई खाली पावर क्वेरी बनाएं डेटा - डेटा प्राप्त करें / अनुरोध बनाएं - अन्य स्रोतों से - खाली अनुरोध (डेटा - डेटा प्राप्त करें / नई क्वेरी - अन्य स्रोतों से - खाली क्वेरी). यदि आपके पास एक्सेल 2010-2013 का पुराना संस्करण है और पावर क्वेरी आपके पास बिल्ट-इन नहीं है, लेकिन एक अलग ऐड-इन के रूप में स्थापित किया गया था, तो यह सब टैब पर होगा पावर क्वेरीऔर नहीं जानकारी.
2. खुलने वाले क्वेरी संपादक की खाली विंडो में, दाएं पैनल में, तुरंत हमारे भविष्य के फ़ंक्शन का नाम दर्ज करें (उदाहरण के लिए, fxRegExpएक्सट्रैक्ट)
3. आइए टैब पर जाएं देखें – उन्नत संपादक (देखें - उन्नत संपादक), हम खाली अनुरोध के पूरे एम-कोड को मिटा देते हैं और वहां हमारे सुपरफंक्शन का कोड पेस्ट करते हैं:
अपने हाथ देखें:
पहली पंक्ति में, हम कहते हैं कि हमारे फ़ंक्शन में तीन टेक्स्ट तर्क होंगे: TXT - मूल पाठ का विश्लेषण किया जा रहा है, regex - नियमित अभिव्यक्ति पैटर्न, परिसीमक - परिणाम प्रदर्शित करने के लिए सीमांकक वर्ण।
आगे हम फंक्शन कहते हैं वेब पृष्ठ, इसके तर्क में ऊपर वर्णित जावास्क्रिप्ट कोड का निर्माण। हम अपने परिवर्तनीय तर्कों को कोड में पेस्ट और प्रतिस्थापित करते हैं।
टुकड़ा:
[डेटा]{0}[बच्चे]{0}[बच्चे]{1}[पाठ]{0}
हमें आवश्यक परिणामों के साथ तालिका में "गिरने" की आवश्यकता है। बात यह है कि समारोह वेब पृष्ठ नतीजतन, यह कई नेस्टेड टेबल तैयार करता है जो वेब पेज की संरचना को दोहराते हैं। एम-कोड के इस टुकड़े के बिना, हमारा कार्य इसे आउटपुट करेगा:
... और हमें इस शब्द को कई बार क्लिक करना होगा तालिका, क्रमिक रूप से कॉलम में चाइल्ड नेस्टेड टेबल में "गिरना" बच्चे:
इन सभी उद्धरणों के बजाय, हम तुरंत अपने फ़ंक्शन के कोड में इंगित करते हैं कि नेस्टेड टेबल और कॉलम (टेक्स्ट) ज़रुरत है।
यहाँ, वास्तव में, सभी रहस्य हैं। बटन दबाना बाकी है अंत खिड़की में उन्नत संपादक, जहां हमने अपना कोड डाला है, और आप सबसे स्वादिष्ट के लिए आगे बढ़ सकते हैं - काम पर हमारे फ़ंक्शन का प्रयास करें।
यहां कुछ बीज उदाहरण दिए गए हैं।
उदाहरण 1. भुगतान विवरण से खाता संख्या और तिथि प्राप्त करना
हमारे पास भुगतानों के विवरण (उद्देश्य) के साथ एक बैंक स्टेटमेंट है, जहां आपको भुगतान किए गए चालानों की संख्या और तारीखों को अलग-अलग कॉलम में निकालने की आवश्यकता है:
हम तालिका को Power Query में मानक तरीके से लोड करते हैं डेटा - टेबल/रेंज से (डेटा - टी . सेसक्षम/आरदेवदूत).
फिर हम अपने फ़ंक्शन के साथ एक परिकलित कॉलम जोड़ते हैं कॉलम जोड़ें - कस्टम फ़ंक्शन को कॉल करें (कॉलम जोड़ें - कस्टम फ़ंक्शन को आमंत्रित करें) और इसके तर्क दर्ज करें:
एक नियमित अभिव्यक्ति के रूप में (तर्क regex) टेम्प्लेट जिसका हम उपयोग करते हैं:
(घ{3,5}|घ{2}.d{2}.d{4})
... मानव भाषा में अनुवादित अर्थ:
3 से 5 अंकों की संख्या (खाता संख्या)
or
फॉर्म के टुकड़े "2-बिट संख्या - बिंदु - 2-बिट संख्या - बिंदु - 4-बिट संख्या", अर्थात्, प्रपत्र DD.MM.YYYY की तिथियां।
एक सीमांकक चरित्र के रूप में (तर्क परिसीमक) एक अर्धविराम दर्ज करें।
क्लिक करने के बाद OK हमारा मैजिक फंक्शन हमारे रेगुलर एक्सप्रेशन के अनुसार सभी प्रारंभिक डेटा का विश्लेषण करता है और इनवॉइस की संख्या और तारीखों के साथ हमारे लिए एक कॉलम बनाता है:
यह कमांड का उपयोग करके इसे अर्धविराम से अलग करना बाकी है होम — स्प्लिट कॉलम — डिलीमीटर द्वारा (होम - स्प्लिट कॉलम - डिलीमीटर द्वारा) और हमें वह मिलता है जो हम चाहते थे:
सुंदरता!
उदाहरण 2: टेक्स्ट से ईमेल पते निकालें
मान लीजिए कि हमारे पास प्रारंभिक डेटा के रूप में निम्न तालिका है:
... जहां से हमें वहां मिले ईमेल पतों को निकालने की आवश्यकता है (स्पष्टता के लिए, मैंने उन्हें पाठ में लाल रंग में हाइलाइट किया है)।
पिछले उदाहरण की तरह, हम तालिका को पावर क्वेरी में मानक तरीके से लोड करते हैं डेटा - टेबल/रेंज से (डेटा - टी . सेसक्षम/आरदेवदूत).
फिर हम अपने फ़ंक्शन के साथ एक परिकलित कॉलम जोड़ते हैं कॉलम जोड़ें - कस्टम फ़ंक्शन को कॉल करें (कॉलम जोड़ें - कस्टम फ़ंक्शन को आमंत्रित करें) और इसके तर्क दर्ज करें:
ईमेल पतों को पार्स करना एक अधिक कठिन कार्य है और इसे हल करने के लिए विभिन्न प्रकार के दुःस्वप्न की नियमित अभिव्यक्तियों का एक समूह है। मैंने सरल विकल्पों में से एक का उपयोग किया - आदर्श नहीं, लेकिन ज्यादातर मामलों में काफी काम कर रहा है:
[w|.|-]*@w*.[w|.]*
विभाजक के रूप में (परिसीमक) आप एक अर्धविराम और एक स्थान दर्ज कर सकते हैं।
पर क्लिक करें OK और हमें मूल पाठ "दलिया" से निकाले गए ई-मेल पते वाला एक कॉलम मिलता है:
जादू!
PS
जैसा कि कहा जाता है: "ऐसी कोई अच्छी चीज नहीं है जिसे और भी बेहतर न बनाया जा सके।" Power Query अपने आप में अच्छा है, और जब इसे नियमित अभिव्यक्तियों के साथ जोड़ा जाता है, तो यह हमें किसी भी पाठ डेटा को संसाधित करने में पूरी तरह से अवास्तविक शक्ति और लचीलापन देता है। मुझे उम्मीद है कि Microsoft किसी दिन Power Query और Power BI अपडेट में RegExp समर्थन जोड़ देगा और टैम्बोरिन के साथ उपरोक्त सभी नृत्य अतीत की बात बन जाएंगे। खैर, अभी के लिए, हाँ।
मैं यह भी जोड़ना चाहता हूं कि साइट https://regexr.com/ पर नियमित अभिव्यक्तियों के साथ खेलना सुविधाजनक है - सीधे ऑनलाइन संपादक में। वहाँ अनुभाग में सामुदायिक पैटर्न सभी अवसरों के लिए बड़ी संख्या में तैयार किए गए नियमित मौसम हैं। प्रयोग – रेगुलर एक्सप्रेशन की सारी शक्ति अब Power Query में आपकी सेवा में है!
- रेगुलर एक्सप्रेशन (RegExp) क्या हैं और एक्सेल में उनका उपयोग कैसे करें
- Power Query में फ़ज़ी टेक्स्ट खोज
- Power Query का उपयोग करके विभिन्न फ़ाइलों से तालिकाओं को असेंबल करना