บทที่ 5 : การจัดการหัวตาราง ใน Power Query

เรื่องพื้นฐานที่ควรจะรู้เลยคือ การจัดการหัวตาราง ไม่ว่างจะเป็นการ Promote บรรทัดแรกให้กลายเป็นหัวตาราง หรือการปรับหัวตารางให้กลับมาเป็น Data บรรทัดแรก รวมถึงการจัดการบรรทัดข้อมูลที่ไม่ต้องการออกไปก่อน กรณีหัวตารางไม่ได้อยู่บรรทัดบนสุด 

บทนี้ให้ Get Data → From Table/Range จากไฟล์ตัวอย่าง (โหลดที่นี่) เพื่อเอาข้อมูลเข้าไปใน Power Query แต่คราวนี้เราไม่ต้องติ๊ก My table has headers เนื่องจากเพราะบรรทัดแรกมันไม่ใช่หัวตาราง

จะได้ Query หน้าตาแบบนี้ ซึ่งจะเห็นว่าหัวตารางจะชื่อ Column1 Column2… ซึ่งไม่ใช่ชื่อหัวตารางที่ควรจะเป็น

สิ่งที่เราควรทำคือ พยายามทำให้ชื่อหัวตารางที่แท้จริงมาอยู่ที่บรรทัดแรกให้ได้ ซึ่งทำได้ 2 วิธี แล้วแต่ความเหมาะสม คือ

1. เลือก Home→ Remove Rows→ Remove Top Rows แล้วใส่เลข 2 จะพบว่า Power Query จะเอา 2 แถวบนออกไปเลย
   
2. เลือกคอลัมน์ที่ข้อมูลในตารางจริงมีครบ แต่ข้างบนว่าง เช่น Column2 แล้วกดที่หัว Filter → Remove Empty (ถ้าไม่มีให้เลือก ต้องกด Filter แล้วเลือกแบบ Not Equal แล้วใส่ว่า null)

ผลลัพธ์จะได้หน้าตาออกมาเหมือนกัน คือ ชื่อหัวตารางที่แท้จริงมาอยู่บรรทัดแรกแล้ว

จากนั้นสิ่งที่เราควรทำคือ Promote ให้ข้อมูลแถวแรกกลายเป็นหัวตารางซะ โดยไปที่ Home → Use First Row as Headers จะได้ดังรูป ซึ่งพบว่ามีการ Detect ประเภทข้อมูลให้อัตโนมัติด้วย (ดูที่ icon ของหัวตาราง) 

ซึ่งสิ่งที่ได้เกือบจะ Perfect แล้ว เหลือแค่คอลัมน์ผลไม้ มันยังมีช่องว่างๆ อยู่ ซึ่งจะเห็นว่าเราควรจะเอาค่าผลไม้ต่างๆ ที่อยู่ด้านบนมาถมลงช่องว่างเหล่านั้นให้หมด เพื่อให้เราสามารถนำข้อมูลผลลัพธ์ไปวิเคราะห์ต่อได้ง่ายขึ้น ไม่ว่าจะเป็นการ Filter/Sort/SUMIFS/Pivot Table เป็นต้น

การถมช่องว่าง

วิธีการถมช่องว่างให้เลือกคอลัมน์ผลไม้ แล้วไปที่ Transform → Fill → Down (เพราะเราจะถมข้อมูลจากบนลงล่าง) แค่นั้นจบเลย ช่างง่ายดายกว่าการใช้ Excel ธรรมดามากนัก 555

แล้วถ้าจะถมไปด้านขวาล่ะ?

Power Query มีแต่ Fill Down กับ Fill Up ซึ่งทำง่ายมาก แต่ไม่มีคำสั่ง Fill Right หรือ Fill Left ให้เราใช้… 

