دسته توابع ِ .....Table.From در Power Query

هدف آشنائی با دسته توابع ِ .....Table.From است 

Table.FromColumns
Table.FromRows
Table.FromRecords
Table.FromList

مقدمه

Table.FromColumns

فرض کنید در یک BlankQuery لیست زیر را وارد کرده ایم . Source ، لیستی ست شامل 3 زیر لیست . اگر مایلید تک تک آیتم های لیست را کلیک کنید و از محتوای آنها مطمئن شوید 
 

حالا به میمنت ، Step ی Add کنید و کد زیر را در آن بنویسید شکل را هم از نظر بگذرانید .

= Table.FromColumns( Source )

سه زیر لیست داشتیم حالا سه ستون داریم . هر زیر لیست را در یک ستون نشاند
 

Table.FromRows

Step ی دیگر Add  کنید اینبار کد زیر را در آن درج کنید شکل را بار دیگر با دقت بیشتری نگاه کنید و با قبلی مقایسه کنید 

= Table.FromRows( Source )

سه زیر لیست داشتیم حالا سه ردیف داریم . هر زیر لیست را در یک ردیف نشاند

هر یک از دو کدی که نوشتیم آرگومان دومی نیز دارند . می توانید به تناسب لیستی شامل سه یا دو آیتم در آرگومان دوم شان اضافه کنید تا ستون ها را از نام بی نصیب نگذاشته باشید 

= Table.FromColumns( Source , {"Col1" , "Col2" , "Col3" } )

= Table.FromRows( Source , { "Col1" , "Col2"  }  )

Table.FromRecords

و برای بار سوم بیائید هر یک از زیر لیستها را به یک ردیف (Record ) تبدیل کنیم و از آنها Table  بسازیم اینکار مستلزم استفاه از List.Transform  برای ایجاد تغییر در زیر لیست ها و تبدیل آنها به Record  است . کد زیر را در یک Step  جدید که  Add  می کنید بنویسد 

List.Transform(Source ,
       each Record.FromList( _ ,  {"Col1" , "Col2" } )
)

1.    List.Transform:
•    این تابع به شما این امکان را می‌دهد که بر روی هر عنصر در لیست Source یک عمل خاص انجام دهید. به عبارت دیگر، این تابع به شما اجازه می‌دهد تا هر عنصر را تغییر دهید یا پردازش کنید.
•    در اینجا، ما از ترکیب  each و _ استفاده کرده‌ایم تا مشخص کنیم که چه عملی باید بر روی هر عنصر انجام شود.
2.    Record.FromList ( _ , {"Col1", "Col2"} ):
•    این تابع برای تبدیل هر لیست از مقادیر به یک رکورد استفاده می‌شود.
•    _  اشاره می کنه به هر عنصر در لیست Source  . بنابراین ، برای هر عنصر(زیر لیست) ، ما یک رکورد جدید ایجاد می‌کنیم.
•    آرگومان دوم { "Col1", "Col2" }  نام‌ ستون‌هارا مشخص می‌کند. در اینجا ما دو ستون به نام‌های "Col1" و "Col2" داریم.
بدین ترتیب از هر زیر لیست ِ  Sourceبا تابع Record.FromList یک رکورد  ساخته‏ ایم (ترکیب each  با _  را در مقاله each ببینید)  
در تصویر ، نتیجهء اجرای کد را از نظر بگذرانید

همینطور که می بینید خروجی تابع قبلی لیستی از رکوردها ست . و سر ِ آخر تابعی را به کار می گیریم  که در آرگومان اولش لیستی از رکوردها را می پذیرد و با کمال افتخار آنها را به Table تبدیل می کند 

Table.FromList

و اما تابع Table.FromList در پاور کوئری به شما این امکان را می‌دهد که یک لیست را به یک جدول تبدیل کنید. در ادامه چند مثال از این تابع را بررسی می‌کنیم تا کاربردهای مختلف آن را بهتر درک کنیم . فرض کنید شما یک لیست از نام‌  تنقلات شب یلدا دارید:

حالا با تابع Table.FromList لیست را به table تبدیل می کنیم آرگومان اول (تنها آرگومان در اینجا) همونجور که انتظار میره یه لیسته .

حالا لیستی از رشته ها داریم که شامل نام و قیمت است (نخندین ، قیمتها به تومان است !!!)

و یک کد ساده ایندو را در دو ستون می نشاند ولی بدون نام گذاری ستون 

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

