Power Query တွင် ဒေတာလမ်းကြောင်းများကို ကန့်သတ်ခြင်း

အကယ်၍ သင်သည် Microsoft Excel တွင် အခမဲ့ Power Query add-in ၏ ကိရိယာများကို စတင်အသုံးပြုပြီးပါက၊ မကြာမီတွင် အရင်းအမြစ်ဒေတာသို့ အဆက်မပြတ် ဖောက်ထွင်းဝင်ရောက်သည့် လင့်ခ်များနှင့် ဆက်စပ်နေသော အလွန်ထူးခြားသော အထူးပြုပြီး စိတ်အနှောင့်အယှက်ဖြစ်စေသော ပြဿနာတစ်ခုနှင့် ကြုံတွေ့ရမည်ဖြစ်သည်။ ပြဿနာ၏ အနှစ်သာရမှာ သင့်မေးမြန်းမှုတွင် ပြင်ပဖိုင်များ သို့မဟုတ် ဖိုင်တွဲများကို ရည်ညွှန်းပါက Power Query သည် query text တွင် ၎င်းတို့ထံ လုံးဝလမ်းကြောင်းကို hardcode ပေးသည်။ သင့်ကွန်ပြူတာတွင် အရာအားလုံး အဆင်ပြေသော်လည်း သင့်လုပ်ဖော်ကိုင်ဖက်များထံ တောင်းဆိုချက်တစ်ခုဖြင့် ဖိုင်တစ်ခုပေးပို့ရန် ဆုံးဖြတ်ပါက ၎င်းတို့ကြောင့် စိတ်ပျက်သွားမည်ဖြစ်သည်။ ၎င်းတို့တွင် ၎င်းတို့၏ကွန်ပျူတာရှိ အရင်းအမြစ်ဒေတာသို့ ကွဲပြားသောလမ်းကြောင်းတစ်ခုရှိ၍ ကျွန်ုပ်တို့၏မေးမြန်းချက်သည် အလုပ်မဖြစ်ပါ။

ဒီလိုအခြေအနေမှာ ဘာလုပ်ရမလဲ။ ဤကိစ္စရပ်ကို အောက်ပါဥပမာဖြင့် ပိုမိုအသေးစိတ်ကြည့်ကြပါစို့။

ဖော်မြူလာပြဿနာ

ငါတို့ folder ထဲမှာရှိတယ်ဆိုပါစို့ E:အရောင်းအစီရင်ခံစာများ ဖိုင်ကိုလိမ်တယ်။ ထိပ်တန်း 100 products.xlsကျွန်ုပ်တို့၏ ကော်ပိုရိတ်ဒေတာဘေ့စ် သို့မဟုတ် ERP စနစ် (1C၊ SAP စသည်ဖြင့်) မှ အပ်လုဒ်တစ်ခုဖြစ်သည့် ဤဖိုင်တွင် လူကြိုက်အများဆုံး ကုန်ပစ္စည်းများအကြောင်း အချက်အလက်ပါရှိပြီး အတွင်းတွင် ဤကဲ့သို့ တွေ့ရသည်-

ဤပုံစံဖြင့် Excel တွင် ၎င်းနှင့်အလုပ်လုပ်ရန် မဖြစ်နိုင်သလောက်ဖြစ်နိုင်သည်- ဒေတာပါရှိသော အတန်းများ၊ ပေါင်းစည်းထားသောဆဲလ်များ၊ ကော်လံများ အပိုများ၊ အဆင့်များစွာရှိသော ခေါင်းစီးစသည်ဖြင့် အနှောင့်အယှက်ပေးမည့် အတန်းများ။

ထို့ကြောင့်၊ တူညီသောဖိုင်တွဲရှိ ဤဖိုင်ဘေးတွင်၊ ကျွန်ုပ်တို့သည် နောက်ထပ်ဖိုင်အသစ်တစ်ခုကို ဖန်တီးပါသည်။ Handler.xlsxအရင်းအမြစ် အပ်လုဒ်ဖိုင်မှ ရုပ်ဆိုးသောဒေတာကို တင်မည့် Power Query query တစ်ခုကို ဖန်တီးပါမည်။ ထိပ်တန်း 100 products.xls၊ သူတို့ကို စနစ်တကျထား။

ပြင်ပဖိုင်တစ်ခုသို့ တောင်းဆိုမှုပြုလုပ်ခြင်း။

