पॉवर क्वेरीमध्ये रेग्युलर एक्सप्रेशन्स (RegExp)

जर तुम्ही रेग्युलर एक्स्प्रेशन्सशी थोडेसे परिचित असाल तर तुम्हाला त्यांची जाहिरात करण्याची गरज नाही. जर तुम्ही विषयात पूर्णपणे नसाल, तर रेग्युलर एक्स्प्रेशन्स (नियमित अभिव्यक्ती = RegExp = “regexps” = “regulars”) ही एक भाषा आहे जिथे, विशेष वर्ण आणि नियम वापरून, मजकूरात आवश्यक सबस्ट्रिंग शोधले जातात, ते काढले जातात. किंवा इतर मजकुराने बदलले. हे एक अतिशय शक्तिशाली आणि सुंदर साधन आहे, मजकुरासह कार्य करण्याच्या इतर सर्व मार्गांपेक्षा मोठेपणाचा क्रम.

मी आधीच तपशीलवार वर्णन केले आहे आणि जीवनातील अनेक उदाहरणांसह तुम्ही साध्या मॅक्रोचा वापर करून एक्सेलमध्ये नियमित अभिव्यक्ती समर्थन कसे जोडू शकता - जर तुम्ही हा लेख वाचला नसेल, तर तुम्ही पुढे जाण्यापूर्वी तो वाचावा अशी मी शिफारस करतो. तुम्हाला बर्‍याच नवीन गोष्टी सापडतील, मी हमी देतो 🙂

तथापि, प्रश्न खुला आहे - पॉवर क्वेरीमध्ये नियमित अभिव्यक्ती वापरण्याची क्षमता कशी जोडायची? पॉवर क्वेरी, अर्थातच, स्वतःहून चांगली आहे आणि मजकूर (कटिंग, ग्लूइंग, साफ करणे इ.) सह बरेच काही करू शकते, परंतु जर तुम्ही ते नियमित अभिव्यक्तीच्या सामर्थ्याने ओलांडू शकत असाल तर ते फक्त एक बॉम्ब असेल.

दुर्दैवाने, Power Query मध्ये RegExps सह कार्य करण्यासाठी कोणतीही अंगभूत कार्ये नाहीत आणि अधिकृत Microsoft मदत आणि तांत्रिक समर्थन या प्रश्नाचे उत्तर नकारात्मक देतात. तथापि, या मर्यादेभोवती एक मार्ग आहे 🙂

पद्धतीचे सार

मुख्य कल्पना अपमानित करणे सोपे आहे.

बिल्ट-इन पॉवर क्वेरी क्षमतांच्या सूचीमध्ये, एक कार्य आहे वेब पृष्ठ. अधिकृत Microsoft मदत साइटवरील या कार्याचे वर्णन अत्यंत संक्षिप्त आहे:

भाषांतरित, हे असे असेल: "एचटीएमएल दस्तऐवजाची सामग्री त्याच्या घटक संरचनांमध्ये विभागली जाते, तसेच संपूर्ण दस्तऐवज आणि टॅग काढून टाकल्यानंतर त्याचे मुख्य भाग दर्शवते." अगदी स्पष्टपणे वर्णन.

सहसा हे कार्य वेबवरून डेटा आयात करताना वापरले जाते आणि स्वयंचलितपणे बदलले जाते, उदाहरणार्थ, जेव्हा आम्ही टॅबवर निवडतो डेटा आदेश इंटरनेटवरून (डेटा — वेबवरून). आम्ही फंक्शनला आर्ग्युमेंट म्हणून वेब पेज देतो आणि ते सर्व टॅग्स आधी साफ करून, सारणीच्या स्वरूपात आम्हाला त्यातील सामग्री परत करते.

एचटीएमएल मार्कअप लँग्वेज व्यतिरिक्त मदत काय म्हणत नाही कार्य वेब पृष्ठ JavaScript स्क्रिप्टला सपोर्ट करते, जे आता इंटरनेटवरील वेबसाइट्सवर सर्वव्यापी आहे. आणि JavaScript, या बदल्यात, नेहमी रेग्युलर एक्स्प्रेशनसह कार्य करण्यास सक्षम आहे आणि RegExps साठी अंगभूत कार्ये आहेत! त्यामुळे Power Query मध्ये रेग्युलर एक्स्प्रेशन्स अंमलात आणण्यासाठी, आम्हाला Web.Page फंक्शन्सना एका छोट्या JavaScript प्रोग्रामला युक्तिवाद म्हणून फीड करावे लागेल जे पॉवर क्वेरीसाठी सर्व काम करेल.

