Thep Excel
Table.TransformColumnTypesเปลี่ยนชนิดข้อมูลของคอลัมน์
Table.TransformColumnTypes เปลี่ยน Data Type ของคอลัมน์ที่ระบุให้เป็นประเภทใหม่ (เช่น Text เป็น Number, Date เป็น Text) โดยใช้ built-in conversion methods เบื้องหลัง รองรับการแปลงหลายคอลัมน์พร้อมกัน และสามารถระบุ culture parameter เพื่อจัดการ locale-specific formatting สำหรับวันที่และตัวเลข เหมาะสำหรับขั้นตอนเตรียมข้อมูลก่อน Load เข้า Data Model เพื่อป้องกัน conversion errors และเพิ่มประสิทธิภาพ ใช้ร่วมกับ Table.TransformColumns, Number.From, Text.From และ Date.From เมื่อต้องการ custom transformation logic
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| table | table | ตารางข้อมูลที่ต้องการเปลี่ยน Data Type | |
| typeTransformations | list | รายการคู่ {“ชื่อคอลัมน์”, type} เช่น {{“Amount”, Int64.Type}} หรือ {{“Date”, type text}, {“Value”, Percentage.Type}} สำหรับหลายคอลัมน์ รองรับ type constants ได้แก่ type text, type number, Int64.Type, Currency.Type, Percentage.Type, type date, type time, type datetime, type datetimezone, type duration, type logical, type binary แต่ไม่รองรับ type list, type record, type table | |
| [culture]ไม่บังคับ | nullable text | null | รหัสวัฒนธรรม (Culture Code) เช่น “en-US” (สหรัฐฯ), “fr-FR” (ฝรั่งเศส), “de-DE” (เยอรมัน), “th-TH” (ไทย) ใช้สำหรับ locale-specific formatting ของวันที่และตัวเลข หรือระบุเป็น record [Culture=”en-US”, MissingField=MissingField.Ignore] เพื่อจัดการคอลัมน์ที่ไม่มีในตาราง (MissingField.UseNull ใส่ null, MissingField.Ignore ข้ามคอลัมน์, default คือ Error) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
Table.TransformColumnTypes ใช้แปลงชนิดข้อมูล (Data Type) ของคอลัมน์ใน Power Query โดยระบบจะใช้ built-in conversion methods ของแต่ละชนิดข้อมูล เช่น Number.From สำหรับแปลงเป็นตัวเลข Text.From สำหรับแปลงเป็นข้อความ และ Date.From สำหรับแปลงเป็นวันที่ ทำงานเบื้องหลังอัตโนมัติ
.
ที่เจ๋งคือฟังก์ชันนี้รองรับการแปลงหลายคอลัมน์พร้อมกันในคำสั่งเดียว และมี culture parameter สำหรับจัดการรูปแบบข้อมูลตามภูมิภาค (locale-specific formatting) สำหรับวันที่และตัวเลขได้ตามมาตรฐานของแต่ละประเทศครับ 😎
.
การเปลี่ยนชนิดข้อมูล (Data Type) เป็นขั้นตอนสำคัญมากในการเตรียมข้อมูลก่อนนำเข้า Data Model เพราะ Power Query จะทำการตรวจสอบและแปลงค่าในแต่ละแถว (row) ตามชนิดข้อมูลที่คุณกำหนดไว้
.
ถ้าการแปลงค่าล้มเหลว เช่น พยายามแปลงข้อความ “abc” เป็นตัวเลข (Number) ซึ่งเป็นไปไม่ได้ ระบบจะแสดง error หรือให้ค่า null ตามการตั้งค่า ด้วยเหตุนี้ การกำหนดชนิดข้อมูลที่ถูกต้องตั้งแต่ขั้นตอนเริ่มต้นจึงช่วยป้องกันปัญหาคุณภาพข้อมูล (data quality issues) และเพิ่มประสิทธิภาพในการประมวลผลข้อมูลได้อย่างมีนัยสำคัญครับ
.
Table.TransformColumnTypes มีความแตกต่างจาก Table.TransformColumns อย่างชัดเจนนะครับ โดย Table.TransformColumnTypes จะใช้ type constants เช่น type number, Int64.Type, type text และมีพารามิเตอร์ culture สำหรับการจัดรูปแบบตามภูมิภาค (locale-specific formatting)
.
ในขณะที่ Table.TransformColumns จะให้คุณระบุฟังก์ชันแปลงค่าที่กำหนดเอง (custom transformation function) เช่น each Text.Upper([Name]) เพื่อแปลงตัวอักษรเป็นตัวใหญ่ได้ยืดหยุ่นกว่า แต่ไม่มีการรองรับพารามิเตอร์ culture
.
เรียกได้ว่า Table.TransformColumnTypes เหมาะสำหรับการแปลงชนิดข้อมูลแบบมาตรฐาน (standard type conversion) ส่วน Table.TransformColumns เหมาะกับการแปลงค่าที่มี custom logic ที่ซับซ้อน เช่น การใช้ List.Sum เพื่อรวมค่าตัวเลข หรือ Table.AddColumn เพื่อเพิ่มคอลัมน์ใหม่ด้วยการคำนวณที่ซับซ้อนครับ
.
ฟังก์ชันนี้มีประโยชน์อย่างมากในการทำงานกับข้อมูลที่นำเข้าจากแหล่งต่างๆ เช่น ไฟล์ CSV หรือฐานข้อมูลภายนอก ซึ่งมักมีปัญหาเรื่องชนิดข้อมูลที่ไม่ตรงตามที่ต้องการ เช่น ตัวเลขที่ถูกเก็บเป็นข้อความ หรือวันที่ที่อยู่ในรูปแบบข้อความ
.
การใช้ Table.TransformColumnTypes ช่วยให้คุณสามารถกำหนดชนิดข้อมูลที่ถูกต้องได้อย่างรวดเร็วและมีประสิทธิภาพ โดยเฉพาะเมื่อต้องการแปลงหลายคอลัมน์พร้อมกัน ซึ่งจะช่วยประหยัดเวลาและทำให้โค้ดของคุณกระชับขึ้นอย่างมากครับ 💡
.
นอกจากนี้ การใช้พารามิเตอร์ culture ยังมีประโยชน์อย่างมากสำหรับการทำงานกับข้อมูลที่มาจากหลายประเทศ เช่น ถ้าคุณมีข้อมูลวันที่จากยุโรปที่ใช้รูปแบบ DD.MM.YYYY หรือจากอเมริกาที่ใช้ MM/DD/YYYY
.
คุณสามารถใช้พารามิเตอร์ culture เพื่อให้ระบบแปลงค่าได้อย่างถูกต้องตามรูปแบบของแต่ละภูมิภาค หรือเมื่อคุณต้องการส่งออกข้อมูลไปยังผู้ใช้ในประเทศต่างๆ และต้องการให้วันที่แสดงในรูปแบบที่เหมาะสมกับภูมิภาคของพวกเขา
.
ส่วนตัวผมใช้ culture parameter บ่อยมากเวลาทำงานกับข้อมูลระหว่างประเทศครับ จะทำให้ข้อมูลของคุณมีความถูกต้องและเข้าใจง่ายสำหรับผู้ใช้ในแต่ละภูมิภาค 😎
How it works
แปลง Text เป็น Number สำหรับคำนวณ
เมื่อ import ข้อมูลจาก CSV หรือ Text file ตัวเลขมักจะเป็น Text ("100", "200") ต้องแปลงเป็น Number (100, 200) ก่อนใช้ในการคำนวณหรือ aggregation เช่น SUM, AVERAGE เพื่อให้ Power Query สามารถประมวลผลตัวเลขได้ถูกต้อง
Format วันที่ตามภูมิภาค (Locale-specific)
แปลง Date เป็น Text พร้อมระบุ Culture Code เพื่อ export ข้อมูลในรูปแบบที่ตรงตามภูมิภาค เช่น "en-US" ให้ MM/DD/YYYY (03/24/2024), "fr-FR" ให้ DD/MM/YYYY (24/03/2024), "de-DE" ให้ DD.MM.YYYY (24.03.2024) สำหรับการสร้างรายงานสำหรับ users ในภูมิภาคต่างๆ
เตรียมข้อมูลก่อน Load เข้า Data Model
กำหนด Data Type ที่ถูกต้องก่อน Load เข้า Data Model เพื่อป้องกัน conversion errors, ลดขนาดไฟล์ (เช่น Int64 เล็กกว่า Text), และเพิ่มประสิทธิภาพการ query โดยการแปลง type ที่ Power Query step แทนที่จะให้ data engine แปลงภายหลัง
แปลงหลายคอลัมน์พร้อมกัน (Bulk Conversion)
แปลง Data Type ของหลายคอลัมน์ในคำสั่งเดียว เช่น แปลง OrderDate, ShipDate, DueDate เป็น type date พร้อมกัน หรือแปลง Amount, Tax, Total เป็น Currency.Type ทำให้ code กระชับและลด query steps
จัดการคอลัมน์ที่ไม่มีด้วย MissingField options
เมื่อใช้ query เดียวกับ data sources หลายตัวที่มี schema แตกต่างกัน (เช่น บาง source ไม่มีคอลัมน์ Discount) ใช้ MissingField.Ignore เพื่อข้ามคอลัมน์ที่ไม่มีแทนที่จะเกิด error ทำให้ query ยืดหยุ่นและใช้ซ้ำได้
Examples
ตัวอย่างที่ 1: Foundation – แปลง Text เป็น Numberlet Source = Table.FromRecords({ [ID = 1, Amount = "100"], [ID = 2, Amount = "200"], [ID = 3, Amount = "350"] }), ChangedType = Table.TransformColumnTypes( Sour…
ตัวอย่างนี้แสดงการแปลงคอลัมน์ Amount จากข้อความ (Text) ที่เป็น "100", "200" ให้เป็นตัวเลข (Number) ที่เป็น 100, 200 โดยใช้ Int64.Type สำหรับจำนวนเต็ม
หลังจากการแปลงแล้ว คอลัมน์ Amount จะสามารถนำไปคำนวณได้ เช่น ใช้ List.Sum เพื่อรวมค่า ฟังก์ชันจะใช้ Number.From เบื้องหลังเพื่อแปลงค่าในแต่ละแถว (row) อัตโนมัติครับ
การแปลงนี้เป็นพื้นฐานที่สำคัญมากเมื่อนำเข้าข้อมูลจากไฟล์ที่ตัวเลขถูกเก็บเป็นข้อความ 💡
ตัวอย่างที่ 2: Practical – แปลงหลายคอลัมน์พร้อมกันlet Source = Table.FromRecords({ [Name = "Product A", Price = "199.50", Quantity = "10", InStock = "true"], [Name = "Product B", Price = "299.00", Quantity = "5…
ตัวอย่างนี้แสดงการแปลงสามคอลัมน์พร้อมกันในคำสั่งเดียว โดยคอลัมน์ Price จากข้อความเป็น type number สำหรับทศนิยม (decimal), คอลัมน์ Quantity เป็น Int64.Type สำหรับจำนวนเต็ม (integer), และคอลัมน์ InStock จากข้อความ "true" หรือ "false" เป็น type logical ที่เป็นค่าตรรกะ (true/false)
ที่เจ๋งคือการใช้รายการ (list) ของคู่ {{column, type}, {column, type}} ช่วยให้คุณระบุหลายการแปลง (transformations) ในคำสั่งเดียวได้อย่างกระชับและมีประสิทธิภาพครับ 😎
ตัวอย่างที่ 3: Advanced – แปลงวันที่พร้อม Culture Code (French)let Source = Table.FromRecords({ [Company = "JS-464", Country = "USA", OrderDate = #date(2024, 3, 24)], [Company = "LT-331", Country = "France", OrderDate = #da…
ตัวอย่างนี้แสดงการแปลงวันที่ (Date) เป็นข้อความ (Text) โดยใช้รหัสวัฒนธรรม (Culture Code) เป็น "fr-FR" สำหรับประเทศฝรั่งเศส ทำให้วันที่ออกมาเป็นรูปแบบ DD/MM/YYYY เช่น 24/03/2024 แทนที่จะเป็น MM/DD/YYYY แบบสหรัฐอเมริกา
การใช้พารามิเตอร์ culture มีประโยชน์มากเมื่อต้องการส่งออกข้อมูลให้ผู้ใช้ในภูมิภาคต่างๆ เช่น ยุโรป เอเชีย หรืออเมริกา
ส่วนตัวผมใช้บ่อยมากเวลาทำรายงานส่งให้ต่างประเทศครับ โดยพารามิเตอร์ culture จะส่งผลต่อทั้งรูปแบบวันที่และตัวเลขตามมาตรฐานของแต่ละภูมิภาค 💡
ตัวอย่างที่ 4: Advanced – แปลงหลายคอลัมน์พร้อม Culture (German)let Source = Table.FromRecords({ [Date = #date(2024, 3, 12), Customer = "134282", Discount = 0.24368], [Date = #date(2024, 5, 30), Customer = "44343", Discount…
ตัวอย่างนี้แสดงการแปลงสองคอลัมน์พร้อมกันโดยใช้รหัสวัฒนธรรม (Culture Code) เป็น "de-DE" สำหรับประเทศเยอรมนี
ทำให้วันที่เป็นรูปแบบ DD.MM.YYYY เช่น 12.03.2024 ซึ่งใช้เครื่องหมายจุด (dot) แทนเครื่องหมายทับ (slash) และคอลัมน์ Discount ถูกแปลงเป็น Percentage.Type เพื่อแสดงผลเป็นเปอร์เซ็นต์ เช่น 24.37% และ 3.56%
ที่น่าสนใจคือพารามิเตอร์ culture จะส่งผลต่อทั้งตัวคั่นวันที่ (date separator) และรูปแบบการแสดงผลเปอร์เซ็นต์ (percentage formatting) ตามมาตรฐานของประเทศนั้นๆ ครับ 😎
ตัวอย่างที่ 5: Real-World – จัดการ Missing Columns ด้วย MissingField.Ignorelet Source = Table.FromRecords({ [OrderID = 1, Amount = "100", Status = "Active"], [OrderID = 2, Amount = "200", Status = "Pending"] }), // ตาราง Source ไม่มีคอ…
ตัวอย่างนี้แสดงการใช้ MissingField.Ignore ภายในพารามิเตอร์ culture เพื่อข้ามคอลัมน์ที่ไม่มีในตาราง แทนที่จะเกิดข้อผิดพลาด (error) แบบปกติ
วิธีนี้มีประโยชน์มากเวลาคุณต้องการใช้ query เดียวกันกับแหล่งข้อมูล (data sources) หลายตัวที่มีโครงสร้าง (schema) แตกต่างกัน เช่น บางแหล่งข้อมูลมีคอลัมน์ Discount แต่บางแหล่งไม่มี
ส่วนตัวผมใช้เทคนิคนี้บ่อยมากครับ เพราะช่วยให้ query ยืดหยุ่นขึ้น ตัวเลือกอื่นๆ ได้แก่ MissingField.UseNull ซึ่งจะสร้างคอลัมน์ที่มีค่า null และตัวเลือกปกติ (default) ซึ่งจะแสดงข้อผิดพลาด (Error) ทันที 💡
FAQs
Table.TransformColumnTypes รองรับ Data Type อะไรบ้าง?+
รองรับ type พื้นฐานได้แก่ type text, type number, Int64.Type, Currency.Type, Percentage.Type, type date, type time, type datetime, type datetimezone, type duration, type logical, type binary และ type any แต่ไม่รองรับ type ที่ซับซ้อนเช่น type list, type record, type table, type function หากพยายามใช้ type เหล่านี้จะเกิด error
ถ้าคอลัมน์ที่ระบุไม่มีในตาราง จะเกิดอะไรขึ้น?+
จะเกิด error ทันที (default behavior) แต่สามารถป้องกันได้โดยระบุ culture parameter เป็น record [Culture="en-US", MissingField=MissingField.Ignore] เพื่อข้าม column ที่ไม่มี หรือใช้ MissingField.UseNull เพื่อสร้าง column ที่มีค่า null ทุก row แทน ตัวเลือกนี้มีประโยชน์เมื่อใช้ query เดียวกับ data sources หลายตัวที่มี schema แตกต่างกัน
Culture Code ส่งผลต่อการแปลง Type อย่างไร?+
Culture Code กำหนดรูปแบบ locale-specific formatting ตามภูมิภาค เช่น วันที่: "en-US" ใช้ MM/DD/YYYY (03/24/2024), "fr-FR" ใช้ DD/MM/YYYY (24/03/2024), "de-DE" ใช้ DD.MM.YYYY (24.03.2024); ตัวเลข: ทศนิยมใช้ . (dot) หรือ , (comma), thousands separator ใช้ , หรือ . ตาม culture; เปอร์เซ็นต์: การแสดงผลของ Percentage.Type culture จะส่งผลต่อการแปลง Date → Text และ Number → Text เป็นหลัก ไม่ส่งผลต่อการแปลง Text → Date/Number
ควรใช้ Culture parameter เมื่อไร?+
ใช้เมื่อต้องการ export ข้อมูลให้ users ในภูมิภาคต่างๆ (เช่น แปลง Date เป็น Text ในรูปแบบที่เหมาะกับภูมิภาค) หรือเมื่อ import ข้อมูลที่มีรูปแบบวันที่/ตัวเลขแบบเฉพาะภูมิภาค เช่น ข้อมูลจากยุโรปที่ใช้ comma (,) เป็นทศนิยมแทน dot (.) หรือเมื่อต้องการจัดการ missing columns ด้วย MissingField options สำหรับการแปลง type ทั่วไป (เช่น Text → Number) ไม่จำเป็นต้องระบุ culture
Table.TransformColumnTypes ต่างจาก Table.TransformColumns อย่างไร?+
Table.TransformColumnTypes ใช้ type constants (เช่น type text, Int64.Type) และรองรับ culture parameter สำหรับ locale-specific formatting โดยใช้ built-in .From methods เบื้องหลัง (เช่น Number.From, Text.From) เหมาะสำหรับการแปลง type แบบ standard ส่วน Table.TransformColumns ให้ระบุ custom transformation function (เช่น each Text.Upper([Name])) ได้ยืดหยุ่นกว่า แต่ไม่มี culture support เหมาะกับ custom logic ที่ซับซ้อน โดยทั่วไปใช้ Table.TransformColumnTypes สำหรับ type conversion และ Table.TransformColumns สำหรับ value transformation
ถ้า conversion ล้มเหลว (เช่น Text "abc" ไม่สามารถแปลงเป็น Number) จะเกิดอะไรขึ้น?+
จะเกิด error ในแถวนั้นและแสดง [Expression.Error] แทนค่า โดยค่าอื่นในแถวเดียวกันยังคงอยู่ แต่คอลัมน์ที่แปลงไม่สำเร็จจะเป็น error สามารถป้องกันได้โดยใช้ try…otherwise ก่อน Table.TransformColumnTypes เช่น Table.AddColumn(Source, "AmountClean", each try Number.From([Amount]) otherwise null) แล้วค่อยแปลง type หรือใช้ Table.TransformColumns กับ custom function ที่มี error handling แทน
Table.TransformColumnTypes มีผลต่อ performance อย่างไร?+
การแปลง type ที่เหมาะสมช่วยเพิ่มประสิทธิภาพ เพราะ (1) Data type ที่ถูกต้องใช้ memory น้อยกว่า เช่น Int64 เล็กกว่า Text, (2) Query engine ทำ aggregations (SUM, AVG) กับ Number เร็วกว่า Text, (3) Type conversion ที่ Power Query step เร็วกว่าให้ data engine แปลงภายหลัง แต่ควรแปลงเฉพาะคอลัมน์ที่จำเป็น และวาง step นี้ไว้หลัง filter steps เพื่อลดจำนวน rows ที่ต้องแปลง หลีกเลี่ยงการแปลง type ซ้ำหลาย steps
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ Table.TransformColumnTypes จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
Table.AddColumn เพิ่มคอลัมน์ใหม่ลงในตารางโดยคำนวณค่าจากฟังก์ชันที่กำหนด ใช้คำสั่ง each เพื่ออ้างอิงข้อมูลในแต่ละแถว เช่น each [Price] * [Quantity] สามารถระบุชนิดข้อมูลได้เพื่อเพิ่มประสิทธิภาพและป้องกันข้อผิดพลาดจากการคาดเดาชนิดข้อมูล ทำงานแบบทีละแถวและเหมาะสำหรับสร้างคอลัมน์คำนวณ รวมข้อมูล ใช้เงื่อนไข และการแปลงข้อมูลต่างๆ ใช้ร่วมกับฟังก์ชัน Table.TransformColumns สำหรับแก้ไขคอลัมน์เดิม Table.AddIndexColumn สำหรับเพิ่มหมายเลขลำดับ Table.SelectRows สำหรับกรองแถว Table.FromRecords สำหรับสร้างตารางจากข้อมูล Text.Combine สำหรับรวมข้อความ Number.Round สำหรับปัดเศษ Date.Year สำหรับดึงค่าปี และ Text.Upper สำหรับแปลงเป็นตัวพิมพ์ใหญ่ เพื่อสร้างกระบวนการแปลงข้อมูลที่สมบูรณ์
Table.Combine ใช้สำหรับรวม list ของ table เข้าเป็น table เดียว ยับยั้ง append ตัว 1 แถวหลาย ๆ ตารางพร้อมกัน
แปลง List ของ Record เป็นตาราง โดยแต่ละ Record กลายเป็นหนึ่งแถว เหมาะสำหรับข้อมูลจาก API หรือ JSON
Table.TransformColumns ใช้สำหรับแปลงข้อมูลในคอลัมน์โดยใช้ฟังก์ชันที่กำหนดไว้ แต่ละคอลัมน์สามารถมีการแปลงแตกต่างกันได้ รองรับการเปลี่ยนแปลงประเภทข้อมูลในเวลาเดียวกัน
Csv.Document แปลงข้อมูลรูปแบบ CSV (Comma-Separated Values) ให้เป็นตารางข้อมูล (table) ใน Power Query โดยรองรับการกำหนดตัวแบ่งข้อมูล (delimiter), รหัสภาษา (encoding) และโครงสร้างคอลัมน์ (column structure) แบบต่างๆ ได้ตามความต้องการ function นี้สามารถแยกวิเคราะห์ทั้งข้อมูลแบบ binary content ที่ได้จาก File.Contents และข้อความแบบ text string ที่ได้จาก Web.Contents พร้อมจัดการข้อความที่ครอบด้วย quote ตามมาตรฐานสากล CSV standard เหมาะสมสำหรับการนำเข้าไฟล์ CSV จากระบบไฟล์ในเครื่อง (local), ที่อยู่เว็บไซต์ (URL) หรือการตอบกลับจาก API (API response) ที่มีตัวแบ่งข้อมูลแบบกำหนดเอง (custom delimiter), เนื้อหาภาษาต่างประเทศ หรือโครงสร้างพิเศษที่ต้องการการควบคุมเฉพาะ คืนค่าออกมาเป็นตารางที่ทุกคอลัมน์มีประเภทข้อมูลเป็นข้อความ (text type) ควรใช้ร่วมกับ Table.PromoteHeaders เพื่อยกแถวแรกขึ้นเป็นหัวตาราง และ Table.TransformColumnTypes เพื่อแปลงประเภทข้อมูลให้เหมาะสมกับการใช้งาน
Date.From แปลงข้อมูลหลายรูปแบบ (ข้อความ ตัวเลข DateTime DateTimeZone) เป็นค่า date ที่สามารถใช้ในการคำนวณและจัดรูปแบบได้ รองรับการระบุ culture สำหรับการแปลงวันที่ localized
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่