ဖိုင်ကိုဖွင့်ခြင်း။ Handler.xlsxတက်ဘ်ပေါ်တွင် ရွေးချယ်ပါ။ ဒေတာများ အမိန့် ဒေတာရယူပါ - ဖိုင်မှ - Excel Workbook မှ (ဒေတာ - ဒေတာရယူပါ - ဖိုင်မှ - Excel မှ)ထို့နောက် အရင်းအမြစ်ဖိုင်၏တည်နေရာနှင့် ကျွန်ုပ်တို့လိုအပ်သောစာရွက်ကို သတ်မှတ်ပါ။ ရွေးချယ်ထားသောဒေတာကို Power Query တည်းဖြတ်သူသို့ တင်ပေးပါမည်-

သူတို့ကို ပုံမှန်ပြန်ဖြစ်အောင် လုပ်ကြရအောင်။

1. အလွတ်လိုင်းများဖြင့် ဖျက်ပါ။ ပင်မ — လိုင်းများကိုဖျက်ပါ — ဗလာလိုင်းများကိုဖျက်ပါ။ (ပင်မ — အတန်းများကို ဖယ်ရှား — ဗလာအတန်းများကို ဖယ်ရှားပါ).
2. မလိုအပ်ဘဲ အပေါ်ဆုံးလိုင်း 4 ခုကို ဖျက်လိုက်ပါ။ ပင်မ - အတန်းများကိုဖျက်ပါ - ထိပ်တန်းအတန်းများကို ဖျက်ပါ။ (ပင်မ - အတန်းများကို ဖယ်ရှားပါ - ထိပ်တန်းအတန်းများကို ဖယ်ရှားပါ).
3. ခလုတ်ဖြင့် ပထမတန်းကို ဇယားခေါင်းစီးသို့ မြှင့်ပါ။ ခေါင်းစီးများအဖြစ် ပထမစာကြောင်းကို အသုံးပြုပါ။ tab ကို ပင်မစာမျက်နှာ (ပင်မ – ပထမတန်းကို ခေါင်းစီးအဖြစ် သုံးပါ).
4. ညွှန်ကြားချက်ကို အသုံးပြု၍ ဒုတိယကော်လံရှိ ထုတ်ကုန်အမည်မှ ဂဏန်းငါးလုံးပါ ဆောင်းပါးကို ခွဲခြားပါ။ ကော်လံကွဲ tab ကို ေျပာင္းလဲျခင္း (အသွင်ပြောင်း — Split Column).
5. မလိုအပ်သောကော်လံများကို ဖျက်ပြီး ပိုမိုကောင်းမွန်စွာမြင်နိုင်စေရန်အတွက် ကျန်ရှိသည့်ခေါင်းစဉ်များကို အမည်ပြောင်းပါ။

ရလဒ်အနေဖြင့်၊ ကျွန်ုပ်တို့သည် အောက်ပါ၊ ပိုမိုသာယာသော ရုပ်ပုံလွှာကို ရရှိသင့်သည်။

ကျွန်ုပ်တို့၏ ဖိုင်ရှိ စာရွက်သို့ ဤ ennobled ဇယားကို အပ်လုဒ်လုပ်ရန် ကျန်ရှိနေပါသည်။ Handler.xlsx အဖွဲ့ ပိတ်ပြီးဒေါင်းလုဒ်လုပ်ပါ။ (ပင်မ - ပိတ်ပြီး တင်ပါ) tab ကို ပင်မစာမျက်နှာ:

တောင်းဆိုချက်တစ်ခုရှိ ဖိုင်တစ်ခုသို့ လမ်းကြောင်းကို ရှာဖွေခြင်း။

ယခု Power Query တွင် တိုတိုတုတ်တုတ်အမည် "M" ဖြင့် တည်ဆောက်ထားသော စက်တွင်းဘာသာစကားဖြင့် ကျွန်ုပ်တို့၏မေးမြန်းချက်သည် "အဖုံးအောက်" ကို မည်သို့မြင်ကြမည်နည်း။ ၎င်းကိုလုပ်ဆောင်ရန် ညာဘက်အကန့်ရှိ ၎င်းကို နှစ်ချက်နှိပ်ခြင်းဖြင့် ကျွန်ုပ်တို့၏မေးမြန်းမှုသို့ ပြန်သွားပါ။ တောင်းဆိုမှုများနှင့် ချိတ်ဆက်မှုများ နှင့် tab ပေါ်တွင် ဆန်းစစ်ခြင်း ရှေးခယျြ အဆင့်မြင့် တည်းဖြတ်သူ (ကြည့်ရန် — အဆင့်မြင့် တည်းဖြတ်သူ):

