سناریو اصلی

در مطلب گذشته در مورد کدهای صورتحساب خود صحبت کردم. به عنوان یادآوری، این کد های 10 رقمی هستند و ساختار آنها به شکل زیر است، مورد بررسی قرار خواهیم داد. این شماره ها همیشه دارای 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)

(توجه کنید که داده های نمونه فقط داده های مربوط به marina را شامل میشود)

منطق های چند شرطی (استفاده از if)

بنابراین، بر اساس دو مطلب قبلی (استفاده از توابع متنی و طراحی دستور if)، ما میتوانیم اولین کاراکتر را با قرار دادن دو آزمون if به صورت تو در تو ، تجزیه کنیم:

=if Text.Start([BillingCode],1)="G" then "Golf" else "two options left"

=if Text.Start([BillingCode],1)="F" then "Fitness" else "Marina"

در

if Text.Start([BillingCode],1)="G" then "Golf" else if Text.Start([BillingCode],1)="F" then "Fitness" else "Marina"

واقعاً زیاد سخت نبود. در حقیقت ما میتوانیم هرکدام از دستور های if را به صورت جداگانه قرار دهیم، سپس دومین دستور را کپی کنیم و با قسمت  “two options left” کپی کنیم و هیچ تغییر دیگری ایجاد نکنیم. نیاز به هیچ پرانتز یا چیز دیگری نیز نمی باشد.

بیش از سه گزینه

اگر تعداد زیادی گزینه داشته باشید می بایست چه کار کنیم؟ به کاراکتر نهم در کدهای billing توجه کنید. من جزئیات مربوط به این کد را توضیح ندادم ولی گزینه های ممکن برای این کد به شرح زیر است:

E(کارکنان)، S(کلاس قایق)، N(بدون نیاز به مالیات)، R(محدود)، I(غیر فعال)، L(اجتماعی)، M(درمانی)، U(معمولی)

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

در اکسل، میتوانستیم این گزینه ها را در یک جدول قرار دهیم و سپس با استفاده از تابع VLOOKUP مقدار مورد نظر را از جدول استخراج کنیم. بنابراین ما نیاز به تابع VLOOKUP در POWER QUERY داریم، ولی این تابع در اینجا موجود نیست.

در حقیقت چند راه برای انجام این کار وجود دارد و ما میتوانیم یکی از کارهای زیر را انجام دهیم:

• کاراکتر اول را در یک ستون قرار دهیم، یک جدول اکسل ایجاد کنیم که حرف اول در ستون 1 و مقدار مطابق آن در ستون 2 قرار بگیرد. سپس با استفاده از تابع MERGE در POWER QUERY دو ستون را با هم ترکیب کنیم.
• تابع CHOOSE مورد نیاز خود را در POWER QUERY طراحی کنیم. (یا از ورژن Dax ابزار power pivot استفاده کنیم). این روش جالب تر است ، پس از همین روش استفاده میکنیم.
• ساخت یک تابع function
• در واقع اگر ساختار اصلی را بدانید، انجام این کار دشوار نیست. اساساً این کار را باید به شکل زیر انجام داد:

function_name=(input)=>let values={ {result_1,return_value_1}, {input,“Undefined”} }, Result=List.First(List.Select(values,each_{0}=input)){1} in Result,

قسمت های کلیدی کدهای بالا به شکل زیر هستند:

• میتوانیم نام تابع را به هرچیزی که مایل باشیم تغییر دهیم.
• Result_1 اولین احتمال ممکن است که برای تابع در نظر میگیریم.
• Return_value1 مقداری است که مایل هستیم در صورت اینکه اولین مقدار result_1 باشد ، برگردانده شود.
• اگر به مقادیر بیشتری نیاز داشته باشیم، فقط کافیست یک کاما بعد از value_1 اضافه کنیم و قسمت value_2 را قرار دهیم.
• ما میتوانیم تا جایی که لازم داریم، مقادیر اضافی ایجاد کنیم.
• اگر مقداری که قرار میدهید در بین مقادیر تعریف شده موجود نباشد، متن “Undefined”برگردانده میشود (این قسمت همان بخش else دستور choose است)

