كيفية استخدام Power Query في Excel: الدليل الكامل

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

أفضل طريقة لمعرفة كيفية استخدام Power Query في Excel هي تنفيذ بعض المهام الشائعة مثل استيراد البيانات واستخدام محرر Power Query لتحويل الجداول الناتجة ودمج البيانات.

تساعدك هذه المقالة على البدء من خلال عرض أمثلة واضحة عن Power Query مع أدلة خطوة بخطوة . من خلال إتقان Power Query ، يمكنك رفع عملك إلى المستوى التالي.

هل أنت جاهز؟ هيا بنا نقوم بذلك.

جدول المحتويات

• كيف تعرف ما إذا كان يمكنك الوصول إلى Power Query
• كيفية استيراد البيانات باستخدام Power Query
• كيفية استخدام محرر Power Query
  • معاينة البيانات
  • ملكيات
• كيفية العمل مع مصادر البيانات المتعددة
• كيفية إلحاق البيانات باستخدام Power Query
• كيفية دمج البيانات مع Power Query
• 5 طرق لتحويل البيانات باستخدام Power Query
  • 1. توسيع البيانات وتكثيفها
  • 2. فرز البيانات
  • 3. تنسيق البيانات
  • 4. إضافة أعمدة وإزالتها
  • 5. تغيير أنواع البيانات
• العمل مع الصيغ واللغة M
  • شريط الصيغة
  • كتابة وتعديل كود M.
• ما هي لغة M؟
• كيفية تحديث اتصالات البيانات
• كيفية دمج Power Query مع نموذج البيانات
• كيفية الأتمتة باستخدام Power Query و VBA
• الميزات المتقدمة لـ Power Query
• التعاون والمشاركة
• دعونا نختتم هذا

كيف تعرف ما إذا كان يمكنك الوصول إلى Power Query

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

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

• Excel 2010 و Excel 2013: متوفر كإضافة تحتاج إلى تنزيلها وتثبيتها.

• Excel 2016: مدمج في Excel باسم "Get & Transform".

• Excel 2019: تحسين الأداء والوظائف.

• Excel 2021 و Excel for Microsoft 365: التكامل مع مصادر البيانات السحابية.

• Excel for Mac: متوفر فقط من Microsoft 365 الإصدار 16.69 والإصدارات الأحدث.

للتحقق من أن الأداة مدمجة في مصنف Excel (2016 أو أحدث) ، انتقل إلى علامة التبويب البيانات وابحث عن قسم "الحصول على البيانات وتحويلها" في هذه الصورة:

إذا كان لديك Excel 2010 أو 2013 على Windows ، فيمكنك تنزيل الوظيفة الإضافية Power Query وتثبيتها وتمكينها.

كيفية استيراد البيانات باستخدام Power Query

تسمح لك أداة Power Query باستيراد مجموعة كبيرة من مصادر البيانات والاتصال بها:

• الملفات ، بما في ذلك تنسيقات CSV و XML و JSON

• عناوين URL على الويب أو نقاط نهاية API

• قواعد البيانات ، بما في ذلك Access و SQL Server و Oracle ومعظم قواعد البيانات الحديثة

• خدمات Azure وملفات SharePoint وموجزات OData

أسهل مصدر بيانات يمكن البدء به هو ملف CSV بسيط به عدة أسطر من البيانات. أنشئ ملفًا نصيًا وأضف هذه الأسماء الأربعة الأولى والأخيرة ، مفصولة بفاصلة:

• جو ، بلوجز

• آن ، ريان

• جيمس ، ستيوارت

• ماري ، براون

تأكد من حفظ الملف بامتداد CSV. سيبدو ملفك كالتالي:

لتحميل البيانات باستخدام Power Query ، اتبع الخطوات التالية:

1. انتقل إلى علامة التبويب البيانات.

2. انقر فوق الزر "إحضار الملفات" في علامة التبويب "الحصول على البيانات وتحويلها".

3. حدد ملف النموذج الخاص بك.

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

نصيحة: قم بإغلاق التطبيقات الأخرى لتسريع الأداء.

سيعرض Power Query نافذة "استيراد النص / CSV" مع معاينة لبياناتك.

إذا بدت البيانات صحيحة ، يمكنك المتابعة لتحميل البيانات أو تحويلها:

• سيقوم الزر "تحميل" بتحميل البيانات مباشرة إلى Excel.

• يتيح لك زر تحويل البيانات تعديل البيانات قبل تحميلها.

هل لاحظت أن نموذج الملف لا يحتوي على رأس؟ إذا تم تحميله مباشرة إلى Excel ، فسيكون العنوانان "العمود 1" و "العمود 2".

بدلاً من ذلك ، يمكنك سحب البيانات إلى محرر Power Query وإجراء بعض التغييرات. للقيام بذلك ، انقر فوق الزر "تحويل البيانات".

يتم الآن فتح محرر Power Query ، والذي ينقلنا إلى القسم التالي.

كيفية استخدام محرر Power Query

في هذا القسم ، سوف نستكشف بعض الجوانب الأساسية للعمل مع محرر Power Query.

معاينة البيانات

في منطقة معاينة البيانات ، يمكنك عرض البيانات المستوردة والتفاعل معها. يمكنك تصفية الصفوف وتعديل أنواع بيانات الأعمدة وإعادة تشكيل البيانات لتناسب احتياجاتك.

عند الانتهاء ، سيقوم Excel بتحميل البيانات المحولة في ورقة عمل.

باستخدام نموذج الملف ، يمكنك تحرير أسماء الأعمدة مباشرة في هذا الجزء.

لنفعل ذلك:

1. انقر بزر الماوس الأيمن فوق رأس العمود.

2. انقر على "إعادة تسمية" في القائمة المنسدلة.

3. اكتب الأسماء المفضلة في مربعات الرأس.

أثناء إجراء التغييرات ، يتم تحديث قسم "الخطوات المطبقة" الموجود على يمين جزء المعاينة ليعكس كل تحويل ، مما يسمح لك بتتبع خطوات معالجة البيانات وتعديلها حسب الحاجة.

ملكيات

يعد تعيين الخصائص لاستعلاماتك أمرًا ضروريًا لتنظيم عملك وتتبعه.

يوجد جزء إعدادات الاستعلام على يمين نافذة Power Query.

1. انقر فوق الارتباط "كافة الخصائص".

2. قم بتحرير اسم الاستعلام ليكون وصفيًا.

3. أضف وصفًا لتقديم مزيد من المعلومات.

من السهل أن تنسى ما تم إعداد استفسارات الأسبوع الماضي للقيام به. في هذه الحالة ، تم تغيير اسم الاستعلام وإضافة وصف يوضح الغرض من الاستعلام.

إذا كنت تريد معرفة المزيد حول الخيارات المتاحة ، فراجع هذه النظرة العامة لمحرر Power Query .

كيفية العمل مع مصادر البيانات المتعددة

يقدم محرر Power Query طريقتين أساسيتين لدمج البيانات من مصادر متعددة: الإلحاق والدمج.

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

لمعرفة كيفية عمل ذلك ، أنشئ ملف CSV صغيرًا آخر بنفس بنية الملف الأول. هذه المرة ، ضع اسمين (الأول والأخير) في الملف:

• جو ، بلوجز

• لورا ، قصب

لاحظ أن أحد هذه الأسماء مكرر من الملف السابق.

الآن اتبع هذه الخطوات لسحب البيانات إلى مصنف Microsoft Excel الخاص بك.

1. أغلق نافذة محرر Power Query واحفظ التغييرات.

2. أضف ورقة عمل جديدة.

3. اتبع الخطوات الواردة في القسم السابق لاستيراد الملف الثاني.

4. انقر فوق الزر Transform.

5. أعد تسمية رؤوس الأعمدة باسم "First Name" و "Last Name".

يعرض الجزء الأيمن في ورقة عمل Excel الاستعلامات والاتصالات الحالية. يجب أن تشاهد الآن استعلامين في الجزء.

كيفية إلحاق البيانات باستخدام Power Query

في هذا القسم ، ستلحق نموذج ملف CSV الثاني بالأول.

اتبع هذه الخطوات لإلحاق البيانات:

1. انقر بزر الماوس الأيمن فوق الجدول الأول (أسماء العملاء) في جزء الاستعلامات والاتصالات.

2. اختر "تحرير" لفتح الجدول في محرر Power Query.

3. انقر فوق "إلحاق الاستعلامات" في قسم "الجمع".

4. اختر الجدول الثاني من القائمة المنسدلة للجداول لإلحاقها.

ستكون النتيجة صفين إضافيين في الجدول الأول.

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

يمكنك إزالة البيانات المكررة في محرر Power Query باتباع الخطوات التالية:

1. انقر فوق رأس عمود "الاسم الأول".

2. اضغط مع الاستمرار على مفتاح "Ctrl" (أو مفتاح "Cmd" في نظام التشغيل Mac).

3. انقر فوق رأسي العمود.

4. انقر فوق "إزالة الصفوف" في علامة التبويب "الصفحة الرئيسية" في شريط Power Query Editor.

5. حدد "إزالة التكرارات" من القائمة المنسدلة.

كيفية دمج البيانات مع Power Query

عند دمج البيانات ، يجب عليك اختيار الأعمدة المتطابقة بين الاستعلامات.

لنفترض أن لديك ملفين مفصولين بفواصل حيث يحتوي أحدهما على أرقام هواتف العميل والآخر يحتوي على عناوين. يبدو الملفان كالتالي:

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

في حالة بياناتنا النموذجية ، يجب اختيار كلا العمودين (الاسم الأول واسم العائلة) كأعمدة مطابقة. اضغط باستمرار على مفتاح Ctrl لاختيار عدة أعمدة.

اتبع هذه الخطوات:

1. انقر بزر الماوس الأيمن فوق الجدول الأول في جزء الاستعلامات والاتصالات.

2. اختر "تحرير" لفتح الجدول في محرر Power Query.

3. حدد "دمج الاستعلامات" من مجموعة "دمج".

4. اختر الجدول الثاني في القائمة المنسدلة للجداول.

5. انقر فوق العمود الأول في الجدول الأول.

6. اضغط باستمرار على مفتاح Ctrl وانقر على العمود الثاني في الجدول الأول.

7. اضغط مع الاستمرار على مفتاح Ctrl وانقر على كلا العمودين في الجدول الثاني.

8. احتفظ بعامل الانضمام الافتراضي باسم "Left Outer".

9. انقر فوق "موافق" لتطبيق التغييرات.

يضمن عامل الربط "Left Outer" إزالة البيانات المكررة.

في محرر Power Query ، سترى عمودًا جديدًا يسمى "جدول" أو "جدول متداخل" يحتوي على البيانات المدمجة من الاستعلام الثاني.

1. انقر فوق أيقونة التوسيع (سهمان) في رأس هذا العمود.

2. قم بإلغاء تحديد مربع "استخدام العمود الأصلي كبادئة" (هذا يتجنب أسماء الأعمدة الطويلة).

3. انقر فوق موافق".

4. انقر فوق "إغلاق وتحميل".

يحتوي الجدول المدمج على أربعة أعمدة ، مع دمج تفاصيل الهاتف والعنوان. قد لا يزال لديك القليل من الترتيب للقيام به.

على سبيل المثال ، قد ترغب في تحرير اسم الجدول (Merge1) وعمود العنوان في الصورة أدناه.

5 طرق لتحويل البيانات باستخدام Power Query

أصبح تحويل البيانات في ملف Excel الخاص بك أمرًا سهلاً باستخدام Power Query ، مما يتيح لك تنظيف بياناتك وتنظيمها ومعالجتها بشكل فعال.