ပွင့်လာသောဝင်းဒိုးတွင်၊ ဒုတိယစာကြောင်းသည် ကျွန်ုပ်တို့၏မူရင်းအပ်လုဒ်ဖိုင်သို့ ခက်ခက်ခဲခဲကုဒ်လုပ်ထားသောလမ်းကြောင်းကို ချက်ချင်းဖော်ပြသည်။ ဤလမ်းကြောင်းကို ကြိုတင်ရေးသားထားသည့် Excel စာရွက်ဆဲလ်တစ်ခုသို့ ကန့်သတ်ဘောင်တစ်ခု၊ ကိန်းရှင် သို့မဟုတ် လင့်ခ်တစ်ခုဖြင့် ဤစာသားစာကြောင်းကို အစားထိုးနိုင်လျှင် ၎င်းကို နောက်ပိုင်းတွင် အလွယ်တကူ ပြောင်းလဲနိုင်သည်။

ဖိုင်လမ်းကြောင်းတစ်ခုနှင့်အတူ စမတ်ဇယားတစ်ခုကို ထည့်ပါ။

Power Query ကို ယခုပိတ်ပြီး ကျွန်ုပ်တို့၏ဖိုင်သို့ ပြန်သွားကြပါစို့ Handler.xlsx. ကျွန်ုပ်တို့၏အရင်းအမြစ်ဒေတာဖိုင်သို့ လမ်းကြောင်းအပြည့်အစုံကို ရေးပေးမည့် တစ်ခုတည်းသောဆဲလ်တွင် အလွတ်စာရွက်အသစ်တစ်ခုကို ထည့်ပြီး ၎င်းပေါ်တွင် "စမတ်" ဇယားလေးတစ်ခုပြုလုပ်ကြပါစို့။

ပုံမှန်အကွာအဝေးမှ စမတ်ဇယားတစ်ခုကို ဖန်တီးရန်၊ သင်သည် ကီးဘုတ်ဖြတ်လမ်းကို အသုံးပြုနိုင်သည်။ Ctrl ++T သို့မဟုတ် ခလုတ် ဇယားအဖြစ် ဖော်မတ်ပါ။ tab ကို ပင်မစာမျက်နှာ (ပင်မ – ဇယားအဖြစ် ပုံစံဖော်ပါ). ကော်လံခေါင်းစီး (ဆဲလ် A1) သည် မည်သည့်အရာမဆို ဖြစ်နိုင်သည်။ ရှင်းရှင်းလင်းလင်းသိရန် ကျွန်ုပ်သည် ဇယားကို အမည်ပေးထားကြောင်းကိုလည်း သတိပြုပါ။ parameters tab ကို လုပ်ငန်းခွင် (ဒီဇိုင်း).

Explorer မှ လမ်းကြောင်းတစ်ခုကို ကူးယူခြင်း သို့မဟုတ် ၎င်းကို ကိုယ်တိုင်ဝင်ရောက်ခြင်းမှာ သေချာပေါက် ခက်ခဲသည်မဟုတ်သော်လည်း လူသားအချက်ကို လျှော့ချရန်နှင့် ဖြစ်နိုင်ပါက လမ်းကြောင်းကို အလိုအလျောက် ဆုံးဖြတ်ရန် အကောင်းဆုံးဖြစ်သည်။ ၎င်းကို standard Excel worksheet function ကို အသုံးပြု၍ အကောင်အထည်ဖော်နိုင်သည်။ ဆဲလ် (ဆဲလ်)အငြင်းအခုံအဖြစ် သတ်မှတ်ထားသည့်ဆဲလ်အကြောင်း အသုံးဝင်သော အချက်အလက်များစွာကို ထုတ်ပေးနိုင်သည့် - လက်ရှိဖိုင်သို့ လမ်းကြောင်းအပါအဝင်၊

အရင်းအမြစ်ဒေတာဖိုင်သည် ကျွန်ုပ်တို့၏ပရိုဆက်ဆာနှင့်တူညီသောဖိုင်တွဲတွင် အမြဲရှိနေသည်ဟု ကျွန်ုပ်တို့ယူဆပါက၊ ကျွန်ုပ်တို့လိုအပ်သောလမ်းကြောင်းကို အောက်ပါဖော်မြူလာဖြင့် ဖွဲ့စည်းနိုင်သည်-

