مفاهیم و کار با لیست‌ها در پاورکوئری اکسل - تبدیل یک ستون به جدول

ای نام تو بهترین سر آغاز

ما اغلب در اکسل با لیست ها سر و کار داریم . لیستی از اعداد ، حروف ، تاریخ ها ، کشورها ، محصولات و.... (مانند اسامی محصولات که در ستون A اکسل تایپ شده‌اند). در پاور کوئری می توانیم لیست اقلام استانداردی مانند اعداد ، حروف و تاریخ را به سرعت و به آسانی تولید کنیم . 

توجه داشته باشید مقادیری ساده‌ای مثل 1 و "دریا" در دنیای کامپیوتر مقادیری اولیه هستند و در مقابل این مقادیر ساده، ما می‌توانیم چیزهایی مانند List  ها ، Record ها داشته باشیم که در دنیای کامپیوترها اصطلاحا به آنها داده ساختارها (Data Strtuctured) می‌گویند و در زبان M پاورکوئری به نام Structured Valueها شناخته می‌شوند.

ساخت یک لیست در پاورکوئری

1- لیستها می توانند بصورت دستی توسط کابر ایجاد شوند. (مانند مثال‌های زیر که از آکولاد استفاده شده است).

2-گاهی خروجی برخی توابع هستند.

3-با نوشتن نام یک ستون در داخل [ ] جلوی نام یک جدولِ پاورکوئری هم یک List ساخته می شود:

TableName[ColumnName]

ساده ترین شکل برای ساختن لیست استفاده از آکولاد است. ایجاد یک لیست بصورت دستی ،  ایجاد لیستی از اعداد متوالی  ، و همینطور لیستی از حروف متوالی در دسته بندی اول قرار می گیرد . بگذارید با یک کار ساده که ساختن لیستی از اعداد است شروع کنیم در مرحله اول نیاز است که یک blank query (کوئری خام) داشته باشیم. نوار ابزاری که می بینید مربوط است به نسخه 2016 اکسل:

به همین سادگی از پنجره شماره 3 در شکل به حیاط پاور کوئری وارد می شوید

یک لیست خالی ساده ترین نمونه است که عضوی (item) هم ندارد ،  در نوار فرمول (مثل formula bar اکسل) عبارت زیر را تایپ کنید و ENTER را بفشارید:

={ }

این یک لیست خالی ست . حالا کافی است داخل آن آکولادها اعداد 1 و 2 را تایپ و آنها را با کاما از هم جدا کنید

={ 1 , 2 }

منتظر چی هستید ؟. مگر فشردن کلید ENTER چشم بر هم زدنی بیشتر طول می کشد . شما همین اندازه با ساختن لیست و رویت آن ، فاصله دارید .

حالا داخل همان آکولادها عبارت زیر را تایپ کنید این یعنی لازم نیست من از ابتدا تا انتهای یک رشته درون آکولاد بنویسم . خدا را چه دیدی ؟، آمدیم و گفتن آقا اعداد 1 تا 1000000 را لیست کنید. لیستی از اعداد متوالی که اشاره شد یعنی همین

={1..10}

خبر ندارید!؟ در Power Query مثل آب خوردن لیستی از حروف را نیز می توانید بسازید:

به تصویر دقت کردید ؟ A  و Z  را داخل " " نوشتیم . اینهم چیز تازه ای نیست آنها Text  هستند در اکسل همین رفتار را با رشته های متنی می کنیم . بعلاوه از آکولاد برای محصور کردن اعضاء لیست استفاده کردیم.

حالا می خواهیم لیستی بسازیم. اگر می بینید جنس اعضاء ش را متفاوت انتخاب می کنیم ،  چون به پاور کوئری بر نخواهد خورد و پیغام خطا نمی دهد

={1,2,"A","D",{"a".."f"},"["}

 اعضاء اول و دوم عدد هستند ( Index در پاور کوئری از صفر شروع می شود ، پس صفرم و اول درست است)  و در شکل سمت چپ سلول نشسته اند و بقیه در سمت راست . کلید ENTER را بفشارید، پنجمین آیتم این لیست خودش یک لیست است .

رویش کلیک کنید . چه جالب ! چراغ غافل گیری روشن شد ، نه ؟

نکته ها

