استعلام برنامج excel power التعليمي (الحصول على التحويل)

إذا كنت أحد هؤلاء الأشخاص الذين يعملون كثيرًا مع البيانات، فيمكنك أن تكون أي شخص ( محاسب ، أو موارد بشرية، أو محلل بيانات، وما إلى ذلك)، ويمكن أن يكون PowerQuery أداتك القوية.

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

هيا بنا نبدأ.

ما هو برنامج Excel Power Query

Power Query عبارة عن وظيفة إضافية في Excel يمكنك استخدامها لـ ETL. وهذا يعني أنه يمكنك استخراج البيانات من مصادر مختلفة، وتحويلها ، ثم تحميلها في جدول البيانات. يمكنك القول أن POWER QUERY عبارة عن آلة لتنظيف البيانات لأنها تحتوي على كافة الخيارات لتحويل البيانات. إنه في الوقت الحقيقي ويسجل كل خطوة تقوم بها.

لماذا يجب عليك استخدام Power Query (الفوائد)؟

إذا كان هذا السؤال يدور في ذهنك فإليك إجابتي لك:

• مصادر بيانات مختلفة : يمكنك تحميل البيانات إلى محرر استعلام قوي من مصادر بيانات مختلفة، مثل CSV ، وTXT، وJSON، وما إلى ذلك.
• تحويل البيانات بسهولة: عادةً، تستخدم الصيغ والجداول المحورية لتحويلات البيانات، ولكن باستخدام POWER QUERY، يمكنك إجراء الكثير ببضع نقرات فقط.
• إنه في الوقت الفعلي: اكتب استعلامًا مرة واحدة ويمكنك تحديثه في كل مرة يحدث فيها تغيير في البيانات، وسيعمل على تحويل البيانات الجديدة التي قمت بتحديثها.

اسمحوا لي أن أشارك مثالا:

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

ولكن باستخدام Power Query، يمكنك القيام بذلك خلال دقائق. هل تشعر بالإثارة؟ جيد.

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

مفهوم الطلب على الطاقة

لتعلم استعلام الطاقة، تحتاج إلى فهم مفهومه والذي يتم في ثلاث خطوات:

1. احصل على البيانات

يتيح لك Power Query الحصول على البيانات من مصادر مختلفة مثل الويب وCSV والملفات النصية والمصنفات المتعددة من مجلد واحد والعديد من المصادر الأخرى حيث يمكننا تخزين البيانات.

2. تحويل البيانات

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

3. تحميل البيانات

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

أين يوجد Power Query (كيفية التثبيت)؟

يمكنك أدناه معرفة كيفية تثبيت Power Query Access في إصدارات مختلفة من Microsoft Excel.

اكسل 2007

إذا كنت تستخدم Excel 2007، فأنا آسف لأن PQ غير متوفر لهذا الإصدار، لذلك تحتاج إلى الترقية إلى latest version من Excel (Excel لـ Office 365، وExcel 2019، وExcel 2016، وExcel 2013، وExcel 2010).

اكسل 2010 و اكسل 2013

بالنسبة لعامي 2010 و2013 ، يتعين عليك تثبيت وظيفة إضافية بشكل منفصل والتي يمكنك تنزيلها من هذا الرابط وبمجرد تثبيتها، ستحصل على علامة تبويب جديدة في شريط Excel كما هو موضح أدناه:

• أولا قم بتحميل الوظيفة الإضافية من هنا (الموقع الرسمي لشركة مايكروسوفت).
• بعد تنزيل الملف، افتحه واتبع التعليمات.
• بعد ذلك، ستحصل تلقائيًا على علامة التبويب “Power Query” على شريط Excel الخاص بك.

إذا لم تظهر علامة التبويب “POWER QUERY” بطريقة أو بأخرى، فلا داعي للقلق بشأن ذلك. يمكنك إضافته باستخدام خيار وظائف COM الإضافية.

• انتقل إلى علامة التبويب ملف ← الخيارات ← الوظائف الإضافية.