=LEFT(CELL(“ဖိုင်အမည်”);FIND(“[“;CELL(“ဖိုင်အမည်”))-၁)&”ထိပ်တန်းထုတ်ကုန် ၁၀၀.xls”

သို့မဟုတ် အင်္ဂလိပ်ဗားရှင်း-

=LEFT(CELL(«ဖိုင်အမည်»);FIND(«[«;CELL(«ဖိုင်အမည်»))-1)&»Топ-100 товаров.xls»

... function ဘယ်မှာလဲ။ LEVSIMV (လက်ဝဲ) လင့်ခ်အပြည့်အစုံမှ အဖွင့်စတုရန်းကွင်းကွင်းအထိ (ဆိုလိုသည်မှာ လက်ရှိဖိုင်တွဲသို့ လမ်းကြောင်း) အထိ စာသားတစ်ပိုင်းကို ယူပြီးနောက် ကျွန်ုပ်တို့၏ အရင်းအမြစ်ဒေတာဖိုင်၏ အမည်နှင့် တိုးချဲ့မှုကို ၎င်းတွင် ကပ်ထားသည်။

မေးခွန်းရှိလမ်းကြောင်းကို ကန့်သတ်ပါ။

နောက်ဆုံးနှင့် အရေးကြီးဆုံးထိတွေ့မှုမှာ - တောင်းဆိုချက်ရှိ အရင်းအမြစ်ဖိုင်သို့ လမ်းကြောင်းကို ရေးသားရန် ကျန်ရှိနေပါသည်။ ထိပ်တန်း 100 products.xlsကျွန်ုပ်တို့ဖန်တီးထားသော “စမတ်” ဇယား၏ဆဲလ် A2 ကို ရည်ညွှန်းသည်။ parameters.

ဒါကိုလုပ်ဖို့၊ Power Query မေးခွန်းကို ပြန်သွားပြီး ပြန်ဖွင့်ကြည့်ရအောင် အဆင့်မြင့် တည်းဖြတ်သူ tab ကို ဆန်းစစ်ခြင်း (ကြည့်ရန် — အဆင့်မြင့် တည်းဖြတ်သူ). ကိုးကားချက်များတွင် စာသား string-path အစား "E: အရောင်းအစီရင်ခံစာထိပ်တန်းထုတ်ကုန် 100.xlsx" အောက်ပါဖွဲ့စည်းပုံကို မိတ်ဆက်ပေးပါရစေ။

Excel.CurrentWorkbook(){[name=”ဆက်တင်များ”]}[အကြောင်းအရာ]0 { }[အရင်းအမြစ်ဒေတာသို့ လမ်းကြောင်း]

ဘာတွေပါဝင်လဲဆိုတာ ကြည့်ရအောင်။

• Excel.CurrentWorkbook() လက်ရှိဖိုင်၏ အကြောင်းအရာများကို ဝင်ရောက်ကြည့်ရှုရန်အတွက် M ဘာသာစကား၏ လုပ်ဆောင်ချက်တစ်ခုဖြစ်သည်။
• {[name=”ဆက်တင်များ”]}[အကြောင်းအရာ] - ဤသည်မှာ ကျွန်ုပ်တို့သည် “စမတ်” ဇယား၏ အကြောင်းအရာများကို ရယူလိုကြောင်း ညွှန်ပြသော ယခင်လုပ်ဆောင်ချက်အတွက် သန့်စင်မှုဆိုင်ရာ ကန့်သတ်ချက်တစ်ခုဖြစ်သည်။ parameters
• [အရင်းအမြစ်ဒေတာသို့ လမ်းကြောင်း] ဇယားရှိ ကော်လံအမည် ဖြစ်သည်။ parametersငါတို့ရည်ညွှန်းသော
  
• 0 { } ဇယားရှိ အတန်းနံပါတ်ဖြစ်သည်။ parametersဒေတာရယူလိုသောနေရာမှ ဦးထုပ်သည် ရေတွက်ခြင်းမရှိပါ၊ နံပါတ်သည် သုညမှစတင်သည်၊ တစ်ခုမှမဟုတ်ပေ။

တကယ်တော့ ဒါပါပဲ။

ကလစ်နှိပ်ရန်ကျန်သေးသည်။ အပြီးသတ် ကျွန်ုပ်တို့၏တောင်းဆိုချက် မည်သို့အလုပ်လုပ်သည်ကို စစ်ဆေးပါ။ ယခု၊ ဖိုင်နှစ်ခုလုံးပါရှိသည့် ဖိုင်တွဲတစ်ခုလုံးကို အခြား PC သို့ ပေးပို့သည့်အခါ တောင်းဆိုချက်သည် ဆက်လက်လည်ပတ်နေပြီး ဒေတာလမ်းကြောင်းကို အလိုအလျောက် ဆုံးဖြတ်မည်ဖြစ်သည်။

• Power Query ဆိုတာ ဘာလဲ၊ Microsoft Excel မှာ အလုပ်လုပ်တဲ့အခါ ဘာကြောင့် လိုအပ်တာလဲ။
• Power Query ထဲသို့ လွင့်နေသော စာသားအတိုအထွာကို မည်သို့တင်သွင်းရမည်နည်း။
• XNUMXD Crosstab ကို Power Query ဖြင့် Flat Table သို့ ပြန်လည်ဒီဇိုင်းလုပ်ခြင်း။