استبدال النص المجمع في Power Query باستخدام List.Accumulate الدالة

كيفية استبدال النص بسرعة وبشكل مجمّع وفقًا لقائمة المراجع بالصيغ - لقد قمنا بالفعل بفرزها. الآن دعنا نحاول القيام بذلك في Power Query.

كما يحدث في كثير من الأحيان نفذ هذه المهمة أسهل بكثير من الشرح لماذا إنه يعمل ، لكن دعنا نحاول القيام بالأمرين 🙂

لذلك ، لدينا جدولين ديناميكيين "ذكيين" تم إنشاؤهما من نطاقات عادية باستخدام اختصار لوحة المفاتيح CTRL+T أو فريق الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول):

اتصلت بالطاولة الأولى البياناتالجدول الثاني - دليلباستخدام المجال اسم الطاولة (اسم الطاولة) علامة التبويب منشئ (التصميم).

المهمة: استبدل العناوين في الجدول البيانات كل التكرارات من عمود لايجاد كتيب إلى نظرائهم المناسبين من العمود استبدل. يجب أن يظل باقي النص في الخلايا كما هو.

الخطوة 1. قم بتحميل الدليل إلى Power Query وقم بتحويله إلى قائمة

بعد تعيين الخلية النشطة إلى أي مكان في الجدول المرجعي ، انقر فوق علامة التبويب البيانات (تاريخ)أو في علامة التبويب استفسار الطاقة (إذا كان لديك إصدار قديم من Excel وقمت بتثبيت Power Query كوظيفة إضافية في علامة تبويب منفصلة) على الزر من الجدول / النطاق (من جدول / نطاق).

سيتم تحميل الجدول المرجعي في محرر استعلام Power Query:

من أجل عدم التدخل ، يتم إضافة خطوة تلقائيًا نوع معدل (نوع تم تغييره) في اللوحة اليمنى ، يمكن حذف الخطوات المطبقة بأمان ، مع ترك الخطوة فقط مصدر (مصدر):

الآن ، لإجراء المزيد من عمليات التحويل والاستبدال ، نحتاج إلى تحويل هذا الجدول إلى قائمة (قائمة).

استطرادا غنائي

• طاولات ومكاتب هي مصفوفة ثنائية الأبعاد تتكون من عدة صفوف وأعمدة.
• سجل (تسجيل) - سلسلة مصفوفة أحادية البعد ، تتكون من عدة عناصر حقول بأسماء ، على سبيل المثال [الاسم = "ماشا" ، الجنس = "و" ، العمر = 25]
  
• قائمة - عمود مصفوفة أحادي البعد ، يتكون من عدة عناصر ، على سبيل المثال {1 و 2 و 3 و 10 و 42} or {"Faith Hope Love"}
  

لحل مشكلتنا ، سنكون مهتمين بشكل أساسي بالنوع قائمة.

الحيلة هنا هي أن عناصر القائمة في Power Query لا يمكن أن تكون مجرد أرقام أو نصوص عادية ، ولكن أيضًا قوائم أو سجلات أخرى. في مثل هذه القائمة (القائمة) الصعبة ، التي تتكون من السجلات (السجلات) ، نحتاج إلى قلب دليلنا. في التدوين النحوي في Power Query (الإدخالات الموجودة بين أقواس مربعة ، والقوائم الموجودة بين قوسين معقوفين) سيبدو هذا كما يلي:

{

    [بحث = "St. بطرسبورغ "، استبدال =" سانت بطرسبرغ " بطرسبورغ "] ,

    [بحث = "St. بطرسبورغ "، استبدال =" سانت بطرسبرغ " بطرسبورغ "] ,

    [البحث = "Peter" ، استبدل = "St. بطرسبورغ "] ,

وما إلى ذلك.

}

يتم إجراء هذا التحويل باستخدام وظيفة خاصة للغة M المضمنة في Power Query - الجدول. لتطبيقه مباشرة في شريط الصيغة ، أضف هذه الوظيفة إلى رمز الخطوة هناك مصدر.

كانت:

بعد:

بعد إضافة وظيفة Table.ToRecords ، سيتغير مظهر الجدول - سيتحول إلى قائمة من السجلات. يمكن رؤية محتويات السجلات الفردية في الجزء السفلي من جزء العرض عن طريق النقر فوق خلفية الخلية المجاورة لأي كلمة القيد (لكن ليس بكلمة واحدة!)

بالإضافة إلى ما سبق ، من المنطقي إضافة ضربة واحدة أخرى - لتخزين (تخزين مؤقت) قائمتنا التي أنشأناها. سيؤدي ذلك إلى إجبار Power Query على تحميل قائمة البحث الخاصة بنا مرة واحدة في الذاكرة وعدم إعادة حسابها مرة أخرى عند الوصول إليها لاحقًا لاستبدالها. للقيام بذلك ، قم بلف صيغتنا في دالة أخرى - List.Buffer:

سيعطي هذا التخزين المؤقت زيادة ملحوظة في السرعة (عدة مرات!) مع قدر كبير من البيانات الأولية ليتم مسحها.

هذا يكمل إعداد الكتيب.

يبقى أن تضغط الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ... (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ..)، حدد اختيارا فقط قم بإنشاء اتصال (فقط إنشاء اتصال) والعودة إلى Excel.

الخطوة 2. تحميل جدول البيانات

كل شيء مبتذل هنا. كما في السابق مع الكتاب المرجعي ، نصل إلى أي مكان في الجدول ، انقر فوق علامة التبويب البيانات زر من الجدول / النطاق وطاولتنا البيانات يدخل في Power Query. الخطوة المضافة تلقائيًا نوع معدل (نوع تم تغييره) يمكنك أيضًا إزالة:

لا يلزم القيام بأي إجراءات تحضيرية خاصة بها ، وننتقل إلى أهم شيء.

الخطوة 3. إجراء عمليات الاستبدال باستخدام وظيفة List.Accumulate

دعنا نضيف عمودًا محسوبًا إلى جدول البيانات الخاص بنا باستخدام الأمر إضافة عمود - عمود مخصص (إضافة عمود - عمود مخصص): وأدخل اسم العمود المضاف في النافذة التي تفتح (على سبيل المثال ، العنوان المصحح) ووظيفتنا السحرية قائمة. تراكم:

يبقى أن تضغط OK - ونحصل على عمود بالبدائل التي تم إجراؤها:

لاحظ أن:

• نظرًا لأن Power Query حساس لحالة الأحرف ، لم يكن هناك بديل في السطر قبل الأخير ، لأنه لدينا في الدليل "SPb" ، وليس "SPb".
• إذا كان هناك العديد من السلاسل الفرعية التي يجب استبدالها مرة واحدة في البيانات المصدر (على سبيل المثال ، في السطر السابع تحتاج إلى استبدال كل من "S-Pb" و "نشرة الإصدار") ، فإن هذا لا يؤدي إلى أي مشاكل (على عكس الاستبدال بالصيغ من الطريقة السابقة).
• إذا لم يكن هناك شيء يمكن استبداله في النص المصدر (السطر التاسع) ، فلن تحدث أخطاء (على عكس الاستبدال بالصيغ مرة أخرى).

سرعة مثل هذا الطلب لائقة للغاية. على سبيل المثال ، بالنسبة لجدول بيانات أولية بحجم 5000 صف ، تم تحديث هذا الاستعلام في أقل من ثانية (بدون تخزين مؤقت ، بالمناسبة ، حوالي 3 ثوان!)

كيف تعمل وظيفة List.Accumulate

من حيث المبدأ ، قد تكون هذه هي نهاية هذه المقالة (بالنسبة لي لكتابة ، ولكم لقراءتها). إذا كنت لا تريد أن تكون قادرًا على ذلك فحسب ، بل تفهم أيضًا كيفية عمله "تحت الغطاء" ، فسيتعين عليك الغوص أعمق قليلاً في حفرة الأرانب والتعامل مع القائمة. العمل بالنسبة لنا.

صيغة هذه الوظيفة هي:

= قائمة تتراكم (قائمة, بذرة, المجمع)

أين

• قائمة هي القائمة التي نكرر عناصرها. 
• بذرة - الحالة الأولية
• المجمع - وظيفة تقوم ببعض العمليات (حسابية ، نصية ، إلخ) على العنصر التالي من القائمة وتجمع نتيجة المعالجة في متغير خاص.
  

بشكل عام ، يبدو بناء الجملة لوظائف الكتابة في Power Query كما يلي:

(الوسيطة 1 ، الوسيطة 2 ، ... الوسيطة N) => بعض الإجراءات مع الوسيطات

على سبيل المثال ، يمكن تمثيل دالة الجمع على النحو التالي:

(أ ، ب) => أ + ب

بالنسبة إلى List.Accumulate ، تشتمل وظيفة المُجمِّع هذه على وسيطتين مطلوبتين (يمكن تسميتهما بأي شيء ، لكن الأسماء المعتادة هي حالة и تيار، كما في المساعدة الرسمية لهذه الوظيفة ، حيث:

• حالة - متغير حيث يتم تجميع النتيجة (قيمته الأولية هي القيمة المذكورة أعلاه بذرة)
  
• تيار - القيمة المكررة التالية من القائمة قائمة
  

على سبيل المثال ، دعنا نلقي نظرة على خطوات منطق البناء التالي:

= قائمة تتراكم ({3 و 2 و 5}, 10, (الحالة ، الحالية) => الحالة + الحالية)

هذا هو آخر تراكم حالة القيمة هي قائمتنا ، قم بتجميع الوظيفة والمخرجات نتيجة لذلك:

إذا كنت تتخيل قليلاً ، فاستخدم القائمة. تراكم وظيفة ، يمكنك محاكاة ، على سبيل المثال ، وظيفة Excel CONCATENATE (في Power Query ، يسمى التناظرية الخاصة بها نص) باستخدام التعبير:

أو حتى ابحث عن القيمة القصوى (تقليد وظيفة MAX في Excel ، والتي تسمى في Power Query قائمة):

ومع ذلك ، فإن الميزة الرئيسية لـ List.Accumulate هي القدرة على معالجة ليس فقط نصًا بسيطًا أو قوائم رقمية كوسائط ، ولكن كائنات أكثر تعقيدًا - على سبيل المثال ، قوائم من قوائم أو قوائم من السجلات (مرحبًا ، دليل!)

دعنا ننظر مرة أخرى إلى البناء الذي أدى إلى الاستبدال في مشكلتنا:

قائمة. تتراكم (دليل, [عنوان], (الحالة ، الحالية) => نص. استبدال (الحالة ، [بحث] الحالي ، [استبدال] الحالي) )

ما يحدث في الواقع هنا؟

1. كقيمة أولية (بذرة) نأخذ أول نص أخرق من العمود [عنوان] طاولتنا: 199034 ، سانت بطرسبرغ ، شارع. بيرينجا ، د. 1
2. ثم قائمة. قم بتجميع التكرارات على عناصر القائمة واحدة تلو الأخرى - كتيب. كل عنصر من عناصر هذه القائمة عبارة عن سجل يتكون من زوج من الحقول "ما يجب البحث عنه - ما الذي يجب استبداله" أو ، بعبارة أخرى ، السطر التالي في الدليل.
3. تضع وظيفة المجمع في متغير حالة القيمة الأولية (العنوان الأول 199034 ، سانت بطرسبرغ ، شارع. بيرينجا ، د. 1) ويقوم بوظيفة تراكم عليها - عملية الاستبدال باستخدام الوظيفة M. نص استبدل (مماثلة لوظيفة الاستبدال في Excel). تركيبها هو:

   نص - استبدل (نص أصلي ، ما نبحث عنه ، ما نستبدل به)

   وهنا لدينا:

• حالة هو عنواننا القذر الذي يقع فيه حالة (الوصول إلى هناك من بذرة)
  
• [بحث] الحالي - قيمة الحقل لايجاد من الإدخال المتكرر التالي في القائمة دليلالتي تقع في المتغير تيار
• الحالي [استبدال] - قيمة الحقل استبدل من الإدخال المتكرر التالي في القائمة دليلالكذب في تيار

وهكذا ، لكل عنوان ، يتم تشغيل دورة كاملة من تعداد جميع الأسطر في الدليل في كل مرة ، مع استبدال النص من حقل [بحث] بالقيمة من الحقل [استبدال].

أتمنى أن تكون قد حصلت على الفكرة 🙂

• استبدال النص المجمع في قائمة باستخدام الصيغ
  
• التعبيرات العادية (RegExp) في Power Query