• في خيارات “الوظائف الإضافية”، حدد “وظائف COM الإضافية” وانقر فوق “انتقال”.
• بعد ذلك، حدد المربع “Microsoft Power Query for Excel”.

• في النهاية، انقر فوق موافق.

إكسل 2016، 2019، أوفيس 365

إذا كنت تستخدم Excel 2016، أو Excel 2019، أو لديك اشتراك في OFFICE 365، فهو موجود بالفعل في علامة التبويب “البيانات” ، كمجموعة تسمى “GET & TRANSFORM” (يعجبني هذا الاسم، أليس كذلك؟).

اكسل ماك

إذا كنت تستخدم Excel على نظام التشغيل Mac، فأنا أخشى أنه لا توجد وظيفة إضافية لاستعلام الطاقة لهذا ويمكنك فقط تحديث استعلام موجود ، ولكن لا يمكنك إنشاء استعلام جديد أو حتى تحرير استعلام ( LINK ).

محرر Power Query

يحتوي Power Query على محرر خاص به حيث يمكنك الحصول على البيانات، والقيام بجميع الخطوات لإنشاء الاستعلامات، ثم تحميلها في جدول البيانات. لفتح محرر Power Query، يتعين عليك الانتقال إلى علامة التبويب “بيانات” وفي الحصول على وتحويل ← الحصول على البيانات ← تشغيل محرر Power Query.

فيما يلي المعاينة الأولى للمحرر الذي ستحصل عليه عند فتحه.

الآن، دعونا نستكشف كل قسم بالتفصيل:

1. الشريط

دعونا نلقي نظرة على جميع علامات التبويب المتاحة:

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

2. الخطوات المطبقة

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

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

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

3. الأسئلة

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

عندما تنقر بزر الماوس الأيمن على اسم استعلام، يمكنك رؤية جميع الخيارات التي يمكنك استخدامها (نسخ، حذف، تكرار، إلخ.)

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

4. شريط الصيغة

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

بمجرد أن تتعلم كيفية استخدام كود M، يمكنك أيضًا إنشاء خطوة عن طريق كتابة الكود والنقر ببساطة على الزر “FX” لإدخال خطوة مخصصة.

5. نظرة عامة على البيانات

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

في أعلى كل عمود، يمكنك رؤية نوع بيانات بيانات العمود. عندما تقوم بتحميل البيانات إلى المحرر، يقوم الاستعلام المتقدم تلقائيًا بتطبيق نوع البيانات الصحيح (في كل مرة تقريبًا) على كل عمود.

يمكنك النقر فوق الزر الموجود أعلى يسار رأس العمود لتغيير نوع البيانات المطبق على العمود. يحتوي على قائمة بجميع أنواع البيانات التي يمكنك الاستفادة منها.

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

إذا نقرت بزر الماوس الأيمن على رأس العمود، يمكنك أن ترى أن هناك قائمة تتضمن قائمة من الخيارات التي يمكنك استخدامها لتحويل البيانات واستخدام أحد الخيارات وPQ الذي يخزنه كخطوة في العملية المطبقة. لا.

مصادر البيانات لـ Power Query

أفضل جزء في Power Query هو أن لديك القدرة على الحصول على البيانات من مصادر متعددة وتحويل تلك البيانات ثم تحميلها في جدول البيانات. عند النقر فوق Get Data in GET & TRANSFORM، يمكنك رؤية القائمة الكاملة لمصادر البيانات التي يمكنك تحميلها في المحرر.

الآن دعونا نلقي نظرة على بعض مصادر البيانات:

• من الجدول/النطاق : باستخدام هذا الخيار، يمكنك تحميل البيانات إلى محرر الاستعلام المتقدم مباشرة من ورقة العمل النشطة.
• من المصنف : من مصنف آخر موجود على جهاز الكمبيوتر الخاص بك. كل ما عليك فعله هو تحديد موقع هذا الملف باستخدام مربع حوار مفتوح وسيحصل تلقائيًا على البيانات من هذا الملف.
• من النص/CSV : احصل على البيانات من ملف نصي أو ملف مفصول بفواصل، ثم يمكنك تحميله في جدول البيانات.
• من المجلد : يأخذ جميع الملفات من المجلد ويدعم البيانات في محرر استعلام قوي. (انظر هذا: دمج ملفات Excel من مجلد واحد).
• من الويب : باستخدام هذا الخيار، يمكنك الحصول على البيانات من عنوان ويب، تخيل أن لديك ملفًا مخزّنًا على الويب أو لديك صفحة ويب تحتاج إلى الحصول على البيانات منها.

