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

لكي تتمكن من تنفيذ هذا البرنامج التعليمي، تحتاج إلى مصنف المنتجات. في مربع الحوار حفظ باسم، قم بتسمية الملف Products and Orders.xlsx.

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

الخطوة 1: الاتصال بمصنف Excel

  1. أنشئ مصنف Excel.

  2. حدد بيانات > البيانات > من ملف > من المصنف.

  3. في مربع الحوار استيراد بيانات، استعرض بحثا عن ملف Products.xlsx الذي قمت بتنزيلها، ثم حدد فتح.

  4. في جزء "المتنقل"، انقر نقرا مزدوجا فوق جدول المنتجات. يظهر محرر Power Query.

الخطوة 2: افحص خطوات الاستعلام

بشكل افتراضي، يضيف Power Query تلقائيا عدة خطوات كملائمة لك. افحص كل خطوة ضمن الخطوات المطبقة في جزء الإعدادات الاستعلام لمعرفة المزيد.

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

  2. انقر بيمين فوق خطوة التنقل، وحدد تحرير الإعدادات. تم إنشاء هذه الخطوة عند تحديد الجدول من مربع الحوار التنقل.

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

الخطوة 3: إزالة أعمدة أخرى لعرض الأعمدة الهامة فقط

ستقوم في هذه الخطوة بإزالة كل الأعمدة باستثناء ProductID وProductName وCategoryID وQuantityPerUnit.

  1. في معاينة البيانات، حدد أعمدة ProductIDو ProductNameو CategoryIDو QuantityPerUnit (استخدم Ctrl+النقر أو Shift+النقر).

  2. حدد إزالة الأعمدة > إزالة أعمدة أخرى.

    إخفاء أعمدة أخرى

الخطوة 4: تحميل استعلام المنتجات

في هذه الخطوة، يمكنك تحميل استعلام المنتجات إلى Excel عمل.

  • حدد الصفحة > إغلاق & تحميل. يظهر الاستعلام في ورقة عمل Excel جديدة.

ملخص: خطوات Power Query التي تم إنشاؤها في المهمة 1

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

المهمة

خطوة الاستعلام

الصيغة

استيراد مصنف Excel

المصدر

= Excel. Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

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

التنقل

= Source{[Item="Products",Kind="Table"]}[Data]

يكشف Power Query تلقائيا عن أنواع بيانات الأعمدة

النوع الذي تم تغييره

= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"تم إيقافه", type logical}})

إزالة أعمدة أخرى لعرض الأعمدة الهامة فقط

أعمدة أخرى تمت إزالتها

= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

في هذه المهمة، يمكنك استيراد البيانات إلى مصنف Excel من موجز Northwind OData النموذجي في http://services.odata.org/Northwind/Northwind.svc،وتوسيع جدول Order_Details، وإزالة الأعمدة، وحساب إجمالي السطر، وتحويل OrderDate، تجميع الصفوف حسب ProductID و Year، وإعادة تسمية الاستعلام، وتعطيل تنزيل الاستعلام إلى مصنف Excel.

الخطوة 1: الاتصال إلى موجز OData

  1. حدد بيانات > الحصول على> من مصادر أخرى > من موجز OData.

  2. في مربع الحوار موجز OData، أدخل عنوان URL الخاص بموجز Northwind OData.

  3. حدد موافق.

  4. في جزء "المتنقل"، انقر نقرا مزدوجا فوق جدول "الطلبات".

الخطوة 2: توسيع الجدول Order_Details

في هذه الخطوة، ستوسّع الجدول Order_Details المرتبط بجدول الطلبيات، لجمع الأعمدة ProductID وUnitPrice والكمية الموجودة في Order_Details في جدول الطلبيات. تقوم العملية توسيع بجمع أعمدة من جدول مرتبط بجدول موضوع. عند تشغيل الاستعلام، يتم دمج الصفوف من الجدول المرتبط(Order_Details ) في صفوف مع الجدول الأساسي (الطلبات).

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

