مفاهیم و کار با Table‌ها در Power Query

ای همه هستی ز تو پیدا شده

در این مقاله قصد داریم شما را با Table در Power Query آشنا کنیم . مباحث ارائه شده به شرح زیر است:

• Table  چیست و کاربردهایش کدام است
• روشهای ساخت Table
  • استفاده از عبارت #table()
    • تعیین نام ستونها در یک لیست
    • تعریف شماری از ستونها بی ذکر نام
  • استفاده از برخی توابع
• عملگرهای Table
• شیوه های دسترسی به عناصر Table
  • دسترسی به ردیف
  • دسترسی به ستون
  • دسترسی به سلول
  • دسترسی به ردیفهائی که واجد شرطی ( شروطی ) هستند                               

مفهوم Table‌ در پاورکوئری

مجموعه ای از مقادیر که در ستونها و سطرها سازماندهی شده است. قطعاً ستونها با نام مشخص می شوند . جداول می توانند خود نتیجه یک Query  (گزارش) باشند یا بعنوان واسطه برای ایجاد جداول و گزارشات دیگر استفاده شوند . یا در محیط Power BI و Power Pivot بارگذاری گردند 

روشهای ساخت

عبارت زیر یک جدول خالی می سازد . نه سطری نه ستونی ، هیچی !؟ شاید در مقالات بعدی لزوم چنین جدولی را شرح دهم

#table( { } , { } )

از همین نحو ( #table )برای معرفی یک جدول ساده استفاده می کنم . شما می توانید این کدها را در یک کوئری خالی ( Blank Query ) کپی کنید . اگر دقت کنید چه در عبارتی که جدول خالی تولید می کرد یا در عبارت زیر دو پارامتر موجود ، دو لیست هستند.  اولی نام ستونها را شامل می شود و دومی لیستی شامل زیر لیستهائی ست .هر زیر لیست ،  ردیف (Record  ) های جدول را می سازند . پس جدول ما شامل سه ردیف است . این جدول با شیوهء تعیین نام ستون ساخته شده است 

#table(
{"Region", "Name", "Sale"} ,
{
  {"South", "Farzad", 80},
   {"North", "Farzaneh", 45},
{"North", "Farzin", 52}
}
)

در شکل زیر همین Table را در محیط پاور کوئری ببینید :

حالا این جدول را بی ذکر نام ستونها و با همین عبارت #table می سازیم عدد 3 نشاندهندهء تعداد ستونهای جدول است

#table( 3 ,
{
   {"South", "Farzad", 80},
   {"North", "Farzaneh", 45},
{"North", "Farzin", 52}
}
)

این کد را در مسیر Home گروه Query و تب ِ Advanced Editor نیز می توانید مشاهده کنید . شکل زیر هم جدول ساخته شده و هم پنجره  Advanced editor را در یک نما نشان می دهد .با پنجره اخیرالذکر در ویدئوهای آقای میدانی آشنا شده اید . به نام ستونها توجه کنید

ساخت Table  با استفاده از توابع

بیائید با بکارگیری تابع Table.FromRows جدول بسازیم . چنانکه می بینید پارامتر اول تابع همان لیستی است ، شامل چند زیر لیست که هر زیر لیست نیز  ردیف (Record  ) های جدول را می سازند . این همان تابعی است که در مقالهء قبلی در خصوص لیست ، برای تبدیل یک ستون ( یک لیست ) به یک جدول به دادمان رسید .

Table.FromRows (
{
  {"South", "Farzad", 80},
   {"North", "Farzaneh", 45},
{"North", "Farzin", 52}
} ,
{"Region", "Name", "Sale"}
)

اما اگر تصمیم به ساخت جدول با تابع Table.FromColumns دارید لازم است زیر لیستهای پارامتر اول به ترتیب شامل اعضاء هر ستون باشند . ولی نام ستونها در قالب یک لیست مثل تابع قبلی در پارامتر دوم گرد هم می آیند.

Table.FromColumns (
{
  {"South", " North ", " North "},
  {" Farzad ", "Farzaneh"," Farzin "},
{80 , 45 , 52}} ,
{"Region", "Name", "Sale"}
)

از جمله توابعی که بکارگیری اش می تواند به ساخت جدول منجر شود Table.FromRecords است . ( ) پرانتز خالی یعنی عمری باشد توضیحش در مقاله بعدی که اختصاص به رکورد دارد از نظر خوانندگان خواهد گذشت .

عملگرهای Table

سه عملگر  ِ "="  و  "<>"  و  "& "  برای مقایسه یا ترکیب جداول بکار می آیند

#table( { "A" , "B" } , { {1,2} } ) = #table( { "B","A" } , { {2,1} } ) → true

جداول مساوی اند اگر هر چهار شرط زیر هم زمان برقرار باشند

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

#table( {"A" , "B" } , { { 1 , 2 } } ) = #table( { "B" , "A" } , { { 2 , 1 } , { 3 , 4 } } ) → false

نتیجه بواسطه اختلاف در تعداد سطرها False  است