كيفية تحميل البيانات إلى Power Query Editor

الآن دعونا نتعلم كيفية تحميل البيانات في محرر الاستعلام القوي. هنا لديك قائمة بأسماء الطلاب ونتائجهم ( رابط ).

سيتم تحميل البيانات مباشرة من جدول البيانات، لذا يتعين عليك فتح الملف أولاً ثم اتباع الخطوات التالية:

• أولاً، قم بتطبيق جدول Excel على البيانات (حتى إذا لم تقم بذلك، فسيقوم Excel بذلك نيابةً عنك قبل تحميل البيانات إلى محرر PQ).
• الآن، حدد خلية في الجدول وانقر على “من الجدول/النطاق” (علامة التبويب الحصول على البيانات وتحويلها).

• بمجرد النقر فوق الزر، يؤكد Excel نطاق البيانات لتطبيق جدول Excel عليه.

• في هذه المرحلة، تكون لديك البيانات في Power Query Editor، ويبدو كما هو موضح أدناه.

• هنا تستطيع ان ترى:
  • في شريط الصيغة، قام PQ بإنشاء رمز M للجدول الذي قمت بتحميله للتو في المحرر.
  • على الجانب الأيسر من المحرر لديك جزء الاستعلامات حيث توجد قائمة الاستعلامات.
  • على الجانب الأيمن، في إعدادات الاستعلام، لديك القسم المسمى “الخطوات التطبيقية” حيث تم إدراج جميع الخطوات. ملاحظة: يجب أن تعتقد أنك لم تقم بأي “نوع معدل”، ولكن هناك خطوة تسمى “النوع المعدل” هناك. دعني أخبرك بذكاء POWER QUERY عندما تقوم بتحميل البيانات إلى المحرر، فإنه يقوم تلقائيًا بفحص أنواع البيانات الصحيحة وتطبيقها على جميع الأعمدة.

أمثلة Power Query (النصائح والحيل)

يمكنك التعرف على كيفية تنفيذ بعض المهام الأساسية التي تقوم بها عادةً باستخدام الصيغ الوظيفية في Excel، ولكن باستخدام PowerQuery يمكنك القيام بذلك ببضع نقرات فقط:

1. استبدال القيم

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

في القائمة أدناه، تحتاج إلى استبدال اسمي “Puneet” بـ “Punit”.

• أولاً، قم بتحرير القائمة في محرر الاستعلام المتقدم.
• بعد ذلك، في محرر Power Query، انتقل إلى “علامة التبويب تحويل” وانقر فوق “استبدال القيم”.

• الآن في “القيمة المراد البحث عنها” أدخل “Puneet” وفي “استبدال بـ” أدخل “Punit” وبعد ذلك انقر فوق “موافق”.

• بمجرد النقر فوق “موافق”، يتم استبدال جميع القيم بالقيم الجديدة والآن انقر فوق “إغلاق وتحميل” لتحميل البيانات في جدول البيانات.

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

تمامًا مثل الفرز العادي، يمكنك فرز البيانات باستخدام PowerQuery وأنا أستخدم نفس قائمة الأسماء التي استخدمتها في المثال أعلاه.

• أولاً، قم بتحميل البيانات في محرر الاستعلام القوي.
• في علامة التبويب الصفحة الرئيسية، لديك زري فرز (تصاعدي وتنازلي).
• انقر فوق أحد هذه الأزرار للفرز.

3. حذف الأعمدة

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

• حدد العمود أو الأعمدة المتعددة التي تريد حذفها.

• الآن انقر بزر الماوس الأيمن واختر “حذف”.

نصيحة سريعة: يوجد أيضًا خيار “إزالة الأعمدة الأخرى” حيث يمكنك حذف جميع الأعمدة غير المحددة.

