الرئيسية | Power BI | مقدمة وتعريف بوظائف آداة Power Query

مقدمة وتعريف بوظائف آداة Power Query


Power Query Editor
Power Query عبارة عن إضافة add-in مجانية تم تصميمها بواسطة مايكروسوفت Microsoft، يقوم ال Power Query بثلاثة وظائف رئيسية هي إستخلاص البيانات Extract من العديد من مصادر البيانات، ثم معالجة هذه البيانات Transform بأدوات وطرق مختلفة تتيح لنا تعديل شكل البيانات المستخلصة من المصدر الى شكل اخر يناسب طريقة عرض التقارير، ثم ختاماً تخزين Load هذه البيانات في ملف الإكسل؛ لذلك ستجد أن هذه الوظائف الثلاثة تختصر في ETL (Extract – Transform – Load).

يُعتبر Power Query أحد عناصر حزمة Power BI (Business Intelligence( التي يمكن إضافتها الى مايكروسوفت اكسل، هذه العناصر هي:

  1. Power Query
  2. Power Pivot
  3. Power View
  4. Power Map

إن كنت لا تجد قائمة أدوات Power Query في الإكسل؟ يمكنك أن تقرأ مقال [تثبيت Power Query في مايكروسوفت اكسل].

وظيفة Power Query الأولى: إستخلاص البيانات Extract data.

أوامر إستخلاص البيانات من المصادر المختلفة
يُتيح لنا Power Query إستخلاص البيانات من العديد من المصادر، بعض هذه المصادر قد تكون متاحة ضمن أدوات الإكسل الأصلية في قائمة Data، إلا أنه حسب إفادة الكثير من مستخدمي power query فإن “أدوات إستخلاص البيانات ضمن power query بها مزايا اضافية عن الادوات الأصلية في الأكسل”.

كما في الصورة السابقة ستجد أننا نستطيع إستخلاص البيانات من

    • صفحات الويب
    • من ملف / فولدر
      • ملفات الإكسل
      • ملفات نصية بالإمتداد txt
      • ملفات من نوع comma separated value (CSV)
      • الملفات التي في فولدر معين from folder

      2 أوامر إستخلاص البيانات من المصادر المختلفة

 

    • إستخلاص البيانات من العديد من برامج قواعد البيانات
      أوامر إستخلاص البيانات من المصادر المختلفة 3

 

وظيفة Power Query الثانية : معالجة وتحويل البيانات Transform data.

ما المقصود بتحويل ومعالجة البيانات؟
هي عملية تغيير شكل البيانات المستخلصة من المصدر الى شكل اخر، فيما يلي بعض الأمثلة الخاصة بتحويل ومعالجة البيانات -سيتم تخصيص موضوعات مستقلة لأكثر عمليات المعالجة إستخداماً إن شاء الله-

    • عمل unpivot للبيانات.
      مثال لتحويل ومعالجة البيانات UnPivot Data
      لاحظ مبيعات شهر يناير في الجدول جهة اليسار ونتيجة عمل unpivot في الجدول جهة اليمين.
      الجدول جهة اليسار يوجد لدينا 72 قيمة للمبيعات (12 شهر * 6 مناطق بيعية) هذه القيم هي التي توجد في الجدول جهة اليمين وعدد الحركات تم تحديده بإطار أحمر في الجهة السفلية / يسار.
    • دمج بيانات اكثر من استعلام append queries (دمج رأسي يترتب عليه اضافة حركات/صفوف الى الجدول الأصلي).
      مثال لتحويل ومعالجة البيانات Append Data

 

  • دمج بيانات اكثر من استعلام merge queries (دمج أفقي يترتب عليه اضافة أعمدة جديدة الى العمود الأصلي).
  • إجراء عمليات مثل text to columns و حذف البيانات المكررة.
  • تحويل الصفوف الى اعمدة والعكس Transpose.

هذه العمليات تتم من خلال الواجهة التالية Power Query Editor
واجهة Power Query - Power Query Editor

أجزاء شاشة محرر الإستعلامات Power Query Editor

    1. قوائم الأوامر المختلفة query editor ribbons
    2. قائمة الإستعلامات Queries
    3. شريط المعادلات Formula Bar، الوضع الإفتراضي أنه مخفي؛ لكي تُظهر شريط المعادلات لابد لنا من تفعيل الإختيار Formula Bar من قائمة View
      إظهار شريط الصيغ Formula Bar

 

  1. مساحة العمل / البيانات التي تم إستخلاصها والمراد معالجتها -هنا لا تُعرض كل الحركات/الصفوف، ولكن جزء من أصل البيانات، -يمكن أن ننظر لهذه الشاشة على أنها عرض مبدئي preview للبيانات-.
  2. عند تحديد أحد الخلايا في مساحة العمل سيظهر بها محتوى هذه الخلية، هذا الجزء تظهر أهميته عند تحديد العناصر التي تتضمن أكثر من قيمة مثل Table / Record / List / Binary
    عرض محتويات الخلية التي بها أكثر من قيمة
    في الصورة السابقة تم تحديد خلية تشتمل على nested table ليظهر لنا القيم المخزنة في هذا الجدول.
  3. قائمة بالخطوات المنفذة على البيانات Applied Steps بداية من الربط مع مصدر البيانات مروراً بعمليات المعالجة والتحويل.

وظيفة Power Query الثالثة : تخزين البيانات Load data

بعد أن إستخلصنا البيانات من المصدر وقمنا بعمليات المعالجة والتحويل، سنحتاج الى تخزين هذه البيانات حتى نستطيع إستخدامها؛ سنجد أن Power Query يمكننا من أحد إختيارين لتخزين البيانات وهي:
طرق تخزين Load البيانات

  1. إنشاء جدول Table به البيانات، هذا الجدول إما أن يكون في ورقة عمل worksheet جديدة أو في ورقة عمل حالية. أو
  2. إنشاء عملية ربط Connection دون عرض البيانات في ورقة عمل، هذا الإختيار يترتب عليه تخزين البيانات داخل power query.

في أسفل الصورة السابقة يوجد أمر خاص بإضافة البيانات الى Data Model، وال Data Model عبارة عن خاصية جديدة بدءًا من الإكسل 2013، ويترتب على إختيار هذا الأمر هو إمكانية إستخدام البيانات التي تم إستخلاصها ومعالجتها داخل باقي أدوات حزمة Power BI مثل Power Pivot.

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

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

فيديو : مقدمة تعريفية بالوظائف الرئيسية لل Power Query وعناصر شاشة محرر الإستعلامات

لماذا نستثمر وقتنا في تعلم إستخدام Power Query؟

هذه النقاط هي نتيجة إستخدامي لل power query لمدة شهر تقريباً، وقد تكون هناك أسباب اخرى لم تظهر لي حتى لحظة كتابة هذه السطور:

  1. القدرة على دمج محتويات العديد من ملفات الاكسل في ملف واحد، ومن ثم القدرة على تحليل محتويات هذه الملفات مرة واحدة بأى آداة من آدوات تحليل البيانات
  2. وجود خاصية تحديث refresh للبيانات، وبالتالي عند التعديل في أحد مصادر البيانات يمكننا أن نُحدث الجدول الناشئ من Power Query.
  3. خانة applied steps والتي من خلالها ستوفر لنا الوقت المستغرف في تكرار العمليات في كل مرة، تخيل أننا كل شهر نقوم بدمج append ملفات العمل الخاصة بالفروع، الأن سنقوم بهذه العملية مرة واحدة والمرات التالية سنقوم بتعديلات بسيطة ليكون الإستعلام query جاهز للإستخدام.
    هذه الخاصية applied steps يمكن أن نشبهها بـ macro recorder الخاص بالفيجوال بيسك داخل الاكسل.

ختاماً نلخص وظيفة عمل power query في استخلاص ومعالجة البيانات ومن ثم تخزين البيانات المعالجة في ملف الاكسل.

شاهد أيضاً

الدمج الأفقي للإستعلامات في Power Query – الأمر Merge Query

Merge Query هو الأمر الثاني لإتمام عملية دمج الإستعلامات (الأمر الأول هو Append Query)، يقوم ...

2 تعليقان

  1. محمد رشدي

    الف شكر على الشرح

اترك تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *