تحويل تخطيط نوع التقويم إلى تنسيق جدولي في LuckyTemplates باستخدام محرر الاستعلام

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

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

قبل أن نبدأ ، دعنا أولاً نفحص البيانات التي لدينا.

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

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

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

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

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

تنظيف البيانات للحصول على تنسيق جدولي مناسب

لتسهيل الأمور ، دعنا نحول بياناتنا داخل محرر Power Query . ولكن قبل القيام بذلك ، لنتأكد من أن Formula Bar مرئي على شاشتك. إذا لم يكن كذلك ، فانتقل إلى علامة التبويب عرض ثم حدد المربع الموجود بجوار شريط الصيغة.

الخطوة الأولى هي إزالة القيم غير الضرورية في بياناتنا. دعنا نزيل العمود الأول والصفين العلويين من بياناتنا لأننا لسنا في حاجة إليها حقًا. للقيام بذلك ، انقر فوق علامة التبويب Remove Rows ، وانقر فوق Remove Top Rows ، وأدخل 2 ، ثم اضغط على OK.

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

دعنا أيضًا نزيل فهرس الصفوف من 14 إلى 16 نظرًا لأنها ممتلئة تمامًا بقيم خالية فقط. مرة أخرى ، انتقل إلى علامة التبويب إزالة الصفوف ثم انقر فوق إزالة الصفوف الفارغة .

هناك مشكلة أخرى لدينا وهي أن قيم التاريخ تتخطى عمودًا في كل مرة. التواريخ متبوعة دائمًا بقيمة فارغة كما هو موضح أدناه. لا يحدث هذا فقط في الصف العلوي ولكن في الصفوف أدناه أيضًا.

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

يجب أن يبدو الجدول الآن وكأنه قد انقلب على جانبه بعد تنفيذ الإجراء.

بعد ذلك ، حدد جميع الأعمدة التي تحتوي على التواريخ ، وانقر بزر الماوس الأيمن فوق رأس العمود ، ثم حدد Fill Down.

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

كسر الجداول

بالنظر إلى البيانات التي لدينا الآن ، نرى أن هناك أربعة جداول متجاورة. تشكل الأعمدة من 1 إلى 7 جدولاً واحداً ، وتشكل الأعمدة من 8 إلى 13 جدولاً آخر ، وهكذا.

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

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

هذه هي الطريقة التي أعددت بها الصيغة.

تنشئ الصيغة قائمة بالأعمدة التي كانت لدينا سابقًا. تحتوي القيمة الأولى من القائمة على العمود 1 ، بينما تحتوي القيمة الثانية على العمود 2 ، وهكذا.

تحديد قائمة الجداول الجديدة للحصول على تنسيق جدولي مناسب

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

للقيام بذلك ، دعنا أولاً نحول القائمة مرة أخرى إلى جدول بالنقر فوق الزر To Table في الزاوية اليسرى العليا.

ثم أضف عمود فهرس بجانبه.

الشيء التالي الذي سنفعله هو إضافة عمود آخر بجوار عمود الفهرس. يجب أن يساعدنا هذا العمود في تحديد القيم الموجودة في Column1 التي تحتوي على قيم التاريخ. دعنا نسمي هذا العمود Index2.

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

سنستخدم ، ثم نشير إلى Column1. ستكون الأقواس بمثابة عامل التشغيل ؛ إضافة 0 بين هذه الأقواس يعني أننا نحصل على القيمة الأولى من القائمة ؛ ثم نحدد ما إذا كان تاريخًا. إذا كان الأمر كذلك ، فأنا أريد أن تعيد الصيغة رقم الفهرس ؛ إذا لم يكن كذلك ، فأنا لا أريده أن يعيد أي قيمة على الإطلاق.

نظرًا لأن القيمة الأولى في Column1 تحتوي على تاريخ ، يجب أن تحتوي القيمة الأولى في Index2 على رقم الفهرس الخاص بها 0. أيضًا ، نظرًا لأن القيمة الثانية في Column1 لا تحتوي على تاريخ ، فيجب أن تحتوي القيمة الثانية في Index2 على قيمة خالية فقط.

هذه هي الطريقة التي أعددت بها معادلة الفهرس 2. فقط تأكد من عدم وجود أخطاء في بناء الجملة في صيغتك.

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

تحديد القيم التي تنتمي إلى الجدول

الآن ، تشير قيم الفهرس هذه داخل الفهرس 2 إلى بدايات ونهايات الجداول المنفصلة التي لدينا. أي أن الرقم 0 يشير إلى بداية الجدول الأول ، و 7 يشير إلى نهاية الجدول الأول ، وبداية الجدول الثاني ، وهكذا.

لمساعدتنا في تحديد القيم الموجودة في العمود 1 التي تنتمي إلى نفس الجدول ، دعنا نطبق إجراء تعبئة في عمودنا الجديد.

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

تجميع البيانات للحصول على تنسيق جدولي مناسب

نظرًا لأننا نعرف بالفعل القيم الموجودة في العمود 1 تنتمي إلى نفس الجدول ، فلنجمعها الآن معًا. للقيام بذلك ، سنستخدم عملية Group By.

من خلال القيام بذلك ، ننتهي الآن بأربع قيم فقط في عمود الفهرس 2 الخاص بنا. لاحظ أن عمودًا آخر يسمى Count يوجد بجانب عمود Index2 الخاص بنا. يحسب هذا العمود عدد الصفوف في كل من قيمنا في العمود Index2.

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

للقيام بذلك ، سنستخدم الجدول .

من المهم التأكد من أننا أشرنا إلى العمود الصحيح في صيغتنا. تحقق مما إذا كان Column1 يحتوي بالفعل على قيم القوائم.

يؤدي هذا إلى تجميع جميع القيم الموجودة في Column1 بقيمة Index2 بقيمة 0 في جدول واحد ، والقيم التي تحتوي على قيمة Index2 من 7 في جدول آخر ، وهكذا.

قمنا بفحص كل سجل إلى القيمة 0 ، ثم استردنا قيمة القائمة من Column1. أخيرًا ، قمنا بتجميع هذه القوائم لتشكيل جدول واحد باستخدام رمز M.

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

إلحاق جداول منفصلة

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

تدمج العملية Table.Combine قائمة الجداول ، ثم تنشئ جدولاً واحداً منها. هنا ، سنشير إلى العمود الذي يحتوي على قائمة الجداول الخاصة بنا ، وهو عمود العدد.

لدينا الآن جدول يتم فيه إلحاق جميع الجداول المتداخلة في جدول واحد كبير.

بعد إلحاق جميع الجداول ، يمكننا الآن تنفيذ الخطوات القليلة الأخيرة في تحويل بياناتنا إلى تنسيق جدولي مناسب. حدد أول عمودين من الجدول ثم انقر فوق الزر Unpivot Other Columns في علامة التبويب Transform.

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

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

انقر نقرًا مزدوجًا فوق رأس العمود لكل عمود لتغيير اسمه وفقًا لذلك.

بالنسبة للعمود الأول ، قم بتغيير نوعه حتى الآن لأنه يحتوي على قيم التاريخ الخاصة بنا.

ثم حدد العمود الثاني والثالث ، واضبط أنواعها على نص.

كله تمام! هذا ما يجب أن يبدو عليه التنسيق الجدولي المناسب.

خاتمة

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

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

يعد فهم المفاهيم المستخدمة هنا أمرًا مهمًا للغاية حيث يمكن أيضًا تطبيقها بعدة طرق أخرى.

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

استمتع بالعمل من خلال هذا!

ميليسا