آموزش دستور each در پاورکوئری

آموزش استفاده از each در پاور کوئری

تا آنجا که من فهمیدم each می‌تواند جایگزین ساختار استاندارد تابعی با یک ورودی شود. برای اشاره به هر یک از آیتم‌های یک مجموعه (این مجموعه می‌تواند اعضای لیست باشد یا اعضای یک ردیف یا نام ستون‌های یک Table و ...) از each بهره می‌بریم. گاهی این علامت "_" به کمکش می‌آید. در این صورت معنایش بر حسب جایی که دارید از each استفاده می‌کنید تغییر می‌کند.

چرا از each استفاده می‌کنیم؟

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

مثال ۱: ساخت تابع ساده

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

(MyValue) => MyValue + 3

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

(_) => _ + 3

و حتی ساده‌تر از این هم می‌شود. دیگر پرانتزها و علامت‌ها را برمی‌داریم و از each استفاده می‌کنیم:

each MyValue + 3

نتیجه هر سه این فرمول‌ها یکسان است.

مثال ۲: استفاده از each در Table.AddColumn

رابط کاربری خود پاور کوئری هم ترجیح می‌دهد each را در توابعی چون Table.AddColumn، Table.SelectRows و Table.ColumnNames و List.Transform بکار بگیرد. می‌گویید نه! این یک مثال را با هم دنبال کنید مطمئنم تا آخرش می‌آیید.

داده‌هایی شامل یک ستون متنی (بعضی کتاب‌های اروین یالوم) و سه ستون عددی (آمار فرضی فروش آنها در بین سال‌های ۲۰۱۰ تا ۲۰۱۲ در یکی از کتابفروشی‌های همدان) است. شما فرض کنید ضمن انتقالش به ادیتور پاور کوئری مرحله Changed Type را هم پشت سر گذاشته و ما قصد داریم در یک ستون جدید به هر یک از اعداد ستون ۲۰۱۲ به عنوان پیش‌بینی فروش سال بعد ۱۰ عدد اضافه کنیم.

برای اضافه کردن یک ستون جدید در مسیر زیر هیچکس سد راهمان نخواهد بود:

Add Column…Custom Column

در پنجره‌ای که پاور کوئری بی دریغ به رویمان می‌گشاید کد ساده زیر را می‌نویسیم:

پس از فشردن صمیمانه کلید OK اتفاق مورد انتظاری که در شکل بالا می‌بینید اضافه شدن ۱۰ به هر یک از مقادیر ستون ۲۰۱۲ است.

