Power Query में List.Accumulate function के साथ बल्क टेक्स्ट रिप्लेसमेंट

सूत्रों के साथ संदर्भ सूची के अनुसार पाठ को जल्दी और थोक में कैसे बदलें - हमने इसे पहले ही सुलझा लिया है। अब इसे Power Query में करने का प्रयास करते हैं।

जैसा अक्सर होता है निष्पादन यह काम समझाने से कहीं ज्यादा आसान है क्यों यह काम करता है, लेकिन चलो दोनों करने की कोशिश करते हैं

तो, हमारे पास दो "स्मार्ट" डायनेमिक टेबल हैं जो सामान्य श्रेणियों से एक कीबोर्ड शॉर्टकट के साथ बनाई गई हैं कंट्रोल+T या टीम होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें):

मैंने पहली टेबल को कॉल किया जानकारी, दूसरी तालिका - निर्देशिकाक्षेत्र का उपयोग कर तालिका नाम (तालिका नाम) टैब निर्माता (डिज़ाइन).

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

चरण 1. निर्देशिका को Power Query में लोड करें और इसे एक सूची में बदल दें

सक्रिय सेल को संदर्भ तालिका में किसी भी स्थान पर सेट करने के बाद, टैब पर क्लिक करें जानकारी (तारीख)या टैब पर पावर क्वेरी (यदि आपके पास एक्सेल का पुराना संस्करण है और आपने पावर क्वेरी को एक अलग टैब पर ऐड-इन के रूप में स्थापित किया है) बटन पर टेबल/रेंज से (टेबल/रेंज से).

संदर्भ तालिका Power Query क्वेरी संपादक में लोड की जाएगी:

हस्तक्षेप न करने के लिए, एक स्वचालित रूप से जोड़ा गया कदम संशोधित प्रकार (परिवर्तित प्रकार) दाएं पैनल में, लागू किए गए चरणों को केवल चरण छोड़कर सुरक्षित रूप से हटाया जा सकता है स्रोत (स्रोत):

अब, आगे के परिवर्तन और प्रतिस्थापन करने के लिए, हमें इस तालिका को एक सूची (सूची) में बदलना होगा।

गीतात्मक विषयांतर

• तालिका एक द्वि-आयामी सरणी है जिसमें कई पंक्तियाँ और स्तंभ होते हैं।
• रिकॉर्ड (रिकॉर्ड) - एक-आयामी सरणी-स्ट्रिंग, जिसमें नामों के साथ कई फ़ील्ड-तत्व शामिल हैं, उदाहरण के लिए [नाम = "माशा", लिंग = "एफ", आयु = 25]
  
• सूची - एक आयामी सरणी-स्तंभ, जिसमें कई तत्व होते हैं, उदाहरण के लिए {1, 2, 3, 10, 42} or { "विश्वास आशा प्यार" }
  

अपनी समस्या को हल करने के लिए, हम मुख्य रूप से टाइप . में रुचि लेंगे सूची.

यहाँ चाल यह है कि Power Query में सूची आइटम न केवल साधारण संख्याएँ या पाठ हो सकते हैं, बल्कि अन्य सूचियाँ या रिकॉर्ड भी हो सकते हैं। यह इतनी मुश्किल सूची (सूची) में है, जिसमें रिकॉर्ड (रिकॉर्ड) शामिल हैं कि हमें अपनी निर्देशिका को चालू करने की आवश्यकता है। Power Query वाक्यात्मक संकेतन (वर्ग कोष्ठक में प्रविष्टियाँ, घुंघराले कोष्ठक में सूचियाँ) में यह इस तरह दिखेगा:

{

    [ढूंढें = "सेंट। पीटर्सबर्ग", बदलें = "सेंट। पीटर्सबर्ग"] ,

    [ढूंढें = "सेंट। पीटर्सबर्ग", बदलें = "सेंट। पीटर्सबर्ग"] ,

    [ढूंढें = "पीटर", बदलें = "सेंट। पीटर्सबर्ग"] ,

इत्यादि

}

पावर क्वेरी में निर्मित एम भाषा के एक विशेष कार्य का उपयोग करके ऐसा परिवर्तन किया जाता है - तालिका। रिकॉर्ड करने के लिए. इसे सीधे सूत्र पट्टी में लागू करने के लिए, इस फ़ंक्शन को वहां चरण कोड में जोड़ें स्रोत.

ये था:

बाद:

Table.ToRecords फ़ंक्शन को जोड़ने के बाद, हमारी तालिका का स्वरूप बदल जाएगा - यह रिकॉर्ड की सूची में बदल जाएगा। व्यक्तिगत रिकॉर्ड की सामग्री को किसी भी शब्द के बगल में सेल पृष्ठभूमि में क्लिक करके दृश्य फलक के नीचे देखा जा सकता है अभिलेख (लेकिन एक शब्द में नहीं!)

उपरोक्त के अलावा, हमारी बनाई गई सूची को कैश (बफर) करने के लिए - एक और स्ट्रोक जोड़ना समझ में आता है। यह Power Query को हमारी लुकअप सूची को एक बार मेमोरी में लोड करने के लिए बाध्य करेगा और जब हम बाद में इसे बदलने के लिए एक्सेस करेंगे तो इसे फिर से परिकलित नहीं करेंगे। ऐसा करने के लिए, हमारे सूत्र को दूसरे फ़ंक्शन में लपेटें - सूची। बफर:

इस तरह के कैशिंग से बड़ी मात्रा में प्रारंभिक डेटा को साफ़ करने के साथ गति में (कई बार!) बहुत ध्यान देने योग्य वृद्धि होगी।

यह हैंडबुक की तैयारी को पूरा करता है।

क्लिक करना बाकी है होम - बंद करें और लोड करें - बंद करें और लोड करें ... (होम - बंद करें और लोड करें - बंद करें और लोड करें ..), कोई विकल्प चुनें बस एक कनेक्शन बनाएं (केवल कनेक्शन बनाएं) और एक्सेल पर लौटें।

चरण 2. डेटा तालिका लोड हो रही है

यहाँ सब कुछ तुच्छ है। संदर्भ पुस्तक के साथ पहले की तरह, हम तालिका में किसी भी स्थान पर पहुंच जाते हैं, टैब पर क्लिक करें जानकारी बटन टेबल/रेंज से और हमारी मेज जानकारी Power Query में आ जाता है. स्वचालित रूप से जोड़ा गया चरण संशोधित प्रकार (परिवर्तित प्रकार) आप भी हटा सकते हैं:

इसके लिए कोई विशेष तैयारी करने की आवश्यकता नहीं है, और हम सबसे महत्वपूर्ण बात पर आगे बढ़ते हैं।

चरण 3. List.Acumulate फ़ंक्शन का उपयोग करके प्रतिस्थापन करें

आइए कमांड का उपयोग करके हमारी डेटा तालिका में एक परिकलित कॉलम जोड़ें एक कॉलम जोड़ना - कस्टम कॉलम (कॉलम जोड़ें — कस्टम कॉलम): और खुलने वाली विंडो में जोड़े गए कॉलम का नाम दर्ज करें (उदाहरण के लिए, सही पता) और हमारा जादू कार्य सूची। जमा करें:

क्लिक करना बाकी है OK - और हमें किए गए प्रतिस्थापन के साथ एक कॉलम मिलता है:

ध्यान दें कि:

• चूंकि पावर क्वेरी केस संवेदनशील है, इसलिए अंतिम पंक्ति में कोई प्रतिस्थापन नहीं था, क्योंकि निर्देशिका में हमारे पास "एसपीबी" है, न कि "एसपीबी"।
• यदि स्रोत डेटा में एक साथ बदलने के लिए कई सबस्ट्रिंग हैं (उदाहरण के लिए, 7 वीं पंक्ति में आपको "एस-पीबी" और "प्रोस्पेक्टस" दोनों को बदलने की आवश्यकता है), तो यह कोई समस्या नहीं पैदा करता है (सूत्रों से बदलने के विपरीत) पिछली विधि)।
• यदि स्रोत पाठ (9वीं पंक्ति) में बदलने के लिए कुछ भी नहीं है, तो कोई त्रुटि नहीं होती है (इसके विपरीत, फिर से, सूत्रों द्वारा प्रतिस्थापन से)।

इस तरह के अनुरोध की गति बहुत ही सभ्य है। उदाहरण के लिए, 5000 पंक्तियों के आकार वाले प्रारंभिक डेटा की तालिका के लिए, यह क्वेरी एक सेकंड से भी कम समय में अपडेट की गई थी (बफ़रिंग के बिना, वैसे, लगभग 3 सेकंड!)

List.accumulate फ़ंक्शन कैसे काम करता है

सिद्धांत रूप में, यह इस लेख का अंत हो सकता है (मेरे लिए लिखने के लिए, और आपके पढ़ने के लिए)। यदि आप न केवल सक्षम होना चाहते हैं, बल्कि यह भी समझना चाहते हैं कि यह "हुड के नीचे" कैसे काम करता है, तो आपको खरगोश के छेद में थोड़ा गहरा गोता लगाना होगा और सूची से निपटना होगा। संचय समारोह, जिसने सभी थोक प्रतिस्थापन किया हमारे लिए काम करें।

इस फ़ंक्शन का सिंटैक्स है:

= सूची। संचित करें (सूची, बीज, बिजली संचयक यंत्र)

जहां

