تعلم كيفية دمج مصادر بيانات متعددة (Power Query)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   

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

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

• حدد Home > Close & Load. يظهر الاستعلام في ورقة عمل 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}, {"تم إيقافه", اكتب 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. حدد Data > Get Data > من مصادر أخرى > من موجز 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+انقر فوق العمود الأخير، Shipper.

   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 ، وحدد Transform > Year.

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

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

   2. Right-Click في العمود OrderDate ، حدد Rename، وأدخل Year.

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

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

2. Right-Click أحد العناوين، وحدد تجميع حسب.

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

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

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

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

4. حدّد موافق.

   

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

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

• في جزء Query Settings ، في مربع Name أدخل Total Sales.

النتائج: الاستعلام النهائي للمهمة 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(Orders, "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" و"Ship" و"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 Total	أعمدة تمت إعادة تسميتها	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
تحويل العمود OrderDate لعرض السنة	السنة المستخرجة	= Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})
تغيير إلى  أسماء ذات معنى أكثر، OrderDate و Year	إعادة تسمية الأعمدة 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. حدد Home > Close & Load.

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

النتيجة

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

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

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

المهمة	خطوة الاستعلام	الصيغة
دمج ProductID في استعلام "إجمالي المبيعات"	المصدر (مصدر بيانات للعملية دمج)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
توسيع عمود دمج	إجمالي المبيعات الموسعة	= Table.ExpandTableColumn(Source, "Total Sales", {"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}})