تابع if در اکسل پاور کوئری (power query)

نکته مهم در فرمول نویسی پاور کوئری

تنها نکته مهمی که می بایست به یاد داشته باشیم این است که تمام توابع در power query، خواه متنی، منطقی یا هر نوع دیگری که باشد، نسبت به حروف بزرگ و کوچک حساس است. و می بایست به یاد داشته باشیم که در اینجا if با IF برابر نیست و power query مورد دوم را قبول نخواهد کرد.

سناریو اصلی

در این مثال میخواهیم جدولی از داده های مربوط به شماره مشتریان را بررسی کنیم، یک boat type و یک billing code را مورد بررسی قرار خواهیم داد. این شماره ها همیشه دارای 10 کاراکتر هستند و به شکل زیر نیز تفسیر میشوند:

• کاراکتر 1 – alpha – نشان دهنده گروه مربوطه است [1](G = Golf, F = Fitness, M = Marina)
• کاراکتر 2 – alpha- نشان دهنده نوع صورتحساب[2] (D = Dues, P = Pass, A = Annual Moorage, P = Periodic Moorage)
• کاراکتر 3-4 – نشان دهنده تعداد ماههای تحت پوشش محصول است (1-12)
• کاراکتر 5-6- عددی- نشان دهنده ماه شروع ( و سال ) مربوط به محصول خریداری شده توسط مشتری
• کاراکتر 7-8 – متغیر- طول (به feet) قایق برای مشتریان قایق های دو نفری
• کاراکتر 9 – متن- مجموعه ای از کدهای تک حرفی که چیزهایی را که ما میخواهیم بدانیم ، مشخص میکنند.
• کاراکتر 10- متن- روش پرداخت را مشخص میکند. (F = Financed, P = Paid up front, C = Comp/Honorary)

در نتیجه ، جدول داده های مشتریان میتواند به شکل زیر باشد:

تبدیل داده ها به داده های مفیدتر

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

البته، گام اول، کلیک کردن بر روی جدول و باز کردن Power Query –> From Table است.

هدف من، ایجاد ستونی است که اگر کاراکتر دوم “A” بود ، annual و اگر”P” بود، periodic را برگرداند. ابتدا، بر روی “Add Custom Column” کلیک میکنم.

نام ستون جدید را “Seasonality” قرار میدهم، و با استفاده از یک فرمول فقط کاراکتر دوم را استخراج میکنم.

و با قرار دادن فرمول در جای خود، میتوانیم بر روی داده های مهم زیر تمرکز کنیم:

نوشتن تابع if در power query

با فرض اینکه، داده ها در جدول قرار گرفته اند و از ردیف دوم ورک شیت شروع میشوند، یکی از فرمول های زیر برای تبدیل  A به annual و P به periodic مورد استفاده قرار میگیرد.

بسیار آسان بود، اینطور نیست؟ ولی توجه کنید که علامت ها در اکسل و power query بسیار با هم متفاوت است:

توجه کنید که در مدل Power Query از هیچ پرانتز یا کاما استفاده نمیشود و شما می بایست از than و else استفاده کنید. بنابراین می بایست برای ساخت چنین چیزی در Power Query ستون جدیدی ایجاد شود که از فرمول زیر استفاده میکند:

=if [Seasonality]="A" then "Annual" else "Periodic"

ولی من ترجیح میدهم که ستون seasonality را که قبلاً ایجاد کردیم، اصلاح کنیم، و متن استخراج شده را در تابع if و به شکل زیر قرار دهیم:

=if Text.Range([BillingCode],1,1)="A" then "Annual" else "Periodic"

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

مشاهدات

یکبار دیگر، می بینیم که در این ابزار تفاوتی با محیط اکسل وجود دارد. اگرچه پس از فهم آن، انجام انتقال کار سختی نیست، ولی اگر زبان بتواند باعث بهبود مهارت هایی شود که تاکنون فراگرفته ایم، بسیار خوب خواهد بود. شما ممکن است ادعا کنید که، طویل بودن فرمول های if در power query باعث آسانتر شدن خواندن فرمول های آن شده است ولی این نوع توابع هنوزه با فرمول هایی که تاکنون آموخته ایم متفاوت و ناسازگار است.

من هنوزه احساس میکنم که، اگر میتوانستیم فرمول هایی به شکل زیر را در power query وارد کنیم بسیار عالی بود:

=IF([Seasonality]="A","Annual","Periodic")

پیام خطا power query که در زمان نوشتن فرمول های if و دیگر فرمول ها ارائه میشود، در واقع هیچ کمکی به شما نمی کند.

بسیاری از متخصصان اکسل، نمیدانند معنی “Token Eof expected.” چیست، من نیز در واقع نمیدانم که این خطا، از من انتظار دارد چه چیزی را اصلاح کنم. امیدواریم، در ورژن های بعدی power query پیام های کمک کننده تری مثل “It looks like you typed an upper case formula name.  Can I fix that for you?” (به نظر می رسد که شما یک نام فرمول با حروف بزرگ تایپ کرده اید، میتوانم آن را برای شما اصلاح کنم؟) را ارائه کند.