بعد توسيع الجدول Order_Details، تتم إضافة ثلاثة أعمدة جديدة وصفوف إضافية إلى الجدول الطلبيات، بحيث يضاف عمود واحد لكل صف في الجدول المتداخل أو المرتبط.

  1. في معاينة البيانات، قم بالتمرير أفقيا إلى العمود Order_Details البيانات.

  2. في العمود Order_Details، حدد أيقونة التوسع (توسيع).

  3. في قائمة توسيع المنسدلة:

    1. حدد (تحديد كافة الأعمدة) لمسح كل الأعمدة.

    2. حدد ProductIDو UnitPriceو Quantity.

    3. حدد موافق.

      توسيع ارتباط جدول تفاصيل_الطلبية

      ملاحظة: في Power Query، يمكنك توسيع الجداول المرتبطة من عمود لتجميع أعمدة الجدول المرتبط قبل توسيع البيانات في جدول الموضوع. ولمزيد من المعلومات حول كيفية إجراء عمليات التجميع، راجع تجميع البيانات من عمود.

الخطوة 3: إزالة أعمدة أخرى لعرض الأعمدة الهامة فقط

ستقوم في هذه الخطوة بإزالة كل الأعمدة باستثناء OrderDate وProductID وUnitPrice والكمية

  1. في معاينة البيانات، حدد الأعمدة التالية:

    1. حدد العمود الأول، OrderID.

    2. Shift+انقر فوق العمود الأخير، الشحن.

    3. اضغط على Ctrl وانقر فوق الأعمدة OrderDate وOrder_Details.ProductID وOrder_Details.UnitPrice وOrder_Details.Quantity.

  2. انقر بزر الماوس الأيمن فوق رأس عمود محدد، وحدد إزالة أعمدة أخرى.

الخطوة 4: حساب إجمالي البند لكل صف من صفوف Order_Details

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

  1. في معاينة البيانات، حدد أيقونة الجدول (أيقونة الجدول) في الزاوية العلوية اليمنى من المعاينة.

  2. انقر فوق إضافة عمود مخصص.

  3. في مربع الحوار عمود مخصص، في مربع صيغة العمود المخصص، أدخل [Order_Details.UnitPrice] * [Order_Details.Quantity].

  4. في المربع اسم العمود الجديد، أدخل إجمالي السطر.

  5. حدد موافق.

حساب إجمالي فئة المنتجات لكل صف من صفوف "تفاصيل_الطلبية"

الخطوة 5: تحويل عمود OrderDate إلى "السنة"

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

  1. في معاينة البيانات،انقر ب زر الماوس الأيمن فوق العمود OrderDate، وحدد تحويل > السنة.

  2. أعد تسمية العمود OrderDate إلى السنة:

    1. انقر نقراً مزدوجاُ فوق العمود OrderDate وأدخل السنة أو

    2. Right-Click العمود OrderDate، حدد إعادة تسمية، وأدخل السنة.

الخطوة 6: تجميع الصفوف حسب ProductID والسنة

  1. في معاينة البيانات، حدد السنةOrder_Details.ProductID.

  2. Right-Click أحد رؤوس، وحدد تجميع حسب.

  3. في مربع الحوار تجميع حسب:

    1. في مربع النص اسم عمود جديد، أدخل إجمالي المبيعات.

    2. في القائمة المنسدلة لـ العملية، حدد المجموع.

    3. في قائمة العمود المنسدلة، حدد إجمالي البند.

  4. حدد موافق.

    مربع الحوار "تجميع حسب" لعمليات التجميع

الخطوة 7: إعادة تسمية استعلام

قبل استيراد بيانات المبيعات إلى Excel، أعد تسمية الاستعلام:

  • في الجزء استعلام الإعدادات، في المربع الاسم أدخل إجمالي المبيعات.

النتائج: الاستعلام النهائي للمهمة 2

بعد تنفيذ كل الخطوات، ستحصل على استعلام "إجمالي المبيعات" حول موجز Northwind OData.

إجمالي المبيعات

ملخص: خطوات Power Query التي تم إنشاؤها في المهمة 2 

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

المهمة

خطوة الاستعلام

الصيغة

الاتصال بموجز OData

المصدر

= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])

تحديد جدول

التنقل

= Source{[Name="Orders"]}[Data]

توسيع الجدول Order_Details

Expand Order_Details

= Table.ExpandTableColumn(الطلبات، "Order_Details"، {"ProductID"، "UnitPrice"، "Quantity"}، {"Order_Details.ProductID"، "Order_Details.UnitPrice"، "Order_Details.Quantity"})

إزالة أعمدة أخرى لعرض الأعمدة الهامة فقط

RemovedColumns

= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

حساب إجمالي فئة المنتجات لكل صف من صفوف "تفاصيل_الطلبية"

مخصص مضاف

= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

= Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

التغيير إلى اسم ذي معنى أكبر، إجمالي Lne

أعمدة تمت إعادة تسميتها

= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})

تحويل العمود OrderDate لعرض السنة