หากเราจะต้องการจะถมข้อมูลไปด้านขวา ก็จะมีเทคนิคในการพลิกข้อมูลจากคอลัมน์เป็นแถวด้วยการ Transpose ซะก่อน แล้วค่อย Fill Down ครับ เช่น ผมมีข้อมูลแบบนี้

พอเอาเข้า Power Query (ไม่ต้องติ๊กว่า Table มี Header นะ) ช่องที่ Merge ก็จะเห็นเป็น null

ซึ่งจะเห็นว่าเราควรจะ Fill Right เพื่อเอาข้อมูลผลไม้ไปถมช่อง null ด้านขวา แต่มันไม่มีให้เลือก

ดังนั้นเราจะพลิกตารางก่อน โดย ไปที่ Transform → Transpose เราจะได้ผลลัพธ์หน้าตาคล้ายๆ ตัวอย่างก่อนหน้านี้ ซึ่งเราก็จะ Fill Down ได้แล้ว และเราควรจะ Promote Header ด้วยโดยไปที่ Home → Use First Row as Headers

ถ้าเราพอใจกับผลลัพธ์นี้จริงๆ ก็จบได้เลยนะ แต่ถ้ายังอยากจะพลิกให้ผลไม้ไปอยู่ที่คอลัมน์เหมือน Data Source เราก็ควรทำให้หัวตารางไม่ซ้ำกัน เราจะเอาข้อมูลในคอลัมน์ผลไม้กับเกรด มา Merge รวมกัน เช่น แอปเปิ้ล A มันจะได้ไม่ซ้ำกัน

จากนั้นเราค่อยเลือกทั้งสองคอลัมน์ แล้วไปที่ Transform → Merge Columns แล้วเราสามารถเลือกตัวคั่นได้ ในที่นี้ผมเลือกเป็น Space

เราจะได้ข้อมูลในคอลัมน์ Merged โดยที่แต่ละตัวไม่ซ้ำกันแล้ว

แล้วเราค่อย Transform → Transpose กลับไปให้หน้าตาเหมือนเดิม

จากนั้นก็ Home → Use First Row as Headers เป็นอันจบครับ

ระวังเรื่องการอ้างอิงชื่อคอลัมน์ในสูตร M Code

อย่างไรก็ตาม จะเห็นว่าการเปลี่ยน Data Type รวมถึงการถมช่องว่างมันมีการอ้างอิงชื่อคอลัมน์ในสูตรด้วย (ซึ่งการ Transform หลายๆ ตัวจะเป็นแบบนี้เช่นกัน) 

การเปลี่ยน Data Type

การ Fill Down

ดังนั้นถ้าหากในอนาคตชื่อคอลัมน์ในตารางต้นทางเปลี่ยนไป Query นี้ก็จะมีปัญหาทันทีครับ

เช่น ผมลองเปลี่ยนชื่อคอลัมน์ ผลไม้ หรือแม้กระทั่งเกรด เป็นชื่ออื่น มันก็จะพัง Refresh ไม่ได้ทันที

หากลองเข้าไป Edit ใน Query ดู จะเก็นว่ามัน Error ตั้งแต่ Step Change Type เลยครับ

จะแก้ไขก็ต้องลบ Step นั้นทิ้งไปซะ และอาจต้องแก้บางอย่างอีก ซึ่งยุ่งยากพอสมควร ดังนั้นการทำงานกับ Power Query โดยทั่วไปแล้วผมแนะนำอย่างยิ่งว่าอย่าไปแก้ชื่อหัวตารางเล่นเด็ดขาดครับ

การเปลี่ยนประเภทข้อมูลใน Power Query

เวลาเราเอาข้อมูลเข้า Power Query ปกติแล้วมันจะ Detect ประเภทข้อมูลของหัวตารางให้เราโดยอัตโนมัติ ซึ่งส่วนใหญ่แล้วมันก็จะทำได้ถูกต้อง อย่างไรก็ตามหากมันทำผิด เราก็ต้องแก้ไขประเภทข้อมูลให้เป็น

