ابزار Unpivot ابزاری کاربردی در Power Query

Unpivot کردن داده ها با ابزار Unpivot

همونطور که میدونیم یکی از بهترین ابزارها برای گزارشگیری ابزار پیوت تیبل هست. این ابزار بدون نیاز به فرمول نویسی، میتونوه گزارش های خیلی متنوع و کاربردی رو در اختیارمون قرار بده. اما همونطور که در مورد این ابزار میدونیم، دیتابیس مورد نیاز این ابزار یک دیتابیس جدولی (Tabular) هست. در واقع اگر دیتابیس ماتریسی داشته باشیم، نمیتونیم از ابزار پیوت تیبل برای گزارشگیری استفاده کنیم. پس باید راهی پیدا کنیم که دیتابیس ماتریسی رو به دیتابیس جدولی (Tabular) تبدیل کنیم (اصطلاحا Unpivot کنیم) تا بتونیم از پیوت تیبل برای گزارشگیری از این داده ها استفاده کنیم. تفاوت این دو نوع دیتابیس در شکل 1 نمایش داده شده است.

شکل 1 – تفاوت ساختار دیتابیس ماتریسی و جدولی

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

برای تبدیل دیتابیس ماتریسی به دیتابیس مسطح چکار باید انجام بدیم؟

پاورکوئری امکانی رو در اختیار ما قرار داده که میتونه این کار رو انجام بده. اسم این ابزار Unpivot هست. که در واقع داده ها رو از حالت ماتریسی یا (پیوت شده) به حالت جدولی (Tabular) تبدیل میکنه. حالا ببینیم که این ابزار به چه صورت کار میکنه.

نکته:
برای انجام این کار میتونیم از کدنویسی VBA هم استفاده کنیم و با استفاده از حلقه های For دیتابیس ماتریسی رو به جدولی (Tabular) تبدیل کنیم.

حتما بخوانید: لیست فایل های یک پوشه در اکسل

برای استفاده از این ابزار مراحل زیر رو طی میکنیم:

گام اول: تبدیل داده ها به Table

روی یکی از سلول های جدول ماتریسی کلیک میکنیم
از تب Insert روی گزینه Table کلیک میکنیم
از پنجره باز شده، محدوده مشخص شده رو در صورت نیاز اصلاح میکنیم.
با زدن Ok محدوده مورد نظر به Table تبدیل میشه

اگر با Table آشنا نیستید مقاله مربوط به Table رو مطالعه کنید.

گام دوم: وارد کردن داده ها در پاور کوئری

روی یک سلول از تیبل کلیک میکنیم و از تب Data و قسمت Get & Transform Data روی گزینه From Table/Range کلیک میکنیم.
در پنجره Power Query Editor (شکل 2) ستون نام محصول که قراره بقیه داده ها بر اساس اون جابجا بشن رو انتخاب میکنیم.

شکل 2 – ورود به پنجره Query Editor

حالا روی ستون محصول کلیک راست کرده و روی گزینه Unpivot other columns کلیک میکنیم.

شکل 3 – انتخاب ستون مورد نظر برای Unpivot

با زدن این گزینه داده ها بصورت جدولی چیده میشوند.

شکل 4 – تبدیل داده های ماتریسی به جدول

حالا کافیه نام سرستون ها رو به عباراتی با معنی تغییر بدیم. برای این کار کافیه روی سرستون دبل کلیک کنیم و نام دلخواه رو تایپ کنیم.

شکل 5 – تغییر نام سرستون داده ها

حالا باید داده های تغییر ساختار یافته رو به شیت اکسل منتقل کنیم. برای این کار کافیه از تب Home روی گزینه Load to Close & کلیک میکنیم.

شکل 6 – انتقال داده ها به شیت اکسل

از پنجره نمایش داده شده، گزینه مورد نظر (در اینجا، Table) رو انتخاب میکنیم تا داده ها در قالب یک Table در یک شیت جدید نمایش داده بشه.

حتما بخوانید: انتقال داده از وبسایت به اکسل

شکل 7 – انتخاب حالت مورد نظر برای نگهداری داده

حالا دیگه داده ها رو در قالب یک دیتابیس جدولی و در یک شیت مجزا داریم و آماده برای گزارشگیری با پیوت تیبل.

بروزرسانی کوئری ایجاد شده

سوال خیلی مهمی که پیش میاد این هست که اگر داده ها تغییر کرد، یا اضافه/کم شد، همه این مراحل رو باید تکرار کرد؟

پاسخ این سوال خیر هست!

یکی از ویژگی های خیلی مهم و کاربردی پاورکوئری اینه که قابلیت آپدیت و بروزرسانی داره. در ادامه نحوه بروزرسانی کوئری مورد نظر رو شرح میدهیم:

داده مورد نظر رو در ادامه ماتریس اولیه (سطر یا ستون) وارد میکنیم.
چون محدوده داده های ماتریسی خاصیت Table داره، در صورتی که داده در (ستون یا ردیف) به داده ها اضافه بشه، این ویژگی بسط داده میشه و داده های جدید هم در قالب Table اولیه در نظر گرفته خواهند شد. اگر این اتفاق بصورت خودکار نیافتاد، بعد از اضافه کردن داده ها، از تب Table Tools و گزینه Resize Table محدوده مورد نظر رو اصلاح کنید.
از تب Data روی گزینه Queries & Connections کلیک میکنیم.
از پنجره باز شده در سمت راست شیت اکسل، روی کوئری مورد نظر کلیک راست کرده و گزینه Refresh رو میزنیم. یا اینکه روی علامت کلیک میکنیم.

شکل 8- آپدیت کردن کوئری ایجاد شده

با طی کردن این مراحل، داده های جدید به دیتابیس جدولی (Tabular) در شیت مورد نظر اضافه میشوند و کافیه که گزارش ایجاد شده توسط پیوت تیبل رو Refresh کنیم. همه گزار شها بروز رسانی میشه و براحتی تغییرات جدید روی داده ها و در نتیجه گزارش اعمال میشوند.

نکته:
اگر فقط مقدار داده ها تغییر پیدا کنه، کافیه که فقط گام 3 و 4 انجام بشه تا جدول داده های Tabular آپدیت شوند.

حتما بخوانید: ترکیب جداول در اکسل با استفاده از Power Query

ابزار پاورکوئری ابزار بسیار قوی برای کار کردن با داده ها و آماده سازی آنهاست. حتما مقالات مرتبط با این ابزار فوق العاده کاربردی رو مطالعه کنید.