Conditional Function and Formula
Power Query တွင် Conditional Logic များ(True or False ရရှိစေသည့် Logical Test များ) အပေါ် မူတည်၍ တွက်ချက်မှုများဆောင်ရွက်ရာတွင် Conditional Column များထည့်သွင်း၍ တွက်ချက် နိုင်သကဲ့ သို့ ပိုမိုရှုပ်ထွေးသည့် အခြေအနေများအတွက် Query Formula များ ရေးသား၍လည်း တွက်ချက်ဖြေရှင်း နိုင်ပါသည်။
Excel တွင် IF , Nested IF ဖြင့် ဖြေရှင်းသည့် အခြေအနေများကို Power Query တွင် Formula မရေးပဲ Conditional Column ထည့်သွင်း၍ လွယ်ကူစွာဖြေရှင်းနိုင်သည်။ Conditional Column ထည့်သွင်းရန် အတွက် Add Column tab အောက်မှ Conditional Column ကို ကလစ်ပါ။
ဆက်လက်လေ့လာရမည့် သင်ခန်းစာ
ယခုစာမျက်နှာတွင် ပါဝင်သည့် သင်ခန်းစာများ
6.1 Conditional Column
အောက်ပါအတိုင်း Conditional Column Box ကျလာပါမည်။
1. New Column Name နေရာတွင်Column အသစ်ကို သတ်မှတ်ပေးမည့် အမည်ကို ရေးသားရ ပါမည်။
2. Column Name နေရာတွင် Dropdown ကို ကလစ်၍ စစ်ဆေးမည့် Column ကို ရွေးချယ်ရပါမည်။
3. Operator နေရာတွင် အသုံးပြုမည့်၊ နှိုင်းယှဉ်စစ်ဆေးရာတွင် အသုံးပြုမည့် Operator (သို့မဟုတ်) Condition ကို ရွေးချယ်ပေးရပါမည်။
4. Value နေရာတွင် စစ်ဆေးမည့် Column နှင့် နှိုင်းယှဉ်စစ်ဆေးမည့် တန်ဖိုး (သို့မဟုတ်) အခြား Column (သို့မဟုတ်) Parameter အမည် တစ်ခုကို ရွေးချယ်ပေးရပါမည်။
အောက်ပါဥပမာတွင် Qty အပေါ်မူတည်၍ Discount သတ်မှတ်ပေးလိုခြင်းဖြစ်သည်။ အရေအတွက် ၁၅၀ နှင့်အထက်ဝယ်ယူလျင် 20% ၊ ၁၀၀ နှင့် အထက် ဝယ်ယူလျင် 15% ၊ 50 နှင့်အထက် ဝယ်ယူလျင် 10% ၊ 50 ထက် နည်းလျင် 0% အသီးသီးသတ်မှတ်ပေးရန်အတွက် Conditional Column ကို အသုံးပြုထားပါသည်။
ပိုမိုရှုပ်ထွေးသည့် တွက်ချက်မှုများဆောင်ရွက်ရာတွင် Conditional Column ဖြင့် တွက်ချက်ရန် မလုံလောက်သည့်အခါ Query Formula ဖြင့် ရေးသားဆောင်ရွက်ရန် လိုအပ်ပါမည်။ ထို့သို့ Formula ရေးသားရန်အတွက် Add Column tab အောက်မှ Custom Column ကို ကလစ်၍ ပေါ်လာသည့် Box တွင် Formula ရေးသားရမည် ဖြစ်သည်။
Power Query တွင် ရေးသားရမည့် IF Formula ၏ Syntax မှာ အောက်ပါအတိုင်း ဖြစ်ပါသည်။
=if logical_test then value_if_true else value_if_false
အောက်ပါ ဥပမာတွင် Quantity ပမာဏ 100 နှင့်အထက် ဆိုလျင် Discount 15% ပေးရန်နှင့် 100 အောက်ဆိုပါက 10% Dsicount ပေးရန် Custom Column တွင် Formula ရေးသားထားခြင်း ဖြစ်သည်။
= if [Quantity] >= 100 then 0.15 else 0.1
6.2 Conditional Column အသုံးပြု၍ Nested IF နှင့် အလားတူသည့် ပြဿနာဖြေရှင်းခြင်း
OK ပေးလိုက်သည့် အခါ အောက်ပါအတိုင်း Discount Column တွင် အဖြေများမှန်ကန်စွာ ရရှိသည် ကို တွေ့ရပါမည်။
အထက်ပါ Custom Column Box ကို OK ပေးလိုက်သည့်အခါ အောက်ပါအတိုင်း Discount % Column တစ်ခု ရရှိမည် ဖြစ်ပါသည်။
Formula တွင် ရေးသားထားသည်ကို သတိပြုကြည့်လျင် Formula ရေးသားရာတွင် % နှုန်းများ ထည့်သွင်း မရေးသားထားသည်ကို တွေ့ရပါမည်။ အဘယ်ကြောင့်ဆိုသော် % ကို ထည့်သွင်း ရေးသားလျင် Power Query Formula မှ % Symbol ကို Query မှ နားမလည်သည့်အတွက် Error ရရှိမည်ဖြစ်သောကြောင့် Decimal ဖြင့်သာ ရေးသားခြင်းဖြစ်ပါသည်။
အရေးကြီးဆုံးအချက်မှာ အထက်တွင် ဖော်ပြခဲ့သည့်အတိုင်း Power Query သည် Case Sensitive ဖြစ်သည့်အတွက် စကားလုံး အကြီး အသေး လွဲမှား၍ မရပါ။ if formula ရေးသားရာတွင် အားလုံးသည် Lower Case ( စကားလုံး အသေးဖြင့်သာ ) ရေးသားရမည် ဖြစ်ပါသည်။
အထက်ပါ အတိုင်း ရေးသားပြီး OK ပေးလိုက်ပါက အောက်ပါအတိုင်း Discount များ သတ်မှတ်ပေး သွားမည် ဖြစ်ပါသည်။
5. အမှတ်စဉ် 2, 3, 4 ပေါင်းစည်း၍ Logical Test တစ်ခုကို ဖြစ်ပေါ်စေမည် ဖြစ်ရာ ထို Logical Test မှ TRUE ပြန်ရလျင် ပြန်လည်ရရှိစေလိုသည့် တန်ဖိုး (သို့မဟုတ်) Column (သို့မဟုတ်) Parameter အမည်ကို Output တွင် ရွေးချယ်ပေးရမည်ဖြစ်သည်။
6. အကယ်၍ Logical Test မှFALSE ပြန်ရလျင် ပြန်လည်ရရှိစေလိုသည့် တန်ဖိုး (သို့မဟုတ်) Column (သို့မဟုတ်) Parameter အမည်ကို Otherwise တွင် ရွေးချယ်ပေးရမည်ဖြစ်သည်။
7. အကယ်၍ Logicatl Test တစ်ခုတည်းမလုံလောက်ပဲ Test များထပ်မံ စစ်ဆေးလိုပါက Add Rule ကို ကလစ်၍ နောက်ထပ် Logical Test တစ်ခု ထည့်သွင်းနိုင်သည်။
6.3 IF Formula
6.4 Nested IFs
အကယ်၍ Logical test တစ်ခုထက်ပို၍ စစ်ဆေးလိုလျင် if formula syntax တွင် else if ထပ်မံ ထည့်သွင်း ရေးသားရမည် ဖြစ်ပါသည်။ else if ထည့်သွင်းရေးသားလိုလျင် အောက်ပါ Syntax အတိုင်း ရေးသားရမည် ဖြစ်သည်။
=if logical_test then value_if_true
else if logical_test then value_if_true
else value_if_false
Conditional Column ဖြင့် Discount သတ်မှတ်ခဲ့သည့် ဥပမာအား ယခုဥပမာတွင် Query Formula ရေးသား၍ ဖြေရှင်းပါမည်။ ၁၅၀ နှင့်အထက်ဝယ်ယူလျင် 20% ၊ ၁၀၀ နှင့် အထက် ဝယ်ယူလျင် 15% ၊ 50 နှင့်အထက် ဝယ်ယူလျင် 10% ၊ 50 ထက် နည်းလျင် 0% အသီးသီးသတ်မှတ်ပေးရန်အတွက် Formula ရေးသားပါမည်။
= if [Quantity] >= 150 then 0.2
else if [Quantity] >= 100 then 0.15
else if [Quantity] >= 50 then 0.10
else 0
6.5 OR သဘောတရားနှင့် ဆင်တူသည့် Formula
Logical Test များကို တစ်ခုထက်ပို၍ စစ်ဆေးပြီး စစ်ဆေးသည့် Logical Test များထဲမှ တစ်ခုမှန်ကန် ရုံဖြင့် TRUE ပြန်လာစေလိုလျင် Excel တွင် OR Formula အသုံးပြုရေးသားလေ့ရှိသည်။ သို့ရာတွင် Power Query ၌ OR Formula မပါရှိသည့်အတွက် OR နှင့် သဘောတရားဆင်တူသည့် List.AnyTrue Formula ကို အသုံးပြုရေးသားရပါမည်။
Formula Syntax
=List.AnyTrue( { logical-test 1, logical-test 2, … } )
Formula ရေးသားရာတွင် L , A, T တို့ကို အကြီးဖြင့် ရေးသားရမည်ဖြစ်ပြီး ကျန်သည့် Character များကို အသေးဖြင့် ရေးသားရမည်ဖြစ်သည်။ ထို့အပြင် Round Bracket ( ) အတွင်း၌ Curyly Bracket { } ပါ ထည့်သွင်း ရေးသားရမည် ဖြစ်ပါသည်။
အောက်ပါ Table တွင် Contoso နှင့် A. Datum Brand ဆိုလျင် Discount 10% သတ်မှတ်ပေးလိုပြီး ကျန်သည့် Brand များဆိုလျင် Discount 5% သတ်မှတ်ပေးလိုပါသည်။
6.6 AND သဘောတရားနှင့် ဆင်တူသည့် Formula
Logical Test များကို တစ်ခုထက်ပို၍ စစ်ဆေးပြီး စစ်ဆေးသည့် Logical Test များ အားလုံးမှန်ကန်မှ TRUE ပြန်လာစေလိုလျင် Excel တွင် AND Formula အသုံးပြုရေးသားလေ့ရှိသည်။ သို့ရာတွင် Power Query ၌ AND Formula မပါရှိသည့်အတွက် AND နှင့် သဘောတရားဆင်တူသည့် List.AllTrue Formula ကို အသုံးပြုရေးသားရပါမည်။
Formula Syntax
=List.AllTrue( { logical-test 1, logical-test 2, … } )
List.AnyTrue ကဲ့သို့ပင် Formula ရေးသားရာတွင် L , A, T တို့ကို အကြီးဖြင့် ရေးသားရမည်ဖြစ်ပြီး ကျန်သည့် Character များကို အသေးဖြင့် ရေးသားရမည်ဖြစ်သည်။ ထို့အတူ Round Bracket ( ) အတွင်း၌ Curyly Bracket { } ပါ ထည့်သွင်း ရေးသားရမည် ဖြစ်ပါသည်။
အောက်ပါ Table မှ Adventure Works Brand များကို Quantity 100 နှင့်အထက် ဝယ်ယူလျင် Discount 10% ပေးလိုပြီး ကျန်သည့် Brand များကို Discount မပေးလိုပါ။
လိုအပ်သည့်အတိုင်း ရရှိရန် Custom Column တစ်ခု ထည့်သွင်းပြီး အောက်ပါအတိုင်း Formula ရေးသားပါမည် -
= if List.AnyTrue({[Brand] ="Contoso", [Brand] ="A. Datum"} )
then 0.1
else 0.05
OK ပေးလိုက်ပါက အောက်ပါအတိုင်း အဖြေများမှန်ကန်စွာ ရရှိသည်ကို တွေ့ရပါမည်။
ထို့အတွက် Custom Column တစ်ခုထည့်သွင်းပြီး အောက်ပါအတိုင်း Formula ရေးသားပါမည် -
= if List.AllTrue({ [Brand] ="Adventure Works", [Quantity]>=100 } )
then 0.1
else 0
OK ပေးလိုက်ပါက အောက်ပါအတိုင်း သတ်မှတ်ချက်နှင့် အညီ Discount တန်ဖိုးများကို Column အသစ်တစ်ခုဖြင့် တွေ့ရပါမည်။
6.7 IFERROR သဘောတရားနှင့် ဆင်တူသည့် Formula
Power Query တွင် IFERROR Formula မပါရှိသည့်အတွက် IFERROR အစား try … otherwise Formula ကို အသုံးပြုရမည် ဖြစ်သည်။
Formula Syntax:
=try <operation> otherwise <alternate result>
try နှင့် otherwise keyword နှစ်ခုလုံးအား Lower Case ( စကားလုံးအသေး ) ဖြင့် ရေသားရမည်။
operation နေရာတွင် အဓိကတွက်ချက်လိုသည့် Formula ကို ရေးသားရမည် ဖြစ်ပြီး အဆိုပါ Formula မှ အဖြေမထွက်ပဲ Error ရရှိပါက Error အစား အခြား ပြန်လည်ရရှိလိုသည့် တန်ဖိုး (သို့မဟုတ်) အခြား Formula တစ်ခု ရေးသားရမည် ဖြစ်သည်။
အောက်ပါ Table တွင် Date များကို စာသားအဖြစ်ဖြင့် ပုံစံမျိုးစုံ ရေးသားထားသည်ကို တွေ့ရပါမည်။
ထိုပုံစံများစွာဖြင့် ရေးသားထားသောစာသား Date များကို ပုံစံတူညီသည့် Date Format များဖြင့် ပြောင်းလဲ ရန်အတွက် Custom Column တွင် အောက်ပါအတိုင်း Date.From Formula အသုံးပြု ရေးသားပါမည်။
ထို့ကဲ့သို့ ရေးသား၍ OK ပေးလိုက်သည့်အခါ အောက်ပါအတိုင်း Date Format တူညီသည့် Column တစ်ခု ရရှိသည်ကို တွေ့ရပါမည်။
အထက်ပါ ရရှိသည့် Table တွင် ကျန်သည့် Date များမှာ အဆင်ပြေသော်လည်း အောက်ဆုံး Row ရှိ Date ကို Date Format အဖြစ် ပြောင်းလဲရာတွင် Error ရရှိသည်ကို တွေ့ရှိရပါသည်။
ထိုသို့ Error ရရှိမည့်အစား Ordered Date ကို Date Format မပြောင်းလဲပဲ မူလတန်ဖိုးသာ ပြန်လည်ရယူ ပေးစေရန် try … otherwise pattern အသုံးပြု၍ အောက်ပါအတိုင်း ပြောင်းလဲ ရေးသားလိုက်ပါမည်။
= try Date.From([Ordered Date])
otherwise ( [Ordered Date] )
အထက်ပါအတိုင်း ရေးသား၍ OK ပေးလိုက်သည့်အခါ Error ရရှိမည့်အစား မူလတန်ဖိုးသာ ပြန်လည်ရရှိသည်ကို တွေ့ရပါမည်။