शुद्ध JavaScript मध्ये ते कसे दिसते

इंटरनेटवर JavaScript मध्ये रेग्युलर एक्स्प्रेशन्ससह काम करण्यासाठी बरीच तपशीलवार शिकवण्या आहेत (उदाहरणार्थ, एक, दोन).

थोडक्यात आणि सरलीकृत, JavaScript कोड असा दिसेल:

येथे:

• var str = 'सॉसेजसाठी 123 आणि 789 बिले भरा'; - एक व्हेरिएबल तयार करा str आणि त्याला स्त्रोत मजकूर नियुक्त करा ज्याचे आम्ही विश्लेषण करू.
• var नमुना = /d+/gi; - एक नियमित अभिव्यक्ती तयार करा आणि व्हेरिएबलमध्ये ठेवा नमुना.

  अभिव्यक्ती स्लॅश (/) ने सुरू होते.

  येथे अभिव्यक्ती स्वतः, उदाहरणार्थ, आहे d+ अंकांच्या कोणत्याही क्रमाचा अर्थ आहे.

  अभिव्यक्तीनंतरच्या अपूर्णांकाद्वारे, अतिरिक्त शोध मापदंड (परिवर्तक) आहेत - ते कोणत्याही क्रमाने निर्दिष्ट केले जाऊ शकतात:
  

  • g – म्हणजे जागतिक शोध, म्हणजे जुळणी शोधल्यानंतर, तुम्ही थांबू नये, परंतु मजकूर संपेपर्यंत शोध सुरू ठेवा. जर हा सुधारक सेट केला नसेल, तर आमची स्क्रिप्ट फक्त पहिला सामना परत करेल (123)
  • i - अक्षरांचा विचार न करता शोधा
  • m - मल्टी-लाइन शोध (स्रोत मजकूर अनेक ओळींमध्ये विभागलेला असताना वापरला जातो)