حالا بیائید بی درد سر null  را در آرگومان دوم تابع بنشانیم (: دِ آخه مرد حسابی null   هم شد تابع ، : پاورکوئریست دیگر) وقتی از null  به عنوان آرگومان دوم استفاده کنید، Power Query  به‌طور خودکار این رشته‌ها را شناسایی کرده و آن‌ها را به ستون‌های مجزا تقسیم می‌کند.یعنی در اصل آرگومان دوم یک تابع است که وظیفه اش تجزیه ستونهاست 

اکنون null  را با تابع جدا کننده جایگزین کنیم   . 

Splitter.SplitTextByDelimiter(",")  

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

نکته : به type ستونها در شکل بالا و پائین دقت کنید در پائینی نوع داده خودبخود به text تغییر یافته حتی ستونهای عددی . حتما فرق های دیگری هم دارند که اگر در این مقاله و مثالهای پیش رو برخوردیم توضیح می‏دهم . نکنه فکر کردین من می دونم چه فرقهائی دارن !!! 

از همون Splitter ِ هست - منتها با یه ویژگیه دیگه - استفاده می کنیم و یه نتیجه جدید می گیریم . می گم شاید یه روزی روزگاری بکار اومد نادیده نگذرید(بی خود که نساختنش ) :   

Splitter.SplitByNothing() 

این خودش با زبون بی زبانی میگه آقا من اهل جدا سازی نیستم SplitByNothing() 
1-    در شکل زیر می بیند که خیلی زیر پوستی آرگومان اول را با List3 که در مراحل قبل تعریف کرده بودم جایگزین کردم تا جا کمتر بگیرد و کد کوتاهتر شود آسمان هم به زمین نیامد 
2-    تابع را در آرگومان دوم طوری بکار گرفتم تا همه در یک ستون بیایند بدون جدا کننده 
3-    نام تک ستون را ببینید . می شد بین نام ستونها هر علامت دیگری بکار برد مثلا / فقط به این دلیل که قبلا و هنگام تعریف لیست جداکننده را "," گذاشته بودیم من هم همان را برگزیدم

از این به بعد در فایل نمونه یک کوئری Blank جدید می سازیم تعداد مراحل مان دارد زیاد می شود

Table.FromList وقتی آرگومان اول لیستی از  Record هاست

یه کوچولو در مورد این تابع توضیح بدم بعد می ریم سر اصل مطلب . خو لازمه 

Record.FieldValues([Name ="Peste", Price = 30])

تووی یه کوئری خالی بنویسین ش . خروجی ش میشه لیستی از مقادیر همان ردیف

{"Peste",  30}

در کل این تابع زمانی مفید است که شما بخواهید تمام مقادیر موجود در یک رکورد را استخراج کنید، بدون اینکه نیازی به نام فیلدها داشته باشید. این کار می‌تواند در پردازش داده‌ها و یا هنگام نیاز به تجزیه و تحلیل داده‌ها بسیار کاربردی باشد.

حالا بریم ببینیم اگر لیستی از رکوردها داشتیم تابع Table.FromList  توی آرگومان هاش از ما چی می خواد ؟ آیا null  اینجا هم کار ساز است  آیا جدا کننده همان است که قبلا دیدیم ؟ 

کد زیر را در یک کوئری خالی بنویسید : 

= Table.FromList(
   { 
       [Name ="Peste", Price = 30 , QTY=3],
       [Name ="Gerdou", Price = 25 , QTY=6],
       [Name ="Fandogh", Price = 35 , QTY=3]
   } ,
   Record.FieldValues ,
   {"Name" , "Price" , "QTY"}
)

آرگومان اولش هنوز یک لیست است ولی لیستی از رکوردها همراه مقادیرشان . آرگومان آخر نیز نام ستونهاست.  mull , null هم ورنمی‏داره امتحان کردم . تابع Record.FieldValues به کمک مان می آید و لیستی از مقادیر را تدارک می بیند. لابد می گین اینکه اصلا آرگومان نداره .بی خیال پاورکوئریه برگ چغندر که نیست . تشخیص میده آرگوماناش رو از لیست رکوردها ورداره  . حالا همین کد رو ببر توی  Blank Query 

کد کمی بالا بلند بود رفتم از پنجره Advanced Editor عکس گرفتم برای همین شما Step رو نمی بینید

 اما نتیجه رو توی تصویر ببینید راستی به پاور کوئری گفتیم مثلا

 Price=30  

(یعنی عددِه )  یا 

Name ="Peste" 

(یعنی رشته متنی ست) اما انگار به اینجور گفتنا گوش ِش بدهکار نبوده یعنی type ستونها اصلا عوض نشده 

