ปกติเวลาเราสั่งให้ 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 นะครับ เดี๋ยวมันก็จะระบุชื่อคอลัมน์อีก ให้ทำเฉพาะคอลัมน์ที่จำเป็นเท่านั้น เช่น ตัวเลข วันที่ เวลา เป็นต้น
