การดึงข้อมูลจากทั้ง Folder มาทีเดียวพร้อมกันได้นี่ ผมคิดว่ามันเป็นสุดยอดแห่งการ Get Data ที่หลายคนปรารถนาเลยล่ะ
ลองคิดดูว่าหากเราต้องทำ Regular Report ทุกอาทิตย์หรือทุกเดือนแล้วเราสามารถโยนข้อมูลเดือนใหม่ที่ได้ไฟล์มาจากฝ่ายงานต่างๆ เข้าไปใน Folder ที่เราออกแบบไว้แล้ว หลังจากนั้นกด Refresh แล้วรายงานทุกอย่างก็ถูก Power Query + Pivot Table ปั่นออกมาจนเสร็จได้เลยมันจะสุดยอดขนาดไหน!!
ประเด็นที่น่าสนใจในการดึงข้อมูลจาก Folder มีอยู่ 3 เรื่องหลักๆ เลยคือ
- มีไฟล์อื่นปนมาหรือไม่ ? : ถ้ามีโอกาสที่จะมีไฟล์อื่นปนมา ก็ควรจะมีการ Filter เลือกเอาเฉพาะสิ่งที่ต้องการเท่านั้น
- มีข้อมูลกี่รูปแบบ? : ถ้าข้อมูลมีอยู่หลายรูปแบบ (Pattern) ให้เลือกมาทำทีละรูปแบบ แล้วสุดท้ายค่อยเอามารวมกันหรือเชื่อมกันทีหลัง
- ต้องการรวมไฟล์แบบ Auto หรือทำแบบสร้าง Custom Column? : แบบ Auto จะง่ายกว่า แต่แบบ Custom Column มีความยืดหยุ่นสูงกว่าครับ
- ไฟล์ที่ต้องการเป็น Excel หรือว่า Text/CSV ? : การจัดการข้อมูลแต่ละแบบไม่เหมือนกัน เดี๋ยวเรามาดูรายละเอียดกันครับ
หลักการดึง Folder ที่มี หลายๆ ไฟล์
ให้เรา Get Data → From File → From Folder แล้วเลือก Folder ที่ต้องการ
โหลดไฟล์ประกอบได้ที่นี่ครับ (เป็นไฟล์ zip ในนั้นมี Folder อยู่ด้วย)
จากนั้นกด Transform Data มันจะดูว่าใน Folder ที่เราเลือกมีไฟล์อะไรบ้าง?
Tips : มันจะ List ทุกไฟล์ใน Folder รวมถึง Sub Folder ทุกอันด้วยนะครับ จะมีคอลัมน์ให้เรา Filter ได้อยู่ด้านขวา
คัดเลือกให้เหลือเฉพาะไฟล์ที่เกี่ยวข้อง
ในที่นี้เราจะ Filter ตามนามสกุลของไฟล์ว่าต้องเป็น File ที่ต้องการก่อน สมมติว่าผมต้องการไฟล์ csv ที่มีคำว่า hora ซึ่งสุดท้ายผม Filter ให้เหลือแต่ไฟล์ csv ที่ผมต้องการได้แล้ว
Tips : ก่อนจะ Filter อย่าลืมแปลง Extension เป็น lowercase ด้วยเพื่อความปลอดภัย เผื่อในอนาคตใส่นามสกุลไฟล์เป็นตัวพิมพ์ใหญ่มาจะได้ใช้ได้
หากการ Filter ที่ชื่อไฟล์กับ Extension ไม่เพียงพอ ลองกดที่ Attribute ดูจะเห็นว่ามันดึงรายละเอียดของ File แต่ละอันมาอีกหลายเรื่องมากๆ ซึ่งเราสามารถนำใช้เป็นตัว Filter ได้ด้วยครับ
รวมข้อมูลแบบ Auto
เราสามารถรวมข้อมูลแบบ Auto ได้โดยการกดที่ลูกศรคู่ ตรงคอลัมน์ที่ชื่อว่า Content เพื่อ Combine File ได้เลย เหมาะกับไฟล์ที่มีหน้าตาเหมือนๆ กัน ดังนั้นเหมาะมากกับการดึงไฟล์ CSV ทั้ง Folder ครับ
มันจะมีหน้าต่างขึ้นมาถามว่าจะให้พยายามดูไฟล์ไหนเป็นตัวอย่าง (เหมาะกับกรณีที่บางไฟล์มีหน้าตาดูดีกว่าไฟล์อื่น) ถ้าไม่คิดอะไรมากก็ปล่อยเป็น First File แล้วปรับค่าให้เหมาะสมแล้ว Ok
ซึ่งเมื่อ OK เจ้า Power Query จะทำการรวมทุกไฟล์เข้าด้วยกัน ซึ่งมันบอกด้วยว่า Data แต่ละแถวมาจากไฟล์ชื่อว่าอะไร
ทีนี้เราก็สามารถดัดแปลงข้อมูลต่อได้ตามที่ต้องการ เช่น จะลบคอลัมน์ที่ไม่ต้องการทิ้ง หรือจะ Filter อะไรก็ทำได้
Power Query จะทำการสร้าง Query หลายๆ ตัวขึ้นมาเองโดยอัตโนมัติ เพื่อจะทำการ Transform ให้เหมาะสมตามไฟล์ตัวอย่างที่เราเลือกไป โดยใช้หลักการของ Custom Function ซึ่งเป็นเรื่องที่ค่อนข้าง Advance ซึ่งผมจะมีการพูดถึงอีกทีตอนหลังครับ
กรณีที่เราอยากจะแก้วิธีการ Transform ของ Sample File ที่ Power Query ทำ เราสามารถไปแก้ Query ที่ชื่อ Transform Sample File ได้เลย แล้วมันจะส่งผลมาที่ Query ผลลัพธ์สุดท้ายหลังรวมไฟล์เองครับ
ยกตัวอย่างเช่น ผมไปลบคอลัมน์ที่ไม่ต้องการทิ้งซะ รวมถึงมีการเปลี่ยนประเภทข้อมูลวันที่ ใน Transform Sample File ดังนี้
จากนั้นกลับไปดูที่ Query MyHora แล้วลบ Step Changed Type ออก (เพราะเราลบ Column ไปเพียบเลย) ให้เหลือสุดท้ายคือ Step Expanded Table Column จะเห็นว่าผลลัพธ์สุดท้ายที่ออกมามันจะเหลือแค่ 3 คอลัมน์ และ Start Date ได้ถูกเปลี่ยนเป็นวันที่เรียบร้อยแล้ว
ดังนั้นการ Transform ข้อมูลสามารถทำได้ 2 จุดหลักๆ คือ ทำในไฟล์ตัวอย่าง (Transform Sample File) หรือ ทำหลังจากรวมทุกอย่างแล้ว (หลัง Expanded Table Column) ก็ได้ครับ
ทีนี้ลอง Load ผลลัพธ์ออกมาเป็น Table ดู
จากนั้นลอง Copy ไฟล์ปี 2562 ที่ชื่อ myhora-holiday-calendar-2562.csv เข้าไปใน Folder เดิมที่เคยมีแค่ปี 2556-2561 แล้วกด Refresh ที่ Table ผลลัพธ์ดูครับ
จะเห็นว่าข้อมูลไฟล์ใหม่ของปี 2562 ได้เข้ามาในตารางเรียบร้อยแล้ว นี่แหละคือความสุดยอดของการดึงข้อมูลจาก Folder ครับ!!
การใช้วิธี Auto กับไฟล์ Excel
หากข้อมูลเป็นไฟล์ Excel ให้เราเลือก Sample File เป็นตัวหลัก ที่มี icon Folder อย่าไปเลือกที่ชื่อ Sheet เพราะว่ามันจะเอาเฉพาะข้อมูลใน Sheet ที่เราเลือกเท่านั้น (และกรณีชื่อ Sheet ไม่เหมือนกันจะมีปัญหาอีก)
พอกด OK มันจะ List ข้อมูล Object ทั้งหมดใน Folder นั้นมาให้จากทุก File แต่มันดันลบชื่อไฟล์ทิ้งไปด้วยซะงั้น
ให้เราย้อนลบ Step หลังๆ ทิ้งให้หมด จนให้เหลือแต่ Remove Other Column แล้วเลือกเอา Name กลับมา
จากนั้นค่อยกดลูกศรขยายเพื่อ Expand คอลัมน์ Transform File จะได้ดังนี้
แล้วเราค่อย Expand คอลัมน์ Data ออกมาอีกที ซึ่งคราวนี้จะมี Data จริงๆ แล้ว
จะเห็นว่าชื่อหัวตารางยังคงอยู่ใน Row ของ Data ปกติอยู่ (ซึ่งมีหลายแถวด้วย) ดังนั้นเราต้องมานั่งแก้ไขเรื่องนี้อีก เช่น เอาบรรทัดแรกเป็นหัวตารางซะด้วย Home → Use First Row As Header
จากนั้นต้องมาหาทาง Filter เอาแถวที่เป็นหัวตารางข้างล่างทิ้งอีก เช่น Filter คอลัมน์สินค้า เอาคำว่า สินค้า ออกไป
ปัญหายังไม่จบเพราะชื่อคอลัมน์ Sales ก อันนี้ก็ผิดอีก และหากไฟล์แรกไม่ใช่ sales ก Query ก็จะพังใน Step ที่เราสั่งเปลี่ยนชื่อคอลัมน์อีก…
จะเห็นว่าวิธีการกด Combine แบบ Auto นี้ไม่ค่อยเหมาะกับไฟล์ Excel เอาซะเลยครับ งั้นมาดูอีกวิธีดีกว่า
รวมข้อมูลแบบสร้าง Custom Column
ถ้าจะรวมไฟล์ Excel ผมแนะนำให้เราสร้าง Custom Column ขึ้นมาครับ
โดยใช้คำสั่ง =Excel.Workbook([Content],true) เพื่อดึงข้อมูลประเภท Excel จากคอลัมน์ชื่อ Content ซึ่งเก็บข้อมูลทั้งไฟล์ไว้ครับ
หมายเหตุ : true ในวงเล็บ คือ การให้ Promote Header ในแต่ละไฟล์ ซึ่งจะช่วยแก้ปัญหาเรื่องหัวตารางที่เราเจอตอนรวมไฟล์แบบ Auto ได้ครับ
คอลัมน์ Custom จะออกมาเป็น Table ซึ่งให้เรากด Expand ออกมาให้หมดครับ
สิ่งที่ออกมามีจุดที่ต้องสนใจ 2 อัน คือ Kind กับ Data ครับ
- Kind (ที่อยู่ใน Custom) จะเป็นตัวบอกว่า Object นั้นๆ เป็นแบบไหน เช่น Sheet หรือ Table ซึ่งเรา Filter ให้เหลือเฉพาะสิ่งที่ต้องการได้ครับ
- Data (ที่อยู่ใน Custom) จะเก็บข้อมูลจริงๆ ไว้ ซึ่งเรา Expand ได้อีกครับ
ซึ่งคราวนี้ข้อมูลทั้งหมดจะออกมา โดยที่เราจะรู้ด้วยว่ามาจากไฟล์ไหน และ Sheet หรือ Table ชื่อว่าอะไร
สำหรับคอลัมน์ Content ที่เป็น Binary เราไม่ใช้แล้ว (เพราะเอา Excel.Workbook ดึงข้อมูลออกมาแล้ว) ก็ลบทิ้งได้เลยครับ แค่นี้เราก็สามารถดึงข้อมูล Excel ทุก File ทุก Sheet หรือทุก Table ได้แล้ว!!
บทความนี้มีที่มายังไง?
บทความนี้เป็นส่วนหนึ่งของตัวอย่างจาก หนังสือ Excel Power Up! เพิ่มพลังการใช้ Excel ของคุณด้วย Power Query โดยผมเอาเนื้อหาบทแรกๆ ซัก 25-30% มาลงในเว็บให้อ่านกันฟรีๆ เลย คนอ่านจะได้ตัดสินใจได้ว่าอยากจะรู้เรื่องราวหลังจากนั้นอีกมั้ย? ซึ่งแค่นี้ก็น่าจะช่วยงานคุณได้เยอะพอสมควรแล้วล่ะ
หากสนใจอ่านตัวอย่างบทอื่นๆ ของหนังสือ ลองดูที่สารบัญข้างล่างได้เลยครับ ^^
สารบัญ Power Query
บทนำ : ทำไมต้องเรียนรู้ Power Query? [ไฟล์ประกอบ]
บทที่ 1 : เข้าใจขั้นตอนการทำรายงานสรุป / วิเคราะห์ข้อมูล [ไฟล์ประกอบ]
บทที่ 2 : ก้าวสู่การเตรียมข้อมูลยุคใหม่ด้วย Power Query [ไฟล์ประกอบ]
บทที่ 3 : ภาพรวมการทำงานกับ Power Query [ไฟล์ประกอบ]
บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query [ไฟล์ประกอบ]
บทที่ 5 : การจัดการหัวตาราง [ไฟล์ประกอบ]
บทที่ 6 : การคำนวณเบื้องต้น [ไฟล์ประกอบ]
บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ [ไฟล์ประกอบ]
บทที่ 8 : การสร้างคอลัมน์ใหม่แบบกำหนดเองด้วย Custom Column [ไฟล์ประกอบ]
บทที่ 9 : การสร้างคอลัมน์ใหม่ตามเงื่อนไข [ไฟล์ประกอบ]
บทที่ 10 : การรวมกลุ่มข้อมูลด้วย Group By [ไฟล์ประกอบ]
บทที่ 11 : การพลิกคอลัมน์เป็นหัวตารางด้วย Pivot Column [ไฟล์ประกอบ]
บทที่ 12 : การยุบหัวตารางหลายคอลัมน์ให้เหลือคอลัมน์เดียวด้วย Unpivot [ไฟล์ประกอบ]
บทที่ 13 : การแยกข้อมูลในคอลัมน์เดียวออกจากกันด้วย Split Column [ไฟล์ประกอบ]
บทที่ 14 : การใช้ Query เป็นตัวแปร [ไฟล์ประกอบ]
บทที่ 15 : การรวมข้อมูลจากหลาย Query [ไฟล์ประกอบ]
บทที่ 16 : การดึงข้อมูลจาก Excel ไฟล์อื่น [ไฟล์ประกอบ]
บทที่ 17 : การดึงข้อมูลจาก Text File/ CSV File [ไฟล์ประกอบ]
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder [ไฟล์ประกอบ]
บทที่ 19 : การดึงข้อมูลจากแหล่งอื่นๆ
บทที่ 20 : การเตรียม Data เพื่อทำ Dashboard
บทที่ 21 : การทำ Pivot Table เพื่อสร้าง Dashboard
บทที่ 22 : เจาะลึก M Code หัวใจของ Power Query
บทที่ 23 : Function คือ ขุมพลังที่แท้จริงของ M Code [ไฟล์ประกอบ]
บทที่ 24 : ตัวอย่างการสร้าง Custom Function [ไฟล์ประกอบ]
บทที่ 25 : การวน Loop [ไฟล์ประกอบ]
บทส่งท้าย : เทพที่แท้จริง
อ่านเนื้อหาบท 22 เป็นต้นไปแบบปรับปรุงใหม่ได้ฟรี ที่นี่ (อัปเดทเรื่อยๆ)
Facebook Group : Power Query Thailand
ผู้ที่สนใจ Power Query อย่างคุณที่มาอ่านบทความนี้ ผมขอเชิญชวนเข้ากลุ่มปิด Power Query Thailand ได้ตาม Link นี้ครับ