من نظرم اینه که type ستونها رو هم در همین کد در آرگومان سوم بترتیب زیر عوض کنیم . شیک تر و حرفه‏ای تره یعنی علاوه بر لیستی از نام ستونها ، جنس شان را نیز با چنین نحوی (Syntax)به نرم افزار معرفی کنیم  

و نتیجه 

به نظرم اصلا قشنگ نیست دانشی کسب کنیم بدون اینکه به کارِش بگیریم . میخوام با همین دانش تازه بدست آمده یه Special Unpivot بزنیم با لب ُ دهن آدم بازی کنه .  : یعنی میخوای بگی تا اینجا هم َش مقدمه بود . گیرم که بود مگه ضرر کردین

 Special Unpivot

دوستان یه جدول داریم البته توی اکسل ، ندیده نگذرید یعنی با دقت نگاه کنید :

همینطور که می بینید شامل سه بخش اصلی میشه ما قصد داریم این سه بخش رو زیر هم بچینیم . لابد    می‏پرسید با نام اون سه کشور بالا می خوای چیکار کنی ؟ اینم چالشی ست !!! یعنی دوست دارین به شکل زیر از کار دربیاد ؟ باشه سعی مون رو می کنیم 

قیافه ش داد میزنه خروجیه پاورکوئری ِ . الهی به امید تو ببینیم چیکار میشه کرد 
در قدم اول جدولی که توی اکسل داریم رو با استفاده از Tab ی که در شکل می بینید به ادیتور پاور کوئری می بریم . و توجه کنید که تیک My table has headers رو هم برداشتیم که وقتی به محیط ادیتور میره ، نام کشورها توی ستون نشینه 

محکم بشینید که داریم وارد پاور کوئری میشیم . به بزرگی خودتون این جدول ریز نقش رو ببخشین فقط خواستم توو یه نما هم اسم Step  رو ببینید هم جدول رو .

به نظر شما برای پَروندن سطر اول از تابع Table.Skip  استفاده نکنیم . اصلا دست و دلتون نلرزه بعداً همین سطر اول رو هر جوری شده بازیابی ش می کنیم .پس با فشردن صمیمانه کلید  fXیه Step  ی اضافه می کنیم و این کد رو توش می نویسیم نتیجه را در شکل ببنید خبری از سطر اول نیست من یواشکی اسم Step  رو  هم به Tables تغییر دادم

حالا سعی می کنیم با تابع  Table.ToColumnsهر ستون رو به لیست تبدیل کنیم یه کمی حرفه ای اینکار رو می‏کنیم و کد بالا را با کمی دستکاری تغییر می دهیم ما هنور در Step  ِ دومیم (همان Tables) خوب دقت کنید متوجه می شین که ما 9 تا ستون داشتیم حالا 9 تا لیست داریم اگه کنجکاویتون گل کرد روی هر یک از لیستها کلیک کنید دیگه تاکید نمی کنم حالا حالا ها توی Step  ِ دوم ماندگاریم .

بیاین یه کاری بکنیم این لیستی که خودش شامل 9 تا زیر لیست میشه رو Split کنیم واقعا تفکیکش کنیم به سه تا لیست لابد می پرسین چرا ؟ خوب هر کدوم از این لیستها یک ستون بود و هر سه ستون یه بخش پس عملا برای اینکه بخش ها رو جدا کنیم باید این لیستها رو به سه گروه تقسیم کنیم ببینید هم کدی که اضافه می کنیم و هم یکی از بخشها را . من ُ وادار می کنید یادآوری کنم ما هنوز توی Step  ِ دومیم . توی شکل هم همین Step  سبز رنگ هستش . 

قبول دارید هر کدوم از این لیستها شامل سه تا زیر لیست هستش می خوایم یه حرکتی بزنیم و روی هر یک از لیستها اصلی یه تغییر ایجاد کنیم . الآن دیگه می دونید برای ایجاد تغییر روی آیتم های یک لیست از  list.Transfom استفاده می کنیم . لابد می پرسین چجوری ؟! تازه شم می خوایم به کجا برسیم . دندون رو جیگر بذار جوون . تغییری رو که صحبتش بود توی شکل زیر ببین . به به به به . یعنی هر کدوم از لیستهای اصلی رو تبدیل کن به Table (طبق اطلاعات موجود در مقدمه هر زیر لیست در یک ستون می نشیند)

یعنی ترکیب each   و _   کار خودش رو کرد و سه زیر لیست رو برامون تبدیل به یک table  کرد وَالله که این پاورکوئری ایول داره . من از Step  ِ 2 نمی گذرم تا یه PromotHeader شیک نزنم . نگاه کنید این کد رو کجا و چجوری بکار می گیریم . 