یکی اینکه یک آیتم ِ لیست ، خودش می تواند لیست باشد دوم شکل نشان  دادنش در power query (همان که در ردیف پنج ظاهر شده است ) و سوم حساس بودن power query به حروف بزرگ و کوچک ( با مثال قبلی مقایسه کنید.)  و بلاخره روی هر یک از اعضاء لیست در این قسمت کلیک کنید آن عضو زیر صفحه به نمایش در می آید .

لیستی از اعداد زوج

=List.Numbers(2, 20, 2)

این تابع لیستی را برمی گرداند که اعضایش از 2 شروع می شوند 20 عضو دارد و هر عضو از عضو قبلی 2 تا فاصله دارد.

لیستی از اعداد اعشاری

= List.Numbers( 1, 11, 0.1)

لیستی از اعداد که با 1 شروع می شوند ، 11 عضو دارد و  هر عضو 0.1 بیش از قبلی است.

معکوس کردن اعضاء یک لیست

تنها آرگومان این تابع یک لیست است که قبلا با آن آشنا شدیم . تابع ، لیست را می گیرد و از آنسو معکوس آن را تحویل می دهد . وقتی گفته می شود لیستی را می گیرد ممکن است اعضاء این لیست ، رشتهء متنی یا نام ستونهای یک جدول یا اعضاء یک رکورد(ردیف) در یک جدول یا .... باشند

=List.Reverse( { 1..100 } )

تکرار لیستی از اعداد

= List.Repeat( {1..5}, 3 )

{1..5} لیستی از اعداد 1 تا 5 را می سازد و آرگومان دوم ، این لیست را 3 بار تکرار می کند.

عملگرهای List ها در پاورکوئری اکسل

سه عملگر وجود دارد که می توانند در ارتباط با لیست ها استفاده شوند: عملگرهای "=" و "<>" امکان مقایسه لیست ها را فراهم می کنند ، در حالی که "&" لیست ها را ترکیب می کند. در اینجا چند نمونه از نحوه استفاده از آن آورده شده است

{ 1 , 2 } = { 1 , 2 } → true

{ 1 , 2 } <> { 2 , 1 } → true

{ 1 , 2 } & { 3 , 4 , 5 } → { 1 , 2 , 3 , 4 , 5 }

List.Combine( { { 1 , 2 } , { 3, 4, 5 } } ) → { 1 , 2 , 3 , 4 , 5 }

دو مثال اخیر را مقایسه کنید . خودمانیم خیلی زیر پوستی با تابع زیر آشنا شدید :

List.Combine

دسترسی به اعضای یک لیست

یکی دیگر از جنبه های جالب توجه در مورد لیست ها نحوه دسترسی به اعضاء یک لیست است . پیش می آید، شما لیستی دارید و لازم است به اعضاء آن دست پیدا کنید. برای رسیدن به ردیف n ام در یک ستون اول آن را به یک لیست تبدیل می کنیم سپس کافیست  n  را در درون این علامت قرار دهیم { }

TableName[ColumnName]{n}

لیست زیر را در نظر بگیرید  یادآوری می کنم شاخص مبتنی بر صفر است به مثالهای زیر توجه کنید :

MyList = { 1 , 2 , 3 , -1 , 8 , 9}

MyList { 0 } = 1

MyList { 1 } = 2

MyList { 2 } = 3 

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

MyList { 6 }

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

MyList { 6 } ? → null

برای آنکه تعداد آیتم های یک لیست را پیدا کنیم بسادگی می توان از تابع زیر استفاده کرد

List.Count( MyList ) → 6

واضح است که در مثال ما خروجی تابع برابر 6 است .

می خواهیم از میان طیف گسترده ای از توابع List که در کتابخانه M موجودند ، فقط با چند تا آشنا شویم . این توابع شامل آنهائی است که دسترسی به اعضاء را در جای جای لیست امکان پذیر می سازند . مثلا اولین آیتم ، آخرین آیتم ، چند تای اول ، چند تای آخر ،یا از آیتم چندم چند تا و یا حتی با اعمال یک شرط که منجر به انتخاب زیر لیستی از لیست اصلی شود

List.First( { 1 , 2 , 3 , -1 , 8 , 9} ) → 1

می توان با استفاده از عبارت زیر نیز به همان نتیجه رسید و این یعنی عضو صفرم MyList

MyList { 0 } → 1

برای دستیابی به عضو آخر لیست از تابع زیر می توان سود جست

List.Last ( MyList ) → 9

نکته : عبارات زیر معادلند

List.Last( MyList ) = MyList { List.Count ( MyList ) – 1 }

سه عضو آخر لیست را چنین جدا کنید :

