قد تكون معتادا على استعلامات المعلمات مع استخدامها في SQL أو Microsoft Query. ومع ذلك، هناك اختلافات رئيسية بين معلمات Power Query:
- يمكن استخدام المعلمات في أي خطوة من خطوات الاستعلام. بالإضافة إلى عملها كعامل تصفية بيانات، يمكن استخدام المعلمات لتحديد أشياء مثل مسار ملف أو اسم خادم.
- لا تطالب المعلمات بالإدخال. بدلا من ذلك، يمكنك تغيير قيمتها بسرعة باستخدام Power Query. يمكنك حتى تخزين القيم واستردادها من الخلايا في Excel.
- يتم حفظ المعلمات في استعلام معلمات بسيط، ولكنها تكون منفصلة عن استعلامات البيانات التي يتم استخدامها فيها. بمجرد الإنشاء، يمكنك إضافة معلمة إلى الاستعلامات حسب الحاجة.
ملاحظة إذا كنت تريد الطريقة الأخرى لإنشاء معلمات الاستعلام، فراجع إنشاء استعلام معلمات في Microsoft Query.
إنشاء معلمة
يمكنك استخدام معلمة لتغيير قيمة في الاستعلام تلقائيا وتجنب تحرير الاستعلام في كل مرة لتغيير القيمة. ما عليك سوى تغيير قيمة المعلمة. بمجرد إنشاء معلمة، يتم حفظها في استعلام معلمات خاص يمكنك تغييره بسهولة من Excel مباشرة.
تحديد البيانات>الحصول على البيانات>مصادر> أخرىابدأ تشغيل محرر Power Query.
في محرر Power Query، حدد الصفحة الرئيسية>إدارة المعلمات > الجديدة.
في مربع الحوار إدارة المعلمة ، حدد جديد.
قم بتعيين ما يلي حسب الحاجة:
الاسم يجب أن يعكس ذلك وظيفة المعلمة، ولكن اجعلها قصيرة قدر الإمكان. الوصف يمكن أن يحتوي هذا على أي تفاصيل من شأنها مساعدة الأشخاص على استخدام المعلمة بشكل صحيح. مطلوب قم بتنفيذ أحد الإجراءات التالية:
أي قيمة يمكنك إدخال أي قيمة من أي نوع بيانات في استعلام المعلمة.
قائمة القيم يمكنك تحديد القيم بقائمة معينة عن طريق إدخالها في الشبكة الصغيرة. يجب عليك أيضا تحديد قيمة افتراضيةوقيمة حالية أدناه.
استعلام حدد استعلام قائمة، يشبه عمود هيكلي "قائمة " مفصول بفواصل ومحاط بأقواس كبيرة.
على سبيل المثال، يمكن أن يحتوي حقل حالة المشاكل على ثلاث قيم: {"جديد"، "مستمر"، "مغلق"}. يجب إنشاء استعلام القائمة مسبقا عن طريق فتح "المحرر المتقدم" (حدد "المحرر المتقدم للشريط الرئيسي>")، وإزالة قالب التعليمات البرمجية، وإدخال قائمة القيم في تنسيق قائمة الاستعلامات، ثم تحديد "تم".
بمجرد الانتهاء من إنشاء المعلمة، يتم عرض استعلام القائمة في قيم المعلمات.Type تحدد هذه الخطوة نوع بيانات المعلمة. القيم المقترحة إذا أردت، أضف قائمة بالقيم أو حدد استعلاما لتقديم اقتراحات للإدخال. القيمة الافتراضية يظهر هذا فقط إذا تم تعيين "القيم المقترحة " إلى قائمة القيم، وتحديد عنصر القائمة الافتراضي. في هذه الحالة، يجب عليك اختيار خيار افتراضي. القيمة الحالية استنادا إلى المكان الذي تستخدم فيه المعلمة، إذا كانت فارغة، فلن يرجع الاستعلام أي نتائج. إذا تم تحديد "مطلوب" ، فلن تكون القيمة الحالية فارغة. لإنشاء المعلمة، حدد موافق.
استخدام معلمة لتغيير مصدر بيانات
فيما يلي طريقة لإدارة التغييرات في مواقع مصادر البيانات والمساعدة على منع أخطاء التحديث. على سبيل المثال، بافتراض وجود مخطط ومصدر بيانات مماثلين، قم بإنشاء معلمة لتغيير مصدر بيانات بسهولة وللمساعدة على منع حدوث أخطاء في تحديث البيانات. في بعض الأحيان يتغير الخادم أو قاعدة البيانات أو المجلد أو اسم الملف أو الموقع. قد يقوم مدير قاعدة البيانات بتبديل خادم من حين لآخر، أو يتم نقل إسقاط شهري من ملفات CSV إلى مجلد آخر، أو قد تحتاج إلى التبديل بسهولة بين بيئة التطوير/الاختبار/الإنتاج.
الخطوة 1: إنشاء معلمة استعلام
في المثال التالي، لديك عدة ملفات CSV يمكنك استيرادها باستخدام عملية استيراد المجلد (تحديد Data>Get data>from FilesFrom>Folder) من المجلد C:\DataFilesCSV1. ولكن في بعض الأحيان يتم استخدام مجلد مختلف أحيانا كموقع لإفلات الملفات، C:\DataFilesCSV2. يمكنك استخدام معلمة في استعلام كقيمة بديلة لمجلد مختلف.
حدد الصفحة الرئيسية>، إدارة المعلمات>معلمة جديدة.
أدخل المعلومات التالية في مربع الحوار إدارة المعلمة :
الاسم CSVFileDrop الوصف موقع بديل لإفلات الملف مطلوب نعم Type نص القيم المقترحة أي قيمة القيمة الحالية C:\DataFilesCSV1 حدّد موافق.
الخطوة 2: إضافة المعلمة إلى استعلام البيانات
- لتعيين اسم المجلد كمعلمة، في إعدادات الاستعلام، ضمن خطوات الاستعلام، حدد المصدر، ثم حدد تحرير الإعدادات.
- تأكد من تعيين خيار مسار الملف إلى "معلمة"، ثم حدد المعلمة التي أنشأتها للتو من القائمة المنسدلة.
- حدّد موافق.
الخطوة 3: تحديث قيمة المعلمة
لقد تغير موقع المجلد للتو، لذا يمكنك الآن ببساطة تحديث استعلام المعلمات.
- حدد اتصالات البيانات> & علامات التبويب "استعلامات>"، وانقر بزر الماوس الأيمن فوق استعلام المعلمات، ثم حدد تحرير.
- أدخل الموقع الجديد في المربع "القيمة الحالية "، على سبيل المثال C:\DataFilesCSV2.
- حدد الصفحة الرئيسية>، أغلق، & تحميل.
- لتأكيد النتائج، أضف بيانات جديدة إلى مصدر البيانات، ثم قم بتحديث استعلام البيانات بالمعلمة المحدثة (حدد تحديث البيانات>الكل).
استخدام معلمة لتصفية البيانات
قد تحتاج في بعض الأحيان إلى طريقة سهلة لتغيير عامل تصفية الاستعلام للحصول على نتائج مختلفة دون تحرير الاستعلام أو إجراء نسخ مختلفة قليلا من نفس الاستعلام. في هذا المثال، نقوم بتغيير تاريخ لتغيير عامل تصفية البيانات بسهولة.
لفتح استعلام، حدد موقع استعلام تم تحميله مسبقا من محرر Power Query، وحدد خلية في البيانات، ثم حددتحريرالاستعلام>. لمزيد من المعلومات، راجع إنشاء استعلام أو تحميله أو تحريره في Excel.
حدد سهم عامل التصفية في أي رأس عمود لتصفية البيانات، ثم حدد أمر عامل تصفية، مثل "عوامل تصفية> التاريخ/الوقت بعد". يظهر مربع الحوار "صفوف التصفية ".
حدد الزر الموجود إلى يمين مربع " القيمة "، ثم قم بأحد الإجراءات التالية:
- لاستخدام معلمة موجودة، حدد معلمة، ثم حدد المعلمة التي تريدها من القائمة التي تظهر على الجانب الأيسر.
- لاستخدام معلمة جديدة، حدد معلمة جديدة، ثم أنشئ معلمة.
أدخل التاريخ الجديد في مربع القيمة الحالية ، ثم حدد إغلاق الصفحة الرئيسية>& تحميل.
لتأكيد النتائج، أضف بيانات جديدة إلى مصدر البيانات، ثم قم بتحديث استعلام البيانات بالمعلمة المحدثة (حدد تحديث البيانات>الكل). على سبيل المثال، قم بتغيير قيمة عامل التصفية إلى تاريخ مختلف لعرض نتائج جديدة.
أدخل التاريخ الجديد في المربع "القيمة الحالية ".
حدد الصفحة الرئيسية>، أغلق، & تحميل.
لتأكيد النتائج، أضف بيانات جديدة إلى مصدر البيانات، ثم قم بتحديث استعلام البيانات بالمعلمة المحدثة (حدد تحديث البيانات>الكل).
استخدام قيمة خلية لتصفية البيانات
في هذا المثال، تتم قراءة القيمة الموجودة في معلمة الاستعلام من خلية في المصنف. لست بحاجة إلى تغيير معلمة الاستعلام، ما عليك سوى تحديث قيمة الخلية. على سبيل المثال، أنت بحاجة إلى تصفية عمود حسب الحرف الأول، ولكن يمكنك بسهولة تغيير القيمة إلى أي حرف من أ إلى ي.
على ورقة العمل في مصنف حيث يتم تحميل الاستعلام الذي تريد تصفيته، قم بإنشاء جدول Excel يتضمن خليتين: رأس وقيمة.
عامل التصفية الخاص بي G حدد خلية في جدول Excel، ثم حدد "الحصول على البيانات>>من الجدول/النطاق". يظهر محرر Power Query.
في المربع " الاسم" في الجزء " إعدادات الاستعلام " على اليسار، قم بتغيير اسم الاستعلام ليكون أكثر معنى، مثل FilterCellValue.
لتمرير القيمة الموجودة في الجدول، وليس الجدول نفسه، انقر بزر الماوس الأيمن فوق القيمة الموجودة في "معاينة البيانات"، ثم حدد "التنقل لأسفل".
لاحظ أن الصيغة تغيرت إلى= #"Changed Type"{0}[MyFilter]
عندما تستخدم جدول Excel كعامل تصفية في الخطوة 10، يشير Power Query إلى قيمة الجدول كشرط التصفية. قد يتسبب الرجوع مباشرة إلى جدول Excel في حدوث خطأ.حدد الصفحة الرئيسية>، أغلق & تحميل>،إغلاق & التحميل إلى. لديك الآن معلمة استعلام تسمى "FilterCellValue" تستخدمها في الخطوة 12.
في مربع الحوار "استيراد البيانات"، حدد "إنشاء اتصال فقط"، ثم حدد "موافق".
افتح الاستعلام الذي تريد تصفيته بالقيمة في جدول FilterCellValue، وهو استعلام تم تحميله مسبقا من محرر Power Query، عبر تحديد خلية في البيانات، ثم تحديدتحريرالاستعلام>. لمزيد من المعلومات، راجع إنشاء استعلام أو تحميله أو تحريره في Excel.
حدد سهم عامل التصفية في أي رأس عمود لتصفية البيانات، ثم حدد أمر عامل تصفية، مثلتصفية النص> تبدأ ب. يظهر مربع الحوار "صفوف التصفية ".
أدخل أي قيمة في مربع "القيمة"، مثل "G"، ثم حدد موافق. في هذه الحالة، تكون القيمة عنصرا نائبا مؤقتا للقيمة الموجودة في الجدول FilterCellValue والتي يمكنك إدخالها في الخطوة التالية.
حدد السهم الموجود على الجانب الأيسر من شريط الصيغة لعرض الصيغة بالكامل. فيما يلي مثال لشرط عامل تصفية في صيغة:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
حدد قيمة عامل التصفية. في الصيغة، حدد "G".
باستخدام M Intellisense، أدخل الأحرف القليلة الأولى من الجدول FilterCellValue الذي قمت بإنشائه، ثم حدده من القائمة التي تظهر.
حدد الصفحة الرئيسية>، وأغلق>،وأغلق، & تحميل.
النتيجة
يستخدم الاستعلام الآن القيمة الموجودة في جدول Excel التي أنشأتها لتصفية نتائج الاستعلام. لاستخدام قيمة جديدة، قم بتحرير محتويات الخلية في جدول Excel الأصلي في الخطوة 1، وغير "G" إلى "V"، ثم قم بتحديث الاستعلام.
التحكم في استخدام معلمات الاستعلامات
يمكنك التحكم فيما إذا كانت معلمات الاستعلامات مسموح بها أم لا.
- في محرر Power Query، حدد خيارات الملفات>والإعدادات> خيارات >الاستعلاممحرر Power Query.
- في الجزء الموجود على اليمين، ضمن عمومي، حدد محرر Power Query.
- في الجزء الموجود على اليسار، ضمن المعلمات، حدد اسمح دوما بالمعلمات في مربعات حوار مصدر البيانات والتحويل.