#table( {"A" , "B" } , { { 1 , 2 } } ) <> #table( { "B" , "A" } , { { 2 , 1 } , { 3 , 4 } } ) → true

این درست که جابجائی ستونها دلیل عدم تساوی نیست ولی اختلاف در تعداد سطرها بطور قطع دلیل نتیجهء true  برای این نامساوی ست . ذیلا با علامت "&" دو جدول را ترکیب کرده ایم

#table( {"A" , "B" } , { { 1 , 2 } } ) &  #table( { "B" , "C" } , { { 3 , 4 } } ) →  #table( { "A" , "B" , "C" } , { { 1 , 2 , null } , { null , 3 , 4 } } )

نکته

لطفا با دقت بیشتری به کد بالا نگاه کنید . اولین جدول واجد دو ستون "A" و "B" است و نام ستونهای جدول دوم "B"  و "C" . و همین اختلاف ، وجود ِ null  را در سلولهای جدول نهائی توضیح می دهد .

به شیوهء زیر و با استفاده از تابع Table.Combine نیز می توان دو جدول را با هم ترکیب کرد

Table.Combine( { #table( { "A" , "B" } , { { 1 , 2 } } ) , #table( { "B" , "C" } , { { 3 , 4 } } ) } )

شیوه های دسترسی به عناصر یک جدول

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

Table.FromRows ( { {"South", "Farzad", 80}, {"North", "Farzaneh", 45},{"North", "Farzin", 52} } , {"Region", "Name", "Sale"} )

این کد بلند بالا اینجا به کار ما نمی آید و ممکن است با دیدنش گیج شویم . پس روی f_x که در شکل نشان داده شده است برای درج یک Step  کلیک کنید . مانند شکل زیر

حالا در قسمت فرمول بار فقط و بطور خلاصه نام مرحله ( Table یا Step) قبل را می بینید (Source). کافیست که برای دسترسی به ستون Region این نام را بشکل [Region] جلوی Source  بنشانید . در این صورت فقط ستون  Region  بصورت یک List قابل مشاهده است . مایل بودید عبارت [Region] را حذف و نام ستون Sale را در داخل کروشه به انتهای Source  بچسبانید . حالا تصویر زیر برای همه ما آشناست

 زنگ تفریح

دوست دارید یواشکی و خارج از بحث مجموع اعضاء List  حاصل را نیز بدست آورید خیلی ساده است کافی است از تابع List.Sum() به شکل زیر استفاده کنید

List.Sum(Source[Sale])

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

List.Sum ( List.FirstN ( Source[Sale] , 2) →  125

دسترسی به سطر (Record)

گفتید از این سه سطر ، چندمی را می خواستید ؟ اگر اولی را می خواهید که 0 را بین دو آکولاد جلوی همان Source  درج کنید . همین جا این بحث را ناتمام می گذارم تا در مقالهء بعد در خصوص Record بصورت مبسوط کیبورد فرسائی کنم . این هم تصویر اتفاقی که افتاده

دسترسی به یک سلول

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

=Source[Name]{2} → Farzin

دسترسی به سطرهائی با شرط خاص

نمی گویم بترسید ولی محکم بنشینید می خواهیم بحث را به سمت تخصصی شدن پیش ببریم . به همان Table مثال اصلی برگردید و برای خلاصی از آن کد بلند بالا دوباره f_x را صمیمانه بفشارید . پاور کوئری Step ی با نام Custom1 ایجاد می کند و شما در فرمول بار Source  را رویت می کنید سپس شماره ها را در تصویر زیر دنبال کنید

 الآن حتما اینجا هستید

  این چند سطر را چند بار بخوانید .

•   به پاور کوئری می گوئیم از Table  ی که تحویل شما دادم (Custom1) هر (each  ) سطری را که ستون [Region] ش برابر North است گزینش کن (SelectRows) و در قالب یک Table  به من برگردان .
• توجه کردید ! گفتم : سطری را ، یعنی دیگر اطلاعات مربوط به آن سطر را نیز برگردانَد .
• عجب ! یکی از ورودیهای و  خروجی این تابع Table است .
• ناگفته پیداست North را چون Text بود در داخل " " قرار دادیم .
• آسمان به زمین نمی آید اگر پرانتزهائی که شرط یا شروط را محصور کرده اند بردارید
• عبارات each و  and وor  با حروف کوچک نوشته می شوند

حالا عبارت زیر را پاک کنید

[Region] = "North"

و این عبارت را جایگزین کنید

[Sale]>50 

به ما ایرانیها یاد داده اند که احساساتمان را نادیده بگیریم(مثلا به پسر 10ساله می گوئیم : مرد که گریه نمی کند ) اما من ذوق زده می شوم شما را نمی دانم ؟! هر دو عبارتی که در شرط تابع نوشتیم قابل ترکیب هستند اگر مایلید بعد از each این دو را با and به هم بیامیزید و ذوق زده تر شوید

[Region] = "North" and [Sale]>50 

دیدن فایل ضمیمه بیشتر به شما کمک می کند .

هر روز با آموختن نکته ای ، خودتان را ذوق زده کنید .