List.LastN( MyList , 3) →{-1, 8, 9}

List.Range( MyList , 2, 3) → { 3 , -1 , 8 }

آرگومان دوم تابع 2 است در لیست عضو دوم (اگر از صفر شروع کنیم) می شود 3 .  قرار است از 3 ، سه عضو برگرداند .

دو تابع بعدی را به عمد در کنار هم توضیح می دهم  

List.FirstN( MyList , n )

این تابع به دو صورت عمل می کند.

اگر دومین پارامتر تابع یک عدد باشد ، از ابتدای لیست تا این تعداد عضو بازگردانده می شوند

List.FirstN( MyList , 2 ) → { 1 , 2 }

اگر دومین پارامتر تابع یک شرط باشد

List.FirstN( MyList, each _ < 3) → { 1 , 2 }

در اینجا each _ < 3 تعداد اعضائی ست که تابع از ابتدای لیست باز می گرداند کمتر از 3 است یعنی 2 تا .

List.Select( MyList, each _ < 3) → { 1 , 2 , -1 }

ولی این تابع اعضائی از لیست را برمی گرداند که شرط موجود را برآورده کنند در اینجا  از 3 کوچکتر باشند یعنی موقعیت اعضاء در لیست مد نظر نیست .

حتی تابعی داریم که می تواند یک List را به چند زیر List تقسیم کند

List.Split ( { 1 ,2 , 3 , -1 , 8 , 9}  , 3)

پیشنهاد می کنم تابع بالا را در یک کوئری خالی بنویسید . با این عبارت به پاورکوئری می گوئیم لیست را به زیر لیستهائی با 3 عضو تقسیم کن . مسلماً 2 زیر لیست خواهیم داشت . ناگفته پیداست ، تعداد اعضاء آرگومان اول باید مضربی از آرگومان دوم باشد .

برخی مثل تابع زیر خروجی شان لیست است:

از میان بسیار توابعی که ورودی شان List  است می توان به تابع زیر نیز اشاره کرد

Table.FromRows(rows as list, optional columns as any) as table

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

مایلم عاقبت مقاله مانند عاقبت خوانندگانش ختم به خیر شود ! . ختم به خیر در اینجا یعنی برخی از آموخته های مان را برای حل یک پروژهء واقعی به کار بگیریم .

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

داده‌های لیست سمت چپ (آبی رنگ) را می‌خواهیم به پاورکوئری منتقل کنیم. برای اینکار مانند تصویر زیر عمل کنید:

اولا تابعی که خودِ پاور کوئری برای این انتقال ترتیب داده است بزرگ نمائی کردم . ثانیا شما و من هیچ دخلی در این انتقال و این تابع ، بجز همان سه کلیکی که انجام دادید نداریم:

در این مرحله فقط برای تبدیل ستون Column1 به یک List این عبارت را [Column1] به انتهای تابع پاور کوئری بیفزائیدو صد البته فشردن Enter به ملایمت فراموش نشود.  شکل زیر:

اگر بخواهیم توابع را بصورت تودرتو نشان دهیم گیج کننده است . پس با هم قسمتی را که در شکل نشان داده شده (fx) و در ویدئوهای آقای میدانی شرح آن رفته است کلیک کنیم . این کار یک مرحله جدید می سازد و شما را به جائی می برد که دیگر خبری از تابع کت و کلفت ی که پاور کوئری نوشته بود نیست در عوض فقط نام مرحله قبل که  Source باشد را نمایش می دهد ملاحظه می کنید که مرحله (STEP) را نیز خود پاور کوئری Cutom1  نام نهاد .

فراموش نکنید Source  حالا یک List است و می تواند در آرگومان اول تابع List.Split خودی نشان دهد . این زیر لیست ها هر یک 3 عضو خواهند داشت . بدین ترتیب آرگومان دوم تابع نیز لو رفت . تابع را بترتیبی که در شکل زیر می بینید اعمال کنید خواهید دید که حاصل آن خود لیستی شامل  5  زیر لیست است روی هر کدام که می خواهید کلیک کنید:

حالا زیر لیستهائی داریم که در شُرُف ِ سطر شدن هستند . دو تابع را بصورت تودرتو بکار می گیریم نتیجه را در تصویر ببینید .

= Table.FromRows (

          List.Split ( Source [Column1] , 3 ) ,

         { "Name" , "Director" , "Year of construction" }

 )

فقط شما دچار تحیُّر نشدید ، من هم