ใช้ Power Query เปลี่ยนข้อมูล PivotTable เป็น ข้อมูลที่พร้อมใช้ ด้วยคำสั่ง Unpivot

สายงานที่ทำงานเกี่ยวการวิเคราะห์ข้อมูล ที่ใช้ข้อมูลจากหลายแหล่ง ทั้งจาก Excel, Access, Web หรือระบบ ERP ต่างๆ เป็นต้น ซึ่งแน่นอนว่าต้องรู้จักเครื่องมือ Power Query (เป็นเครื่องมือที่มีทั้ง Excel และ Power BI Desktop) ซึ่งวันนี้ทางทีมงาน 9Expert จะมาแนะนำวิธีการทำ Data preparation (การเตรียมข้อมูล) กับข้อมูลที่ได้มีการทำสรุปผลบางอย่างมาแล้ว เป็นแนวนอนหรือง่ายๆ เปรียบเหมือนทำ PivotTable มาแล้ว ให้เป็นแนวตั้งหรือเป็นแถวแนวตารางฐานข้อมูล (ข้อมูลที่พร้อมใช้) เพื่อไปใช้ในการสรุปประมวลผลในด้านอื่นๆ ต่อได้ จากรูปแสดงข้อมูล A เป็นข้อมูลสรุปผลยอดขายรายไตรมาสแยกตามปี และตามหน่วยธุรกิจ โดยเป็นข้อมูลในลักษณะแนวนอนหรือเป็น PivotTable และเมื่อแปลงเป็นแนวตั้ง จะได้ข้อมูล B ในแนวตั้งหรือแถวที่แสดงข้อมูลยอดขายเรียงลำดับปี, หน่วยธุรกิจ และไตรมาสตามลำดับ

การแปลงตารางแนวนอน เป็น แนวตั้ง (Unpivot)

ซึ่งวิธีการทำปรับแก้ไขข้อมูลจากข้อมูล A ให้เป็นข้อมูล B จะใช้คำสั่งใน Power Query Editor ที่เรียกว่า Unpivot columns ซึ่งมีขั้นตอนดำเนินการทำดังต่อไปนี้

1. คลิกเลือกตารางที่ต้องการแปลงเป็นแนวตั้ง และไปที่แถบ Data ที่ส่วน Get & Transform Data เลือก From Table/Range

ขั้นตอนการเลือกตารางนำเข้า Power Query Editor

จากนั้น Excel จะขึ้นกล่องข้อความแจ้งว่า Create Table ให้คลิก OK (หากตารางข้อมูลมีการทำเป็น Table แล้วจะไม่แสดงกล่องข้อความแจ้งดังกล่าว)

Create Table จาก Range ที่เลือกเข้า Power Query Editor

2. จากนั้นจะแสดงหน้าต่าง Power Query Editor พร้อมกับหน้าตารางข้อมูลที่เลือกมา โดยเลือกคอลัมน์ยอดขายข้อมูล Q1, Q2, Q3, Q4 ที่ต้องการจะเปลี่ยนจากแนวนอนให้เป็นแนวตั้ง หรือ Unpivot column นั้นเอง ไปที่แถบ Transform ในส่วน Any Column คลิก Unpivot Columns

หลังจากนั้นตารางข้อมูลยอดขายรายไตรมาสจะแปลงจากข้อมูลในแนวนอนมาเป็นแนวตั้ง ตามรูป

ผลของการ Unpivot Columns ใน Power Query Editor

3. ทำการเปลี่ยนชื่อคอลัมน์หลัง Unpivot จาก Attribute เป็น Quarter และ Value เป็น Sales โดยการคลิกขวาที่คอลัมน์ต้องการเปลี่ยนชื่อ แล้วเลือก Rename

วิธีการเปลี่ยนชื่อคอลัมน์ (Column) ใน Power Query Editor

4. ทำการ Sort ข้อมูลตารางตามต้องการ โดยในที่นี้จะทำการเรียงข้อมูลเป็นรายปี, หน่วยธุรกิจ และไตรมาส ตามลำดับ ซึ่งเรียงแบบ Sort Ascending (เรียงจากน้อยไปหามาก) สามารถทำโดยการเลือกคอลัมน์ที่ต้องเรียงลำดับ จากนั้นไปที่แถบ Home ในส่วน Sort เลือก Sort Ascending

การเรียงข้อมูลจากน้อยไปหามาก (Sort Ascending) ใน Power Query Editor

5. เมื่อทำการ Data preparation ในส่วน Unpivot column และปรับแก้ไขรายละเอียดของตาราง เรียบร้อย จะทำการนำข้อมูลนี้ Load ออกมาใส่ในตาราง Excel โดยไปที่แถบ Home ในส่วน Close ให้คลิก Close & Load To…

การ Load ข้อมูลจาก Power Query Editor ไปใส่ในตารางใน Excel

6. จากนั้นจะมาที่หน้าต่าง Excel พร้อมขึ้นกล่องข้อความ Import Data ซึ่งจะเป็นการระบุว่าข้อมูลที่ทำ Data preparation ไปไว้ส่วนไหน ในที่นี้เลือก Table และเลือกตำแหน่งที่จะว่างข้อมูลที่ Cell J2 จากนั้นคลิก OK

หน้าต่าง Import Data เพื่อเลือกตำแหน่งการวางข้อมูลที่นำออกจาก Power Query Editor

ซึ่งจะได้ข้อมูลข้อมูลยอดขายที่เป็นในแนวตั้ง พร้อมเรียงลำดับรายปี, หน่วยธุรกิจ และรายไตรมาสตามลำดับ

แสดงผลลัพธ์ของการแปลงข้อมูลจากแนวนอน เป็นแนวตั้ง ด้วย Unpivot ใน Power Query Editor

จะเห็นว่าหากเรารู้เครื่องมือในการทำ Data preparation (การเตรียมข้อมูลให้พร้อมใช้) ด้วย Power Query Editor นั้น ก็จะทำให้เราสามารถนำข้อมูลจากหลายๆ แหล่งที่ต้องการมาใช้ในการทำงานต่อได้อย่างง่ายได้ อย่างครั้งนี้จะเห็นว่า ถึงแม้ข้อมูลจะถูกจัดในรูปแบบแนวนอนแบบสรุปผลเป็น PivotTable มาแล้ว เรายังสามารถ Unpivot ให้เป็นข้อมูลแนวตั้ง โดยเครื่องมือ Power Query Editor ด้วยคำสั่ง Unpivot columns เพียงกดไม่กี่ขั้นตอนก็สามารถได้ผลลัพธ์ที่ต้องการออกมาแล้ว