Thep Excel
Table.Unpivotแปลงคอลัมน์ให้เป็นแถว (Unpivot)
Table.Unpivot แปลงคอลัมน์ที่ระบุให้กลายเป็นแถว (Unpivot) โดยสร้าง 2 คอลัมน์ใหม่ คอลัมน์แรกเก็บชื่อคอลัมน์เดิม (Attribute) และคอลัมน์ที่สองเก็บค่า (Value) ฟังก์ชันนี้จะข้ามค่า null โดยอัตโนมัติ เหมาะสำหรับเตรียมข้อมูลแบบ Wide Format ให้กลายเป็น Long Format สำหรับใช้ใน Power BI หรือ Pivot Table
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| table | table | ตารางต้นทางที่ต้องการแปลงข้อมูล |
| pivotColumns | list | List ของชื่อคอลัมน์ที่ต้องการ unpivot (แปลงเป็นแถว) เช่น {“Jan”, “Feb”, “Mar”} |
| attributeColumn | text | ชื่อคอลัมน์ใหม่ที่จะเก็บชื่อคอลัมน์เดิม (Attribute) |
| valueColumn | text | ชื่อคอลัมน์ใหม่ที่จะเก็บค่าข้อมูล (Value) |
Additional Notes
Table.Unpivot ใช้สำหรับแปลงข้อมูลจากรูปแบบ Wide (หลายคอลัมน์) ให้เป็นรูปแบบ Long (หลายแถว) โดยจะนำชื่อคอลัมน์ที่ระบุมาเก็บไว้ใน Attribute Column และนำค่าในคอลัมน์เหล่านั้นมาเก็บไว้ใน Value Column
.
ที่เจ๋งคือฟังก์ชันนี้จะ skip ค่า null ออกไปโดยอัตโนมัติ ทำให้ข้อมูลที่ได้สะอาดและพร้อมใช้งานทันที ไม่ต้องมา filter ค่าว่างทีหลังครับ
.
ส่วนตัวผมใช้ Table.Unpivot บ่อยมากตอนต้องเตรียมข้อมูลสำหรับ Power BI หรือทำ Pivot Table เพราะ Data Model ส่วนใหญ่ต้องการข้อมูลแบบ Long Format ถึงจะทำงานได้ดี 😎
How it works
เตรียมข้อมูลสำหรับ Power BI Data Model
แปลงข้อมูลยอดขายรายเดือนที่มีคอลัมน์ Jan, Feb, Mar… ให้กลายเป็นคอลัมน์ Month และ Sales เพื่อใช้ใน Data Model
Normalize ข้อมูลจาก Excel Spreadsheet
แปลงข้อมูลที่ออกแบบมาให้อ่านง่าย (Wide Format) ให้อยู่ในรูปแบบที่เหมาะสำหรับ Database หรือ Analysis
เตรียมข้อมูลสำหรับ Pivot Table
แปลงข้อมูลให้อยู่ในรูปแบบ Long Format ก่อนนำไปสร้าง Pivot Table ใน Excel
Examples
ตัวอย่างที่ 1: Unpivot ข้อมูลยอดขายรายปีlet // สร้างตารางยอดขายรายปี (Wide Format) Source = Table.FromRecords({ [Region = "North", Y2022 = 100, Y2023 = 120], [Region = "South", Y2022 = 200, Y2023 = 25…
ตารางเดิมมี 3 แถว 3 คอลัมน์ พอ unpivot แล้วจะได้ 6 แถว 3 คอลัมน์ โดย Region ยังคงอยู่ ส่วนคอลัมน์ Y2022 และ Y2023 ถูกแปลงเป็นแถวใน Year และค่าไปอยู่ใน Sales ครับ
| Region | Year | Sales |
|——–|——-|——-|
| North | Y2022 | 100 |
| North | Y2023 | 120 |
| South | Y2022 | 200 |
| South | Y2023 | 250 |
| East | Y2022 | 150 |
| East | Y2023 | 180 |
ตัวอย่างที่ 2: Unpivot กับค่า null (ข้อมูลไม่ครบ)let // ตารางที่มีค่า null บางช่อง Source = Table.FromRecords({ [Product = "Apple", Jan = 10, Feb = null, Mar = 30], [Product = "Banana", Jan = 20, Feb = 25, Mar…
สังเกตว่า Apple-Feb และ Banana-Mar หายไปเพราะเป็น null ที่เจ๋งคือ Table.Unpivot จะ filter ค่า null ออกให้อัตโนมัติ ไม่ต้องมาทำเองทีหลัง 💡
| Product | Month | Quantity |
|———|——-|———-|
| Apple | Jan | 10 |
| Apple | Mar | 30 |
| Banana | Jan | 20 |
| Banana | Feb | 25 |
ตัวอย่างที่ 3: Unpivot ข้อมูลรายไตรมาสสำหรับ Power BIlet // ข้อมูลยอดขายรายไตรมาส (แบบที่เห็นบ่อยใน Excel) Source = Table.FromRecords({ [ID = 1, Product = "Laptop", Q1 = 500, Q2 = 600, Q3 = 550, Q4 = 700], [ID = 2…
รูปแบบนี้เหมาะสำหรับ Power BI มากครับ เพราะสามารถสร้าง Date Table แยก แล้ว join กับคอลัมน์ Quarter ได้ ทำให้วิเคราะห์แนวโน้มรายไตรมาสได้ง่าย
| ID | Product | Quarter | Revenue |
|—-|———-|———|——–|
| 1 | Laptop | Q1 | 500 |
| 1 | Laptop | Q2 | 600 |
| …| … | … | … |
| 3 | Keyboard | Q4 | 100 |
ตัวอย่างที่ 4: เปรียบเทียบ Table.Unpivot vs Table.UnpivotOtherColumnslet // ตารางต้นฉบับ Source = Table.FromRecords({ [Store = "A", Manager = "John", Jan = 100, Feb = 120, Mar = 110] }), // วิธีที่ 1: Table.Unpivot – ระบุคอลัมน์ท…
ทั้ง 2 วิธีให้ผลลัพธ์เหมือนกัน แต่เลือกใช้ต่างกันตามสถานการณ์:
– ใช้ Table.Unpivot เมื่อรู้แน่ชัดว่าคอลัมน์ไหนจะ unpivot (เช่น Jan, Feb, Mar)
– ใช้ Table.UnpivotOtherColumns เมื่อคอลัมน์อาจเพิ่มขึ้นในอนาคต (เช่น มี Apr, May เพิ่มมา) 😎
| Store | Manager | Month | Sales |
|——-|———|——-|——-|
| A | John | Jan | 100 |
| A | John | Feb | 120 |
| A | John | Mar | 110 |
FAQs
Table.Unpivot กับ Table.UnpivotOtherColumns ต่างกันอย่างไร?+
Table.Unpivot ต้องระบุคอลัมน์ที่จะ unpivot โดยตรง ส่วน Table.UnpivotOtherColumns ต้องระบุคอลัมน์ที่จะ keep ไว้แทน ถ้าข้อมูลมีคอลัมน์เพิ่มมาใหม่บ่อยๆ (เช่น เดือนใหม่) แนะนำใช้ UnpivotOtherColumns ครับ เพราะไม่ต้องมาแก้ code ทุกครั้ง
ทำไมบางแถวหายไปหลัง Unpivot?+
Table.Unpivot จะข้ามค่า null โดยอัตโนมัติครับ ถ้าต้องการเก็บ null ไว้ด้วย ให้เปลี่ยนค่า null เป็นค่าอื่นก่อน (เช่น 0 หรือ "N/A") ด้วย Table.ReplaceValue แล้วค่อย unpivot
จะ Unpivot กลับเป็น Pivot ได้อย่างไร?+
ใช้ฟังก์ชัน Table.Pivot ครับ โดยระบุ Attribute Column เป็นคอลัมน์ที่จะกระจายเป็น header และ Value Column เป็นค่าที่จะใส่ในตาราง ถ้าต้องการ aggregate ค่า ให้ใส่ aggregation function เป็น argument สุดท้าย
Unpivot แล้วลำดับคอลัมน์เปลี่ยน ทำอย่างไร?+
ใช้ Table.ReorderColumns หลัง unpivot เพื่อจัดลำดับคอลัมน์ใหม่ได้ครับ เช่น Table.ReorderColumns(Unpivoted, {"ID", "Name", "Attribute", "Value"})
Unpivot คอลัมน์ที่มี Data Type ต่างกันได้ไหม?+
ได้ครับ แต่ค่าใน Value Column จะถูกแปลงเป็น type any ทำให้ต้องมา cast type ใหม่ทีหลัง แนะนำให้ unpivot คอลัมน์ที่มี type เดียวกันจะดีกว่า
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ Table.Unpivot จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
Table.ColumnNames ดึงชื่อคอลัมน์ทั้งหมดจากตาราง และส่งกลับมาเป็นลิสต์ข้อความ ซึ่งจำเป็นสำหรับการตรวจสอบ schema แบบ dynamic
Table.FromRows สร้างตารางจาก List ของ List โดยแต่ละ List ย่อยแทนข้อมูลในแถวหนึ่ง คล้ายกับการ transpose ข้อมูลแบบแถว-คอลัมน์ แต่ทำให้เป็นตารางจริงในทันที
Table.Pivot แปลงข้อมูล long format (แนวตั้ง) เป็น wide format (แนวนอน) โดยหมุนค่า attribute ให้เป็นคอลัมน์ใหม่
Table.Transpose จะกลับด้านตาราง คล้ายกับการ Copy > Paste Special > Transpose ใน Excel โดยข้อมูลในแถวที่ 1 จะกลายเป็นคอลัมน์ที่ 1 แถวที่ 2 เป็นคอลัมน์ที่ 2 ไปเรื่อยๆ
Table.UnpivotOtherColumns แปลงคอลัมน์ที่ไม่ได้ระบุทั้งหมดให้กลายเป็นแถว (Unpivot) โดยระบุเฉพาะคอลัมน์ที่ต้องการคงไว้ ข้อดีคือเป็น Dynamic ถ้ามีคอลัมน์ใหม่เพิ่มเข้ามาในอนาคตก็จะถูก Unpivot ให้อัตโนมัติ เหมาะสำหรับแปลงข้อมูลจาก Wide Format (หลายคอลัมน์) ให้เป็น Long Format (หลายแถว) สำหรับ PivotTable และ Database
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่