بعض الطرق الأكثر شيوعًا لتحويل البيانات هي:

• توسيع البيانات وتكثيفها

• فرز البيانات

• تنسيق البيانات

• إضافة وحذف الأعمدة

• تغيير أنواع البيانات

1. توسيع البيانات وتكثيفها

يعد توسيع البيانات وتكثيفها أمرًا مهمًا لجعل مجموعات البيانات الخاصة بك أكثر قابلية للإدارة. يسمح لك Power Query بتوسيع الجداول أو طيها للحصول على طريقة عرض أكثر تنظيماً.

يوضح القسم السابق كيفية التوسيع بتضمين البيانات من الجداول ذات الصلة.

يمكنك أيضًا تكثيفها لتضمين البيانات الملخصة فقط بناءً على تجميع من اختيارك مثل المجموع أو العدد أو النسبة المئوية.

2. فرز البيانات

لفرز البيانات ، يمكنك تغيير الترتيب بسرعة عن طريق القيم التصاعدية أو التنازلية.

انقر فوق السهم لأسفل بجوار اسم العمود واختر "فرز تصاعدي" أو "فرز تنازلي".

3. تنسيق البيانات

توفر علامة التبويب Transform في Power Query Editor الكثير من الخيارات.

على سبيل المثال ، تتيح لك القائمة المنسدلة "تنسيق" تغيير جميع البيانات في عمود أو الجدول بأكمله إلى أحرف كبيرة أو صغيرة.

4. إضافة أعمدة وإزالتها

يبسط Power Query عملية إضافة الأعمدة وحذفها وتعديلها.

لإزالة عمود ، ما عليك سوى النقر بزر الماوس الأيمن على رأس العمود غير المرغوب فيه وتحديد "إزالة". سيؤدي هذا إلى حذف العمود وكافة بياناته من الجدول بشكل دائم.

لإضافة عمود جديد ، حدد العمود الموجود حيث تريد ظهور العمود الجديد. ثم استخدم ميزة "إضافة عمود من الأمثلة" لإنشاء عمود مخصص أو لإجراء عمليات حسابية بين عمودين أو أكثر.

5. تغيير أنواع البيانات

يعد العمل باستخدام أنواع بيانات متسقة ودقيقة أمرًا بالغ الأهمية لإجراء تحليل دقيق. يسمح لك Power Query بتغيير أنواع البيانات بسهولة:

1. انقر بزر الماوس الأيمن فوق رأس العمود.

2. اختر "تغيير النوع".

3. حدد نوع البيانات المطلوب من القائمة المتوفرة.

يمكن أن يكون تغيير أنواع البيانات مفيدًا بشكل خاص عند التعامل مع بيانات النسبة المئوية.

على سبيل المثال ، قد تحتاج إلى تحويل النقاط العشرية إلى نسب مئوية أو تحويل النسب المئوية المستندة إلى النص إلى نسب رقمية لتسهيل الحساب والمقارنة.

العمل مع الصيغ واللغة M

عند استخدام Power Query في Excel ، ستواجه لغة M ، وهي جوهر إنشاء صيغ متقدمة ومعالجة البيانات وإنتاج استعلامات قوية. ستتعرف في هذا القسم على:

• شريط الصيغة

• كود M.

• العناصر الأساسية في بناء جملة لغة M

شريط الصيغة

شريط الصيغة في محرر Power Query في Excel هو المنطقة التي ستدخل فيها الصيغ ورمز M.

لاستخدامه ، انتقل إلى محرر Power Query عن طريق تحديد استعلام من جزء الاستعلامات والاتصالات. يقع شريط الصيغة أعلى جزء معاينة البيانات.

كتابة وتعديل كود M.

لإنشاء استعلامات متعددة الاستخدامات وقوية في Excel ، ستحتاج إلى كتابة وتعديل لغة Power Query المعروفة باسم M code. فيما يلي بعض النصائح حول كيفية البدء:

