با استفاده از مجموعه get & transform (افزونه پاور کوئری – power query) شما میتوانید به کوئری خود یک از انواع ستون index یا ستون custom در پاور کوئری اضافه کنید. ستون های index اعدادی یکتایی را به هر سطر در پاور کوئری اختصاصی می دهند و ستون های custom، یک فرمول یا مقدار را در پاور کوئری ایجاد می کنند. مثلا اگر یک ستون از داده های ما تعداد کالای فروش رفته و ستون دیگر قیمت واحد کالا باشد، با ایجاد یک ستون custom می توان قیمت فروش که فرمول تعداد*قیمت واحد به دست می آید را محاسبه کرد.

در صورتی که با ابزار قدرتمند پاور کوئری در اکسل آشنا نیستید، میتوانید به صفحه زیر مراجعه کنید و به صورت مرحله به مرحله و از ابتدا تا انتها با پاور کوئری آشنا شوید: دوره رایگان آموزش پاور کوئری در اکسل

اضافه کردن یک ستون index در پاور کوئری

ستون index شمارنده یک سطر است که عددی یکتا در این ستون خواهد بود. توسط این عدد می توانید سطرهای خود را متمایز کنید. ستون index در جدول داده ها شبیه شماره ملی، شماره پرسنلی، شماره دانشجویی و … برای هر فرد است. شما با داشتن شماره ملی می توانید هویت یک شخص را مشخص کنید. با داشتن شماره پرسنلی می توانید اطلاعات یک فرد را استخراج کنید. با داشتن عدد ستون index در داده های خود می توانید هویت آن سطر را مشخص کنید.

برای ایجاد روابط بین جداول (که در مباحث پاور پیوت یا نرم افزار اکسس و پایگاه های داده بحث می شود) شما نیاز دارید که سطرهای جدول مورد نظر شما حتما یک شمارنده یکتا داشته باشند تا بتوانید با اطلاعات جدول های دیگر ارتباط برقرار کنید.

برای مثال:

فرض کنید در جدول اول اسامی فروشندگان شرکت را اختیار دارید. در جدول دوم فروش های شرکت را ثبت می کنید و در ستونی از این جدول (ستون فروشنده)  نام فروشندگان نیز ثبت می شود.

از نظر مباحث پایگاه داده این کار اشتباه است و به عنوان افزونگی داده ها مطرح می شود، زیرا شما به ازای هر فروش اسم کامل شخص را ثبت می کنید، در صورتی که می توانستید از کد فروشنده استفاده کنید.

شاید برای شما هم سوال پیش آید که چرا این کار اشتباه است!

فرض کنید کدهای فروشندگان هر کدام یک عدد سه رقمی باشد. (عدد 100- 999 که می توانید 899 فروشنده داشته باشید.) و به صورت میانگین تعداد کاراکترهای اسم و فایل هر شخص 10 کاراکتر باشد. اگر شما به جای نام فروشنده، کد فروشنده را ثبت کنید، به ازای هر فروش 7 کاراکتر کمتر ذخیره می کنید. (10-3=7) حال ممکن است که فکر کنید این موضوع چه اهمیتی دارد! اگر شما روزانه 1000 فروش داشته باشید 7*1000=7000 کاراکتر به صورت روزانه فایل شما سبک تر خواهد بود و اگر برای حدود یک سال حساب کنیم، 7000*300=2100000 کاراکتر کمتر در این فایل ذخیره کردید. پس در حجم انبوه داده ها و جدول های زیاد این موضوع بسیار با اهمیت خواهد بود.

پس ابتدا یک ستون index به جدول فروشندگان خود اضافه می کنیم، سپس از کد ستون index برای هر شخص جهت مشخص کردن فرد در سایر جداول استفاده می کنیم. پس جدول داده های ما بدین صورت خواهد شد:

استفاده از ریبون query editor

1. در ریبون query editor و در تب Add column، گزینه add Index Column را انتخاب کنید.

یا از آیکن جدول در قسمت preview grid استفاده کنید

1. بر روی آیکن جدول کلیک کنید.
2. از منو context گزینه Insert Index Column را انتخاب کنید. یک ستون index به جدول کوئری اضافه میشود. نام ستون را به صورتی که مایل هستید تغییر دهید.

اضافه کردن یک ستون custom در پاور کوئری

یک ستون custom در پاور کوئری ستونی است که مقدار سلول های درون آن بر اساس فرمولی قرار میگیرد که شما مشخص می کنید.

استفاده از query editor

1. در ریبون query editor گزینه Insert Custom Column را برای ایجاد یک ستون custom در پاور کوئری انتخاب کنید.

یا آیکن جدول در preview gird استفاده کنید.

بر روی آیکن جدول کلیک کنید و گزینه  Insert Custom Column را انتخاب کنید.

پس از ظاهر شدن دیالوگ باکس Insert custom column مراحل زیر را برای ایجاد ستون custom در پاور کوئری طی کنید:

1. در دیالوگ باکس در بخش new column name یک نام ستون وارد کنید و یک فرمول در باکس Custom Column Formula وارد کنید:

بر اضافه کردن یک ستون به فرمول، روی یک ستون در لیست Available columns دابل کلیک کنید.

یا بر روی یک ستون در لیست Available column کلیک کنید، و insert را انتخاب کنید.

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

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

مثال های فرمولی

Formula	توضیحات
“abc”	یک ستون با عبارت abc در تمام ردیف ها ایجاد میشود.
1+1	یک ستون با نتیجه 1+1 (2) در تمام ردیف ها ایجاد خواهید شد.
[UnitPrice] * [Quantity]	یک ستون ایجاد خواهد شد و در تمام ستون ها حاصل ضرب دو ستون داده قرار داده میشود.
[UnitPrice] * (1 – [Discount]) *[Quantity]	با توجه به ستون discount(تخفیف) میزان قیمت کل را محاسبه میکند.
[Hello” & [Name”	Hello را با محتوای ستون name ترکیب میکند و در یک ردیف جدید قرار میدهد.

نکته:

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

[/vc_column_text][/vc_column][/vc_row]