4. تقسيم الأعمدة

تمامًا مثل خيار تحويل النص إلى عمود، لديك “تقسيم العمود” في استعلام الطاقة. دعني أخبرك كيف يعمل.

• حدد العمود وانتقل إلى علامة التبويب الصفحة الرئيسية ➜ تحويل ➜ تقسيم العمود ➜ حسب المحدد.

• حدد المخصص من القائمة المنسدلة وأدخل “-” فيه.
• الآن لديك ثلاثة خيارات مختلفة لتقسيم العمود.
  • المحدد في أقصى اليسار
  • المحدد في أقصى اليمين
  • كل حدوث المحدد

إذا كان لديك محدد واحد فقط في الخلية، فستعمل الثلاثة جميعًا بنفس الطريقة، ولكن إذا كان لديك أكثر من محدد واحد، فيجب عليك الاختيار وفقًا لذلك.

5. إعادة تسمية عمود

يمكنك ببساطة إعادة تسمية عمود بالنقر بزر الماوس الأيمن ثم النقر فوق “إعادة تسمية”.

نصيحة سريعة : لنفترض أن لديك استعلامًا لإعادة تسمية عمود وقام شخص آخر بإعادة تسميته عن طريق الخطأ. يمكنك استعادة هذا الاسم بنقرة واحدة.

6. عمود مكرر

في Power Query، يوجد خيار بسيط لإنشاء عمود مكرر. ما عليك سوى النقر بزر الماوس الأيمن على العمود الذي تريد إنشاء عمود مكرر له، ثم النقر على “عمود مكرر”.

7. تبديل العمود أو الصف

في استعلام الطاقة، يعتبر النقل بمثابة لعبة أطفال. نعم، بنقرة واحدة فقط.

• بمجرد تحميل البيانات في محرر الاستعلام المتقدم، كل ما عليك فعله هو تحديد العمود (الأعمدة) أو الصف (الصفوف).
• انتقل إلى علامة التبويب تحويل ➜ جدول ➜ تبديل.

8. استبدال/إزالة الأخطاء

عادةً، لاستبدال الأخطاء أو إزالتها في Excel، يمكنك استخدام خيار البحث والاستبدال أو رمز VBA. ولكن في PowerQuery يكون الأمر أسهل بكثير. انظر إلى العمود أدناه حيث توجد أخطاء ويمكنك إزالتها واستبدالها.

عند النقر بزر الماوس الأيمن على العمود، سيكون لديك كلا الخيارين.

• استبدال الأخطاء
• إزالة الأخطاء

9. تغيير نوع البيانات

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

• أولاً، قم بتحرير البيانات في محرر الاستعلام القوي.
• بعد ذلك، حدد العمود وانتقل إلى علامة التبويب تحويل.
• الآن، من نوع البيانات، حدد “التاريخ” كنوع.

10. أضف عمودًا من الأمثلة

في الاستعلام المتقدم، يوجد خيار لإضافة عمود مثال لا يمثل في الواقع مثالًا متعلقًا بالعمود الحالي.

اسمحوا لي أن أقدم لكم مثالا:

تخيل أنك بحاجة إلى أسماء الأيام من عمود التاريخ. بدلاً من استخدام صيغة أو أي خيار آخر يمكنك استخدامه، يمكنك استخدام “إضافة عمود من الأمثلة”.

هيريس كيفية القيام بذلك:

• انقر بزر الماوس الأيمن على أحد الأعمدة وانقر على “إضافة عمود من الأمثلة”.

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

• حدد “اسم يوم الأسبوع من التاريخ” وانقر فوق “موافق”.

فقاعة! عمودك الجديد هنا.

11. تغيير الصناديق

لديك الخيارات التالية لتغيير حالة النص في PowerQuery.

• صغير الحجم
• الأحرف الكبيرة
• تكبير كل كلمة

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

12. تقليم وتنظيف

لمسح البيانات أو إزالة المساحات غير المرغوب فيها ، يمكنك استخدام خيارات TRIM وCLEAN في PowerQuery. الخطوات بسيطة:

