Thep Excel
Table.AddColumn – เพิ่มคอลัมน์ใหม่ด้วย Calculated Values
Table.AddColumn เพิ่มคอลัมน์ใหม่ลงในตารางโดยคำนวณค่าจากฟังก์ชันที่กำหนด ใช้คำสั่ง each เพื่ออ้างอิงข้อมูลในแต่ละแถว เช่น each [Price] * [Quantity] สามารถระบุชนิดข้อมูลได้เพื่อเพิ่มประสิทธิภาพและป้องกันข้อผิดพลาดจากการคาดเดาชนิดข้อมูล ทำงานแบบทีละแถวและเหมาะสำหรับสร้างคอลัมน์คำนวณ รวมข้อมูล ใช้เงื่อนไข และการแปลงข้อมูลต่างๆ ใช้ร่วมกับฟังก์ชัน Table.TransformColumns สำหรับแก้ไขคอลัมน์เดิม Table.AddIndexColumn สำหรับเพิ่มหมายเลขลำดับ Table.SelectRows สำหรับกรองแถว Table.FromRecords สำหรับสร้างตารางจากข้อมูล Text.Combine สำหรับรวมข้อความ Number.Round สำหรับปัดเศษ Date.Year สำหรับดึงค่าปี และ Text.Upper สำหรับแปลงเป็นตัวพิมพ์ใหญ่ เพื่อสร้างกระบวนการแปลงข้อมูลที่สมบูรณ์
=Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table
Function Metrics
Popularity
10/10
Difficulty
3/10
Usefulness
10/10
Syntax & Arguments
=Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| table | table | Yes | ตารางข้อมูลต้นฉบับที่ต้องการเพิ่มคอลัมน์ใหม่เข้าไป สามารถเป็นตารางที่มาจากแหล่งข้อมูล สร้างด้วย Table.FromRecords หรือมาจากการแปลงข้อมูลขั้นก่อนหน้า | |
| newColumnName | text | Yes | ชื่อคอลัมน์ใหม่ที่ต้องการสร้างขึ้นมา ต้องเป็นข้อความและไม่ซ้ำกับชื่อคอลัมน์ที่มีอยู่แล้วในตาราง หากชื่อซ้ำกันจะเกิดข้อผิดพลาด | |
| columnGenerator | function | Yes | ฟังก์ชันสำหรับคำนวณค่าในแต่ละแถว โดยส่วนใหญ่ใช้คำสั่ง each เพื่ออ้างอิงคอลัมน์ในแถวปัจจุบัน ตัวอย่างเช่น each [Price] + [Tax] คำสั่ง each เป็นรูปแบบย่อสำหรับ (_) => expression โดย _ คือแถวปัจจุบัน และ [ColumnName] คือการเข้าถึงค่าจากคอลัมน์นั้นในแถวปัจจุบัน สามารถใช้ฟังก์ชันได้ทุกตัว เช่น Text.Upper สำหรับแปลงเป็นตัวพิมพ์ใหญ่, Number.Round สำหรับปัดเศษตัวเลข, Date.Year สำหรับดึงค่าปีจากวันที่ | |
| columnType | nullable type | Optional | null | ชนิดข้อมูลของคอลัมน์ใหม่ เช่น type number สำหรับตัวเลข, type text สำหรับข้อความ, type date สำหรับวันที่, type logical สำหรับจริงเท็จ ถ้าไม่ระบุระบบจะคาดเดาชนิดข้อมูลจากค่าที่คำนวณได้ แนะนำให้ระบุเพื่อเพิ่มประสิทธิภาพโดยลดเวลาการคาดเดาชนิดข้อมูลและป้องกันข้อผิดพลาดจากชนิดข้อมูลที่ไม่ตรงกัน โดยเฉพาะกับตารางขนาดใหญ่ |
How it works
คำนวณ Calculated Columns ในการวิเคราะห์ข้อมูล
สร้างคอลัมน์ที่คำนวณจากคอลัมน์อื่นใน table เช่น ยอดรวม = ราคา + ค่าจัดส่ง, กำไร = รายได้ – ต้นทุน, ส่วนลด = ราคา × เปอร์เซ็นต์ส่วนลด เหมาะสำหรับการวิเคราะห์ทางการเงิน sales reporting และ business intelligence
รวมข้อมูลหลายคอลัมน์เป็น Composite Fields
Combine ข้อมูลจากหลายคอลัมน์เป็นคอลัมน์เดียว เช่น ชื่อเต็ม = ชื่อ + นามสกุล, ที่อยู่เต็ม = บ้านเลขที่ + ตำบล + จังหวัด + รหัสไปรษณีย์, SKU = รหัสสินค้า + รหัสสี + รหัสขนาด ใช้ Text.Combine สำหรับ text concatenation และระบุ delimiter ได้
จัดกลุ่มข้อมูลตาม Business Rules
สร้างคอลัมน์ที่จัดกลุ่มข้อมูลตามเงื่อนไข เช่น ระดับยอดขาย (สูง/ปานกลาง/ต่ำ), สถานะลูกค้า (ใหม่/เก่า/VIP), age group (0-18, 19-35, 36-50, 51+), กลุ่มราคา (budget/mid-range/premium) ใช้ if-then-else หรือ nested conditions
ETL Data Transformations และ Data Cleaning
แปลงข้อมูลในระหว่าง ETL pipeline เช่น แปลง raw data เป็น format ที่ต้องการ, คำนวณ derived metrics, สร้าง flag columns สำหรับ data quality checks, normalize ข้อมูล, หรือสร้าง surrogate keys ใช้ร่วมกับ Table.SelectRows และ Table.TransformColumns เพื่อ data cleansing workflow ที่สมบูรณ์
Examples
ตัวอย่างที่ 1: คำนวณยอดรวม (Price + Shipping) – พื้นฐาน
let Source = Table.FromRecords({ [OrderID = 1, Item = "Fishing rod", Price = 100.0, Shipping = 10.00], [OrderID = 2, Item = "Worms", Price = 5.0, Shipping = 15.…
.
คำสั่ง each เป็นแบบย่อสำหรับ function ที่รับแถวเป็น parameter และ [ColumnName] คือการดึงค่าจากคอลัมน์นั้นในแถวปัจจุบัน ส่วน type number บังคับให้ผลลัพธ์เป็นตัวเลขและช่วย optimize performance ด้วย
let
Source = Table.FromRecords({
[OrderID = 1, Item = "Fishing rod", Price = 100.0, Shipping = 10.00],
[OrderID = 2, Item = "Worms", Price = 5.0, Shipping = 15.00],
[OrderID = 3, Item = "Fishing net", Price = 25.0, Shipping = 10.00]
}),
AddTotal = Table.AddColumn(Source, "TotalPrice", each [Price] + [Shipping], type number)
in
AddTotal
Table มี 4 คอลัมน์ (OrderID, Item, Price, Shipping) + คอลัมน์ใหม่ TotalPrice: 110.00, 20.00, 35.00
ตัวอย่างที่ 2: รวมข้อมูล Text (Concatenate) ด้วย Text.Combine
let Source = Table.FromRecords({ [EmployeeID = "E001", FirstName = "John", LastName = "Doe", Department = "Sales"], [EmployeeID = "E002", FirstName = "Jane", La…
.
Text.Combine ยืดหยุ่นกว่า & operator เพราะรองรับการจัดการ null values และ custom delimiters ได้ดีกว่าครับ 😎
let
Source = Table.FromRecords({
[EmployeeID = "E001", FirstName = "John", LastName = "Doe", Department = "Sales"],
[EmployeeID = "E002", FirstName = "Jane", LastName = "Smith", Department = "Marketing"],
[EmployeeID = "E003", FirstName = "Bob", LastName = "Johnson", Department = "IT"]
}),
AddFullName = Table.AddColumn(
Source,
"FullName",
each Text.Combine({[FirstName], [LastName]}, " "),
type text
)
in
AddFullName
Table มีคอลัมน์ FullName: "John Doe", "Jane Smith", "Bob Johnson"
ตัวอย่างที่ 3: Conditional Logic (if-then-else) สำหรับจัดกลุ่ม
let Sales = Table.FromRecords({ [Product = "A", Amount = 150, Region = "North"], [Product = "B", Amount = 50, Region = "South"], [Product = "C", Amount = 200, R…
.
คอลัมน์แรก (Category) เช็คว่า [Amount] > 100 หรือไม่ คอลัมน์ที่สอง (Priority) ใช้ค่าจากคอลัมน์ Category ที่สร้างไปแล้ว (combined condition)
.
Pattern นี้เหมาะมากสำหรับ business rules ที่ซับซ้อน เช่น customer segmentation หรือ sales tier classification
let
Sales = Table.FromRecords({
[Product = "A", Amount = 150, Region = "North"],
[Product = "B", Amount = 50, Region = "South"],
[Product = "C", Amount = 200, Region = "East"],
[Product = "D", Amount = 95, Region = "West"]
}),
AddCategory = Table.AddColumn(
Sales,
"Category",
each if [Amount] > 100 then "High" else "Low",
type text
),
AddPriority = Table.AddColumn(
AddCategory,
"Priority",
each if [Category] = "High" and [Amount] > 150 then "Urgent" else "Normal",
type text
)
in
AddPriority
Table มี Category: "High", "Low", "High", "Low" และ Priority: "Urgent", "Normal", "Urgent", "Normal"
ตัวอย่างที่ 4: Custom Function ภายใน columnGenerator สำหรับ Complex Calculations
let // Define custom function for tax calculation CalculateTax = (price as number, taxRate as number) as number => Number.Round(price * taxRate, 2), Source = Ta…
.
ขั้นตอนแรกกำหนด function สำหรับคำนวณภาษีพร้อม Number.Round เพื่อจำกัดทศนิยม จากนั้นเพิ่มคอลัมน์ TaxAmount โดยเรียก CalculateTax([Price], [TaxRate]) สุดท้ายเพิ่มคอลัมน์ FinalPrice โดยรวมราคากับภาษี
.
ส่วนตัวผมชอบ pattern นี้มากครับ เพราะทำให้โค้ดอ่านง่ายและ maintain ได้ดีกว่าการยัดทุกอย่างไว้ใน columnGenerator เดียว 💡
let
// Define custom function for tax calculation
CalculateTax = (price as number, taxRate as number) as number =>
Number.Round(price * taxRate, 2),
Source = Table.FromRecords({
[Product = "Widget", Price = 100.00, TaxRate = 0.07],
[Product = "Gadget", Price = 250.00, TaxRate = 0.07],
[Product = "Tool", Price = 75.50, TaxRate = 0.05]
}),
AddTaxAmount = Table.AddColumn(
Source,
"TaxAmount",
each CalculateTax([Price], [TaxRate]),
type number
),
AddFinalPrice = Table.AddColumn(
AddTaxAmount,
"FinalPrice",
each [Price] + [TaxAmount],
type number
)
in
AddFinalPrice
Table มี TaxAmount: 7.00, 17.50, 3.78 และ FinalPrice: 107.00, 267.50, 79.28
ตัวอย่างที่ 5: Advanced – Nested Conditions และ Multiple M Functions
let Orders = Table.FromRecords({ [OrderID = "ORD001", CustomerType = "VIP", Amount = 5000, OrderDate = #date(2025, 1, 15)], [OrderID = "ORD002", CustomerType =…
.
(1) Nested if-then-else conditions เพื่อกำหนด discount rate ตาม customer type และ amount (business rules ที่ซับซ้อน)
.
(2) Chain multiple Table.AddColumn calls โดยแต่ละคอลัมน์ใหม่สามารถอ้างอิงคอลัมน์ที่สร้างไปแล้ว (เช่น DiscountAmount ใช้ DiscountRate)
.
(3) ใช้ Date functions (Date.QuarterOfYear) และ Text.From สำหรับ type conversion ภายใน columnGenerator
.
Pattern นี้เป็น standard ใน ETL pipelines และ data transformation workflows ที่ต้องการ calculated metrics หลายชั้น เรียกได้ว่าเป็นเทคนิคหลักของการทำ Power Query เลยก็ว่าได้ 😎
let
Orders = Table.FromRecords({
[OrderID = "ORD001", CustomerType = "VIP", Amount = 5000, OrderDate = #date(2025, 1, 15)],
[OrderID = "ORD002", CustomerType = "Regular", Amount = 1500, OrderDate = #date(2025, 2, 20)],
[OrderID = "ORD003", CustomerType = "VIP", Amount = 800, OrderDate = #date(2025, 3, 10)],
[OrderID = "ORD004", CustomerType = "New", Amount = 2500, OrderDate = #date(2025, 1, 5)]
}),
AddDiscount = Table.AddColumn(
Orders,
"DiscountRate",
each if [CustomerType] = "VIP" then
if [Amount] >= 1000 then 0.15 else 0.10
else if [CustomerType] = "Regular" then
if [Amount] >= 2000 then 0.08 else 0.05
else 0.03,
type number
),
AddDiscountAmount = Table.AddColumn(
AddDiscount,
"DiscountAmount",
each Number.Round([Amount] * [DiscountRate], 2),
type number
),
AddQuarter = Table.AddColumn(
AddDiscountAmount,
"Quarter",
each "Q" & Text.From(Date.QuarterOfYear([OrderDate])),
type text
)
in
AddQuarter
Table พร้อม DiscountRate (0.15, 0.05, 0.10, 0.03), DiscountAmount (750.00, 75.00, 80.00, 75.00), Quarter ("Q1", "Q1", "Q1", "Q1")
FAQs
คำสั่ง each ใช้ทำอะไร และแตกต่างจากฟังก์ชันปกติอย่างไร?
คำสั่ง each เป็นรูปแบบย่อสำหรับฟังก์ชันที่ไม่มีชื่อซึ่งรับพารามิเตอร์เดียวชื่อ _ (underscore) การเขียน each [Price] + [Tax] มีความหมายเท่ากับ (_) => _[Price] + _[Tax] โดย _ แทนแถวปัจจุบัน และ [ColumnName] คือการเข้าถึงค่าในคอลัมน์นั้นจากแถวปัจจุบันโดยอัตโนมัติ ข้อดีของ each คือทำให้โค้ดอ่านง่ายและสั้นกว่า โดยเฉพาะในฟังก์ชันตารางที่ทำงานทีละแถว
ควรระบุชนิดข้อมูลคอลัมน์หรือไม่ และมีผลกับประสิทธิภาพอย่างไร?
ไม่บังคับครับ แต่ส่วนตัวผมแนะนำให้ระบุเสมอเพื่อเพิ่มประสิทธิภาพและป้องกันข้อผิดพลาด 💡
.
ถ้าไม่ระบุ Power Query จะคาดเดาชนิดข้อมูลจากค่าที่คำนวณได้ ซึ่งใช้เวลาและอาจไม่ตรงความต้องการ การระบุชนิดข้อมูลช่วย:
.
(1) ลดเวลาการคาดเดาชนิดข้อมูลโดยเฉพาะในตารางขนาดใหญ่หลายพันหลายหมื่นแถว
.
(2) ป้องกันข้อผิดพลาดจากชนิดข้อมูลที่ไม่ตรงกันเมื่อข้อมูลไม่สอดคล้องกับที่คาดหวัง
.
(3) ทำให้แผนการประมวลผลชัดเจนและคาดการณ์ได้
.
สำหรับชนิดข้อมูลที่ใช้บ่อย: type number สำหรับตัวเลข, type text สำหรับข้อความ, type date สำหรับวันที่, type logical สำหรับจริงเท็จ, type datetime สำหรับวันเวลา
สามารถใช้ฟังก์ชันอื่นภายในฟังก์ชันคำนวณได้ไหม? มีฟังก์ชันอะไรใช้ได้บ้าง?
ได้ สามารถใช้ฟังก์ชันทุกตัวภายในฟังก์ชันคำนวณ เช่น ฟังก์ชันข้อความ (Text.Upper สำหรับแปลงเป็นตัวพิมพ์ใหญ่, Text.Combine สำหรับรวมข้อความ, Text.Length สำหรับนับจำนวนตัวอักษร), ฟังก์ชันตัวเลข (Number.Round สำหรับปัดเศษ, Number.Abs สำหรับค่าสัมบูรณ์, Number.Mod สำหรับหารเอาเศษ), ฟังก์ชันวันที่ (Date.Year สำหรับดึงปี, Date.AddDays สำหรับเพิ่มวัน, Date.DayOfWeek สำหรับหาวันในสัปดาห์), ฟังก์ชันลอจิก (Logical.And, Logical.Or), ฟังก์ชันลิสต์ (List.Sum สำหรับรวมค่า, List.Count สำหรับนับจำนวน) และอื่นๆ ตัวอย่างการใช้งาน: each Text.Upper([Name]) แปลงชื่อเป็นตัวพิมพ์ใหญ่, each Number.Round([Price] * 1.07, 2) คำนวณราคารวมภาษีและปัดเศษ, each Date.Year([OrderDate]) ดึงปีจากวันที่สั่งซื้อ, each List.Sum([Items]) รวมค่าในลิสต์สินค้า ข้อจำกัดเดียวคือฟังก์ชันต้องคืนค่าที่เหมาะสมสำหรับเซลล์เดียวในคอลัมน์ ไม่ใช่ตารางหรือโครงสร้างที่ซับซ้อน
ถ้าอ้างถึงคอลัมน์ที่ไม่มีใน table จะเกิดอะไรขึ้น?
จะเกิด error “Expression.Error: The field ‘ColumnName’ of the record wasn’t found” ทันที 😭
.
ต้องตรวจสอบให้แน่ใจว่า:
.
(1) ชื่อคอลัมน์ใน [ColumnName] ถูกต้องและ match กับคอลัมน์ใน table (case-sensitive นะครับ)
.
(2) คอลัมน์ที่อ้างอิงมีอยู่จริงใน table ณ ขั้นตอนนั้น (ถ้า chain หลาย transformations ต้องเช็คว่าคอลัมน์ยังไม่ถูกลบไป)
.
(3) ถ้าคอลัมน์อาจไม่มีใน some cases สามารถใช้ Record.HasFields หรือ try…otherwise เพื่อ handle gracefully
Table.AddColumn ต่างจาก Table.TransformColumns อย่างไร? ใช้ตัวไหนดี?
คำถามนี้เจอบ่อยมากครับ 😅 ต่างกันที่:
.
Table.AddColumn สร้างคอลัมน์ใหม่โดยไม่แก้ไขคอลัมน์เดิม (เก็บทั้งข้อมูลต้นฉบับและข้อมูลใหม่)
.
ส่วน Table.TransformColumns แก้ไขค่าในคอลัมน์เดิม (replace values in-place)
.
ใช้ AddColumn เมื่อ: (1) ต้องการเก็บทั้งค่าเดิมและค่าใหม่ เช่น Price และ PriceWithTax, (2) คำนวณจากหลายคอลัมน์ เช่น Total = Price + Shipping, (3) สร้าง derived columns สำหรับ analysis
.
ใช้ TransformColumns เมื่อ: (1) ต้องการ clean data in-place เช่น trim spaces, uppercase, (2) แปลง type เช่น text → number, (3) ไม่ต้องการเก็บค่าเดิม
.
หลักคิด: AddColumn = add new, TransformColumns = modify existing 💡
สามารถเพิ่มหลายคอลัมน์พร้อมกันได้ไหม? หรือต้องเรียก Table.AddColumn หลายครั้ง?
ต้องเรียก Table.AddColumn หลายครั้ง (chain calls) หรือใช้ let…in เพื่อ add คอลัมน์ทีละตัวครับ
.
เช่น: AddCol1 = Table.AddColumn(…), AddCol2 = Table.AddColumn(AddCol1, …), AddCol3 = Table.AddColumn(AddCol2, …)
.
ข้อดีของ chaining คือคอลัมน์ที่สร้างทีหลังสามารถอ้างอิงคอลัมน์ที่สร้างก่อนหน้าได้ (เช่น FinalPrice อ้างอิง TaxAmount)
.
ถ้าต้องการเพิ่มหลายคอลัมน์โดยไม่ขึ้นต่อกัน อาจพิจารณา custom function หรือ Table.FromColumns + Table.Combine patterns แต่โดยทั่วไป chaining Table.AddColumn เป็นวิธีที่ชัดเจนและ maintainable ที่สุด 😎
columnGenerator function จัดการ null values อย่างไร? ต้อง handle พิเศษไหม?
columnGenerator function ทำงานกับทุกแถว รวมถึงแถวที่มี null values ด้วย
.
ถ้าไม่ handle null พฤติกรรมขึ้นกับ operation:
.
(1) Arithmetic (each [A] + [B]) → null ถ้า A หรือ B เป็น null
.
(2) Text concatenation (each [A] & [B]) → null ถ้าฝั่งใดฝั่งหนึ่งเป็น null
.
(3) Comparisons (each [A] > 100) → error ถ้า A เป็น null 😭
.
วิธี handle: ใช้ if [Column] null then … else … หรือใช้ ?? operator (null coalescing) เช่น each ([Price] ?? 0) * ([Qty] ?? 1) หรือใช้ try…otherwise เช่น each try [Price] + [Tax] otherwise 0
.
ส่วนตัวผมชอบใช้ ?? operator เพราะอ่านง่ายและสั้นดีครับ 💡
Resources & Related
Resources
Related posts
Related functions
Additional Notes
Table.AddColumn เป็นหนึ่งในฟังก์ชันที่ผมใช้บ่อยที่สุดตอนแปลงข้อมูลใน Power Query เลยก็ว่าได้ 😎 ทำหน้าที่เพิ่มคอลัมน์ใหม่ลงในตารางโดยคำนวณค่าจากฟังก์ชันที่เรากำหนด โดยสามารถอ้างอิงข้อมูลจากคอลัมน์อื่นในแต่ละแถวได้ด้วย each keyword และรูปแบบ [ColumnName]
.
ซึ่งทำให้เหมาะมากสำหรับการสร้างคอลัมน์คำนวณ การรวมข้อมูลจากหลายคอลัมน์ การใช้เงื่อนไขตามกฎธุรกิจ และการแปลงข้อมูลในกระบวนการ ETL ต่างๆ ฟังก์ชันนี้ใช้งานร่วมกับ Table.TransformColumns สำหรับแก้ไขคอลัมน์เดิม Table.SelectRows สำหรับกรองแถวตามเงื่อนไข Table.AddIndexColumn สำหรับเพิ่มหมายเลขลำดับ และ Table.RenameColumns สำหรับเปลี่ยนชื่อคอลัมน์
การทำงานของฟังก์ชันนี้เป็นแบบทีละแถว โดยจะวนซ้ำทุกแถวในตารางและเรียกใช้ฟังก์ชันคำนวณเพื่อหาค่าสำหรับคอลัมน์ใหม่ในแต่ละแถว
.
ซึ่งต่างจาก Table.TransformColumns ที่แก้ไขคอลัมน์เดิม ฟังก์ชัน Table.AddColumn จะสร้างคอลัมน์ใหม่โดยไม่กระทบต่อคอลัมน์ที่มีอยู่ ทำให้สามารถเก็บทั้งข้อมูลต้นฉบับและข้อมูลที่คำนวณไว้พร้อมกันได้
.
ภายในฟังก์ชันสามารถใช้ฟังก์ชันอื่นๆ ได้หลากหลาย เช่น Text.Combine สำหรับรวมข้อความ Number.Round สำหรับปัดเศษทศนิยม Date.Year สำหรับดึงปี List.Sum สำหรับรวมค่าในลิสต์ และ Text.Upper สำหรับแปลงเป็นตัวพิมพ์ใหญ่ เป็นต้น