• var परिणाम = str.match(पॅटर्न).जॉइन(';'); - स्त्रोत मजकूरात शोध घ्या (strदिलेल्या नियमित अभिव्यक्तीद्वारे (नमुना) आणि परिणाम व्हेरिएबलमध्ये ठेवा परिणाम, कमांड वापरून त्यांना अर्धविरामाने जोडणे सामील व्हा
• document.write(परिणाम); - परिणाम व्हेरिएबलची सामग्री प्रदर्शित करा

हे देखील लक्षात ठेवा की JavaScript मधील मजकूर स्ट्रिंग (रेग्युलर एक्सप्रेशन्स वगळून) पॉवर क्वेरी किंवा VBA मधील कोट्स नसून अपॉस्ट्रॉफीमध्ये संलग्न आहेत.

आउटपुटवर, ही स्क्रिप्ट आम्हाला स्त्रोत मजकूरात सापडलेल्या सर्व संख्या देईल:

123, 789

JavaScript शॉर्ट कोर्स संपला आहे, सर्वांचे आभार. आशा आहे की तुम्हाला तर्क समजला असेल 🙂

हे बांधकाम पॉवर क्वेरीकडे हस्तांतरित करणे बाकी आहे.

पॉवर क्वेरीमध्ये रेग्युलर एक्सप्रेशनद्वारे टेक्स्ट फंक्शन शोधा आणि काढा

आम्ही खालील गोष्टी करतो:

1. एक्सेल उघडा आणि टॅबमध्ये एक नवीन रिक्त पॉवर क्वेरी तयार करा डेटा - डेटा मिळवा / विनंती तयार करा - इतर स्त्रोतांकडून - रिक्त विनंती (डेटा — डेटा मिळवा / नवीन क्वेरी — इतर स्त्रोतांकडून — रिक्त क्वेरी). तुमच्याकडे एक्सेल 2010-2013 ची जुनी आवृत्ती आणि पॉवर क्वेरी तुमच्याकडे अंगभूत नसल्यास, परंतु स्वतंत्र अॅड-इन म्हणून स्थापित केली असल्यास, हे सर्व टॅबवर असेल. उर्जा प्रश्नआणि नाही डेटा.

2. उघडलेल्या क्वेरी एडिटरच्या रिकाम्या विंडोमध्ये, उजव्या पॅनेलमध्ये, आमच्या भविष्यातील कार्याचे नाव त्वरित प्रविष्ट करा (उदाहरणार्थ, fxRegExpExtract)

3. चला टॅबवर जाऊया पहा - प्रगत संपादक (पहा — प्रगत संपादक), आम्ही रिक्त विनंतीचा संपूर्ण M-कोड पुसून टाकतो आणि आमच्या सुपरफंक्शनचा कोड तेथे पेस्ट करतो:

आपले हात पहा:

पहिल्या ओळीत, आम्ही म्हणतो की आमच्या फंक्शनमध्ये तीन मजकूर वितर्क असतील: txt - मूळ मजकूराचे विश्लेषण केले जात आहे, regex - नियमित अभिव्यक्ती नमुना, डेलीम - परिणाम प्रदर्शित करण्यासाठी परिसीमक वर्ण.

पुढे आपण फंक्शनला कॉल करतो वेब पृष्ठ, त्याच्या युक्तिवादात वर वर्णन केलेले JavaScript कोड तयार करणे. आम्ही आमच्या व्हेरिएबल आर्ग्युमेंट्स कोडमध्ये पेस्ट करतो आणि बदलतो.

तुकडा:

[डेटा]{0}[मुले]{0}[मुले]{1}[मजकूर]{0}

… आम्हाला आवश्यक असलेल्या परिणामांसह टेबलमध्ये "पडणे" आवश्यक आहे. मुद्दा असा आहे की कार्य वेब पृष्ठ परिणामी, ते अनेक नेस्टेड टेबल तयार करते जे वेब पृष्ठाच्या संरचनेची पुनरावृत्ती करतात. एम-कोडच्या या तुकड्याशिवाय, आमचे कार्य हे आउटपुट करेल:

… आणि आपल्याला शब्दावर अनेक वेळा क्लिक करावे लागेल टेबल, कॉलम्समधील चाइल्ड नेस्टेड टेबलमध्ये क्रमशः "घसणे". मुले:

या सर्व अवतरणांऐवजी, आम्ही आमच्या फंक्शनच्या कोडमध्ये ताबडतोब सूचित करतो ज्याने टेबल आणि कॉलम नेस्टेड केले आहे (मजकूर) आम्हाला गरज आहे.

येथे, खरं तर, सर्व रहस्ये आहेत. बटण दाबणे बाकी आहे समाप्त खिडकीत प्रगत संपादक, जिथे आम्ही आमचा कोड घातला आहे आणि तुम्ही सर्वात स्वादिष्ट वर जाऊ शकता - कामावर आमचे कार्य वापरून पहा.

येथे बियाणे उदाहरणे दोन आहेत.

उदाहरण 1. देयक वर्णनावरून खाते क्रमांक आणि तारीख पुनर्प्राप्त करणे

आमच्याकडे पेमेंटचे वर्णन (उद्देश) असलेले बँक स्टेटमेंट आहे, जिथे तुम्हाला पेड इनव्हॉइसचे नंबर आणि तारखा वेगळ्या कॉलममध्ये काढण्याची आवश्यकता आहे:

आम्ही टेबल पॉवर क्वेरीमध्ये मानक पद्धतीने लोड करतो डेटा - सारणी/श्रेणीवरून (डेटा - टी. कडूनसक्षम/आरदेवदूत).

मग आम्ही आमच्या फंक्शन द्वारे गणना केलेला कॉलम जोडतो कॉलम जोडा - कस्टम फंक्शनला कॉल करा (स्तंभ जोडा — सानुकूल कार्य सुरू करा) आणि त्याचे युक्तिवाद प्रविष्ट करा:

नियमित अभिव्यक्ती म्हणून (वाद regex) टेम्पलेट आम्ही वापरतो:

(डी{3,5}|d{2}.d{2}.d{4})

… मानवी भाषेत अनुवादित अर्थ: 

3 ते 5 अंकी संख्या (खाते क्रमांक)

or

फॉर्मचे तुकडे “2-बिट संख्या – बिंदू – 2-बिट संख्या – बिंदू – 4-बिट संख्या”, म्हणजेच DD.MM.YYYY फॉर्मच्या तारखा.

सीमांकक वर्ण म्हणून (वाद डेलीम) अर्धविराम प्रविष्ट करा.

वर क्लिक केल्यानंतर OK आमचे मॅजिक फंक्शन आमच्या रेग्युलर एक्सप्रेशननुसार सर्व प्रारंभिक डेटाचे विश्लेषण करते आणि आमच्यासाठी इनव्हॉइसच्या सापडलेल्या क्रमांक आणि तारखांसह एक स्तंभ तयार करते:

कमांड वापरून अर्धविरामाने वेगळे करणे बाकी आहे होम — स्प्लिट कॉलम — डिलिमिटरद्वारे (मुख्यपृष्ठ — स्तंभ विभाजित करा — परिसीमकानुसार) आणि आम्हाला पाहिजे ते मिळते:

सौंदर्य!

उदाहरण २: मजकूरातून ईमेल पत्ते काढा

समजा आपल्याकडे प्रारंभिक डेटा म्हणून खालील सारणी आहे:

… जिथून आम्हाला तेथे सापडलेले ईमेल पत्ते काढायचे आहेत (स्पष्टतेसाठी, मी ते मजकूरात लाल रंगात हायलाइट केले आहेत).

मागील उदाहरणाप्रमाणे, आम्ही टेबल पॉवर क्वेरीमध्ये मानक मार्गाने लोड करतो डेटा - सारणी/श्रेणीवरून (डेटा - टी. कडूनसक्षम/आरदेवदूत).

मग आम्ही आमच्या फंक्शन द्वारे गणना केलेला कॉलम जोडतो कॉलम जोडा - कस्टम फंक्शनला कॉल करा (स्तंभ जोडा — सानुकूल कार्य सुरू करा) आणि त्याचे युक्तिवाद प्रविष्ट करा:

ईमेल पत्ते पार्स करणे हे अधिक कठीण काम आहे आणि ते सोडवण्यासाठी दुःस्वप्नांच्या वेगवेगळ्या प्रमाणात नियमित अभिव्यक्ती आहेत. मी सोप्या पर्यायांपैकी एक वापरला - आदर्श नाही, परंतु बर्‍याच प्रकरणांमध्ये कार्य करते:

[w|.|-]*@w*.[w|.]*

विभाजक म्हणून (डेलीम) तुम्ही अर्धविराम आणि स्पेस टाकू शकता.

क्लिक करा OK आणि आम्हाला मूळ मजकूर "पोरिज" मधून काढलेल्या ई-मेल पत्त्यांसह एक स्तंभ मिळेल:

जादू!

PS

या म्हणीप्रमाणे: "अशी कोणतीही चांगली गोष्ट नाही जी आणखी चांगली केली जाऊ शकत नाही." पॉवर क्वेरी स्वतःच छान आहे आणि जेव्हा रेग्युलर एक्स्प्रेशन्ससह एकत्रित केली जाते तेव्हा ती आम्हाला कोणत्याही मजकूर डेटावर प्रक्रिया करण्यासाठी पूर्णपणे अवास्तव शक्ती आणि लवचिकता देते. मला आशा आहे की मायक्रोसॉफ्ट कधीतरी Power Query आणि Power BI अद्यतनांमध्ये RegExp समर्थन जोडेल आणि टॅम्बोरिनसह वरील सर्व नृत्य भूतकाळातील गोष्ट होईल. बरं, आतासाठी, होय.

मला हे देखील जोडायचे आहे की https://regexr.com/ साइटवर नियमित अभिव्यक्तीसह खेळणे सोयीचे आहे – अगदी ऑनलाइन संपादकात. तेथे विभागात समुदाय नमुने सर्व प्रसंगांसाठी मोठ्या संख्येने तयार नियमित हंगाम आहेत. प्रयोग – रेग्युलर एक्स्प्रेशन्सची सर्व शक्ती आता Power Query मध्ये तुमच्या सेवेत आहे!

• रेग्युलर एक्स्प्रेशन्स (RegExp) म्हणजे काय आणि ते Excel मध्ये कसे वापरायचे
• Power Query मध्ये अस्पष्ट मजकूर शोध
• पॉवर क्वेरी वापरून वेगवेगळ्या फायलींमधून टेबल्स एकत्र करणे