• انقر بزر الماوس الأيمن على عمود أو حدد جميع الأعمدة إذا كان لديك عدة أعمدة.
• انتقل إلى علامة التبويب تحويل ➜ عمود النص ➜ التنسيق.
  1. TRIM: لإزالة المسافات البيضاء من نهاية وبداية الخلية.
  2. CLEAN: لإزالة الأحرف غير المطبوعة من الخلية.

13. أضف بادئة/لاحقة

إذن لديك قائمة بالقيم ومن هذه القائمة تريد إضافة بادئة/لاحقة في كل خلية. في Excel، يمكنك استخدام أسلوب التسلسل، ولكن في PowerQuery يوجد خيار سهل الاستخدام لكليهما.

• أولاً، حدد العمود الذي تريد إضافة بادئة/لاحقة إليه.
• ثم انتقل إلى علامة التبويب تحويل ➜ عمود النص ➜ التنسيق ➜ إضافة بادئة/إضافة لاحقة.

• بمجرد النقر على أي من الخيارات، سوف تحصل على مربع حوار لإدخال النص.

• وبعد إدخال النص، انقر فوق موافق.

14. استخراج القيم

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

15. التاريخ أو الوقت فقط

يحدث غالبًا أن يكون لديك التاريخ والوقت، كلاهما في خلية واحدة، لكنك تحتاج إلى أحدهما.

• حدد العمود الذي يوجد به التاريخ والوقت المدمجان.
• إذا أردت:
  • التاريخ : انقر بزر الماوس الأيمن على ➜ تحويل ➜ التاريخ فقط.
  • الوقت : انقر بزر الماوس الأيمن على ➜ تحويل ➜ الوقت فقط.

16. الجمع بين التاريخ والوقت

الآن أنت تعرف كيفية فصل التاريخ والوقت. ولكن بعد ذلك، عليك أن تعرف كيفية الجمع بينهما.

• أولاً، حدد عمود التاريخ وانقر على خيار “التاريخ فقط”.
• بعد ذلك، حدد كلا العمودين (التاريخ والوقت) وانتقل إلى علامة التبويب “تحويل” ومن مجموعة “عمود التاريخ والوقت”، انتقل إلى “التاريخ” وانقر على “دمج التاريخ والوقت”.

17. الأرقام المقربة

فيما يلي الخيارات التالية المتاحة لك لتقريب الأرقام.

• التقريب للأسفل: لتقريب رقم للأسفل.
• الجولة: لتقريب رقم.
• التقريب: يمكنك اختيار مدى تقريب المنازل العشرية الخاصة بي.

فيما يلي الخطوات:

• حدد العمود وانقر بزر الماوس الأيمن على ➜ تحويل ➜ دائري .
  1. التقريب للأسفل: لتقريب رقم للأسفل.
  2. الجولة: لتقريب رقم.
  3. التقريب: يمكنك اختيار مدى تقريب المنازل العشرية الخاصة بي.

ملاحظة: عند تحديد الخيار “#3 Round”، يجب عليك إدخال عدد المنازل العشرية المطلوب تقريبها.

18. الحسابات

هناك خيارات يمكنك استخدامها لإجراء العمليات الحسابية (الكثير منها). يمكنك العثور على كل هذه الخيارات في علامة التبويب “تحويل” (في مجموعة الأعمدة الرقمية).

• أساسي
• إحصائيات
• عالم
• علم المثلثات
• دائري
• معلومة

لإجراء أي من هذه الحسابات، يجب عليك تحديد العمود ثم الخيار.

19. تجميع حسب

لنفترض أن لديك مجموعة بيانات كبيرة وتريد إنشاء جدول ملخص. إليك ما عليك القيام به:

• في علامة التبويب “تحويل”، انقر فوق الزر “التجميع حسب” وسيظهر لك مربع حوار.

• الآن من مربع الحوار هذا، حدد العمود الذي تريد التجميع معه وبعد ذلك قم بإضافة اسم، وحدد العملية والعمود الذي توجد به القيم.

• في النهاية، انقر فوق موافق.

