2.5 Folder တစ်ခုအတွင်းမှ Excel ဖိုင်များရှိ ဒေတာများအား Dataset တစ်ခုအဖြစ် ပေါင်းစပ် ထည့်သွင်းခြင်း
ယခုသင်ခန်းစာတွင် Folder တစ်ခုအတွင်းရှိ Excel ဖိုင်များမှ ဒေတာများ ကို Dataset တစ်ခုတည်းအဖြစ် ပေါင်းစည်းရန်အတွက် Power Query အတွင်းသို့ ချိတ်ဆက်ထည့်သွင်းမည် ဖြစ်ပါသည်။
Excel Workbook အသစ် (သို့မဟုတ်) လက်ရှိ အလုပ်လုပ်နေသော Excel Work -book တွင် Excel Version 2016/ 2019 ဆိုပါက Data tab အောက်မှ New Query ( သို့မဟုတ် ) Get Data မှ တစ်ဆင့် From File ကိုသွားပါ။ ထိုမှတစ်ဆင့် From Folder ကို ရွေးချယ်ကလစ်ပါ။ Excel 2010/ 2013 ဖြစ်ပါက Power Query Tab အောက်မှ From File မှတစ်ဆင့် From Folder ကို ဆက်လက် Click ပေးရပါမည်။
Browse Window ပေါ်လာပါက ထည့်သွင်း မည့် Folder ကို ရွေးချယ်ရပါမည်။
ဆက်လက်လေ့လာရမည့် သင်ခန်းစာ
Query Editor တွင် Edit လုပ်ရန် Transform / Edit ကို ရွေးချယ်ပါ။ အချို့ Version များတွင် Navigator Window မပေါ်လာပဲ Folder ကို ရွေးချယ်ပြီး OK ပေးလိုက်သည်နှင့် Query Editor သို့ တစ်ခါတည်း ရောက်ရှိသွားမည် ဖြစ်ပါသည်။
ဤနေရာတွင် တစ်ခုစဉ်းစားရန်ဖြစ်သည်။
ယခုသင်ခန်းစာ၏ အဓိက ရည်ရွယ်ချက်မှာ Folder တစ်ခုအတွင်းရှိ Excel ဖိုင်များအားလုံးကို Query အတွင်းသို့ ထည့်သွင်း Edit ပြုလုပ်လို ခြင်းဖြစ်သည်။ အကယ်၍ Folder အတွင်းတွင် Excel ဖိုင် မဟုတ်ပဲ အခြား ဖိုင်အမျိုးအစား တစ်ခုခု ရှိနေခဲ့ပါက Query အတွင်းသို့ မထည့်သွင်း လိုပဲ Excel ဖိုင်များကိုသာ Query အတွင်းသို့ ထည့်သွင်းလိုခြင်းဖြစ်သည်။
ယခုလောလောဆယ် Folder အတွင်းတွင် Excel ဖိုင်များသာရှိလျင် အဆင်ပြေမည်ဖြစ်သော်လည်း နောင်တွင် တစ်စုံတစ်ယောက်မှ အခြားဖိုင် (ဥပမာ Word File, Pdf File ) အမျိုးအစားများ ထည့်သွင်းလိုက်ပါက Power Query တွင် Refresh လုပ်သည့်အချိန်၌ Error ဖြစ်ပေါ်မည် ဖြစ်ပါ သည်။ ထို့ကြောင့် ယခုအဆင့်မှ ရှေ့သို့မဆက်မီ Folder အတွင်းတွင် မည်သည့် ဖိုင်များ ရှိနေသည်ဖြစ်စေ Excel ဖိုင်များကိုသာ ကျန်ရှိစေရန် Extension Column တွင် Filter စစ်ထုတ်ပါမည်။
ထိုသို့ စစ်ထုတ်ရန်အတွက် Extension Column တွင် Filter ထောက်၍ Text Filter မှ တစ်ဆင့် End With ကို ရွေးချယ်ပါ။
Folder ကို ရွေးချယ်ပြီးနောက် OK ပေးလိုက်ပါက Folder အတွင်းရှိ File List နှင့်အတူ Window တစ်ခု ပေါ်လာပါမည်။
Filter Rows Box ပေါ်လာလျင် End with တွင် .xlsx ဟု ရိုက်ထည့်ပါ။ ထို့နောက် OK ပေးလိုက်ပါက Query Editor တွင် Excel ဖိုင်များသာ ကျန်ရှိသွားမည်ဖြစ်ပါသည်။
Query Editor အတွင်းတွင် ရရှိထားသည့် Column များအတွင်းမှ Data များ ပါဝင်နေသည့် Content Column နှင့် Excel File အမည်များရှိသည့် Name Column သာ လိုအပ်သည်ဖြစ်၍ အခြား Column များကို ဖျက်မည် ဖြစ်ပါ သည်။ Name Column သည် ရရှိလာသော Data များသည် မည်သည့် Excel File မှ ရရှိလိုသည်ကို သိရှိရန် လိုအပ်ပါက ချန်ထားရန်ဖြစ်ပါသည်။ ဆွဲထုတ်ရယူမည့် ဒေတာများသည် Content Column အတွင်း သာ ရှိသည်ဖြစ်၍ Excel File အမည်ကို သိရှိရန်မလိုအပ်ပါက Name Column ကိုပါ ဖျက်နိုင်ပါသည်။ ယခု ဥပမာတွင် Excel File တွင်ပါရှိသည့် သက္ကရာဇ်ခုနှစ်ကို သိရှိလိုသည်ဖြစ်၍ Name Column ကို ချန်ထားခြင်းဖြစ်ပါသည်။
Content နှင့် Name Column မှလွဲ၍ အခြား Column များကို ဖျက်ရန်အတွက် အဆိုပါ Column နှစ်ခုကို Select မှတ်၍ Right Click ထောက်ကာ Remove Other Columns ကို ရွေးချယ်ကလစ်ပါမည်။
Excel ဖိုင်အမည်တွင် ခုနှစ်၏ နောက်၌ Sales.xlsx ဟူ၍ ပါရှိနေရာ ထိုစာသားအား ဖျက်ထုတ်ပြီး ခုနှစ် အမည်ကိုသာ ရရှိလိုပါသည်။ ထိုသို့ Sales.xlsx စာသားအား ဖျက်ထုတ်ရန်အတွက် ပထမဦးစွာ Name Column ရှိ စာသားများကို Space အသုံးပြု၍ ခွဲထုတ်ပါမည်။ ထိုသို့ခွဲထုတ်ရန် အတွက် Name Column ကို Select မှတ်၍ Home Tab အောက်ရှိ Split Column မှတစ်ဆင့် By Delimiter ကို ရွေးချယ်ပါ။
Dialog box ပေါ်လာသည့်အခါ Delimiter အဖြစ် “Space” ကိုရွေးချယ်ပါ။
Name column သည် Column နှစ်ခုအဖြစ်ကွဲသွားမည်ဖြစ်ပြီး Sales.xlsx များပါရှိသည့် Column ကို ဖျက်ထုတ်မည်ဖြစ်ပါသည်။ ထို့အတွက် ၎င်း Column ကို Select မှတ်၍ Right Click ထောက် Remove Columns ကိုရွေးချယ်လိုက်ပါမည်။
Content Column အတွင်းမှ Data အချက်အလက်များ ရယူ ရန်အတွက် Custom Column တစ်ခု ထည့်သွင်းရမည် ဖြစ်ပါသည ်။ ထိုသို့ ထည့်သွင်း ရန်အတွက် Add Column Tab အောက်မှ Custom Column ကို ရွေးချယ်ကလစ်ပါ။
“Add Custom Column” box ပေါ်လာပါမည်။ New column name နေရာတွင် နှစ်သက်သည့် အမည် ထည့်သွင်းရေးသားနိုင်ပါသည်။ Custom Column Formula box နေရာတွင် အောက်ပါ Formula ကို ရေးသားပါမည်။
=Excel.Workbook([Content])
Formula ရေးသားပြီးနောက် OK ပေးလိုက်ပါက Column အသစ်တစ်ခု Query Editor တွင် တွေ့ရမည် ဖြစ်ပါသည်။ ၎င်း Column ထဲမှ Data အချက်အလက်များ ဆွဲထုတ် ရယူလိုသည့်အတွက် Column Heading တွင် Extract Icon ကို ကလစ်ပါ။ အသစ်ရရှိမည့် Column များတွင် မူလ Column အမည် မပါရှိစေရန် “Use original column name as prefix” ကို အမှန်ခြစ်ဖြုတ်ပါမည်။
OK ပေးလိုက်ပါက အောက်ပါအတိုင်း Excel Workbook များအတွင်းရှိ Excel Worksheet အမည်များ ကို အောက်ပါအတိုင်းတွေ့ရှိရပါမည်။
Kind Column တွင် Sheet နှင့် Defined Name ဟူ၍ နှစ်မျိုးရှိရာ Worksheet မှ ဒေတာများကိုသာ ရယူလိုသောကြောင့် Kind Column တွင် Filter ထောက်၍ Sheet ကိုသာ ရွေးချယ်ပါမည်။
Query အတွင်းရှိ Column များအတွင်းမှ Year, Month နှင့် Data Column များသာ လိုအပ်သည့် အတွက် အခြား Column များအား ဖျက်ရန် လိုအပ်သည့် Column များကို Select မှတ်၊ Right Click ထောက်၍ Remove Other Columns ကို ရွေးချယ်ပါမည်။
Data Column အတွင်းမှ ဒေတာအချက်အလက်များရယူရန် Data Column Heading ရှိ Extract Icon ကို ကလစ်ပါ။
OK ပေးလိုက်ပါက အောက်ပါပုံတွင် တွေ့ရှိရ သည့်အတိုင်း Worksheet အသီးသီးမှ ဒေတာများကို Query Editor တွင် တစ်စုတစ်စည်းထဲ မြင်တွေ့ ရမည် ဖြစ်ပါသည်။ သို့သော် Column Heading များသည် Heading Row တွင် ရှိမနေပဲ Data Row အဖြစ်ရှိနေ သည့်အတွက် ထို ပထမ ဆုံး Data Row အား Heading Row အဖြစ် သတ်မှတ်ပေးရန် Transform Tab အောက်မှ Use First Row as Headers ကို ရွေးချယ်ကလစ်ပါမည်။
ယခုအခါ Heading Row များ မှန်ကန်စွာ ရရှိပြီ ဖြစ်သော်လည်း ထည့်သွင်းသည့် Data များသည် Table အဖြစ် Format ချမထားသည့်အတွက် Query အတွင်းသို့ ထည့်သွင်းသည့်အခါ Extra Columns / Extra Rows များ ပါရှိနေမည် ဖြစ်ပါသည်။ Column အပိုများ ဖျက်ရန်အတွက် Data ရှိသည့် Column များကို Select မှတ်ပြီး Remove Other Columns ကို Click လိုက်ပါမည်။
အထက်တွင် ဖော်ပြခဲ့သည့်အတိုင်း ထည့်သွင်းသည့် Data များသည် Data Table အဖြစ် Format ချမထား သည့်အတွက် Extra Row တနည်းအားဖြင့် Blank Row များလည်း ပါဝင်နေမည်ဖြစ်ရာ ၎င်း Blank Row များကို ဖယ်ထုတ်ရန် လိုအပ်သည်။ ထို့အပြင် ယခု Query တွင် Worksheet အသီးသီးမှ Data များကို Dataset တစ်ခုအဖြစ် ပေါင်းစပ်ထည့်သွင်းခြင်းဖြစ်ရာ ဒေတာများကို အပေါ်အောက်ကပ်၍ ထည့်သွင်းပေး သွားမည်ဖြစ်သည်။ သို့ဖြစ်ရာ Worksheet တစ်ခုချင်း၏ Header Row များသည်လည်း Data Row များအဖြစ်သာ ပါဝင်နေမည် ဖြစ်ရာ အဆိုပါ Row များကိုလည်း ဖယ်ထုတ်ပစ်ရန်လိုအပ်သည်။
ထို့ကြောင့် Blank Row များ နှင့် Column အမည်များ ဖယ်ထုတ်ရန်အတွက် Column Heading တွင် Filter ကိုထောက်၍ null နှင့် Column အမည် ( အောက်ပါ ဥပမာတွင် Product ) ကို အမှန်ခြစ် ဖြုတ်ရပါမည်။ Column Heading တွင် Filter ထောက်သည့်အခါ Column အမည်ကို မမြင်ရလျင် Filter Menu အောက်နားရှိ Load More ကို ကလစ်ရပါမည်။ ထိုအခါ Column အမည် ပေါ်လာမည်ဖြစ်ပြီး null နှင့်အတူ ထို Column အမည်ကို အမှန်ခြစ် ဖြုတ်ခဲ့ပါမည်။
Excel File အသီးသီးမှ ဒေတာများကို ပေါင်းစပ်ခြင်းဖြစ်သည့်အတွက် နံပါတ်စဉ် Column သည် အစ မှ အဆုံး အစဉ်အတိုင်း ဖြစ်တော့မည် မဟုတ် ပါ။ ထို့ကြောင့် ယခုလက်ရှိ No. Column အား Select မှတ်၍ Remove ပြုလုပ်လိုက်ပါမည်။ ထို့နောက် နံပါတ်စဉ်ထည့်ရန်အတွက် Add Column အောက်ရှိ Index Column မှ From 1 ကို ရွေးချယ် လိုက်ပါမည်။
ယခုအခါ Query Editor တွင် Index အမည်ဖြင့် နံပါတ်စဉ်များ မှန်ကန်စွာ ထည့်သွင်းထားသည်ကို တွေ့ရ ပါမည်။ ထို နံပါတ်စဉ် Index Column အား ရှေ့ဆုံးတွင်ထားရန် Drag and Drop ဖြင့် ပြောင်းရွှေ့နိုင်ပါ သည်။
ထို့နောက် Column အမည်များကို သင့်လျော်သည့် အမည်များဖြင့် ပြောင်းလဲပေးလိုက်ပါမည်။ ယခုအခါ လိုအပ်သည့်အတိုင်း Excel ဖိုင်အားလုံးမှ ဒေတာများကို Dataset တစ်ခုအဖြစ် အောက်ပါအတိုင်း မှန်ကန် စွာ ရရှိပြီ ဖြစ်ပါသည်။
လိုအပ်သည့်အတိုင်း ရရှိပြီဖြစ်၍ Close & Load ကို အသုံးပြု၍ Excel (သို့မဟုတ်) Power Pivot Data Model သို့ထည့်သွင်းနိုင်ပါသည်။ မူရင်း Folder တွင် Excel ဖိုင်များ ထပ်မံထည့်သွင်းလိုက်သည့်အခါ ယခု Query ရှိသည့် Excel Workbook သို့ Updated Data များ ရရှိလာစေရန် Output Table အပေါ်တွင် Right Click ထောက်၍ Refresh နှိပ်ရန် လိုအပ်မည် ဖြစ်ပါသည်။
ဤစာမျက်နှာတွင်ပါဝင်သည့် ခေါင်းစဉ်များ