• استخدم جزء "الخطوات المطبقة" لكتابة التعليمات البرمجية خطوة بخطوة ، مما يسهل تتبع وتعديل منطق الاستعلام.

• ادمج التعليمات البرمجية M مع جدول Excel ، والجداول المحورية ، ومصادر البيانات الخارجية ، مثل Azure والملفات النصية ، لإنشاء تقارير ديناميكية ومؤتمتة.

• اضبط خيارات الاستعلام لتحسين تفضيل تحميل البيانات وتحسين الأداء.

عندما تتعرف على كود M ، ستجد أنه يمكنك أتمتة وتحسين العديد من العمليات ، مما يجعل تحليل بياناتك في Excel أكثر كفاءة وفعالية.

لعرض الكود الموجود خلف الاستعلام ، اتبع الخطوات التالية:

1. افتح الاستعلام في محرر Power Query.

2. انقر فوق "محرر متقدم" في علامة التبويب الصفحة الرئيسية.

هذا هو الكود الموجود خلف نموذج الجدول المدمج (قد يبدو جدولك مختلفًا):

ما هي لغة M؟

يعد فهم بناء جملة لغة M أمرًا ضروريًا لإنشاء صيغ Power Query فعالة. ضع في اعتبارك النقاط التالية:

• لغة M حساسة لحالة الأحرف ، لذا كن حذرًا عند استخدام الأحرف الكبيرة.

• استخدم الأقواس المربعة [] للإشارة إلى الأعمدة والسجلات والقوائم.

• للإشارة إلى خطوة سابقة في الاستعلام الخاص بك ، استخدم اسم المتغير الخاص به (على سبيل المثال ، # "اسم الخطوة").

• قم بإخراج خطوة صيغة الاستعلام باستخدام العبارة "in".

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

تحقق من الفيديو أدناه ، حان الوقت لإخراج فرشاة التنظيف وتنظيف بياناتك الفوضوية.

كيفية تحديث اتصالات البيانات

تتمثل إحدى الميزات الرئيسية لبرنامج Power Query في القدرة على تحديث اتصال Power Query بسهولة.

عندما يتم تحديث مصادر البيانات الخاصة بك ، وتحتاج إلى أحدث المعلومات في المصنف الخاص بك ، يمكنك تحديث اتصالات البيانات في جزء الاستعلامات & الاتصالات.

إذا كنت لا ترى الجزء الموجود على يمين ورقة العمل الخاصة بك ، فيمكنك فتحه بالانتقال إلى جدول البيانات والنقر فوق "استعلامات واتصالات".

عندما يكون جزء الاستعلامات والاتصالات مفتوحًا ، فإنه يسرد جميع الاستعلامات. اتبع هذه الخطوات:

1. انقر بزر الماوس الأيمن فوق الاستعلام الذي تريد تحديثه.

2. حدد تحديث من قائمة السياق.

سيؤدي هذا إلى تحديث المصنف الخاص بك بأحدث البيانات من مصادر البيانات المتصلة.

كيفية دمج Power Query مع نموذج البيانات

يعد نموذج بيانات Excel ميزة قوية تتيح لك إنشاء أدوات ذكاء الأعمال ، مثل الجداول المحورية ، بناءً على علاقات البيانات.

نموذج البيانات هو في الأساس قاعدة بيانات مخفية تحتفظ بالبيانات المستوردة والعلاقات بين الجداول.

إنه جزء من ميزة Power Pivot المضمنة في الإصدارات الحالية من Excel. قد تضطر إلى تثبيت Power Pivot كوظيفة إضافية للوصول إليها في الإصدارات السابقة).

يمكنك دمج اتصالات بيانات Power Query مع نموذج البيانات باتباع الخطوات التالية:

1. قم بتحميل بياناتك باستخدام Power Query كما هو موضح في الأقسام السابقة.

2. بدلاً من تحميله مباشرةً في المصنف ، اختر تحميل إلى من قائمة التحميل.

