List.Acumulate फंक्शनसह पॉवर क्वेरीमध्ये मोठ्या प्रमाणात मजकूर बदलणे

संदर्भ सूचीनुसार मजकूर त्वरीत आणि मोठ्या प्रमाणात सूत्रांसह कसा बदलायचा - आम्ही ते आधीच सोडवले आहे. आता Power Query मध्ये ते करण्याचा प्रयत्न करूया.

जसे अनेकदा घडते सुरू करा हे कार्य स्पष्ट करण्यापेक्षा खूप सोपे आहे का ते कार्य करते, परंतु दोन्ही करण्याचा प्रयत्न करूया 🙂

तर, आमच्याकडे दोन "स्मार्ट" डायनॅमिक टेबल आहेत जे कीबोर्ड शॉर्टकटसह सामान्य श्रेणींमधून तयार केले जातात Ctrl+T किंवा संघ मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित):

मी पहिल्या टेबलला फोन केला डेटा, दुसरा टेबल - निर्देशिकाफील्ड वापरणे टेबल नाव (सारणीचे नाव) टॅब रचनाकार (डिझाइन).

कार्य: टेबलमधील पत्ते बदला डेटा स्तंभातील सर्व घटना शोधण्यासाठी हँडबुक स्तंभातील त्यांच्या संबंधित योग्य समकक्षांना पर्याय. सेलमधील उर्वरित मजकूर अस्पर्शित राहिला पाहिजे.

पायरी 1. पॉवर क्वेरीमध्ये डिरेक्ट्री लोड करा आणि ती सूचीमध्ये बदला

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

संदर्भ सारणी पॉवर क्वेरी क्वेरी एडिटरमध्ये लोड केली जाईल:

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

आता, पुढील परिवर्तने आणि पुनर्स्थापने करण्यासाठी, आम्हाला या सारणीला सूची (सूची) मध्ये बदलण्याची आवश्यकता आहे.

गेय विषयांतर

• टेबल अनेक पंक्ती आणि स्तंभांचा समावेश असलेला द्विमितीय अॅरे आहे.
• रेकॉर्ड (रेकॉर्ड) - एक-आयामी अॅरे-स्ट्रिंग, ज्यामध्ये नावांसह अनेक फील्ड-घटक असतात, उदाहरणार्थ [नाव = “माशा”, लिंग = “फ”, वय = २५]
  
• यादी – एक-आयामी अॅरे-स्तंभ, ज्यामध्ये अनेक घटक असतात, उदाहरणार्थ {1, 2, 3, 10, 42} or { "विश्वास आशा प्रेम" }
  

आमच्या समस्येचे निराकरण करण्यासाठी, आम्हाला प्रामुख्याने प्रकारात रस असेल यादी.

येथे युक्ती अशी आहे की पॉवर क्वेरी मधील सूची आयटम केवळ बॅनल नंबर किंवा मजकूर नसून इतर सूची किंवा रेकॉर्ड देखील असू शकतात. रेकॉर्ड्स (रेकॉर्ड्स) असलेल्या अशा अवघड यादीत (यादी) आम्हाला आमची निर्देशिका फिरवायची आहे. पॉवर क्वेरी सिंटॅक्टिक नोटेशनमध्ये (चौकोनी कंसातील नोंदी, कुरळे कंसातील याद्या) हे असे दिसेल:

{

    [ शोधा = “सेंट. पीटर्सबर्ग", बदली = "सेंट. पीटर्सबर्ग"] ,

    [ शोधा = “सेंट. पीटर्सबर्ग", बदली = "सेंट. पीटर्सबर्ग"] ,

    [ शोधा = “पीटर”, बदला = “सेंट. पीटर्सबर्ग"] ,

इ

}

पॉवर क्वेरीमध्ये तयार केलेल्या एम भाषेचे विशेष कार्य वापरून असे परिवर्तन केले जाते - टेबल.ToRecords. ते थेट फॉर्म्युला बारमध्ये लागू करण्यासाठी, तेथे चरण कोडमध्ये हे कार्य जोडा स्रोत.

ते होते:

नंतर:

Table.ToRecords फंक्शन जोडल्यानंतर, आमच्या टेबलचे स्वरूप बदलेल – ते रेकॉर्डच्या सूचीमध्ये बदलेल. कोणत्याही शब्दाच्या पुढील सेल बॅकग्राउंडमध्ये क्लिक करून वैयक्तिक रेकॉर्डची सामग्री दृश्य उपखंडाच्या तळाशी पाहिली जाऊ शकते. विक्रम (पण एका शब्दात नाही!)

वरील व्यतिरिक्त, आणखी एक स्ट्रोक जोडणे अर्थपूर्ण आहे - आमच्या तयार केलेल्या सूचीला कॅशे (बफर) करण्यासाठी. हे Power Query ला आमची लुकअप सूची मेमरीमध्ये एकदा लोड करण्यास भाग पाडेल आणि जेव्हा आम्ही नंतर ती बदलण्यासाठी ती ऍक्सेस करू तेव्हा पुन्हा गणना करणार नाही. हे करण्यासाठी, आमचे सूत्र दुसर्‍या फंक्शनमध्ये गुंडाळा - यादी.बफर:

अशा कॅशिंगमुळे मोठ्या प्रमाणात प्रारंभिक डेटा साफ होण्यास गती (अनेक पटीने!) लक्षणीय वाढ होईल.

यामुळे हँडबुकची तयारी पूर्ण होते.

त्यावर क्लिक करणे बाकी आहे मुख्यपृष्ठ – बंद करा आणि लोड करा – बंद करा आणि लोड करा… (होम — बंद करा आणि लोड करा — बंद करा आणि लोड करा..), एक पर्याय निवडा फक्त एक कनेक्शन तयार करा (केवळ कनेक्शन तयार करा) आणि Excel वर परत या.

पायरी 2. डेटा टेबल लोड करत आहे

येथे सर्व काही क्षुल्लक आहे. संदर्भ पुस्तकाप्रमाणेच, आम्ही टेबलमधील कोणत्याही ठिकाणी पोहोचतो, टॅबवर क्लिक करतो डेटा बटण टेबल/श्रेणीतून आणि आमचे टेबल डेटा पॉवर क्वेरीमध्ये येतो. आपोआप जोडलेली पायरी सुधारित प्रकार (बदललेला प्रकार) आपण देखील काढू शकता:

त्यासाठी कोणतीही विशेष तयारी करण्याची आवश्यकता नाही आणि आम्ही सर्वात महत्वाच्या गोष्टीकडे जाऊ.

पायरी 3. List.Acumulate फंक्शन वापरून बदली करा

कमांड वापरून आमच्या डेटा टेबलमध्ये गणना केलेला कॉलम जोडू एक स्तंभ जोडणे - सानुकूल स्तंभ (स्तंभ जोडा — सानुकूल स्तंभ): आणि उघडणाऱ्या विंडोमध्ये जोडलेल्या स्तंभाचे नाव प्रविष्ट करा (उदाहरणार्थ, दुरुस्त केलेला पत्ता) आणि आमचे जादूचे कार्य यादी.जमा करा:

त्यावर क्लिक करणे बाकी आहे OK - आणि आम्हाला केलेल्या बदलांसह एक स्तंभ मिळेल:

लक्षात ठेवा की:

• पॉवर क्वेरी केस सेन्सिटिव्ह असल्याने, उपांत्य रेषेत कोणतेही बदल नाही, कारण निर्देशिकेत आमच्याकडे "SPb" आहे, "SPb" नाही.
• जर स्त्रोत डेटामध्ये एकाच वेळी बदलण्यासाठी अनेक सबस्ट्रिंग्स असतील (उदाहरणार्थ, 7 व्या ओळीत तुम्हाला "S-Pb" आणि "प्रॉस्पेक्टस" दोन्ही बदलण्याची आवश्यकता आहे), तर यामुळे कोणतीही समस्या निर्माण होत नाही (सूत्रांसह बदलण्यासारखे नाही. मागील पद्धत).
• जर स्त्रोत मजकूर (9वी ओळ) मध्ये बदलण्यासाठी काहीही नसेल, तर कोणतीही त्रुटी उद्भवत नाही (पुन्हा, सूत्रांद्वारे बदलण्यापासून वेगळे).

अशा विनंतीचा वेग खूप, अतिशय सभ्य आहे. उदाहरणार्थ, 5000 पंक्तींच्या आकाराच्या प्रारंभिक डेटाच्या सारणीसाठी, ही क्वेरी एका सेकंदापेक्षा कमी वेळेत अद्यतनित केली गेली (बफरिंगशिवाय, तसे, सुमारे 3 सेकंद!)

List.Acumulate फंक्शन कसे कार्य करते

तत्वतः, हा लेख शेवटचा असू शकतो (माझ्यासाठी लिहिण्यासाठी आणि तुम्ही वाचण्यासाठी). जर तुम्हाला केवळ सक्षम व्हायचे नसेल, तर ते "हूडखाली" कसे कार्य करते हे देखील समजून घ्या, तर तुम्हाला सशाच्या छिद्रात थोडे खोल जावे लागेल आणि यादीला सामोरे जावे लागेल. एकुम्युलेट फंक्शन, ज्याने सर्व मोठ्या प्रमाणात बदलले. आमच्यासाठी काम करा.

या कार्यासाठी वाक्यरचना आहे:

= यादी. जमा करा(यादी, बी, जमा करणारा)

जेथे

• यादी ही यादी आहे ज्याचे घटक आपण पुनरावृत्ती करत आहोत. 
• बी - प्रारंभिक स्थिती
• जमा करणारा - एक फंक्शन जे सूचीच्या पुढील घटकावर काही ऑपरेशन (गणितीय, मजकूर इ.) करते आणि विशेष व्हेरिएबलमध्ये प्रक्रियेचे परिणाम जमा करते.
  

सर्वसाधारणपणे, पॉवर क्वेरीमध्ये फंक्शन्स लिहिण्यासाठी वाक्यरचना असे दिसते:

(argument1, argument2, … argumentN) => वितर्कांसह काही क्रिया

उदाहरणार्थ, समेशन फंक्शन असे दर्शविले जाऊ शकते:

(a, b) => a + b

List.Accumulate साठी, या संचयक फंक्शनमध्ये दोन आवश्यक वितर्क आहेत (त्यांना काहीही नाव दिले जाऊ शकते, परंतु नेहमीची नावे राज्य и वर्तमान, या कार्यासाठी अधिकृत मदतीप्रमाणे, जेथे:

• राज्य – एक व्हेरिएबल जिथे परिणाम जमा होतो (त्याचे प्रारंभिक मूल्य वर नमूद केलेले आहे बी)
  
• वर्तमान - सूचीमधून पुढील पुनरावृत्ती मूल्य यादी
  

उदाहरणार्थ, खालील बांधकामाच्या तर्कशास्त्राच्या चरणांवर एक नजर टाकूया:

= यादी. जमा करा({3, 2, 5}, 10, (state, current) => state + current)

हे नवीनतम जमा आहे राज्य मूल्य ही आमची यादी आहे. परिणाम म्हणून कार्य आणि आउटपुट जमा करा:

जर तुम्ही थोडी कल्पना केली तर List.Acumulate फंक्शन वापरून तुम्ही सिम्युलेट करू शकता, उदाहरणार्थ, एक्सेल फंक्शन CONCATENATE (पॉवर क्वेरीमध्ये, त्याचे अॅनालॉग म्हणतात. मजकूर. एकत्र करा) अभिव्यक्ती वापरून:

किंवा कमाल मूल्य शोधा (एक्सेलच्या MAX फंक्शनचे अनुकरण, ज्याला पॉवर क्वेरी म्हणतात सूची.मॅक्स):

तथापि, List.Acumulate चे मुख्य वैशिष्ट्य म्हणजे केवळ साधा मजकूर किंवा अंकीय याद्यांवर युक्तिवाद म्हणून प्रक्रिया करण्याची क्षमता नाही, तर अधिक जटिल वस्तू - उदाहरणार्थ, सूची-यादी किंवा रेकॉर्डमधून सूची (हॅलो, निर्देशिका!)

आमच्या समस्येमध्ये पुनर्स्थित केलेल्या बांधकामाकडे पुन्हा पाहू या:

यादी. जमा करा(निर्देशिका, [पत्ता], (state,current) => Text.Replace(state, current[शोधा], current[बदला]) )

इथे खरोखर काय चालले आहे?

1. प्रारंभिक मूल्य म्हणून (बी) आम्ही स्तंभातून पहिला अनाड़ी मजकूर घेतो [पत्ता] आमचे टेबल: 199034, सेंट पीटर्सबर्ग, str. बेरिंगा, दि. १
2. नंतर यादी. यादीतील घटकांवर एक एक करून पुनरावृत्ती जमा करा – हँडबुक. या सूचीतील प्रत्येक घटक एक रेकॉर्ड आहे ज्यामध्ये फील्डच्या जोडीचा समावेश आहे "काय शोधायचे - कशासह बदलायचे" किंवा दुसर्‍या शब्दात, निर्देशिकेतील पुढील ओळ.
3. संचयक फंक्शन व्हेरिएबलमध्ये ठेवते राज्य प्रारंभिक मूल्य (पहिला पत्ता 199034, सेंट पीटर्सबर्ग, str. बेरिंगा, दि. १) आणि त्यावर एक संचयक कार्य करते – मानक M-फंक्शन वापरून बदली ऑपरेशन मजकूर.बदला (एक्सेलच्या SUBSTITUTE फंक्शनशी साधर्म्य असलेले). त्याची वाक्यरचना आहे:

   Text.Replace(मूळ मजकूर, आम्ही काय शोधत आहोत, आम्ही कशासह बदलत आहोत)

   आणि आमच्याकडे आहे:

• राज्य आमचा गलिच्छ पत्ता आहे, जो आत आहे राज्य (तेथून पोहोचणे बी)
  
• वर्तमान[शोध] - फील्ड मूल्य शोधण्यासाठी सूचीच्या पुढील पुनरावृत्ती एंट्रीमधून निर्देशिका, जे व्हेरिएबलमध्ये आहे वर्तमान
• वर्तमान[बदला] - फील्ड मूल्य पर्याय सूचीच्या पुढील पुनरावृत्ती एंट्रीमधून निर्देशिकामध्ये पडलेला वर्तमान

अशा प्रकारे, प्रत्येक पत्त्यासाठी, प्रत्येक वेळी निर्देशिकेतील सर्व ओळींच्या गणनेचे संपूर्ण चक्र चालवले जाते, [शोधा] फील्डमधील मजकूर [बदला] फील्डमधील मूल्यासह बदलून.

आशा आहे की तुम्हाला कल्पना आली असेल 🙂

• सूत्रांचा वापर करून सूचीमधील मजकूर मोठ्या प्रमाणात बदला
  
• पॉवर क्वेरीमध्ये रेग्युलर एक्सप्रेशन्स (RegExp)