تا اینجا لیستی داریم که اعضاءش Table هستن . ولی ولی می دونم همه دلواپس ستون چهارم ین . یعنی باید به یه ترفندی اسم کشورها رو هم اضافه کنیم . یه نفس عمیق بکشید و یهStep  ِ جدید Add کنید توی Step  جدید بنویسین Source همین 

برای اینکه سطر اول را بصورت یک رکورد ببینید جلوی این Source این علامت را بگذارید {0} . می دانید برای اینکه به ستون اشاره کنیم نام ستون را در داخل کروشه می نویسیم مثل [Column1] و وقتی می خواهیم سطری (Record ی )را نمایش دهیم از آکلاد استفاده می کنیم و خوب بکنیم 

خبر داشتین یه تابعی داریم که رکورد رو تبدیل به لیست می کنه ؟  : که چی گیرم که کرد . خوب بعدش می تونیم null  ها رو بپرونیم ، ذوق زده نشدین . ما 9 تا ستون نداشتیم حالا این لیستمون شامل 9 عضو میشه که 6 تاش null ِ  . 

تابعی هم داریم که null  ها رو از یک لیست remove می کنه میگی نه ، نیگا کن . با طمأنینه اون تابع رو به ابتدای این کد اضافه کنیم .

الآن باید لیستِ شکل قبل رو به Table تبدیل کنیم . برای اینکار هم پاورکوئری تابعی داره 

Table.FromList

با این تابع در مقدمه بصورت مبسوط آشنا شدیم . من میخوام اینجا شکل ساده شدهء این تابع رو استفاده کنم که شامل فقط سه آرگومان هستش . آرگومان اول طبیعتا یک لیست است چون قرار است که لیست به Table تبدیل شود وقتی null  را به عنوان آرگومان دوم وارد می‌کنید، Power Query  هر عنصر در لیست را به صورت یک رکورد مستقل قرار می‌‏دهد. و آخرین آرگومانش نام ستون یا ستونهائیست که قرار است Table جدید داشته باشد تابع  را بشکل زیر به Step  ِ قبلی اضافه می کنیم . کاری که ازم بر می اومد این بود که لیست قبلی رو که الآن توی آرگومان اول تابع نشسته بذارم توی یه کادر . آرگومان دومش هم که null ِ و سومیش هم اسم ستون .

یک ستون Index هم فوراً به این Table اضافه کنیم بعداً می فهمین چرا ؟ 

خاطرتون باشه در سطرهای قبلی برای اینکه روی آیتم های یک لیست تغییر ایجاد کنیم از تابع زیر استفاده کردیم حالا می خوایم روی آیتم های ستونی از یک Table تغییری اعمال کنیم . اولین آرگومان می شود نام Table و سپس داخل آکولاد نام ستونی که تغییر روی آن اعمال می شود و عامل تغییر که یک تابع است 

Table.TransformColumns

در مثال حاضر این تغییر روی ستون Index اعمال می شود !!! ببینیم چگونه . الآن یدونه Step اضافه کنید و تابع رو بشکل زیر داخلش بنویسید 

: یعنی الآن مثلا چیزی تغییر کرد . اتفاقاً نه . اگر مقاله each را خوانده باشید این کد روی ستون Index هنوز هیچ تغییری ایجاد نکرده . کد میگه هر چی توی Index هست همون رو جایگزین قبلی کن . یعنی هیچی به هیچی. اما توی همین مقاله یه جا گفتم اگر می خواهید به سطرهای یک Table اشاره کنید اول نام Table را بیاورید و شماره سطری را که می خواهید برگردد داخل آکولاد بنویسید . ما اون همه سختی کشیدیم تابع تو در تو نوشتیم که Step  دو رو بسازیم (Tables) قرار نیست بی مصرف بذاریمش که . ببینید کد رو چگونه تغییر می دهیم 

بچه ها این یه Loop میشه حسابش کرد هر بار یک آیتم(سطر ) را از ستون Index برمی دارد و داخل آکولاد داخلی می گذارد یعنی {_}Tablees  . مثلا در اولین دور  Index=0 است پس سطر اول را از  {0}Tables  برمی‏گرداند  و در دور دوم  Index=1 است پس سطر دوم را از  {1}Tables بازمی گرداند . می توانید به Step ی که نامش را در این کد آورده ایم یعنی Tables باز گردید و روی تک تک آیتمهای آن لیست کلیک کنید . تا توانائی های پاور کوئری بیش از پیش برایتان آشکار شود . تنها مرحله باقی مانده Expand ِ ستون Index  است .

اینهم جدولی که تعهد کرده بودیم . خیرش ُ ببینی . تا Unpivot ی دیگر شما را به خدا می سپارم