با استفاده از این ساختار، میتوانیم یک تابع choose برای سناریو خود به شکل زیر بنویسیم:

fnChoose_CustCode=(input)=>let values={ {“E”,”Employee”}, {“S”,”SCYC”}, {“N”,”Non-Taxable”}, {“R”,”Restricted”}, {“I”,”Inactive”}, {“L”,”Social”}, {“M”,”Medical”}, {“U”,”Regular”}, {input,”Undefined”} }, Result=List.First(List.Select(values,each_{0}=input)){1} in Result,

توجه کنید که من بعضی از چیزها را تغییر دادم.

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

به کارگیری تابع choose

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

ابتدا، یک query جدید اضافه کردیم که جدول ما را به power query منتقل میکند، و نتیجه به شکل زیر میشود:

کدهایی را که این جدول را به power query دمنتقل میکنند را بررسی کنید: (دستور View –> Advanced Editor را کلیک کنید)

let Source=Excel.CurrentWorkbook(){[Name=”Customers”]}

[Content] in Source

مشاهده می کنید که هنوزه بسیار پیچیده نیست (فعلا). در جلوی source= کد های زیر را تایپ کنید:

let

fnChoose_CustCode=(input)=>let values={ {“E”,”Employee”}, {“S”,”SCYC”}, {“N”,”Non-Taxable”}, {“R”,”Restricted”}, {“I”,”Inactive”}, {“L”,”Social”}, {“M”,”Medical”},blah {“U”,”Regular”}, {input,”Undefined”} }, Result=List.First(List.Select(values,each_{0}=input)){1} in Result,

Source=Excel.CurrentWorkbook(){[Name=”Customers”]}

[Content] in Source

هنوز تبدیل به چیزی که لازم داریم نشده است. در حقیقت به جز اضافه کردن یک سطر جدید در قسمت steps ، Editor، چیز دیگری را مشاهده نمیکنیم:

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

استفاده از تابع choose جدید

از سهولت استفاده از آن شگفت زده خواهید شد.

ابتدا باید یک ستون جدید اضافه کنیم (Add Column –> Add Custom Column). زمانی که دیالوگ باکس ظاهر شد، فرمولی ایجاد کنید تا حروف مورد نظر ما را به تابع بدهد:

=Text.Range([BillingCode],8,1)

نتیجه زیر بدست خواهد آمد (با فرض اینکه نام ستون را status قرار دادیم.)

حال، فراتر میرویم و از تابع خود استفاده میکنیم. بر روی gear کنار status step کلیک کنید و فرمول زیر را با صدا زدن تابعی که طراحی کردیم، در آنجا قرار دهید:

=fnChoose_CustCode(Text.Range([BillingCode],8,1))

نتیجه زیر حاصل میشود:

تأثیر نهایی

مشاهده میکنید که تمام کدهای نمونه در داده درست کار میکند و هیچ پیام undefined ارائه نمیشود. اگر مایل هستید که بدانید query در برابر آیتم های مختلف چگونه واکنش نشان میدهد، به جدول مشتریان برگردید و حروف دوم تا آخر را تغییر دهید. زمانی که جدول را refresh کردید، مشاهده خواهید کرد که کاراکترهای جدید مورد بررسی قرار میگیرند و نتیجه صحیح برگردانده میشود.

هشدار

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

اگر میخواهید که هر دو نوع حروف پذیرفته شود، می بایست تابع Text.Range  را تغییر دهیم تا آن را تبدیل به حروف بزرگ کند:

=fnChoose_CustCode(Text.Upper(Text.Range([BillingCode],8,1)))

همانطور که مشاهده میکنید این روش مشکل را حل میکند:

[1] .marina: قایق تفریحی   fitness: قایق استقامتی        golf: دو نفره

[2]  Annual Moorage: سالانه    periodic: دوره ای         due: اعتباری         pass: نقدی

[/vc_column_text][/vc_column][/vc_row]