السنة المستخرجة

= Table.TransformColumns(#"Rows grouped Rows",{{"Year", Date.Year, Int64.Type}})

تغيير إلى 

أسماء ذات معنى أكبر، "OrderDate" و"السنة"

الأعمدة المعاد تسميتها 1

Table.RenameColumns

‎(TransformedColumn,{{"OrderDate", "Year"}})‎

تجميع الصفوف بحسب ProductID والسنة

GroupedRows

= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

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

في هذه المهمة، يمكنك دمج استعلامي "المنتجات" و"إجمالي المبيعات" باستخدام استعلام دمج وتشغيل "توسيع"، ثم تحميل استعلام "إجمالي المبيعات لكل منتج" إلى Excel البيانات.

الخطوة 1: دمج ProductID في استعلام "إجمالي المبيعات"

  1. في Excel، انتقل إلى استعلام المنتجات على علامة التبويب ورقة عمل المنتجات.

  2. حدد خلية في الاستعلام، ثم حدد استعلام > دمج.

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

  4. لمطابقة إجمالي المبيعات مع المنتجات حسب ProductID، حدد العمود ProductID من جدول المنتجات، والعمود Order_Details.ProductID من جدول إجمالي المبيعات.

  5. في مربع الحوار مستويات الخصوصية:

    1. حدد تنظيمي لمستوى عزل الخصوصية الخاص بمصدري البيانات.

    2. حدد حفظ.

  6. حدد موافق.

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

    مربع الحوار "دمج"

النتيجة

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

دمج نهائي

الخطوة 2: توسيع عمود مدمج

في هذه الخطوة، يمكنك توسيع العمود المدمج بالاسم NewColumn لإنشاء عمودين جديدين في استعلام المنتجات: السنة وإجمالي المبيعات.

  1. في معاينة البيانات، حدد أيقونة توسيع (توسيع) الموجودة بجانب NewColumn.

  2. في القائمة المنسدل توسيع:

    1. حدد (تحديد كافة الأعمدة) لمسح كل الأعمدة.

    2. حدد السنةوإجمالي المبيعات.

    3. حدد موافق.

  3. أعد تسمية هذين العمودين إلى السنة وإجمالي المبيعات.

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

  5. قم بـ إعادة تسمية الاستعلام إلى إجمالي المبيعات حسب المنتج.

النتيجة

ارتباط توسيع الجدول

الخطوة 3: تحميل استعلام "إجمالي المبيعات حسب المنتج" إلى نموذج بيانات Excel

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

  1. حدد الصفحة > إغلاق & تحميل.

  2. في مربع الحوار استيراد البيانات، تأكد من تحديد إضافة هذه البيانات إلى نموذج البيانات. لمزيد من المعلومات حول استخدام مربع الحوار هذا، حدد علامة استفهام (؟).

النتيجة

لديك استعلام إجمالي المبيعات لكل منتج يجمع البيانات من ملف Products.xlsx والتغذية في Northwind OData. يتم تطبيق هذا الاستعلام على نموذج Power Pivot. بالإضافة إلى ذلك، تقوم التغييرات التي يتم إدخالها على الاستعلام بتعديل الجدول الناتج وتحديثه في نموذج البيانات.

ملخص: خطوات Power Query التي تم إنشاؤها في المهمة 3

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

المهمة

خطوة الاستعلام

الصيغة

دمج ProductID في استعلام "إجمالي المبيعات"

المصدر (مصدر بيانات للعملية دمج)

= Table.NestedJoin(المنتجات، {"ProductID"}، #"Total Sales"، {"Order_Details.ProductID"}، "إجمالي المبيعات"، JoinKind.leftOuter)

توسيع عمود دمج

إجمالي المبيعات الموسعة

= Table.ExpandTableColumn(المصدر، "إجمالي المبيعات"، {"Year"، "Total Sales"}، {"Total Sales.Year"، "Total Sales.Total Sales"})

إعادة تسمية عمودين

أعمدة تمت إعادة تسميتها

= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})

فرز إجمالي المبيعات بالترتيب التصاعدي

الصفوف التي تم فرزها

= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})

اطلع أيضاً على

تعليمات Power Query Excel

هل تحتاج إلى مزيد من المساعدة؟

توسيع المهارات
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Microsoft Insider

هل كانت المعلومات مفيدة؟

ما مدى رضاك عن جودة الترجمة؟
ما الذي أثّر في تجربتك؟

نشكرك على ملاحظاتك!

×