معلمة مسارات البيانات في Power Query

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

ماذا تفعل في مثل هذه الحالة؟ دعونا نلقي نظرة على هذه الحالة بمزيد من التفصيل مع المثال التالي.

صياغة المشكلة

افترض أن لدينا في المجلد E:تقارير المبيعات يكمن الملف أفضل 100 منتج. xls، وهو تحميل من قاعدة بيانات الشركة أو نظام تخطيط موارد المؤسسات (1C ، SAP ، إلخ.) يحتوي هذا الملف على معلومات حول عناصر السلع الأكثر شيوعًا ويبدو كما يلي:

ربما يكون من الواضح فورًا أنه من المستحيل تقريبًا العمل معه في Excel في هذا النموذج: ستتداخل الصفوف الفارغة من خلال واحد يحتوي على بيانات وخلايا مدمجة وأعمدة إضافية ورأس متعدد المستويات وما إلى ذلك.

لذلك ، بجانب هذا الملف في نفس المجلد ، نقوم بإنشاء ملف جديد آخر معالج xlsx، حيث سننشئ استعلام Power Query من شأنه تحميل البيانات القبيحة من ملف التحميل المصدر أفضل 100 منتج. xls، ورتبهم بالترتيب:

تقديم طلب لملف خارجي

فتح الملف معالج xlsx، حدد في علامة التبويب البيانات أمر إحضار البيانات - من ملف - من مصنف Excel (بيانات - إحضار بيانات - من ملف - من Excel)، ثم حدد موقع الملف المصدر والورقة التي نحتاجها. سيتم تحميل البيانات المحددة في محرر Power Query:

دعنا نعيدهم إلى طبيعتهم:

1. احذف الأسطر الفارغة باستخدام الصفحة الرئيسية - حذف الأسطر - حذف الأسطر الفارغة (الصفحة الرئيسية - إزالة الصفوف - إزالة الصفوف الفارغة).
2. احذف أعلى 4 أسطر غير ضرورية الصفحة الرئيسية - حذف الصفوف - حذف الصفوف العليا (الصفحة الرئيسية - إزالة الصفوف - إزالة الصفوف العلوية).
3. ارفع الصف الأول إلى رأس الجدول بالزر استخدم السطر الأول كرؤوس علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - استخدم الصف الأول كرأس).
4. افصل المقالة المكونة من خمسة أرقام عن اسم المنتج في العمود الثاني باستخدام الأمر عمود الانقسام علامة التبويب تحول (تحويل - عمود انقسام).
5. احذف الأعمدة غير الضرورية وأعد تسمية عناوين الأعمدة المتبقية لرؤية أفضل.

نتيجة لذلك ، يجب أن نحصل على الصورة التالية الأكثر إمتاعًا:

يبقى تحميل هذا الجدول المسقوف مرة أخرى إلى الورقة في ملفنا معالج xlsx فريق إغلاق وتحميل (الصفحة الرئيسية - إغلاق وتحميل) علامة التبويب الصفحة الرئيسية:

البحث عن مسار الملف في الطلب

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

في النافذة التي تفتح ، يكشف السطر الثاني على الفور عن مسار مشفر إلى ملف التحميل الأصلي. إذا تمكنا من استبدال هذه السلسلة النصية بمعلمة أو متغير أو رابط إلى خلية ورقة Excel حيث يكون هذا المسار مكتوبًا مسبقًا ، فيمكننا تغييره بسهولة لاحقًا.

أضف جدولًا ذكيًا بمسار ملف

دعنا نغلق Power Query الآن ونعود إلى ملفنا معالج xlsx. دعنا نضيف ورقة فارغة جديدة وننشئ جدولاً "ذكيًا" صغيرًا عليها ، في الخلية الوحيدة التي سيتم كتابة المسار الكامل لملف بيانات المصدر الخاص بنا:

لإنشاء جدول ذكي من نطاق عادي ، يمكنك استخدام اختصار لوحة المفاتيح CTRL+T أو زر تنسيق كجدول علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - تنسيق كجدول). يمكن أن يكون عنوان العمود (الخلية A1) أي شيء على الإطلاق. لاحظ أيضًا أنه من أجل التوضيح ، أعطيت الجدول اسمًا المعلمات علامة التبويب منشئ (التصميم).

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

إذا افترضنا أن ملف البيانات المصدر يكمن دائمًا في نفس المجلد مثل المعالج الخاص بنا ، فيمكن تشكيل المسار الذي نحتاجه بالصيغة التالية:

= LEFT (CELL ("اسم الملف") ؛ البحث عن ("[" ؛ CELL ("اسم الملف")) - 1) & "أفضل 100 منتج. xls"

أو باللغة الإنجليزية:

= اليسار (CELL («اسم الملف») ؛ البحث عن («[« ؛ CELL («اسم الملف»))) - 1) & »Топ-100 товаров.xls»

… أين هي الوظيفة ليفسيمف (اليسار) يأخذ جزءًا من النص من الرابط الكامل حتى القوس المربع الافتتاحي (أي المسار إلى المجلد الحالي) ، ثم يتم لصق اسم ملف البيانات المصدر وامتداده عليه.

معلمة المسار في الاستعلام

تبقى اللمسة الأخيرة والأكثر أهمية - لكتابة المسار إلى الملف المصدر في الطلب أفضل 100 منتج. xls، بالإشارة إلى الخلية A2 في الجدول "الذكي" الذي أنشأناه المعلمات.

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

Excel.CurrentWorkbook (){[Name = ”Settings”]} [المحتوى]0،XNUMX،XNUMX {}[مسار البيانات المصدر]

دعونا نرى ما يتكون منه:

• Excel.CurrentWorkbook () هي وظيفة للغة M للوصول إلى محتويات الملف الحالي
• {[Name = ”Settings”]} [المحتوى] - هذه معلمة تنقيح للوظيفة السابقة ، تشير إلى أننا نريد الحصول على محتويات الجدول "الذكي" المعلمات
• [مسار البيانات المصدر] هو اسم العمود في الجدول المعلماتالذي نشير إليه
  
• 0،XNUMX،XNUMX {} هو رقم الصف في الجدول المعلماتالتي نريد أخذ البيانات منها. لا يتم احتساب الحد الأقصى ويبدأ الترقيم من الصفر وليس من واحد.

هذا كل شيء في الواقع.

يبقى أن تضغط نهاية وتحقق من كيفية عمل طلبنا. الآن ، عند إرسال المجلد بأكمله مع كلا الملفين بداخله إلى جهاز كمبيوتر آخر ، سيظل الطلب قيد التشغيل ويحدد المسار إلى البيانات تلقائيًا.

• ما هو Power Query ولماذا هو مطلوب عند العمل في Microsoft Excel
• كيفية استيراد مقتطف نص عائم إلى Power Query
• إعادة تصميم XNUMXD Crosstab إلى جدول مسطح باستخدام Power Query