วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query

ปกติเวลาเราสั่งให้ Power Query รวมทุกไฟล์ใน Folder เดียวกัน แม้มันจะเอาข้อมูลรวมกันได้ แต่ก็อาจจะมีปัญหาเรื่องชื่อคอลัมน์ได้ในอนาคต

สาเหตุเพราะ มันมีการระบุชื่อคอลัมน์ที่แตกออกมาอย่างชัดเจนในสูตร M Code ทำให้ หากในอนาคตมีคอลัมน์เพิ่มขึ้นหรือลดลง ก็จะมีปัญหาทันที (อย่างเช่นที่ผมบอกไว้ในหนังสือ Excel Power Up ว่า เมื่อไหร่ก็ตามที่มีการระบุชื่อคอลัมน์ในสูตร M Code นั้น จะอันตรายต่อการ Refresh ทันที)

ลองทำดู

โหลดไฟล์ประกอบได้ที่นี่

ตัวอย่างเช่น ผมสั่งรวมข้อมูลจาก Folder ที่ชื่อ MyFolder และสร้าง Custom Column ดังนี้ เพื่อดึงข้อมูลจาก Excel ด้วยสูตรว่า

=Excel.Workbook([Content],true)

ผมแตกคอลัมน์ Custom ออกมาจะได้ดังนี้

เดี๋ยวผมลบคอลัมน์ที่ไม่ใช้ทิ้งก่อน โดยจะเก็บแค่ ชื่อไฟล์ ชื่อ Item และ Data ไว้ โดยเลือกที่จะเอา แล้วสั่ง Remove Other Columns ซะ

ปัญหาเรื่องชื่อคอลัมน์ เกิดขึ้นตอน Expand Data

พอเรากด Expand Data ออกมา จะพบว่าสูตรที่ได้มีการระบุชื่อคอลัมน์ออกมาโต้งๆ เลย ว่า

= Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"}, {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"})

ถ้าเป็นแบบนี้ หากในข้อมูลต้นฉบับบางคอลัมน์หายไป หรือมีคอลัมน์เพิ่มเข้ามา จะมีปัญหาทันที!!

ลองเพิ่ม/ลดคอลัมน์ดู

จะพบว่าถ้าเพิ่มคอลัมน์ เช่น ยอดขาย มันจะไม่ถูดดึงมาด้วย (เพราะสูตรไม่ได้ระบุคอลัมน์นั้น)

ถ้าลบคอลัมน์ซักอันนึง เช่น ผมเอาวิธีการชำระเงินออกออกจากทุกไฟล์ มันก็จะยังมีคอลัมน์ค้างอยู่เป็น null อย่างงั้นเลย (เพราะสูตรมีการระบุคอลัมน์นั้น)

ถ้าปล่อยให้เป็นแบบนี้ การอัปเดทข้อมูลจะยากเกินไป

แก้ปัญหาโดยแก้ M Code เล็กน้อย

เราสามารถทำให้ชื่อ Column มีความ Dynamic ยืดได้หนดได้ โดยต้องแก้บางส่วนของ Code เดิมอันนี้

= Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"}, {"วันที่", "ลูกค้า", "สินค้า", "ราคาต่อชิ้น", "จำนวนชิ้น", "วิธีการชำระเงิน"})

จะเห็นว่า สีแดง มันคือ List ของชื่อคอลัมน์ทั้งหมด

= Table.ExpandTableColumn(#"Removed Other Columns", "Data", List ของชื่อคอลัมน์)

ซึ่ง List ของชื่อคอลัมน์เกิดจากการรวมชื่อคอลัมน์ทุก Table ดังนี้

List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_)))

หลักการคือ เราจะใช้สูตร List.Transform โดยใช้ Table.ColumnNames(_) กับทุกๆ Table เพื่อให้แต่ละ Table คืนค่ามาเป็น List ของหัวตารางใน Table นั้นๆ จากนั้นใช้ List.Union มารวม item ทุกตัวเข้าด้วยกัน

ใครอยากรู้ว่าทำไมสูตรนี้ถึงได้ผล สามารถอ่านได้ที่นี่ https://www.thepexcel.com/m-code-power-query-03-list/

สรุปแล้วเราจะแก้สูตรเป็นดังนี้

= Table.ExpandTableColumn(#"Removed Other Columns", "Data", List.Union(List.Transform(#"Removed Other Columns"[Data],each Table.ColumnNames(_))))

เราก็จะได้ Column ที่ Expand ได้แบบ Dynamic สุดๆ เจ๋งป่ะ!

ผมมั่นใจว่าถ้าเพื่อนๆ ใช้วิธีที่ผมบอกนี้ ตอนอัปเดทข้อมูลชีวิตจะง่ายขึ้นเยอะเลยครับ

สุดท้าย อย่าเผลอไปกด Auto Detect Data Type นะครับ เดี๋ยวมันก็จะระบุชื่อคอลัมน์อีก ให้ทำเฉพาะคอลัมน์ที่จำเป็นเท่านั้น เช่น ตัวเลข วันที่ เวลา เป็นต้น