ملاحظة: هناك أيضًا خيارات متقدمة في خيار “التجميع حسب” يمكنك استخدامها لإنشاء جدول مجموعة متعدد المستويات.

20. إزالة القيم السلبية

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

يؤدي هذا إلى إزالة جميع الإشارات السلبية من القيم على الفور.

كيفية تحميل البيانات في جدول البيانات

بمجرد قيامك بتحويل بياناتك، يمكنك تحميلها في جدول البيانات واستخدامها لمزيد من التحليل. يوجد في علامة التبويب الرئيسية زر يسمى “إغلاق وتحميل” عندما تنقر عليه تحصل على قائمة منسدلة توفر خيارات إضافية:

• إغلاق وتحميل
• إغلاق وتحميل

• بمجرد الضغط على الزر ستظهر الخيارات التالية:

• حدد الطريقة التي تريد بها عرض هذه البيانات في جدول البيانات الخاص بك.
  • تلوين
  • تقرير الجدول المحوري:
  • محور الجدول
  • إنشاء اتصال واحد فقط
  • أين تريد وضع البيانات؟
    • ورقة العمل الموجودة
    • ورقة عمل جديدة.
  • أضف هذه البيانات إلى نموذج البيانات.
• ما عليك سوى تحديد خيار الجدول وورقة العمل الجديدة وترك نموذج البيانات بدون تحديد ثم النقر فوق “موافق”.

• في اللحظة التي تنقر فيها فوق “موافق”، فإنه يضيف ورقة عمل جديدة مع البيانات.

المزيد من الأمثلة للتعلم

تحديث الاستعلام تلقائيًا

ومن بين جميع الأمثلة التي ذكرتها هنا، هذا هو الأهم. عندما تقوم بإنشاء استعلام، يمكنك تحديثه تلقائيًا (يمكنك ضبط مؤقت).

وإليك الخطوات:

• في علامة التبويب “البيانات”، انقر فوق “الاستعلامات والاتصالات” وستحصل على جزء الاستعلامات والاتصال على الجانب الأيمن من النافذة.

• الآن انقر بزر الماوس الأيمن على الاستعلام وحدد “تحديث كل” وأدخل الدقائق.

كيفية استخدام الصيغة والدالة في Power Query

مثلما يمكنك استخدام الوظائف والصيغ في جدول بيانات Excel، فإن الاستعلام المتقدم لديه قائمة خاصة به من الوظائف التي يمكنك استخدامها. أساسيات الوظائف والصيغ في PowerQuery هي نفس وظائف جداول البيانات في Excel.

في PQ تحتاج إلى إضافة عمود مخصص جديد لإضافة دالة أو صيغة.

لنأخذ مثالاً: في البيانات أدناه (الموجودة بالفعل في محرر PQ) لديك الاسم الأول والأخير ( رابط التنزيل ) .

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

• أولاً، انتقل إلى علامة التبويب “إضافة عمود ” وانقر فوق “عمود مخصص”.

• الآن في مربع حوار العمود المخصص، أدخل اسم العمود الجديد “الاسم الكامل” أو ما تريد تسمية العمود الجديد.

• • صيغة العمود المخصص هي المكان الذي تحتاج إلى إدخال الصيغة فيه. لذلك أدخل الصيغة أدناه :

 [First Name]&" "&[Last Name] 

• عند إدخال صيغة في “صيغة العمود المخصصة”، يتحقق PQ من الصيغة التي أدخلتها ويعرض رسالة “لم يتم اكتشاف أخطاء في بناء الجملة” وإذا كان هناك خطأ، فسوف يعرض رسالة خطأ بناءً على نوع الخطأ.
• بمجرد إدخال الصيغة وعدم وجود أخطاء فيها، فقط اضغط على “موافق”.
• لديك الآن عمود جديد في نهاية البيانات يحتوي على قيم من عمودين (الاسم الأول واسم العائلة).

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

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

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

لكنك الآن بحاجة إلى تحويل نص الاسم الكامل الموجود في هذا العمود إلى أحرف كبيرة. الوظيفة التي يمكنك استخدامها هي ” Text.Upper “ . كما يوحي الاسم، فإنه يحول النص إلى نص كبير.

