خاصية Power Query تُعتبر نقلة نوعية من حيث الوقت الذي أوفره، فهي تضمن أن البيانات المستوردة جاهزة للتحليل في Excel. إذا كنت جديدًا على هذه الأداة، فمن المفيد معرفة بعض الأوامر التي يمكن أن تنقل مهاراتك في تحويل البيانات إلى المستوى التالي. وحتى إذا لم تسمع بـ Power Query من قبل، فإنه لا يزال من المفيد إلقاء نظرة على هذه الأمثلة لترى ما إذا كنت ستتحمس لتعلّم كيفية استخدام Power Query.
تنظيف البيانات في Excel، خاصةً لعدة مصنفات، هو أحد أكثر المهام إزعاجًا التي يتعين على أي مستخدم Excel التعامل معها. ولكن مع Power Query، يمكن أن تصبح هذه المهمة ممتعة إلى حد ما.
روابط سريعة
تقسيم الخلايا باستخدام الفواصل
تقسيم البيانات بسهولة إلى أعمدة
إحدى المشكلات الشائعة التي ستواجهها عند تنظيف ورقة Excel هي عندما يتم دمج البيانات التي يجب تقسيمها إلى عمودين في عمود واحد. ألق نظرة على لقطة الشاشة أدناه، حيث يحتوي عمود Order ID أيضًا على اسم العميل.
سنقوم بتقسيم هذا العمود في Power Query باستخدام فاصل، وهو حرف أو رمز (مثل فاصلة أو مسافة أو واصلة) يفصل البيانات داخل الخلية. الفاصل هو الواصلة (-) التي تفصل معرف الطلب عن اسم العميل في المثال أعلاه.
لنفترض أن لديك هذه الورقة محفوظة في مكان ما على جهاز الكمبيوتر الخاص بك. لاستيرادها، حدد علامة التبويب Data وانقر فوق Get Data -> From File -> From Excel Workbook في مجموعة أوامر Get & Transform Data في الشريط.
من هناك، انتقل إلى مكان وجود الملف، وحدده، وانقر فوق Import. ضمن Display Options على اليسار، حدد الورقة التي تحتوي على البيانات وانقر فوق Transform Data. ضع في اعتبارك أن هذه مجرد طريقة واحدة لاستيراد البيانات إلى المحرر – ستكون الخيارات مختلفة اعتمادًا على المكان الذي تحصل منه على البيانات.
في محرر Power Query، قم بتمييز العمود (Order ID في مثالنا) وانقر فوق Split Column -> By Delimiter في مجموعة أوامر Transform في علامة التبويب Home. في القائمة المنسدلة Select or enter delimiter، حدد Custom. أدخل مباشرةً أدناه الفاصل الذي تريد استخدامه وانقر فوق OK.
بمجرد تقسيم العمود، أعد تسمية الأعمدة الجديدة إلى شيء منطقي. لقد أعدت تسميتها إلى Order ID و Customer Name على التوالي. بعد ذلك، انقر فوق
إغلاق وتحميل الموجودة في مجموعة أوامر إغلاق.
طرح التواريخ
الحصول على فروق دقيقة بين التواريخ
بالعودة إلى مثال الجدول السابق، يمكننا أيضًا إجراء عمليات حسابية متنوعة على الأعمدة في محرر Power Query. عادةً ما أستغل هذه الفرصة لإضافة أعمدة إضافية لبعض العمليات الحسابية. وأكثر العمليات شيوعًا هي إيجاد الفرق بين التواريخ.
هنا، أريد معرفة عدد الأيام التي استغرقتها الطلبات للتسليم، لذا سأقوم بطرح تاريخ الطلب من تاريخ التسليم. للقيام بذلك، حدد عمود Delivery Date (تاريخ التسليم)، واضغط على مفتاح Ctrl، ثم حدد عمود Order Date (تاريخ الطلب) (انتبه إلى ترتيب التحديد). بعد ذلك، حدد علامة التبويب Add Column (إضافة عمود)، وانقر على Date -> Subtract Days (تاريخ -> طرح أيام) في مجموعة أوامر From Date & Time (من تاريخ ووقت) في الشريط.
سيحتوي العمود الجديد على اسم عام مثل Subtraction (طرح). يمكنك النقر المزدوج عليه وإعادة تسميته إلى شيء أكثر وضوحًا. من هناك، يمكنك إغلاق وتحميل الورقة إلى Excel لتحليل البيانات.
تصفية الأخطاء
تأكد من أنك ترى فقط الصفوف المتأثرة
لا يوفر Power Query طريقة سهلة للتصفية بحيث يتم عرض الصفوف التي تحتوي على أخطاء فقط. قد يكون هذا الأمر مزعجًا إذا كانت الأخطاء موجودة في ملف يحتوي على مئات الصفوف أو أكثر.
لنفترض أنك تعلم أن عمود Order Date (تاريخ الطلب) يحتوي على أخطاء، وتريد حلها يدويًا قبل تحميل البيانات في Excel. يمكنك إنشاء عامل تصفية لعرض الصفوف المتأثرة فقط.
حدد علامة التبويب Add Column (إضافة عمود) وانقر على Custom Column (عمود مخصص) في مجموعة أوامر General (عام) في الشريط. في منطقة النص Custom column formula (صيغة العمود المخصص)، أدخل الصيغة التالية وانقر على OK (موافق):
try [Order Date]
سيضيف Power Query عمودًا جديدًا يسمى Custom (مخصص)، وستحتوي الصفوف إما على قيمة TRUE (صحيح) إذا كانت تحتوي على أخطاء أو FALSE (خطأ) إذا لم تكن كذلك. الآن، قم بما يلي:
- انقر على رمز السهم الموجود على يسار Custom
column - ألغِ تحديد كل شيء ما عدا Has Error.
- انقر على موافق (OK).
- انقر على رمز السهم الموجود بجوار عمود Custom مرة أخرى.
- ألغِ تحديد False.
- انقر على موافق (OK).
الآن سترى فقط الصفوف التي تحتوي على أخطاء لتفحصها. إذا كانت الصفوف المتأثرة غير مهمة، فإن أسرع طريقة للتعامل معها هي النقر بزر الماوس الأيمن على العمود وتحديد إزالة الأخطاء (Remove Errors) من القائمة. سيؤدي هذا إلى حذف الصفوف، لذا استخدمه بحذر.
إذا كانت مهمة، انقر بزر الماوس الأيمن على العمود وحدد استبدال الأخطاء (Replace Errors). إذا كنت تريد أن تحتوي جميعها على نفس القيمة، فأدخلها في مربع النص وانقر على موافق (OK). إذا لم يكن الأمر كذلك، وكنت لا تزال ترغب في الاحتفاظ بها ولكن التخلص من الأخطاء، قم بتعيين قيمة مثل null حتى يتم حل الأخطاء.
يمكنك أيضًا الرجوع إلى المصدر وتصحيح الأخطاء وإعادة استيراد البيانات بعد ذلك.
بعد ذلك، احذف خطوات إنشاء الفلتر في قسم الخطوات المطبقة (Applied Steps) في لوحة إعدادات الاستعلام (Query Settings) على اليمين. بالنسبة لي، كانت الخطوات هي Added Custom1 و Expanded Custom1 و Filtered Rows1. ما عليك سوى النقر على رمز X بجوارها والنقر على حذف (Delete) في النافذة المنبثقة.
تحويل البيانات من تنسيق أفقي إلى عمودي (Unpivot)
المساعدة في تحليل البيانات
البيانات المحورية (Pivoted data) هي البيانات المنظمة بتنسيق أفقي واسع، مع توزيع القيم عبر أعمدة متعددة. يمكن أن يجعل هذا تحليل البيانات صعبًا عند استخدام شيء مثل جداول Pivot في Excel.
ألقِ نظرة على لقطة الشاشة للجدول أدناه، والذي يعرض بيانات المبيعات في ثلاث ولايات. بما أن الأشهر تمثل فئة، فمن الأفضل أن تكون في صفوف بدلًا من أعمدة لتسهيل تحليل البيانات.
لتصحيح ذلك، نحتاج إلى إلغاء تجميع البيانات، أي إعادة تشكيلها إلى تنسيق طولي. بمعنى آخر، يجب أن تكون الولايات والأشهر والمبيعات في صفوف منفصلة.
القيام بذلك يدويًا سيستلزم الكثير من النسخ واللصق، ومحاذاة القيم يدويًا، والتأكد من عدم وجود أخطاء. ولكن Power Query يمكن أن يجعل الأمر أسهل.
للقيام بذلك، حدد علامة التبويب Data، وقم بتمييز الجدول، وانقر فوق From Table/Range في مجموعة أوامر Get & Transform Data في الشريط. في محرر Power Query، حدد الأعمدة التي تريد إلغاء تجميعها بالضغط باستمرار على مفتاح Ctrl والنقر عليها (على سبيل المثال، January, February، و March). انقر بزر الماوس الأيمن عليها وحدد Unpivot Columns في القائمة.
تم الآن إلغاء تجميع بياناتك وهي جاهزة لمزيد من تحليل البيانات. تأكد من إعادة تسمية رؤوس Attribute و Value إلى شيء أكثر وصفية. سيكون هذا Months و Sales في حالتنا.
استعلام بسيط يقطع شوطًا طويلاً
ليس من المبالغة القول بأن Power Query سيجعلك تتطلع إلى تنظيف بيانات Excel. هذا صحيح بشكل خاص لأنه يمكنك إنشاء عملية آلية لتنظيف أوراق متعددة بنفس الطريقة. وكما ترون، يمكنك فعل الكثير ببضعة أوامر بسيطة.
