ترکیب داده های اکسل در پاور کوئری

در این مطلب با استفاده از مجموعه get&transform (افزونه پاور کوئری – power query)  داده های مربوط به یک محصول را از یک فایل اکسل و داده های مربوط به سفارش های محصول را از یک منبع داده odata feed فراخوانی میکنیم. در نهایت با ترکیب داده ها در پاور کوئری این دو منبع را یکی میکنیم و یک گزارش در مورد میزان فروش سالانه و فروش هر محصول ایجاد خواهیم کرد.

این مطلب یک قسمت از سری آموزش های ما در مورد مجموعه get&transform اکسل (افزونه پاور کوئری-power query) است

برای انجام تمرینات این مطلب به ورک بوک سفارشات و محصولات احتیاج دارید . شما میتوانید این فایل را از پیوست همین پست ملاحضه و دانلود کنید. با استفاده از خاصیت save as فایل را به نام Products and Orders.xlsx ذخیره سازی کنید.

وارد کردن لیست محصولات به یک ورک بوک اکسل

برای انجام این کار می بایست محصولات موجود در لیست Products and Orders.xlsx را در یک فایل ورک بوک اکسل قرار داد.

گام ۱: اتصال به یک ورک بوک اکسل با پاور کوئری

1. 1. یک ورک بوک اکسل ایجاد کنید.
   2. در تب Data و در قسمت get&transform یا پاور کوئری گزینه new query را انتخاب کنید و دستور From File > From WORKBOOK را کلیک نمائید.

1. در دیالوگ باکس browse اکسل، Products and Orders.xlsx را تایپ یا جستجو کنید.
2. در پنجره navigator، بر روی شیت product دابل کلیک کنید یا product را کلیک کنید و edit query را انتخاب کنید. زمانی که یک کوئری را اصلاح میکنید یا به یک منبع داده متصل می شوید، پنجره query editor ظاهر میشود.

گام ۲: ردیف اول را بعنوان عنوان ستون جدول در پاور کوئری قرار دهید

اگر سطر اول که نام های ستون ها هستن به عنوان سرستون انتخاب شده بود نیاز به انجام این مرحله نیست.

در شبکه Query preview ، اولین ردیف جدول شامل نام ستون های جدول نمی باشد. برای قراردادن ردیف اول بعنوان عنوان ستون های جدول مراحل زیر را طی کنید:

1. در گوشه بالا سمت چپ Use First Row as Headers را کلیک کنید.

گام ۳: ستون های غیرضروری را در پاور کوئری حذف کنید.

در این مرحله، تمام ستون ها به جز  ProductID، ProductName، CategoryID و  QuantityPerUnitرا حذف کنید.

1. در قسمت Query preview، ستون های ProductID، ProductName، CategoryID و QuantityPerUnitرا انتخاب کنید. ( از Ctrl+Click  یا Shift+Click استفاده کنید.)
2. در ریبون query editor فرمان Remove Columns > Remove Other Columns را انتخاب کنید یا بر روی عنوان ستون راست کلیک کنید و گزینه  Remove Other Columns را انتخاب کنید.

زمانی که مراحل گفته شده را انجام دادید، گام های کوئری ایجاد شده در پاور کوئری و در پنجره Query Settings لیست میشوند. هر گام از کوئری دارای یک فرمول است که به زبان M نوشته شده است.

وظیفه	گام های کوئری	فرمول
اتصال به بک ورک بوک اکسل	Source	Source{[Name=”Products”]}[Data]
تبدیل اولین ردیف به عنوان ستون های جدول (در صورتی که مرحله دوم نیاز به انجام داشته باشد.)	FirstRowAsHeader	Table.PromoteHeaders (Products)
حذف ستون های اضافی	RemovedOtherColumns (Products)	Table.SelectColumns (FirstRowAsHeader,{“ProductID”, “ProductName”, “CategoryID”, “QuantityPerUnit”})

گام ۴: فراخوانی کوئری محصول با پاور کوئری

در این مرحله، کوئری مربوط به محصول را به ورک بوک اکسل وارد خواهیم کرد.

1. در ریبون query editor، گزینه َapply&close را انتخاب کنید. نتیجه در یک ورک شیت جدید اکسل ظاهر خواهد شد.

وارد کردن داده های مربوط به سفارش از یک پایگاه داده odata feed

حال داده های مورد نظر را از پایگاه داده odata به آدرس http://services.odata.org/northwind/northwind.svc به ورک شیت اکسل خود وارد میکنیم.

گام ۱:  اتصال به یک پایگاه odata feed با پاور کوئری

1. در تب پاور کوئری، From Other Sources > From OData Feed را انتخاب کنید.
2. در دیالوگ باکس odata feed، آدرس اینترنتی ذکر شده در بالا را وارد کنید.
3. Ok را بزنید.
4. در پنجره navigator، بر روی جدول orders دابل کلیک کنید و یا orders را کلیک کنید و edit را بزنید.

نکته: زمانی که اشاره گر ماوس را بر روی یک جدول قرار دهید، خلاصه ای از اطلاعات جدول را مشاهده خواهید کرد.

گام ۲: یک جدول شامل خلاصه سفارشات در پاور کوئری ایجاد کنید.

در این مرحله، جدول Order_Details را گسترش میدهیم و ستون های productid،  UnitPrice، Quantity را از جدول Order_Details با جدول Orders  ترکیب می کنیم.  عملیات expand ستون های مختلف را از یک جدول گرفته و در جدولی دیگر ترکیب میکند. زمانی که کوئری اجرا شود، ردیف های مربوط به جدول وابسته(Order_Details) با ردیف های مربوط به جدول اصلی (orders) ترکیب میشود.

1. در پنجره Query Preview ، در ستون Order_Details قرار گیرید.
2. در ستون Order_Details، دکمه expand(فلش دو سر کنار عنوان ستون) را کلیک کنید.
3. در لیست کشویی expand:

الف) (Select All Columns) را انتخاب کنید تا چک تمام ستون ها برداشته شود.

ب) باکس های  ProductID، UnitPrice و Quantity را انتخاب کنید.

ج) ok را بزنید.

گام ۳ : ستون های غیر ضروری را در پاور کوئری حذف کنید.

در این مرحله، می بایست تمام ستون ها به جز ProductID، UnitPrice و Quantity، orderdate، حذف شوند.

حذف ستون های انتخاب شده

1. در پنجره query preview تمام ستون ها را انتخاب کنید.

الف) اولین ستون(OrderID) را انتخاب کنید.

ب) shift را نگه داشته و آخرین ستون(shipper) را کلیک کنید.

ج) ctrl را نگه داشته و ستون های OrderDate، Order_Details.ProductID، Order_Details.UnitPrice و Order_Details.Quantity را انتخاب کنید.

2. بر روی عنوان یکی از ستون های انتخاب شده کلیک کنید و Remove Columns را انتخاب کنید.

گام ۴: محاسبه line total (قیمت* تعداد واحد) برای هر کدام از ردیف های Order_Details با ایجاد ستون custom در پاور کوئری

 

1. گزینه   Insert Column> CustomColumn را انتخاب کنید.
2. در دیالوگ باکس Insert Custom Column و در تکست باکس Custom Column Formulaمقدار [Order_Details.UnitPrice] * [Order_Details.Quantity] را وارد کنید. (یا می توانید از بخش Available columns در سمت راست پنجره ستون ها را انتخاب کنید و فرمول را بنویسید.)
3. در تکست باکس New column name، مقدار line total را وارد کنید.
4. Ok را بزنید

 گام ۵: تغییر ستون orderdate (تغییر تاریخ و ساعت کامل به سال)

در این مرحله، ستون OrderDate را تغییر می دهیم تا سال مربوط به تاریخ سفارش را ارائه دهد.

1. در شبکه preview، بر روی ستون OrderDate راست کلیک کنید و Transform->Year را انتخاب کنید.
2. نام ستون OrderDate را تغییر داده و year قرار دهید.

الف) بر روی ستون OrderDate راست کلیک کنید و year را وارد کنید یا

ب) بر روی ستون OrderDate کلیک کنید، rename را انتخاب کنید و year را وارد کنید.

گام ۶: ردیف ها را بر اساس productID و year گروه بندی کنید.

1. در شبکه query preview،ستون های Year و  Order_Details.ProductID را انتخاب کنید. 
2. بر روی یکی از عنوان ها راست کلیک کنید و Group By را انتخاب کنید.
3. در دیالوگ باکس Group By :

الف) در باکس New column name عبارت Total Sales را وارد کنید.

ب) در لیست کشویی operation گزینه sum را انتخاب کنید.

ج) در لیست column گزینه line total را انتخاب کنید.

4. Ok را کلیک کنید.

گام ۷: یک کوئری را تغییر نام دهید

قبل از اینکه داده های فروش را وارد اکسل کنید، نام کوئری را total sale قرار دهید:

1. در پنجره Query setting، در باکس name عبارت total sale را وارد کنید.

نتایج نهایی کوئری

بعد از اینکه گام های گفته شده را انجام دادید، یک کوئری به نام total sale خواهید داشت که از داده های Northwind OData feed تشکیل شده است.

گام های ایجاد شده پاور کوئری

فعالیت هایی که در پاور کوئری انجام میدهید به صورت مرحله ای در قسمت query setting و در لیست applied steps ثبت و ضبط  میشود. هر گام از کوئری دارای یک فرمول است که به زبان M نوشته شده است.

نوع کار	مراحل کوئری	فرمول
اتصال به یک پایگاه داده odata	Source	Source{[Name=”Orders”]}[Data]
توسعه جدول Order_Details	Expand Order_Details	(Orders, “Order_Details”, {“ProductID”, “UnitPrice”, “Quantity”}, {“Order_Details.ProductID”, “Order_Details.UnitPrice”, “Order_Details.Quantity”})
حذف ستون های اضافی	RemovedColumns	(#”Expand Order_Details”,{“OrderID”, “CustomerID”, “EmployeeID”, “RequiredDate”, “ShippedDate”, “ShipVia”, “Freight”, “ShipName”, “ShipAddress”, “ShipCity”, “ShipRegion”, “ShipPostalCode”, “ShipCountry”, “Customer”, “Employee”, “Shipper”})
محاسبه line total برای هر ردیف order_detail	InsertedColumns	(RemovedColumns, “Custom”, each [Order_Details.UnitPrice] * [Order_Details.Quantity])
تغییر ستون order_date برای نشان دادن سال	RenamedColumns	(InsertedCustom,{{“Custom”, “Line Total”}})
	TransformedColumn	(RenamedColumns,{{“OrderDate”, Date.Year}})
	RenamedColumns1	(TransformedColumn,{{“OrderDate”, “Year”}})
گروه بندی ردیف ها بر اساس productID و year	GroupedRows	(RenamedColumns1, {“Year”, “Order_Details.ProductID”}, {{“Total Sales”, each List.Sum([Line Total]), type number}})

گام ۸: خاصیت دانلود کوئری در ورک بوک اکسل را غیر فعال کنید.

از آنجایی که کوئری total sale نشان دهنده مقدار نهایی فروش سالانه و هر محصول نیست، شما باید بارگذاری کوئری در ورک بوک اکسل را غیر فعال کنید. زمانی که گزینه  Load to Worksheet در قسمت Queries settings غیر فعال باشد، داده های منتج از این کوئری، دانلود نمیشود ولی کوئری میتواند با دیگر کوئریها ترکیب شود تا نتیجه مورد نظر بدست آید. در قسمت بعد با نحوه ترکیب این کوئری با کوئری محصول آشنا خواهید شد.

غیر فعال کردن خاصیت بارگزاری کوئری

1. در قسمت Query setting، چک مربوط به  Load to worksheet.را بردارید.
2. در ریبون query editor، گزینه apply&close را انتخاب کنید.

اگر در قسمت query setting این گزینه فعال نبود می توانید تنظیمات عمومی پاور کوئری را از این قسمت تغییر دهید.

کوئری های محصول و فروش کل را با هم ترکیب کنید.

پاور کوئری شما را قادر می سازد که به ترکیب داده ها در پاور کوئری بپردازید. این ترکیب را میتوان از طریق ادغام یا اتصال انجام داد. ادغام را میتوان برای هر کوئری با شکل جدولی و بدون توجه به منبع داده ای که از آن ناشی میشود، انجام داد.

در اینجا میخواهیم کوئری محصول(product) و کوئری فروش کل (Total Sales) را با استفاده از ادغام و گسترش کوئری با هم ترکیب کنیم.

گام ۱: کوئری productID را با total sale با هم ادغام کنید.

1. در ورک بوک اکسل،در کوئری products قرار گیرید.
2. در تب Combine گزینه merge queries را انتخاب کنید.
3. در دیالوگ باکس ظاهر شده، products را بعنوان جدول اولیه و total sales را بعنوان جدول ثانویه انتخاب کنید. Total sales تبدیل به یک ستون قابل گسترش جدید خواهد شد.
4. برای مطابقت total sales با product بر اساس productID، ستون productID را از جدول product و ستون Order_Details.ProductID را از جدول Total Sales انتخاب کنید.
5. در دیالوگ باکس privacy levels:

الف) برای هر دو منبع  Organizational را بعنوان سطح امنیت انتخاب کنید.

ب) save را کلیک کنید.

ج) ok را بزنید.

نکته امنیتی: Privacy Levels باعث میشود کاربر به صورت ناخواسته داده های مربوط به منابع مختلف داده که ممکن است خصوصی یا سازمانی باشند، با هم ترکیب نکند. بسته به کوئری، یک کاربر ممکن است به صورت ناخواسته داده ها را از یک منبع خصوصی به یک منبع خطرناک ارسال کند. پاور کوئری هر منبع داده را تحلیل میکند و آنها را در سطوح مختلف امنیتی از جمله عمومی، سازمانی و خصوصی طبقه بندی میکند.

پس از اینکه ok را کلیک کردید، عملیات ادغام(merge)، یک کوئری ایجاد میکند. کوئری جدید شامل تمام ستون های جدول اولیه( products) و یک ستون  که شامل لینک راهبری به جدول وابسته( total sales) میشود، می باشد.

 

گام ۲: گسترش یک ستون ادغام شده

در این مرحله، ستون ادغام شده به نام new column را بسط می دهیم تا دو ستون جدید در کوئری محصول ایجاد کنیم: year و total sales

گسترش ستون new column

1. در شبکه Query Preview، آیکن بسط(آیکن شبیه به فلش دو سر) مربوط به ستون new column را کلیک کنید.
2. در لیست کشویی expand:

الف) Select All Columns را کلیک کنید تا تمام ستون ها غیر فعال شوند.

ب) year و total sales را کلیک کنید.

ج) ok را کلیک کنید.

3. نام این دو ستون را به year و total sales تغییر دهید.
4. برای مشخص کردن اینکه چه محصولی و در چه سالی بیشترین حجم از فروش را داشته است، آنها را بر اساس total sales به صورت نزولی مرتب کنید.
5. نام کوئری را به Total Sales per Product تغییر دهید.

گام های ایجاد شده پاور کوئری

همانطور که شما عملیات ادغام را در پاور کوئری انجام میدهید، گام های انجام کار در قسمت query setting و در لیست applied steps ذخیره سازی میشود. هر مرحله دارای یک فرمول است که به زبان برنامه نویسی M نوشته شده است.

نوع کار	مرحله کوئری	فرمول
ادغام productID در کوری total sales	Source (data source for Mergeoperation)	(Products,{“ProductID”},#”Total Sales”,{“Order_Details.ProductID”},”NewColumn”)
گسترش یک ستون ادغام شده	ExpandNewColumn	(Source, “NewColumn”, {“Year”, “Total Sales”}, {“NewColumn.Year”, “NewColumn.Total Sales”})
	RenamedColumns	(#”Expand NewColumn”,{{“NewColumn.Year”, “Year”}, {“NewColumn.Total Sales”, “Total Sales”}})
	SortedRows	(RenamedColumns,{{“Total Sales”, Order.Descending}})

گام ۳: یک کوئری فروش کل برای هر محصول را در یک مدل داده اکسل بارگذاری کنید.

در این مرحله، گزینه  Load to Worksheet را غیر فعال میکنیم و یک کوئری در مدل داده اکسل(Excel Data Model) بارگذاری میکنیم تا گزارشی ساخته شود که به نتایج کوئری متصل باشد. پس از اینکه داده های مورد نظر را در مدل داده بارگذاری کردید، میتوانید با استفاده از power pivot و power view تحلیل های بیشتری را انجام دهید.

کوئری فروش کل هر محصول را در مدل داده اکسل بارگذاری کنید.

1. در قسمت Query setting، چک Load to worksheet را بردارید و چک  Load to data model را قرار دهید.
2. برای بارگذاری کوئری در مدل داده اکسل، گزینه apply&close را انتخاب کنید.

کوئری نهایی فروش کل برای هر محصول

پس از اینکه تمام مراحل گفته شده را انجام دادید، کوئری را در اختیار دارید که داده های فایل product orders.xlsx و Northwind OData feed را با هم ترکیب میکند. این کوئری میتواند در یک مدل power pivot مورد استفاده قرار گیرد. علاوه بر این، تغییرات در کوئری ، نتایج ارائه شده در مدل power pivot را تغییر و به روز رسانی میکند.

نکته:

query editor فقط زمانی ظاهر میشود که یک کوئری را بارگذاری یا اصلاح کنید یا یک کوئری جدید ایجاد کنید. ویدئو زیر نشان میدهد query editor زمانی ظاهر میشود که یک کوئری در ورک بوک اکسل را اصلاح میکنیم. برای مشاهده query editor بدون ایجاد یا اصلاح یک کوئری، در بخش  Get External Dataدر تب power query، دستور  From Other Sources > Blank Query را انتخاب کنید. ویدئوی زیر یک راه برای ظاهر شدن این ابزار را نشان میدهد.