برنامج Microsoft Power Query التعليمي حول كيفية إصلاح مشاكل عرض العمود الثابت المختلط

مدثر: اليوم لدينا مشكلة شيقة للغاية يجب التعامل معها. تكمن المشكلة في هذا الملف في أنه تم إصلاحه محددًا بواسطة أعمدة ولا أعرف كيفية حل هذا باستخدام Microsoft Power Query. يمكنك مشاهدة الفيديو الكامل لهذا البرنامج التعليمي أسفل هذه المدونة.

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

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

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

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

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

يمكننا أن نرى أنه خط ذو طول ثابت. يمكننا أيضًا رؤية الرؤوس والجداول الأولية والجداول الفرعية. هذه هي الأجزاء التي نهتم بها ونريد الاستخلاص منها.

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

لقد قمت بإنشاء معلمة لموقع الملف حيث قمت بتخزين ملف CSV. لقد أحضرت ذلك كملف مرحلي وقمت بتثبيته في معلمة موقع الملف الخاص بي. ثم قمت بإنشاء مرجع وسأعمل من هذا المرجع. هذا ما نبحث عنه الآن في استعلام الطاقة من Microsoft.

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

• إضافة عمود فهرس
• إنشاء قائمة مخزن مؤقت في Microsoft Power Query
• تقسيم الرؤوس من الصفوف
• إزالة القيم الفارغة والفراغات في Microsoft Power Query
• تقسيم الاقسام
• اقتطاع السلاسل النصية في Microsoft Power Query
• خاتمة

إضافة عمود فهرس

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

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

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

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

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

للبدء ، دعنا ننسخ هذه القيمة:

أضف استعلامًا فارغًا جديدًا ، والصقه فيه ، ثم قم باستدعاء HeaderID .

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

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

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

لنقم بتمكين التحميل لهذين الاستعلامات.

إنشاء قائمة مخزن مؤقت في Microsoft Power Query

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

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

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

سأسمي هذا BufferList والإشارة Column1. لتحميله في الذاكرة ، سأضيف List.Buffer خطوة.

ها هو المتغير الخاص بي على طول الطريق في الأعلى. يمكنني الرجوع إليها مرارًا وتكرارًا.

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

سأكتب أنه إذا كان Column1 يساوي معرف الرأس الخاص بنا ، فأنا أريد أن يكون رقم الفهرس الخاص بي فارغًا.

نتيجة لذلك ، عثر على النص وعاد 5 و 23.

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

في هذه الحالة ، أضفت Table.FillDown وفي السلسلة النصية ، أشرت إلى العمود الذي نريد تعبئته (Header).

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

تقسيم الرؤوس من الصفوف

الخطوة التالية هي فصل الرؤوس من الصفوف. سأضيف عمودًا مخصصًا آخر وأطلق عليه اسم Temp . هذه المرة ، سنفعل شيئًا أكثر تفصيلاً ونستفيد من قائمة BufferList التي أنشأتها سابقًا.

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

سأبدأ بعبارة if واستخدم List.Contains للبحث عن موضع معين في BufferList والإشارة إلى HeaderID الاستعلام .

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

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

نحتاج أيضًا إلى تحديد قسم الصف. إذا كانت القائمة لا تحتوي على HeaderID ، ولكن DetailID ، فنحن في قسم الصف.

إذا كان Column1 عبارة عن سلسلة نصية فارغة ، فأنا أريد أن يظل فارغًا . إذا لم يكن الأمر كذلك ، فأنا أريد أن يكون فارغًا .

حصلت هذه المعارضة على صف الرأس وأعادت H ، ثم عثرت على صف مفصل وأعادت الحرف R. ثم أعادت 0s لجميع العناصر التي تمت مشاركتها داخل قسم الصف هذا.

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

سنفعل ذلك في شريط الصيغة ونستخدم Table.FillDown مرة أخرى. إنها تريد قائمة باسم العمود ، وهو عمود Temp الخاص بنا.

الآن لدينا قيمتا H و R مكررتان في جميع أنحاء هذا العمود ، مما يعني أنه يمكننا بالفعل فصل الرؤوس من الأقسام التفصيلية.

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

إزالة القيم الفارغة والفراغات في Microsoft Power Query

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

تقسيم الاقسام

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

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

الآن ، لدي كل صفوف الرؤوس هذه هنا.

يمكنني تحديد Column1 ، والانتقال إلى شريط الصيغة ، وتحديد تقسيم العمود ، ثم التقسيم حسب المواضع.

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

الأشياء الوحيدة التي نهتم بها من الرؤوس هي العنصر ورقم الوظيفة .

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

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

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

لذلك نفتح هذا ونلغي تحديد الشرطات والعناصر.

الآن ، تم الانتهاء من جميع الرؤوس.

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

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

سأقوم بنسخ هذا وإضافته إلى عامل التصفية الخاص بي. هذه المرة ، لا أقوم باختيار H ولكني أقوم باختيار R.

ثم سأحدد Column1 ، وانتقل إلى عمود Split ، مقسمًا حسب المواضع ، ثم اجعل استعلام الطاقة يكتشفه.

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

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

اقتطاع السلاسل النصية في Microsoft Power Query

سأحدد هذا العمود الأول ، ثم اضغط على Down + Shift للتحديد حتى العمود 1.10. انتقل إلى التحويل ، وحدد التنسيق ، ثم قص . لن يؤدي التشذيب إلا إلى إزالة المسافات الزائدة أمام السلسلة أو في نهايتها ، وليس بينها.

بعد ذلك ، يمكننا فقط ترقية الرؤوس ، لذلك لا يتعين علي كتابة جميع رؤوس أو عناوين هذه الأعمدة. في خطوة الانقسام ، قمت بإعادة تسمية عمودين. الآن ، بالطبع ، مع وجود 10 أعمدة ، هذا أمر مزعج بعض الشيء.

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

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

لذلك ، يمكننا استخدام الدمج الذاتي حتى نتمكن من دمج الجدول مع نفسه لإعادة تجميع هذه المعلومات معًا. في علامة التبويب الصفحة الرئيسية ، حدد دمج ، ثم حدد العمود 5 والاستعلام نفسه.

بدلاً من AllDetails ، أريد AllHeaders كجدولي الأولي الذي أريد الدمج معه.

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

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

سنقوم بإزالة العمود الأخير هنا ثم ننتهي من إصلاح عرض العمود الثابت المختلط في استعلام Microsoft power.

خاتمة

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

لدينا قدر هائل من المحتوى يصدر طوال الوقت مني ومن مجموعة من منشئي المحتوى ، وجميعهم مكرسون لتحسين طريقة استخدامك لـ LuckyTemplates ومنصة Power.

ميليسا