• أولاً، انتقل إلى علامة التبويب “إضافة عمود” وانقر على العمود المخصص.

• • الآن، في مربع حوار العمود المخصص، أدخل اسم العمود والصيغة أدناه في مربع صيغة العمود المخصص:

 Text.Upper([Full Name]) 

• وعند النقر فوق “موافق”، يتم إنشاء عمود جديد بجميع الأسماء بالأحرف الكبيرة.
• والشيء التالي هو حذف العمود القديم وإعادة تسمية العمود الجديد. لذلك انقر بزر الماوس الأيمن على العمود الأول وحدد حذف.

• في النهاية، قم بإعادة تسمية العمود الجديد إلى “الاسم الكامل”.

هناك إجمالي 700 وظيفة يمكنك استخدامها في استعلام الطاقة أثناء إضافة عمود جديد، وإليك القائمة الكاملة التي توفرها Microsoft لهذه الوظائف، تحقق منها.

كيفية تعديل استعلام في PQ

إذا كنت تريد إجراء تغييرات على الاستعلام الموجود بالفعل في المصنف الخاص بك، فيمكنك ببساطة تحريره ثم إجراء هذه التغييرات. في علامة التبويب “البيانات”، يوجد زر يسمى “الاستعلامات والاتصالات”.

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

يمكنك النقر بزر الماوس الأيمن على اسم الاستعلام وتحديد “تحرير” وستحصل عليه في محرر الاستعلام القوي لتحريره.

عندما تقوم بتحرير استعلام، يمكنك أن ترى أن جميع الخطوات التي قمت بها سابقًا مدرجة في “الخطوات المطبقة” والتي يمكنك أيضًا تعديلها أو يمكنك تنفيذ خطوات جديدة.

وبمجرد الانتهاء من تعديلاتك، يمكنك ببساطة النقر فوق الزر “إغلاق وتحميل”.

تصدير واستيراد الاتصالات

إذا كان لديك اتصال استخدمته للاستعلام وتريد الآن مشاركة هذا الاتصال مع شخص آخر، فيمكنك تصدير هذا الاتصال كملف odc.

يوجد في جدول الاستعلام زر يسمى “تصدير الاتصال” وعند النقر عليه يسمح لك بحفظ اتصال هذا الاستعلام في نظامك.

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

ثم انقر فوق الزر “استعراض للمزيد” حيث يمكنك تحديد موقع ملف الاتصال الذي تمت مشاركته معك واستيراده إلى المصنف الخاص بك.

لغة PowerQuery (رمز M)

كما ذكرت سابقًا، لكل خطوة تقوم بها في PowerQuery، يتم إنشاء رمز (في الواجهة الخلفية) يسمى M Code . في علامة التبويب الصفحة الرئيسية، يوجد زر يسمى ” المحرر المتقدم” الذي يمكنك استخدامه لعرض الكود.

وعند الضغط على المحرر المتقدم سيظهر لك محرر الكود وهذا الكود يبدو كالتالي:

M هي لغة حساسة لحالة الأحرف وهي مثل جميع اللغات الأخرى تستخدم المتغيرات والتعبيرات. يبدو الهيكل الأساسي للكود أدناه حيث يبدأ الكود بتعبير LET.

في هذا الكود لدينا متغيرين والقيم المحددة لهما. في النهاية، للحصول على القيمة، تم استخدام التعبير IN. الآن عند النقر فوق “موافق”، سيُرجع القيمة المخصصة للمتغير “Variablename” في النتيجة.

راجع هذا المورد لمعرفة المزيد حول لغة Power Query.

في نهايةالمطاف

لا يمكنك تجنب POWER QUERY. إذا كنت تفكر بهذه الطريقة، فإن الكثير من الأشياء التي نقوم بها باستخدام وظائف Excel أو رموز VBA يمكن أتمتة استخدامها، وأنا متأكد من أن هذا البرنامج التعليمي سوف يلهمك لاستخدامه أكثر فأكثر.