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

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

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

المهمة 1: استيراد منتجات إلى مصنف Excel

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

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

1. أنشئ مصنف Excel.
2. حدد البيانات>التي تحصل عليها>من ملف>من مصنف.
3. في مربع الحوار "استيراد البيانات"، استعرض وصولا إلى الملفProducts.xlsx الذي قمت بتنزيله وحدد موقعه، ثم حدد "فتح".
4. في جزء المتصفح ، انقر نقرا مزدوجا فوق جدول المنتجات . يظهر محرر Power Query.

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

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

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

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

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

1. في معاينة البيانات، حدد الأعمدة معرف المنتج واسم المنتج ومعرف الفئةوالكمية بحسب الوحدة (استخدم Ctrl+النقر أو Shift+النقر).
2. حدد إزالة أعمدة وإزالة>أعمدة أخرى.
   

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

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

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

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

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

المهمة	خطوة الاستعلام	الصيغة
استيراد مصنف Excel	المصدر	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
حدد جدول المنتجات	التنقل	= المصدر{[العنصر="المنتجات",النوع="الجدول"]}[البيانات]
يكتشف 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}, {"Discontinued ", type logical}})
إزالة أعمدة أخرى لعرض الأعمدة الهامة فقط	الأعمدة الأخرى التي تمت إزالتها	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

المهمة 2: استيراد بيانات طلبية من موجز OData

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

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

      ملاحظة

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

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

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

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.سعر الوحدة] * [Order_Details.الكمية].
4. في المربع اسم العمود الجديد ، أدخل إجمالي البند.
5. حدّد موافق.

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

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

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

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

   1. انقر نقرا مزدوجا فوق العمود OrderDate وأدخل Year .
   2. انقر بزر الماوس الأيمن فوق العمود OrderDate وحدد إعادة تسمية وأدخل السنة.

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

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

2. انقر بزر الماوس الأيمن فوق أحد الرؤوس وحدد تجميع حسب.

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"])
تحديد جدول	التنقل	= المصدر{[الاسم="الطلبات"]}[البيانات]
توسيع الجدول 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", "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 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([إجمالي السطر]), اكتب number}})

المهمة 3: جمع الاستعلامين "المنتجات" و"إجمالي المبيعات"

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

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

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

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

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

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

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

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

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

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

   ملاحظة

   تمنع مستويات الخصوصية المستخدم من جمع بيانات من مصادر بيانات متعددة عن غير قصد، الأمر الذي يعتبر خاصاً أو تنظيمياً. ووفقاً للاستعلام، بإمكان المستخدم إرسال بيانات عن غير قصد من مصدر البيانات الخاص إلى مصدر بيانات آخر قد يكون ضاراً. يحلل Power Query كل مصدر بيانات ويصنّفه في مستوى الخصوصية المحدد: عام وتنظيمي وخاص. لمزيد من المعلومات حول مستويات الخصوصية، راجع تعيين مستويات الخصوصية (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(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}})

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

تعليماتPower Query لـ Excel