Table.AddColumn(#"Changed Type", "2013", each [2012] + 10)

عبارت each را خود پاور کوئری اضافه کرد. به هر یک از اعضای ستون ۲۰۱۲ عدد ۱۰ را اضافه کرد.

مثال ۳: استفاده از each در Table.TransformColumns

شاید دوست داشته باشید در مثالی به کارگیری each را در Table.TransformColumns نیز تجربه کنید. این تابع چنانکه از نامش پیداست برای دستکاری مقادیر ستون استفاده می‌شود. مثلا به هر یک از آیتم‌های همین ستون نام کتاب‌ها عبارت "From Yalom: " را اضافه کنیم. در سربرگ Transform گروه Text Column روی Format کلیک کنید و Add Prefix را برگزینید تا یک پنجره باز شود. در درون آن عبارت From Yalom: را مانند شکل بنویسید. خوب است پیش از فشردن صمیمانه کلید OK در همین پنجره، یک Space بعد از From Yalom: بگذارید.

حالا باز هم به فرمول بار می‌رویم و تابعی را که تمام و کمال خود پاور کوئری تدارک دیده با هم بررسی می‌کنیم:

Table.TransformColumns(AddedNumber, {{"Book",

 each "From Yalom: " & _ , type text}})

ورودی اول نام Table قبلی است. قسمتی که بین دو جفت آکولاد قرار دارد شامل سه بخش است. بخش اول نام ستونی که پاور کوئری قرار است پیشوندی به هر عضو آن بیافزاید و بخش سوم تعیین فرمت ستون جدید که Text است. و اما بخش دوم: ترکیب each _ اشاره می‌کند به هر عضو ستون Book و عبارت From Yalom را با & به ابتدای هر آیتم ستون می‌چسباند.

آموزش استفاده از each در "لیست‌ها" در پاور کوئری

می‌خواهیم کار را با همین فایل ادامه دهیم و چون نیاز به یک لیست داریم روی یکی از ستون‌های عددی، کلیک راست می‌کنیم. حالا باید لیستی شامل درست ۲۰ آیتم قابل مشاهده باشد. من با چشم مسلح توانستم آیتم Drill down را تقریباً در انتهای این پنجره ببینم و روی آن کلیک کنم. این کار اتفاقی مبارک را برایمان رقم می‌زند و لیستی از اعداد را برایمان تدارک می‌بیند. در تصویر ببینید.

نکته: به جای کلیک راست روی ستون کافی بود در مرحله (Step) Changed Type روی fx کلیک کنیم و جلوی عبارت "Changed Type" در فرمول بار نام ستون را آنگونه که می‌بینید تایپ کنیم تا ستون به یک لیست تبدیل شود و بقیه ستون‌ها Remove شوند.

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

حالا می‌خواهم به هر یک از اعضای لیست ۱۰ تا اضافه کنم. خوف نکنید، این کار را با تابع List.Transform به سرانجام می‌رسانیم. اصطلاحاً Syntax یا نحو تابع طبق نص صریح سایت مایکروسافت یعنی عبارتی که شکل نوشتن و آرگومان‌ها و خروجی تابع را نشان می‌دهد، چنین است:

List.Transform(list as list, transform as function) as list

پس آرگومان اول تابع یک لیست و آرگومان دوم آن یک تابع است و صد البته خروجی هم یک لیست. لابد می‌گویید این دیگر چه جور تابعی است. آره تا چشممان عادت کند کمی طول می‌کشد. بپذیرید در عبارت زیر آرگومان دوم واقعاً تابع است:

= List.Transform(ListOfNumbers, each _ +10)

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

= List.Transform(ListOfNumbers, (Item) => Item +10)

و این کد:

= List.Transform(ListOfNumbers, (_) => _ +10)

چه ترکیب each _ چه Item داخل و بیرون پرانتز و چه _ در مثال سوم همه اشاره می‌کنند به اعضای لیست ListOfNumbers.

پذیرش تابع

گفتم بپذیرید این عبارات تابع هستند. این پذیرش دو سه هفته‌ای طول می‌کشد. این دیگر کارکرد مغز است. برای من هم که برنامه‌نویس نبودم و فقط درصدی با VBA آشنا بودم و سنی ازم گذشته بود بیش از این طول کشید تا این شکل را به صورت تابع بپذیرم. در ادامه دوباره روی fx کلیک کنید تا باز هم نام مرحله قبلی را در فرمول بار ببینید (ListOfNumbers). حالا صفحه کلید را به لمس سرانگشتان خود مفتخر کنید و همان عبارت را اینگونه تغییر دهید:

= List.Transform(ListOfNumbers, each _ +10)

آرگومان دوم یا همان تابع را بخوانیم: به هر عضو لیست، ۱۰ تا اضافه کن. پس در اینجا علامت _ به هر عضو لیست اشاره می‌کند. این تابع چنانکه از نامش پیداست برای اعمال تغییر روی اعضای لیست بکار می‌رود.

مثال در VBA

شاید اگر به نتیجه توجه کنید باورپذیر بشود.

 این حلقه را در VBA بیاد بیاورید و فرض کنید در محیط اکسل هستید: اعداد ۱ تا ۱۰ را در ستون اول داریم و در ستون دوم به هر یک ۱۰ را می‌افزاییم:

For I = 1 To 10
    Cells(I, 2).Value = Cells(I, 1).Value + 10
Next I

مثال‌های بیشتر

به باور من هر چه بیشتر مثال ببینید برای جا افتادن مطلب بهتر است. سه کد زیر را یک به یک جایگزین کدهای قبلی کنید و قبل از جایگزینی فکر کنید نتیجه چه خواهد بود بعد حدستان را به امتحان بنشینید:

= List.Transform(ListOfNumbers, each _ *_)
= List.Transform(ListOfNumbers, (Item) => Item *Item)
= List.Transform(ListOfNumbers, each Number.Power(_ , 2))

استفاده از List.Select

حالا دست به دامان List.Select می‌شویم. ورودی‌های این تابع نیز یک لیست و یک تابع است. اعضایی از لیست را به انتخاب کاربر و بر اساس تابع در خروجی بی سر و صدا تحویل می‌دهد. ببینیم چگونه:

در همین مرحله خیلی محترمانه باز هم روی fx کلیک کنید تا یک Step جدید ساخته شود. نام Step را خود پاور کوئری می‌سازد ولی اگر برای تغییر نام Step اعمال سلیقه کنیم پاور کوئری مانعمان نمی‌شود. حالا به شکلی که در زیر می‌بینید تابع List.Select را اعمال کنید و نتیجه را در شکل ببینید:

List.Select(Add10 , each _<80)

بخوانیم آرگومان دوم را: هر عضوی از لیست Add10 را که از ۸۰ کوچکتر است در خروجی تحویل بده.

اضافه کردن شرط

موافقید یک شرط دیگر نیز اضافه کنیم بدین صورت که علاوه بر شرط قبلی اعضایی که بزرگتر از ۲۰ هستند نیز در خروجی بیایند. معطل چی هستید؟ مرحله‌ای جدید با کلیک روی fx بنا کنید و عبارت زیر را در آن بنویسید (and را در خط فرمان ببینید، چه مشفقانه دو شرط را در آغوش یکدیگر جای می‌دهد):

List.Select(LessThan80 , each _ >20 and _<80)

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

List.Select(LessThan80 , each not(_ >20 and _<80))

اگر موافق باشید و همراه، اعداد زوج را از لیست جدا کنم. برای این کار هر عضو را از فیلتر تابع Number.IsEven بصورتی که می‌بینید عبور می‌دهم. (در حالت عادی ورودی تابع عدد است اگر زوج باشد True و اگر فرد باشد False را برمی‌گرداند البته که اعضاء لیست ما هم عدد هستند) با دقت به مکان علامت _ توجه کنید. با وجود این علامت _ تابع هر بار یک عضو لیست TwoCondition را در خود جای می‌دهد.

آرگومان دوم را بخوانیم: تابع Number.IsEven هر عضو TwoCondition را چک می‌کند اگر زوج بود به خروجی می‌فرستد.

نکته: دقت کرده باشید تابع Number.IsEven یک ورودی می‌گیرد پس می‌توان تابع را به صورت ساده‌تری نوشت و از ترکیب each _ نیز صرف نظر کرد:

List.Select(ListOfNumbers, Number.IsEven)

each _ در Record ها

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

حالا در تصویر بعد پنجره Custom Column که فقط یک علامت _ پس از مساوی درج شده حاصل کار هم دیده می‌شود به ردیف اول ستون جدید و گوشه پایین سمت چپ نگاه کنید خودبخود متوجه می‌شویم که در اینجا اشاره این علامت _ با رکوردها (سطرها) ی یک Table است.

خواننده محترم لطفا خودت دست به کیبورد شو و در این مسیر با من همراه. پس از فشردن کلید OK، در نوار فرمول تابع را به این شکل می‌بینید:

Table.AddColumn(#"Changed Type", "MaxOfRow", each _, type number)

نکته: با دست خودم ورودی آخر تابع را (type number) را اضافه کردم تا نه حالا نه هیچ وقت دیگر نیازی به تغییر فرمت ستون نباشد. حالا می‌فهمیم Table.AddColumn ممکن است دارای چهار ورودی باشد. یک Table در آرگومان اولش، نام ستون جدید و تابعی که ممکن است روی یک ستون، حتی چند ستون یا روی تک تک ردیف‌ها یا ... اعمال شود. و دست آخر تعیین فرمت ستون.

چه در همان پنجره Custom Column چه در نوار فرمول (formula bar) می‌توانید تابع را به شکلی تغییر دهید که در چشم برهم‌زدنی هر یک از رکوردها به لیست مبدل شوند. اکنون ما در هر ردیف ستون مذکور یک سطر جدول را از جنس لیست داریم و می‌توانیم با استفاده از توابع زیر مجموعه لیست، برایشان تصمیم جدید بگیریم:

= Record.ToList(_)

روی تک تک ردیف‌های ستون MaxOfRow کلیک کنید (اگر با دست خودتان تابع را نوشته باشید خوش‌آیند است)

من قصد دارم بین اعضاء این لیست‌ها ماکزیمم هر لیست را در ستون نگهدارم ولی با شرایط کنونی که در لیست هم عدد هست هم رشته متنی آیا می‌توان از پاور کوئری چنین انتظاری داشت؟ واضح است که باید از تابع List.Skip استفاده کنیم و عضو اول لیست را که هم جنس بقیه نیست بپرانیم.

می‌توان یک ستون جدید ایجاد کرد و تابع را به شکل زیر در آن نوشت توجه کنید اعضاء ستون MaxOfRow، لیست هستند و می‌توانند در آرگومان اول تابع List.Skip خودنمایی کنند:

List.Skip([MaxOfRow], 1)

اما ما چنین نمی‌کنیم و مثل یک حرفه‌ای در نوار فرمول یا در همان پنجره Custom Column قبلی، تابع را به شکل زیر تغییر می‌دهیم. در شکل، ردیف سوم ستون MaxOfRow، Highlight است و خبری از "The Spinoza Problem" هم نیست:

Table.AddColumn(#"Changed Type", "MaxOfRow", each List.Skip(Record.ToList(_), 1))

حالا می‌توان از این لیست که اعضاءَش به عدد بودن خود باور دارند، ماکزیمم را استخراج کرد. در ادامه با بکارگیری List.Max ما هم چنین می‌کنیم در نوار فرمول عبارت بعد از each را به شکل زیر تغییر دهید:

List.Max(List.Skip(Record.ToList(_), 1))

در ستون آخر می‌بینید که حق به حق دار رسیده و به ترتیب ۱۵ و ۳۰ و ۴۵ و ... جای خود را در سطر محکم کرده‌اند و هنوز این علامت _ به ردیف (رکورد)های جدول اشاره می‌کند.

نکته: فرض کنید در ستون‌های عددی برخی از سطرها همه مقادیرشان صفر بود و ما می‌خواهیم این سطرها را پیش از بارگذاری در اکسل، حذف کنیم. کافی بود بجای List.Max از List.Sum استفاده کنیم و با فیلتر کردن، هر چه صفر را از ستون MaxOfRow حذف کنیم (بجای نوشتن یک if تودرتو و برداشتن تمام True ها در ستون MaxOfRow).

each _ در Table ها

قصد داریم در یک مثال ساده به ابتدای نام ستون‌های یک جدول عبارت Perfix. را اضافه کنیم. همین مثال قبلی را بارگذاری می‌کنیم. پاور کوئری تغییر نام مرحله Changed Type را یک شیطنت کوچولو تلقی خواهد کرد و از آن بسادگی می‌گذرد. ما هم از نام عریض و طویل Changed Type در مرحله بعد خلاص خواهیم شد.

روی fx به آرامی کلیک کنید تا پاور کوئری یک Step جدید بسازد سپس داخل نوار فرمول کد زیر را بنویسید:

= Table.TransformColumnNames(ChType, each "Prefix." & _)

درست است که نام Table را در آرگومان اول خود دارد، اسم تابع می‌گوید: من با سر ستون‌ها کار دارم و قصد تغییر آنها را دارم نه با خود Table. با وجود این علامت _ شبیه یک حلقه عمل می‌کند و در هر لحظه یک سر ستون را در خودش می‌ریزد و عبارت Perfix. را به ابتدای آن می‌چسباند. به عبارتی ما به پاور کوئری می‌گوییم: به ابتدای هر سر ستون یک Perfix. بچسبان.

each در متن‌هایی مثل شرح حسابداری

یک ستون تکست داریم (ستون اول جدول قبلی با کمی دستکاری) که در ادیتور پاور کوئری Load شده است. هدف برداشتن تمام جاهای خالی از دور و بر و لابلای این رشته‌هاست. اگر خواستید از مسیر Add Column… From Text … Extract… Length طول هر رشته را بیابید. ولی قول بدهید Step را بعد از دیدن طول‌ها حذف کنید.

مرحله Changed Type را نیز اضافه کردم و تغییر نام دادم. توجه کنید نام Query را نیز به TextCleaner تغییر دادم. شما نیز چنین کنید و نام Query ها و Step ها را متناسب با کاری که می‌کنند تغییر دهید.

به سراغ یک Custom Column می‌رویم و تابع Text.Trim را روی ستون اعمال می‌کنیم. نتیجه را در شکل پایین می‌بینید:

= Text.Trim([Book])

تابع بالا جاهای خالی اطراف و اکناف هر رشته را حذف می‌کند. هنوز برای حیرت کردن زود است. به سراغ یک Custom Column دیگر می‌رویم. این بار تابع Text.Split را به شکل زیر صدا می‌زنیم:

Text.Split([Custom], " ")

چنانکه انتظار می‌رفت خروجی تابع برای هر سلول در ستون آخر، یک لیست است. اگر مایلید با رفتار تابع اخیرالذکر بیشتر آشنا شوید روی تک تک سلول‌های ستون Custom.1 کلیک کنید و لیست‌های ساخته شده را از نظر بگذرانید. حالا قرار است از تابع List.Select بهره گرفته و از هر یک از لیست‌های موجود تمام جاهای خالی را حذف کنیم یا به عبارتی فقط اعضایی از هر لیست را که یک رشته متنی است نگه داریم. این تابع دارای دو آرگومان است. اولین آرگومانش یک لیست و دومین آرگومان تابعی که روی اعضای لیست اعمال می‌شود. چنانکه از نام تابع آشکار است برخی از اعضا را نگه می‌دارد و مابقی را حذف می‌کند. حاصل کار را با دقت در شکل زیر ببینید:

List.Select([Custom.1], each _ <> "")

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

تابع Text.Combine دو آرگومان دارد که اولی لیستی از رشته‌های متنی و دومی تعیین کاراکتری است که اجزای لیست را به هم می‌چسباند. ما آرگومان دوم را این " " (یک جای خالی) انتخاب می‌کنیم. به ترتیبی که در فرمول زیر می‌بینید:

Text.Combine([Custom.2], " ")

در این مرحله سه ستون کمکی وسط را حذف می‌کنم.

جمع‌بندی همه Step ها در یک فرمول تو در تو

حالا مایلم همه Step ها را بصورت یک فرمول تو در تو و در یک مرحله جمع کنم. برای اینکار پس از بارگذاری تک ستون اولیه در پاور کوئری یک Custom Column فراخوان کنید و ضمن تغییر نام آن کجا کدهای زیر را در آن بنویسید:

Text.Combine(
  List.Select(
    Text.Split(
       Text.Trim([Book]), " "), 
       each _ <> ""
        ), 
        " ")

اگر ضرورت ایجاب کرد از آنها یک تابع بسازید:

(TEXT) =>
let
  OUTPUT = Text.Combine(
  List.Select(
    Text.Split(
       Text.Trim(TEXT), " "), 
       each _ <> ""
        ), 
        " ")
in
OUTPUT

و هرجا لازم شد آن را فراخوان کنید. مثلا همان تک ستون اولیه را دارید، می‌توانید به شکل زیر از تابع خود ساخته استفاده کنید. در اینجا تابع، TextCleaner نام‌گذاری شده. باز هم ستونی ایجاد کرده‌ام و تابع را به شکل زیر در آن درج کردم. در دل تابع نیز به ستون Book اشاره می‌کنم:

fnTextCleaner([Book])