บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder

การดึงข้อมูลจากทั้ง Folder มาทีเดียวพร้อมกันได้นี่ ผมคิดว่ามันเป็นสุดยอดแห่งการ Get Data ที่หลายคนปรารถนาเลยล่ะ 

ลองคิดดูว่าหากเราต้องทำ Regular Report ทุกอาทิตย์หรือทุกเดือนแล้วเราสามารถโยนข้อมูลเดือนใหม่ที่ได้ไฟล์มาจากฝ่ายงานต่างๆ เข้าไปใน Folder ที่เราออกแบบไว้แล้ว หลังจากนั้นกด Refresh แล้วรายงานทุกอย่างก็ถูก Power Query + Pivot Table ปั่นออกมาจนเสร็จได้เลยมันจะสุดยอดขนาดไหน!!

ประเด็นที่น่าสนใจในการดึงข้อมูลจาก Folder มีอยู่ 3 เรื่องหลักๆ เลยคือ 

1. มีไฟล์อื่นปนมาหรือไม่ ? : ถ้ามีโอกาสที่จะมีไฟล์อื่นปนมา ก็ควรจะมีการ Filter เลือกเอาเฉพาะสิ่งที่ต้องการเท่านั้น 
2. มีข้อมูลกี่รูปแบบ? : ถ้าข้อมูลมีอยู่หลายรูปแบบ (Pattern) ให้เลือกมาทำทีละรูปแบบ แล้วสุดท้ายค่อยเอามารวมกันหรือเชื่อมกันทีหลัง
3. ต้องการรวมไฟล์แบบ Auto หรือทำแบบสร้าง Custom Column? : แบบ Auto จะง่ายกว่า แต่แบบ Custom Column มีความยืดหยุ่นสูงกว่าครับ
4. ไฟล์ที่ต้องการเป็น 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 ได้แล้ว!!