- Power Query ช่วยให้คุณสามารถรวมไฟล์หลายไฟล์จากโฟลเดอร์ในเครื่องหรือบนคลาวด์ได้ ตราบใดที่ไฟล์เหล่านั้นมีโครงสร้างข้อมูลเดียวกัน
- การใช้ไฟล์ตัวอย่างช่วยให้การแปลงข้อมูลจำนวนมากทำได้ง่ายขึ้น เช่น การลบแถวบนสุดหรือการเปลี่ยนส่วนหัว
- มีข้อจำกัดเมื่อคอลัมน์ไม่ตรงกันอย่างแม่นยำ ซึ่งจำเป็นต้องแก้ไขด้วยตนเอง หรือใช้สคริปต์ VBA สำหรับกรณีที่ซับซ้อน
ลองนึกภาพว่าคุณมีรายงานรายเดือนหรือรายสัปดาห์จำนวนมากกระจัดกระจายอยู่ในโฟลเดอร์ต่างๆ และแทนที่จะเสียเวลาช่วงเช้าไปกับการคัดลอกและวางข้อมูลอย่างวุ่นวาย คุณสามารถรวมทุกอย่างเข้าไว้ในสเปรดชีต Excel เดียวได้ด้วยการคลิกเพียงไม่กี่ครั้ง นี่คือจุดที่ Power Query เข้ามามีบทบาท เครื่องมือที่เปรียบเสมือนมือขวาของทุกคนที่ทำงานกับข้อมูล ช่วยให้เราสามารถรวมไฟล์ที่มีโครงสร้างเดียวกันได้อย่างง่ายดาย
สิ่งที่เจ๋งที่สุดเกี่ยวกับระบบนี้คือ มันไม่จำกัดคุณอยู่แค่โฟลเดอร์ในเครื่องพีซีของคุณ คุณสามารถดึงข้อมูลจากไซต์ SharePoint, Azure Data Lake หรือ Azure blobsได้ กุญแจสำคัญที่จะทำให้ระบบนี้ทำงานได้อย่างราบรื่นคือ ไฟล์ต้องมีโครงสร้างข้อมูล (schema) เดียวกัน นั่นหมายความว่าคอลัมน์ต้องตรงกัน แม้ว่า Power Query จะฉลาดพอที่จะช่วยทำความสะอาดข้อมูลหากมีข้อมูลที่ไม่ถูกต้องหรือแถวที่ไม่จำเป็นอยู่ก็ตาม
กระบวนการผสมผสานทีละขั้นตอน
เมื่อคุณเชื่อมต่อกับแหล่งข้อมูล สิ่งแรกที่คุณจะเห็นคือมุมมองระบบไฟล์ ในที่นี้ คอลัมน์ที่สำคัญที่สุดคือเนื้อหา (Content) ซึ่งประกอบด้วยค่าไบนารีของเอกสารแต่ละฉบับ หากคุณคลิกที่ค่าไบนารีเหล่านี้ โปรแกรมจะพยายามตรวจสอบว่าเป็นไฟล์ CSV, JSON หรือ Excel เพื่อเปิดไฟล์นั้น สำหรับการรวมไฟล์ทั้งหมด คุณมีสองตัวเลือก: คลิกปุ่ม ผสาน (Merge) โดยตรง หรือหากคุณต้องการความละเอียดรอบคอบมากขึ้น ให้เลือกแปลงข้อมูล (Transform Data) เพื่อกรองรายการไฟล์ (เช่น เพื่อไม่รวมโฟลเดอร์เฉพาะ) ก่อนดำเนินการผสาน
เมื่อคุณเข้าสู่กล่องโต้ตอบการผสานข้อมูล Power Query จะใช้ไฟล์ตัวอย่างเพื่อวิเคราะห์โครงสร้างโดยค่าเริ่มต้น ระบบจะเลือกไฟล์แรกในรายการ แต่คุณสามารถเปลี่ยนได้หากมีไฟล์อื่นที่เหมาะสมกับชุดข้อมูลมากกว่า นอกจากนี้ คุณยังสามารถเลือกตัวเลือกเพื่อข้ามไฟล์ที่มีข้อผิดพลาดซึ่งเป็นประโยชน์อย่างมากเมื่อรายงานเสียหายหรือว่างเปล่า ป้องกันไม่ให้กระบวนการทั้งหมดหยุดชะงัก
การจัดการผลลัพธ์และการทำความสะอาดข้อมูล
หลังจากยอมรับการรวมแล้ว ระบบจะทำการประมวลผลทางเทคนิคบางอย่างเบื้องหลัง: มันจะสร้างแบบสอบถามตัวอย่างและฟังก์ชันที่มีพารามิเตอร์ ซึ่งหมายความว่าการเปลี่ยนแปลงใดๆ ที่คุณทำกับไฟล์ตัวอย่างจะถูกทำซ้ำไปยังไฟล์อื่นๆ ทั้งหมดในโฟลเดอร์โดยอัตโนมัติ ตัวอย่างเช่น หากไฟล์ CSV ของคุณมีส่วนหัวที่ไม่จำเป็นสี่แถวก่อนข้อมูลจริง คุณสามารถไปที่แบบสอบถามการแปลงและใช้ตัวเลือกในการลบแถวบนสุดเพื่อล้างข้อมูลที่ไม่จำเป็นเหล่านั้นออกได้
หลังจากทำความสะอาดแถวแล้ว สิ่งสำคัญคือต้องใช้ฟังก์ชัน " ใช้แถวแรกเป็นส่วนหัว"โปรดจำไว้ว่าเมื่อคุณทำเช่นนี้ Power Query มักจะเพิ่มขั้นตอนการแปลงประเภทข้อมูลโดยอัตโนมัติ หากเมื่อกลับไปยังคิวรีหลัก คุณเห็นข้อผิดพลาดที่ระบุว่าไม่พบคอลัมน์ 'Column1' ไม่ต้องกังวล นี่เป็นเรื่องปกติเนื่องจากคุณได้เปลี่ยนชื่อคอลัมน์ในตัวอย่างและขั้นตอนถัดไปไม่รู้จักชื่อเดิมอีกต่อไป เพียงลบขั้นตอนที่ผิดพลาดนั้นออกจากบานหน้าต่างขั้นตอนที่ใช้แล้ว และทุกอย่างจะกลับสู่สภาวะปกติ
ความท้าทายทั่วไปและทางเลือกขั้นสูง
บางครั้งชีวิตก็ไม่ได้ง่ายอย่างนั้น และเราอาจเจอกับไฟล์ที่ไม่เหมือนกัน ตัวอย่างเช่น หากคุณมีรายงาน ServiceNow ที่ส่วนหัวหนึ่งชื่อว่า 'resolved_on' และอีกส่วนหัวหนึ่งชื่อว่า 'closed_on' Power Query อาจทำให้เกิดปัญหาได้ เพราะมันต้องการโครงสร้างที่สอดคล้องกันหากไฟล์ตัวอย่างไม่ตรงกับส่วนที่เหลือ เครื่องมืออาจละเว้นข้อมูลจากไฟล์ที่ไม่ตรงกัน หรือแสดงข้อผิดพลาดในการนำเข้า
ในกรณีที่โครงสร้างข้อมูลยุ่งเหยิงมาก หรือปริมาณข้อมูลมหาศาล (เกินหนึ่งล้านแถวในไฟล์ Excel) ก็มีทางเลือกอื่น ผู้ใช้บางรายเลือกใช้สคริปต์ VBA โดยใช้ส่วนต่อประสาน CSVซึ่งช่วยให้สามารถรวมไฟล์ได้โดยไม่ต้องมีการทำซ้ำส่วนหัว ไม่ว่าตัวคั่นฟิลด์หรือตัวคั่นระเบียนจะแตกต่างกันไปในแต่ละไฟล์ก็ตาม แม้ว่า Windows Shell จะทำงานได้เร็วมาก แต่ก็มักจะล้มเหลวในการจัดการกับการขึ้นบรรทัดใหม่ภายในข้อมูล ในขณะที่โซลูชัน VBA ที่มีประสิทธิภาพจะไม่ทำให้ RAM ทำงานหนักเกินไปและสามารถจัดการกับระเบียนนับล้านได้อย่างง่ายดาย
เพื่อให้กระบวนการเสร็จสมบูรณ์ เมื่อรวมข้อมูลใน Power Query แล้ว อย่าลืมกำหนดชนิดข้อมูลที่ถูกต้องให้กับแต่ละคอลัมน์ (เช่น Date สำหรับวันที่, Currency สำหรับรายได้ เป็นต้น) ก่อนที่จะโหลดตารางสุดท้าย คุณสามารถตรวจสอบความถูกต้องได้โดยการกรองคอลัมน์ Source Name เพื่อให้แน่ใจว่าไฟล์ทั้งหมดในโฟลเดอร์ได้รับการประมวลผลเรียบร้อยแล้ว
ความสามารถในการรวมไฟล์ CSV หลายไฟล์โดยอัตโนมัติ ช่วยให้คุณเปลี่ยนจากการทำงานด้วยตนเองที่น่าเบื่อ ไปสู่เวิร์กโฟลว์แบบมืออาชีพ ไม่ว่าจะเป็นการใช้ประโยชน์จากความยืดหยุ่นของ Power Query สำหรับการทำความสะอาดข้อมูล หรือการใช้ VBA เมื่อโครงสร้างไม่เข้ากัน หรือปริมาณข้อมูลเกินขีดจำกัดของสเปรดชีต