Power Query في Excel: الاستيراد والتنظيف والدمج والتحديث للبيانات
By Mirko Peters · · Updated
يستورد Power Query في Excel البيانات وينظفها دون الحاجة إلى صيغ. تعرّف على مكانه، وأمر From Table/Range، والخطوات المطبّقة (Applied Steps)، والفرق بين الدمج والإلحاق، والتحميل والتحديث.
Automatically translated with AI from the original post. Read the English original
Power Query في Excel هو الأداة الموجودة في علامة التبويب Data، ضمن المجموعة Get & Transform Data، والتي تتصل بالبيانات وتنظفها وتعيد تشكيلها، ثم تحمّل النتيجة إلى ورقة عمل. تُسجَّل الخطوات التي تنفذها وتُعاد عند كل تحديث، فيصبح التنظيف الشهري ضغطة زر واحدة. هذه المقالة موجهة إلى مستخدمي Excel الذين ينسخون البيانات المصدّرة ويلصقونها ويصلحونها كل أسبوع ويريدون التوقف عن ذلك. ستجد فيها نقطة البداية، وكيفية تنظيف الجدول، وكيفية الإلحاق والدمج، وكيفية التحميل والتحديث، وحدود الأداة على Mac وعلى الويب.
النقاط الرئيسية
- يقع Power Query في Excel ضمن علامة التبويب Data في المجموعة Get & Transform Data. يتصل بالبيانات ويعيد تشكيلها ويحمّلها إلى ورقة عمل أو إلى نموذج البيانات (Data Model).
- تُسجَّل كل تغيير تجريه كخطوة في Applied Steps. لا يتغير المصدر الأصلي، وتُنفَّذ الخطوات من جديد عند كل تحديث.
- يضيف الإلحاق صفوف جداول لها الأعمدة نفسها. ويربط الدمج جدولين استنادًا إلى أعمدة مفتاح متطابقة، ويجب أن تكون الأعمدة المتطابقة من نوع البيانات نفسه.
- استخدم Data ثم Refresh All لإعادة تشغيل الاستعلامات. وفي Connection Properties يمكنك التحديث على فترات زمنية محددة أو عند فتح الملف.
- يدعم Excel لنظام Mac وExcel على الويب أجزاءً من Power Query، وتختلف صفحات Microsoft في تحديد ما يمكن لكل منهما إنشاؤه، لذا تحقق من الإصدار الذي تستخدمه.
تصف Microsoft أربع مراحل: الاتصال بالبيانات، ثم تحويلها، ثم دمجها مع مصادر أخرى، ثم تحميلها. يبقى المصدر الأصلي دون تغيير طوال هذه المراحل. وهذه النقطة الأخيرة هي ما يجعل التجربة آمنة، فلن تُفسد الملف الذي بدأت منه.
ما هو Power Query وأين تجده
Power Query محرك لإعداد البيانات مزود بمحرر رسومي. وفقًا لصفحة Microsoft حول ماهية Power Query، فهو يتولى مهام الاستخراج والتحويل والتحميل، ولا يملك مساحة تخزين خاصة به، ويترك الوجهة للمنتج المضيف. وExcel أحد هذه المنتجات المضيفة. وPower BI Desktop مضيف آخر، ويشتركان في المحرر الأساسي نفسه. قد تختلف الموصلات والوجهات بحسب المنتج المضيف، لذا لا تفترض أن الاستعلام الذي رأيته في منتج واحد سيبدو مطابقًا تمامًا في الآخر.
يختلف مكانه باختلاف إصدار Excel، وفقًا لـنظرة عامة Microsoft على Power Query في Excel:
- Excel 2016 و2019 وMicrosoft 365 على Windows. مدمج في البرنامج. انتقل إلى Data وانظر إلى المجموعة Get & Transform Data.
- Excel 2010 و2013 على Windows. كان Power Query إضافة مجانية لها علامة تبويب خاصة في الشريط. تقول Microsoft إنه أُوقف دعمه، though قد يظل يعمل.
- Excel لنظام Mac. مدعوم للتحديث من عدة مصادر. وهو غير متاح في Excel 2016 أو 2019 لنظام Mac.
- Excel على الويب. مدعوم جزئيًا. التفاصيل أدناه.
تذكر Microsoft أيضًا على Windows أن المتطلبات تشمل .NET Framework 4.7.2 أو إصدارًا أحدث، وWebView2 Runtime للموصل الخاص بالويب. وإذا كانت الأوامر مفقودة على جهاز عمل، فاسأل فريق تقنية المعلومات عما إذا كان الإصدار الحالي محدثًا، قبل أن تفترض أن الميزة قد أُزيلت.
كيفية استخدام Power Query في Excel: أول استعلام لك
أسهل بداية هي البيانات الموجودة لديك بالفعل في مصنف.
- انقر على أي خلية في جدول Excel الخاص بك. إذا كانت بياناتك نطاقًا عاديًا، فاضغط أولًا على Ctrl+T لتحويلها إلى جدول.
- انتقل إلى البيانات > الحصول على البيانات وتحويلها > من جدول/نطاق.
- يُفتح Power Query Editor مع معاينة لبياناتك.
- أجرِ التغييرات التي تحتاجها (انظر القسم التالي).
- حدد Home > Close & Load.
تذكر صفحة Microsoft حول الاستيراد من جدول Excel أن النتيجة تُنقل افتراضيًا إلى ورقة عمل جديدة، ولا يتغير الجدول الأصلي. استخدم Load To إذا أردت وجهة مختلفة. وأعد تسمية الورقة الجديدة حتى لا يخلط أحد بينها وبين المصدر.
للاتصال بمصدر آخر، استخدم Data > Get Data واختر المصدر: ملف، أو قاعدة بيانات، أو خدمة عبر الإنترنت، أو الويب. بعد تحديد المصدر، تحصل على معاينة واختيار بين Load (نقل مباشر إلى Excel) وTransform Data (فتح المحرر أولًا). نصيحتي أن تختار Transform Data في معظم الأحيان، حتى عندما تعتقد أن البيانات نظيفة. فالمحرر هو المكان الذي تضبط فيه أنواع الأعمدة، والأنواع الخاطئة هي السبب الأكبر لأخطاء التحديث لاحقًا.
إذا أردت بداية فارغة، تسرد الصفحة نفسها Data > Get Data > From Other Sources > Blank Query، أو Launch Power Query Editor ضمن Get Data.
المحرر بلغة بسيطة
- تعرض معاينة البيانات الجدول الخاص بك، وتتحدث بعد كل تغيير.
- يسرد الجزء Query Settings الخطوات APPLIED STEPS. كل إجراء تتخذه يضيف خطوة، مثل Source أو Changed Type. يمكنك تحديد خطوة لرؤية البيانات في تلك المرحلة، أو تعديل الخطوة، أو حذفها.
- يسرد الجزء Queries جميع الاستعلامات في المصنف. افتحه بالسهم بجوار المعاينة.
- يتيح لك Advanced Editor رؤية السكربت الأساسي وتحريره بلغة Power Query M. تنص Microsoft على أن كل خطوة تبنيها في الواجهة تُكتب بلغة M. ولا تحتاج إلى لمسها للاستفادة من الأداة.
تنظيف البيانات وإعادة تشكيلها بالخطوات
من مهام التنظيف الشائعة في المحرر: تصفية الصفوف، وحذف الأعمدة، وتغيير أنواع البيانات، وتقسيم عمود، واستبدال القيم. يسجل المحرر كل منها كخطوة. توجد الأوامر في شريط المحرر وفي القائمة التي تظهر عند النقر بالزر الأيمن على رأس العمود، وتختلف الأوامر المعروضة حسب نوع العمود، لذا تحقق من قائمة رأس العمود قبل البحث في الشريط.
روتين يعمل مع معظم عمليات التصدير:
- اضبط الأنواع أولًا. تحقق من أيقونة النوع بجوار كل عنوان عمود. فالتواريخ والأرقام والنصوص تتصرف بشكل مختلف في الخطوات اللاحقة وعند الدمج.
- احذف ما لا تحتاجه. كلما قلّ عدد الأعمدة، كانت النتيجة أصغر وأسرع وأقل مفاجآت عندما يتغير المصدر.
- صفِّ الصفوف. استخدم السهم في رأس العمود لإزالة الخانات الفارغة أو القيم التي لا تريدها.
- أصلح النصوص. صحح الأخطاء الإملائية والرموز القديمة، أو احذف المسافات الزائدة، أو قسّم عمودًا يحتوي على معلومتين.
- أعد تسمية الاستعلام في جزء Query Settings حتى تتمكن من العثور عليه لاحقًا.
بما أن Applied Steps قائمة، يمكنك تصحيح خطأ سابق دون البدء من جديد. إذا فشلت خطوة بعد تغيير المصدر، فانظر إلى أول خطوة تحمل خطأ. في معظم الحالات تشير إلى عمود تغير اسمه أو نوعه.
تنبيه بشأن الأعمدة التي أُعيدت تسميتها: تنص صفحة التحديث في Microsoft على أن الأعمدة الجديدة في المصدر لا تظهر في Power Query إلا بعد مراجعة خطوة Source، وأن الشريط الأصفر الذي يفيد بأن المعاينة قد تكون قديمة يعني أنه يجب تحديث الذاكرة المؤقتة المحلية. إذا لم يظهر عمود جديد، فتحقق من هذين الأمرين أولًا.
دمج البيانات: الإلحاق والدمج
يؤدي هذان الأمران مهمتين مختلفتين، ويخلط الناس بينهما في الغالب.
Append يكدّس الصفوف. استخدمه عندما يكون لديك الجدول نفسه بالبنية نفسها عدة مرات، مثل تصدير مختلف لكل شهر. تصف Microsoft عملية Append بأنها تكديس صفوف استعلام واحد فوق استعلام آخر. وهناك نوعان: Inline append يبني على الاستعلام الحالي، أما Intermediate append فينشئ استعلامًا جديدًا في كل مرة.
Merge يربط جدولين جنبًا إلى جنب استنادًا إلى القيم المتطابقة، تمامًا كعملية البحث. تعرض صفحة Microsoft حول دمج الاستعلامات الخطوات:
- تأكد من وجود استعلامين على الأقل يحتويان على عمود للمطابقة. يجب أن يكون لأعمدة المطابقة نوع البيانات نفسه، مثل Text أو Number.
- في المحرر، انتقل إلى Home > Merge Queries. تصبح النتيجة خطوة جديدة في الاستعلام الحالي. إذا أردت استعلامًا منفصلًا بدلًا من ذلك، فافتح السهم بجوار Merge Queries واختر Merge Queries as New.
- اختر الجدول الأساسي وعمود المفتاح الخاص به، ثم الجدول المرتبط وعمود المفتاح الخاص به. إذا كانت لديك عدة أعمدة مفتاح، فحدّدها بالترتيب نفسه في الجدولين.
- تحقّق من عدد التطابقات المعروض في مربع الحوار. إذا كان أقل مما تتوقع، فمن المرجّح أن أعمدة المفتاح تختلف في الإملاء أو المسافات أو النوع.
- اختر Join Kind (انظر أدناه) ثم حدّد OK.
- يظهر عمود جديد. حدّد أيقونة Expand بجوار عنوانه، ثم ضع علامة على الأعمدة المرتبطة التي تريد إضافتها.
أنواع الربط، حسب Microsoft:
- Left outer يُبقي على جميع صفوف الجدول الأساسي والتطابقات من الجدول المرتبط.
- Right outer يُبقي على جميع صفوف الجدول المرتبط والتطابقات من الجدول الأساسي.
- Full outer يُبقي على جميع الصفوف من الجدولين.
- Inner يُبقي فقط الصفوف المتطابقة في الجدولين.
- Left anti يُبقي صفوف الجدول الأساسي التي لا تملك تطابقًا. أما Right anti فيُبقي صفوف الجدول المرتبط التي لا تملك تطابقًا.
- Cross يقرن كل صف في الجدول الأساسي بكل صف في الجدول المرتبط.
لترتيب الجدول أهمية، لأن معنى left وright يعتمد على الجدول الذي اخترته أولًا. يطابق مربع الاختيار Use fuzzy matching to perform the merge القيم التقريبية، وتقول Microsoft إنه يعمل على أعمدة النص فقط. إذا كانت عملية البحث هي عادتك السابقة، فإن الدمج باستخدام Left outer هو الأقرب إليها، أما Left anti فطريقة سريعة لمعرفة الصفوف التي لا تملك تطابقًا، مثل الطلبات التي ليس لها سجل عميل.
تذكر Microsoft أيضًا مستويات الخصوصية. يصنّف Power Query كل مصدر على أنه Public أو Organizational أو Private، ويستخدم هذه التصنيفات لمنع الدمج العرضي للبيانات بطرق قد تكشف معلومات خاصة لمصدر آخر. إذا طلب منك الدمج تحديد مستويات الخصوصية، فاقرأ الرسالة قبل المتابعة.
تحميل الاستعلام وتحديثه
أغلق المحرر عبر Home > Close & Load. تشرح صفحة Microsoft حول إنشاء الاستعلام وتحميله وتحريره هذه الوجهات:
- Close & Load يرسل النتيجة إلى ورقة عمل.
- Close & Load To يتيح لك اختيار وجهة أخرى، مثل نموذج البيانات Data Model. في مربع الحوار Import Data، ضع علامة على Add this data to the Data Model إذا كنت تخطط لإنشاء PivotTables من عدة جداول.
- إذا كان Load To معطّلًا، فاستخدم Close & Load ثم انتقل إلى Data > Queries & Connections، وانقر بزر الماوس الأيمن على الاستعلام في علامة التبويب Queries واختر Load To.
لتغيير الإعدادات الافتراضية، انتقل إلى File > Options and settings > Query Options > Data Load. يمكنك استخدام إعدادات التحميل القياسية أو تعيين إعدادات افتراضية مخصصة للتحميل إلى ورقة عمل أو إلى نموذج البيانات.
لتحرير الاستعلام لاحقًا، حدّد خلية ضمن بياناته واختر Query > Edit، أو انقر بزر الماوس الأيمن على الاستعلام في Queries & Connections واختر Edit.
التحديث
يعيد التحديث تشغيل كل خطوة مقابل المصدر الحالي. الطرق المتاحة، من صفحة تحديث اتصال البيانات الخارجية في Microsoft:
- Data > Refresh All يحدّث كل شيء في المصنف (Ctrl+Alt+F5). يوفّر السهم بجانبه الخيار Refresh للعنصر المحدد (Alt+F5). يوقف Esc عملية التحديث.
- للتحديث التلقائي، انتقل إلى Data > Queries & Connections، وافتح علامة التبويب Connections، ثم انقر بزر الماوس الأيمن على أحد الاتصالات واختر Properties. في علامة التبويب Usage يمكنك ضبط Refresh every (بالدقائق) وRefresh data when opening the file.
- يتيح لك الخيار Enable background refresh في علامة التبويب نفسها متابعة العمل أثناء تشغيل الاستعلام. ولا يتوفر هذا الخيار للاستعلامات من نوع OLAP أو للاتصالات التي تغذي نموذج البيانات.
كلمات المرور المحفوظة غير مشفّرة، وتنصح Microsoft بعدم حفظها. ألغِ تحديد Save password في علامة التبويب Definition، وسيطلب Excel منك كلمة المرور مرة واحدة في كل جلسة.
إذا أرسلت المصنف إلى شخص آخر، فقد يحتاج إلى صلاحية الوصول إلى المصدر. فالاستعلام الذي يقرأ ملفًا على محركك المحلي أو موقعًا لا يستطيع المستلم فتحه سيفشل على جهازه. ضع مصدر البيانات في موقع مشترك، أو أرسل النتائج المحمّلة كقيم.
Power Query على Mac وفي Excel للويب
تدعم المنصتان جزءًا من الأداة، لكن صفحات Microsoft لا تتطابق تمامًا.
Excel for the web. تذكر صفحة Microsoft أنه يمكنك الاستيراد من مصادر تشمل مصنف Excel، وText/CSV، وXML، وJSON، وSQL Server، وقائمة SharePoint Online، وOData، إضافةً إلى جدول فارغ أو استعلام فارغ. يمكنك فتح Power Query Editor (Data > Get Data > Launch Power Query Editor)، والتحميل باستخدام Close & Load، والتحديث عبر Data > Refresh All. عرض الاستعلامات وتحديثها متاح لجميع مشتركي Microsoft 365، بينما تتطلب الوظائف الإضافية خطة للأعمال أو خطة للمؤسسات (E3 فما فوق). وتحتاج بعض الميزات إلى تفعيل ملفات تعريف الارتباط من أطراف ثالثة في المتصفح.
Excel for Mac. تذكر إحدى صفحات Microsoft أنك تبدأ من Data > Get Data > Get Data (Power Query)، وأن تشكيل البيانات يتطلب Microsoft 365 والإصدار 16.69 أو أحدث، وأن Transform Data يفتح المحرر. وفي الصفحة نفسها، يذكر قسم منفصل أن إنشاء الاستعلامات في المحرر غير متاح في Excel for Mac، ويصف كتابة الاستعلامات بلغة VBA على Windows واستيراد الوحدة على Mac. لا أستطيع التوفيق بين هاتين العبارتين، لذا اختبر الأمر على جهاز Mac الخاص بك قبل بناء أي عملية تعتمد عليه. ويُوصف استيراد SQL Server بأنه متاح فقط في نسخة Insiders Beta.
متى لا يكفي Power Query
سأكون صريحًا في هذا الشأن: Power Query مخصص لإعداد البيانات، وليس لتخزينها. وتذكر Microsoft أن المنتج المضيف هو ما يحدد وجهة النتائج، وفي Excel يعني ذلك ورقة العمل أو نموذج البيانات. ولورقة العمل حد ثابت لعدد الصفوف، لذا لن تتسع نتيجة استعلام كبيرة جدًا داخل ورقة واحدة. حمّلها إلى نموذج البيانات بدلًا من ذلك، أو انقل العمل إلى قاعدة بيانات أو إلى مسار معالجة مخصص.
حدود أخرى تستحق المعرفة:
- تقرأ الاستعلامات المصدر عند تشغيلها، ولا تنقل التغييرات الحية إلى المصنف بشكل مستمر.
- التعاون في هذا المجال صعب. فتحرير الاستعلام نفسه من شخصين في مصنف مشترك فكرة سيئة. اتفقوا على مالك واحد، وأعطوا الاستعلامات أسماء واضحة.
- إذا كنت تتعامل مع بيانات خاضعة للتنظيم، فتذكّر أن المصنف سهل النسخ. اتبع قواعد مؤسستك بشأن أماكن تخزين هذه الملفات.
الأخطاء الشائعة
- تجاهل أنواع البيانات، ثم التساؤل عن سبب عدم تطابق أي نتيجة في الدمج.
- الدمج على أعمدة ذات أنواع مختلفة أو تحتوي على مسافات زائدة في النهاية.
- استخدام الدمج حين كنت تقصد الإلحاق، أو العكس.
- تحميل كل شيء إلى ورقة عمل في حين يناسب الاستعلام الذي يُستخدم للاتصال فقط أو نموذج البيانات بشكل أفضل.
- حفظ كلمة مرور المصدر ضمن الاتصال.
- نسيان التحديث قبل إرسال التقرير.
- نقل ملف المصدر ثم عدم فهم سبب فشل الاستعلام.
موضوعات ذات صلة يمكنك استكشافها
- إزالة التكرارات في المحرر. يمكن لـ Power Query إزالة التكرارات من جدول كخطوة قابلة للتكرار، على عكس الأمر الفوري في الشريط.
- إلغاء تدوير الأعمدة. حوّل أعمدة الأشهر إلى صفوف قبل إنشاء PivotTable. تذكر Microsoft Power Query لهذا الغرض تحديدًا.
- المعلمات. لدى Microsoft صفحة عن استعلامات المعلمات لإعادة استخدام الاستعلام بمدخلات مختلفة.
- لغة M. تعلّم كيفية قراءة السكربت في المحرر المتقدم، بدءًا من صفحة Power Query M.
- Power BI. تعرّف على طريقة عمل المحرر نفسه في Power BI Desktop عندما تتجاوز بياناتك قدرات Excel.
الخطوة التالية
اختر تصديرًا واحدًا تنظّفه يدويًا كل أسبوع. حوّله إلى جدول، ومرّره عبر From Table/Range، واضبط أنواع الأعمدة، ثم حمّله إلى ورقة عمل جديدة. في الأسبوع التالي، الصق التصدير الجديد في الجدول واضغط Refresh All. هذا هو Power Query في Excel وهو يؤدي عمله.
المصادر
- حول Power Query في Excel (دعم Microsoft)
- ما هو Power Query؟ (Microsoft Learn)
- الاستيراد من جدول Excel (دعم Microsoft)
- إنشاء استعلام أو تحميله أو تحريره في Excel (Power Query) (دعم Microsoft)
- إلحاق الاستعلامات (Power Query) (دعم Microsoft)
- دمج الاستعلامات (Power Query) (دعم Microsoft)
- تحديث اتصال البيانات الخارجية في Excel (دعم Microsoft)
- استخدام Power Query في Excel للويب (دعم Microsoft)
- استيراد البيانات وتشكيلها في Excel for Mac (Power Query) (دعم Microsoft)