สมมติผมเอาข้อมูล Table แบบนี้เข้าใน Power Query โดย Get Data → From Table/Range

จะเห็นว่ามันเปลี่ยน Data Type ให้เราโดยอัตโนมัติ (สังเกตที่สัญลักษณ์ที่ซ้ายมือของชื่อคอลัมน์)

ที่ Applied Step ก็จะมี Change Type โดยอัตโนมัติด้วย

ตรงนี้หากเราอยากให้ ID สินค้า เป็น Text แทนที่จะเป็นตัวเลข ก็สามารถกดเปลี่ยนที่ icon ด้านซ้ายของหัวตารางแต่ละคอลัมน์ที่ต้องการได้เลย 

ซึ่งจะเห็นว่าจริงๆ แล้วมีข้อมูลประเภทอื่นๆ อีกเต็มเลย!!

กลุ่มตัวเลขจะมี 4 แบบ คือ 

• Decimal ทศนิยมแบบละเอียด
• Currency สกุลเงิน โดยมีทศนิยมสูงสุดแค่ 4 ตำแหน่ง
• Whole Number จำนวนเต็ม
• Percentage ก็คือ แสดงให้เห็นเป็นเปอร์เซ็นต์ใน Preview (แต่โหลดออกมาก็เหมือน Decimal)

วันที่และเวลา มี 5 แบบ คือ 

• Date/Time มีทั้งวันที่/เวลา
• Date มีแต่วันที่
• Time มีแต่เวลา
• Date/Time/Timezone จะมีทั้งวันที่/เวลา/timezone เช่น +7:00 คือ GMT+7
• Duration เป็นระยะเวลา แสดงเป็น วัน:ชม:นาที:วินาที (มักเกิดจากการคำนวณ)

นอกนั้นมีอย่างละแบบ

• Text ข้อความ
• True/False เป็นค่า Logic จริง เท็จ
• Binary คือ ตัวข้อมูลกลุ่มโครงสร้างพิเศษ ไม่ได้มีตัวเดียว
• Using Locale เอาไว้จัดการเวลาเจอข้อมูลที่มี Format แปลกๆ ที่ขึ้นอยู่กับแต่ละประเทศ เช่น วันที่ หรือ สกุลเงิน ซึ่งจะอธิบายละเอียดในบทหลังๆ ไปครับ

กรณีที่ Step ก่อนหน้าเคยเปลี่ยน Data Type ไปแล้ว และเรากดเปลี่ยน Data Type อีก (เช่น เปลี่ยน ID สินค้าเป็น Text หลังจาก Changed Type แบบ Auto เป็นตัวเลขไปแล้ว) มันจะขึ้นมาถามว่า จะสร้าง Step ใหม่ หรือ แก้ไข Step เดิม

ถ้าเราตอบว่า Add New Step มันจะมีการแปลงประเภทข้อมูลโดย Add Step ใหม่เพิ่มเข้ามา

แต่ถ้ากด Replace Current มันจะแก้สูตรใน Step เดิม ให้ ID สินค้าเป็น Text โดยไม่เพิ่ม Step ใหม่

สังเกตว่าสูตรเดิมมีการเปลี่ยน 

จาก

= Table.TransformColumnTypes(Source,{{“ID สินค้า”, Int64.Type}…

เป็น

= Table.TransformColumnTypes(Source,{{“ID สินค้า”, type text}…

ส่วนตัวผมขอแนะนำให้ Replace Current จะดีกว่าครับ เพราะบางครั้งการแก้ Data Type ไปแล้วมันอาจสูญเสียข้อมูลบางอย่างไปเรียบร้อย เช่น แก้ให้เป็นจำนวนเต็มก่อน แล้วปรับเป็นทศนิยมทีหลังก็จะไม่มีผล เพราะค่าทศนิยมมันก็หายกลายเป็น 0 ไปหมดแล้ว