• सूची वह सूची है जिसके तत्वों पर हम पुनरावृति कर रहे हैं। 
• बीज - आरंभिक राज्य
• बिजली संचयक यंत्र - एक फ़ंक्शन जो सूची के अगले तत्व पर कुछ ऑपरेशन (गणितीय, पाठ, आदि) करता है और एक विशेष चर में प्रसंस्करण के परिणाम को जमा करता है।
  

सामान्य तौर पर, Power Query में फ़ंक्शन लिखने का सिंटैक्स इस तरह दिखता है:

(तर्क 1, तर्क 2, ... तर्क एन) => तर्कों के साथ कुछ क्रियाएं

उदाहरण के लिए, योग फ़ंक्शन को इस प्रकार दर्शाया जा सकता है:

(ए, बी) => ए + बी

List.Accumulate के लिए, इस संचायक फ़ंक्शन में दो आवश्यक तर्क हैं (उन्हें कुछ भी नाम दिया जा सकता है, लेकिन सामान्य नाम हैं राज्य и वर्तमान, इस समारोह के लिए आधिकारिक सहायता के रूप में, जहां:

• राज्य - एक चर जहां परिणाम जमा होता है (इसका प्रारंभिक मूल्य ऊपर वर्णित एक है बीज)
  
• वर्तमान - सूची से अगला पुनरावृत्त मूल्य सूची
  

उदाहरण के लिए, आइए निम्नलिखित निर्माण के तर्क के चरणों पर एक नज़र डालें:

= सूची। संचित करें ({3, 2, 5}, 10, (राज्य, वर्तमान) => राज्य + वर्तमान)

यह नवीनतम संचित है राज्य मूल्य हमारी सूची है। परिणाम के रूप में कार्य और आउटपुट जमा करें:

यदि आप थोड़ी कल्पना करते हैं, तो List.Accumulate फ़ंक्शन का उपयोग करके, आप अनुकरण कर सकते हैं, उदाहरण के लिए, एक्सेल फ़ंक्शन CONCATENATE (पावर क्वेरी में, इसके एनालॉग को कहा जाता है) पाठ.संयोजन) अभिव्यक्ति का उपयोग करना:

या यहां तक ​​कि अधिकतम मान की खोज करें (एक्सेल के मैक्स फ़ंक्शन की नकल, जिसे पावर क्वेरी में कहा जाता है सूची.अधिकतम):

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

आइए फिर से उस निर्माण को देखें जिसने हमारी समस्या में प्रतिस्थापन किया:

सूची। जमा करें (निर्देशिका, [पता], (राज्य, वर्तमान) => पाठ। बदलें (राज्य, वर्तमान [ढूंढें], वर्तमान [बदलें]) )

वास्तव में यहाँ क्या हो रहा है?

1. प्रारंभिक मूल्य के रूप में (बीज) हम कॉलम से पहला अनाड़ी पाठ लेते हैं [पता] हमारी मेज: 199034, सेंट पीटर्सबर्ग, स्ट्र। बेरिंगा, डी। 1
2. फिर सूची। एक-एक करके सूची के तत्वों पर पुनरावृति जमा करें - पुस्तिका. इस सूची का प्रत्येक तत्व एक रिकॉर्ड है जिसमें "क्या खोजें - क्या बदलें" या, दूसरे शब्दों में, निर्देशिका में अगली पंक्ति फ़ील्ड की एक जोड़ी शामिल है।
3. संचायक फ़ंक्शन एक चर में डालता है राज्य प्रारंभिक मूल्य (पहला पता 199034, सेंट पीटर्सबर्ग, स्ट्र। बेरिंगा, डी। 1) और उस पर एक संचायक कार्य करता है - मानक एम-फ़ंक्शन का उपयोग करके प्रतिस्थापन ऑपरेशन टेक्स्ट.बदलें (एक्सेल के सबस्टिट्यूट फ़ंक्शन के अनुरूप)। इसका सिंटैक्स है:

   Text.Replace (मूल पाठ, जो हम खोज रहे हैं, जिसे हम बदल रहे हैं)

   और यहाँ हमारे पास है:

• राज्य हमारा गंदा पता है, जो में है राज्य (वहां से वहां पहुंचना बीज)
  
• वर्तमान [खोज] - क्षेत्र मूल्य ढूँढ़ने के लिए सूची की अगली पुनरावृत्त प्रविष्टि से निर्देशिका, जो चर में निहित है वर्तमान
• वर्तमान [बदलें] - क्षेत्र मूल्य विकल्प सूची की अगली पुनरावृत्त प्रविष्टि से निर्देशिकामें लेटा हुआ वर्तमान

इस प्रकार, प्रत्येक पते के लिए, निर्देशिका में सभी पंक्तियों की गणना का एक पूरा चक्र हर बार चलाया जाता है, [ढूंढें] फ़ील्ड से टेक्स्ट को [बदलें] फ़ील्ड से मान के साथ बदल दिया जाता है।

आशा है कि आपको विचार मिल गया होगा

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