جدول الأبعاد في ملف نصي: حل Power Query

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

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

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

ستجد فئة في مشكلة الأسبوع في منتدى LuckyTemplates.

ينصب التركيز الأساسي على العملية وليس على النتيجة.

إذا نجحت في تقسيم المشكلة إلى أجزاء أصغر ، فستتمكن من حل أي مشكلة تواجهها.

ميليسا دي كورتي

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

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

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

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

لدي بعض الخبرة في برنامج Excel ، وأحد أقوى ميزاته من وجهة نظري هي الجداول المحورية .

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

• الجداول المحورية في Excel
• استخدام Power Query لإنشاء جدول أبعاد
• وظيفة تنظيف النص المخصص
• تمحور البيانات
• خاتمة

الجداول المحورية في Excel

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

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

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

وفي الصفحة التالية ، قمت بإنشاء جدول محوري فارغ.

الآن ، ما لم أعرضه لكم هو أن لديّ ثلاثة أعمدة هنا بدلاً من العمودين اللذين أريتكم إياهما بالفعل.

سأقوم بسحب الجزء الخاص بي إلى الصفوف.

نظرًا لأن العمود 1 يحتوي على أسماء الحقول ، فسوف أسحب ذلك إلى قسم العمود.

يحتوي العمود 2 على جميع قيم الحقول ، لذلك سأقوم بسحبها إلى قسم القيمة.

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

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

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

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

استخدام Power Query لإنشاء جدول أبعاد

دعنا ننتقل إلى استعلام الطاقة ونراجع الحل الخاص بي.

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

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

هذه هي مجموعتي الأساسية التي تم إنشاؤها باستخدام واجهة المستخدم.

يمكنك أن ترى أن هناك الكثير من الخطوات في الخطوات المطبقة على الجانب الأيمن.

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

لنفتح المحرر المتقدم.

نرى أن هذا الاستعلام يحتوي على 31 خطوة.

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

أول شيء فعلته هو إزالة خطوة نوع التغيير. أقترح إنشاء وظيفة مخصصة للقيام بكل خطوات تحويل النص هذه.

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

والثاني هو الخطوة المحورية لإعادة هذا النوع من البيانات إلى تنسيق جدولي مناسب .

وظيفة تنظيف النص المخصص

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

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

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

لنلقِ نظرة على رمز M لوظيفة تنظيف النص.

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

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

إذا لم تكن معتادًا عليهم ، يمكنك البحث عن جميع وظائف M في دليل الصيغة M. هذا هو الرابط الذي تريد الذهاب إليه.

ستجد قسمًا مخصصًا لاستعلام الطاقة والوظائف.

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

إذا كنت تبحث عن تحول معين ، يمكنك البحث عنه هنا.

تمحور البيانات

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

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

كل ما علينا القيام به هو جعل البيانات محورية. في علامة تبويب التحويل ، ستجد العمود المحوري. مع تحديد العمود 1 ، انقر فوق العمود المحوري.

سيستخدم قيم العمود 1 كاسم العمود الجديد. كما يريد أيضًا معرفة مكان قيم أسماء الحقول هذه. هؤلاء موجودون في عمودنا المدمج.

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

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

خاتمة

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

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

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

ميليسا