3. حدد خانة الاختيار إضافة هذه البيانات إلى نموذج البيانات في مربع الحوار استيراد البيانات.

كيفية الأتمتة باستخدام Power Query و VBA

باستخدام تكامل VBA (Visual Basic for Applications) ، يمكنك إنشاء اتصالات بيانات مخصصة وأتمتة خطوات تحويل البيانات في Power Query.

يمكنك الاستفادة من مهارات VBA الحالية لديك لبناء نماذج بيانات متقدمة من خلال دمج البيانات من الأنظمة الأساسية مثل Microsoft Access و SharePoint و Analysis Services.

على الرغم من أن Power Query لا يحتوي على دعم أصلي لـ VBA ، يمكنك استخدام التعليمات البرمجية لـ VBA لفتح استعلامات Power Query وتحديثها ومعالجتها بشكل غير مباشر.

يتيح لك ذلك إنشاء تدفقات عمل بيانات مؤتمتة قوية وتبسيط عملية تحليل البيانات الخاصة بك.

فيما يلي بعض التعليمات البرمجية النموذجية التي تقوم بتحديث استعلام الطاقة في المصنف الحالي.

:

Dim wb كمصنف ، pq ككائن

تعيين wb = ThisWorkbook

تعيين pq = wb.Queries ("أسماء العملاء")

 pq تحديث

فيما يلي الكود داخل ماكرو Excel VBA:

الميزات المتقدمة لـ Power Query

يوفر Power Query في Excel عددًا كبيرًا من الميزات المتقدمة لتبسيط عملية تحليل البيانات وتحسينها. من خلال تعلم كيفية الاستخدام الفعال لهذه الميزات ، يمكنك نقل مهارات تحليل البيانات إلى مستوى جديد تمامًا.

تتضمن أدوات تحليل البيانات المضمنة ما يلي:

• أعمدة شرطية لتطبيق منطق معقد على تحويلات البيانات الخاصة بك.

• وظائف التجميع والتجميع لتلخيص بياناتك بطريقة هادفة.

• عمليات Pivot و Unpivot لإعادة تشكيل بياناتك لتحليل أسهل.

ستجد العديد من هذه الميزات في علامة التبويب التحويل في محرر Power Query.

بالإضافة إلى ذلك ، يمكنك توصيل Power Query بأدوات Business Intelligence مثل LuckyTemplates للحصول على خيارات أكثر تقدمًا لتحليل البيانات والتصور .

التعاون والمشاركة

تعد مشاركة اتصالات البيانات والاستعلامات والتحويلات الخاصة بك مع أعضاء فريقك أمرًا ضروريًا للتعاون الفعال.

يمكنك مشاركة مصنف مع الاستعلامات عن طريق حفظه في محرك أقراص الشبكة أو SharePoint أو بعض مواقع التخزين المشتركة الأخرى.

يمكنك أيضًا تصدير رمز M خلف استعلام واحد باتباع الخطوات التالية:

1. قم بتحرير الاستعلام في محرر Power Query.

2. انقر فوق "محرر متقدم" في علامة التبويب الصفحة الرئيسية.

3. انسخ رمز M بالكامل في نافذة المحرر المتقدم.

4. افتح محرر نصوص (مثل المفكرة) والصق كود M المنسوخ في محرر النصوص.

5. احفظ الملف النصي وأرسله إلى زميلك.

دعونا نختتم هذا

مع مجموعة من الميزات وواجهة سهلة الاستخدام ، يعد Power Query أداة أساسية لمستخدمي Excel الذين يتطلعون إلى العمل بفعالية مع البيانات الخارجية.

تساعد هذه الميزة القيمة في تبسيط عملية إنشاء التقارير وتنقية البيانات وإجراء تحليلات معقدة على جداول البيانات الخاصة بك. استخدام

يوفر Power Query في النهاية الوقت ويزيد من الكفاءة عند العمل باستخدام ملفات Excel.