ข้อมูลส่งออกจากอีคอมเมิร์ซ (E-commerce) โดยส่วนใหญ่จะเป็นเรื่องรก (Messy) ไฟล์เดียวอาจมี SKU หลายรูปแบบ ข้อมูลลูกค้าที่ไม่สม่ำเสมอ ช่องที่อยู่ที่รวมอยู่ด้วยกัน ค่าซ้ำหรือค่าว่าง (Null) และรายละเอียดราคาที่กระจายกระจาย หากคุณเคยพยายามวิเคราะห์ข้อมูลแบบนี้ใน Excel หรือ Power BI คุณจะรู้ว่าเรื่องซับซ้อนได้เร็วแค่ไหน นี่คือที่ที่ Power Query มีประโยชน์อย่างยิ่ง ไม่ว่าคุณจะใช้เครื่องมือใด — Excel หรือ Power BI — Power Query ช่วยทำความสะอาดและปรับโครงสร้างข้อมูลอีคอมเมิร์ซดิบ (Raw Data) ได้โดยไม่ต้องเขียนสูตรซับซ้อน
ในบทช่วยสอนนี้ เราจะเรียนรู้ 5 การแปลงข้อมูล (Transformations) พื้นฐานของ Power Query สำหรับข้อมูลยอดขายอีคอมเมิร์ซ ที่มีประโยชน์โดยเฉพาะสำหรับชุดข้อมูลร้านค้าออนไลน์
1. การ Unpivot คอลัมน์ SKU ที่มีหลายรูปแบบ
การส่งออกข้อมูลอีคอมเมิร์ซหลายแห่งเก็บสินค้าที่สั่งซื้อกระจายอยู่หลายคอลัมน์ เช่น Stock_S, SKU 1, SKU 2, SKU 3, Variant 1, Variant 2, และ Variant 3 โดยแต่ละคอลัมน์เก็บจำนวน (Quantity) โครงสร้างนี้ยากต่อการวิเคราะห์ เพราะรายละเอียดสินค้าของคำสั่งซื้อเดียวกระจายอยู่หลายคอลัมน์ Pivot Tables, DAX Measures และสูตร SUM ไม่สามารถรวมข้อมูล (Aggregate) ได้อย่างมีประสิทธิภาพในรูปแบบกว้าง (Wide Format) การ Unpivot จะสร้างตารางข้อเท็จจริง (Fact Table) แบบยาวและปกติ (Normalized) ที่เหมาะสมสำหรับการวิเคราะห์
ขั้นตอน:
- นำเข้าข้อมูลเข้า Power Query
- เลือกคอลัมน์ SKU (Stock S, M, L, XL)
- ไปที่แท็บ Transform >> เลือก Unpivot Columns
- Power Query จะแปลงคอลัมน์ SKU เหล่านี้เป็นคอลัมน์ใหม่ 2 คอลัมน์ และเปลี่ยนชื่อเป็น:
- Attribute: Size (ขนาด)
- Value: Quantity (จำนวน)
ใช้การแปลงนี้เมื่อการส่งออกของคุณเก็บสินค้าที่สั่งซื้อหลายรายการในคอลัมน์แยกกัน แทนที่จะเป็นแถวแยกกัน
2. การแยกสตริงที่อยู่จัดส่งเป็นหลายฟิลด์
ประมาณ 90% ของไฟล์อีคอมเมิร์ซดิบ เก็บที่อยู่จัดส่งทั้งหมดในสตริงเดียว การแยกฟิลด์นี้ช่วยให้สามารถรายงานภาษีระดับรัฐ (State-level) เพิ่มประสิทธิภาพค่าขนส่ง วิเคราะห์ประสิทธิภาพตามภูมิภาค และทำแผนที่ได้
ขั้นตอน:
- เลือกคอลัมน์ Shipping Address
- ไปที่แท็บ Home >> เลือก Split Column >> เลือก By Delimiter
- ขึ้นอยู่กับข้อมูลของคุณ ใช้ตัวคั่น (Delimiter) เช่น:
- คอมมา (Comma)
- ขีดกลาง (Hyphen)
- ขึ้นบรรทัดใหม่ (Line break)
- ตัวคั่นกำหนดเอง (Custom separator)
- สำหรับข้อมูลตัวอย่าง ให้เลือก Comma เป็นตัวคั่น
- คลิก OK
- เปลี่ยนชื่อคอลัมน์ที่ได้เป็นชื่อที่มีความหมาย: Street, City, Postcode, และ Country
เมื่อที่อยู่ถูกแยกแล้ว คุณสามารถวิเคราะห์คำสั่งซื้อตามเมือง ประสิทธิภาพการจัดส่งตามโซน ยอดขายตามภูมิภาค และลูกค้าที่ซ้ำจากสถานที่เฉพาะได้ นี่มีประโยชน์อย่างยิ่งสำหรับธุรกิจจัดส่งในพื้นที่และรายงานยอดขายตามภูมิภาค
หมายเหตุขั้นสูง: ไม่ใช่ทุกที่อยู่ที่ปฏิบัติตามรูปแบบเดียวกัน บางอันอาจมีส่วนเพิ่ม ส่วนขาด หรือรายละเอียดห้อง/ชั้น ในกรณีเหล่านี้ คุณสามารถ:
- Trim ช่องว่างหลังแยกคอลัมน์
- รวมส่วนที่เลือกกลับเข้าด้วยกัน
- ใช้ Extract Text Before/After Delimiter
- สร้างคอลัมน์กำหนดเองสำหรับจัดการข้อยกเว้น
3. การ Trim, Clean และมาตรฐานชนิดข้อมูล (Data Types)
ในชุดข้อมูลอีคอมเมิร์ซขนาดใหญ่ ชื่อลูกค้า SKU อีเมล และหมวดหมู่สินค้ามักมีช่องว่างนำหน้าหรือท้าย ตัวอักษรที่พิมพ์ไม่ได้ (Non-printable characters) และตัวพิมพ์เล็ก/ใหญ่ที่ไม่สม่ำเสมอ ชนิดข้อมูลอาจไม่ตรงกัน เช่น ค่าตัวเลขที่เก็บเป็นข้อความ (Text)
ปัญหาเล็กๆ เหล่านี้อาจก่อให้เกิดปัญหาใหญ่ ข้อมูลเดียวกันอาจปรากฏเป็นค่าที่ต่างกัน ล้มเหลวในการรวม (Merge) หรือสร้างกลุ่มซ้ำ
การทำความสะอาดข้อมูล:
- Trim ช่องว่าง:
- เลือกคอลัมน์ข้อความ
- ไปที่ Transform >> เลือก Format >> เลือก Trim
- สิ่งนี้จะลบช่องว่างเกินที่จุดเริ่มต้นและจุดสิ้นสุด
- Clean ตัวอักษรซ่อน:
- เลือกคอลัมน์เดียวกัน
- เลือก Format >> Clean
- สิ่งนี้จะลบตัวอักษรที่พิมพ์ไม่ได้
- มาตรฐานตัวพิมพ์ (Casing):
- ไปที่ Transform >> เลือก Format >> เลือก:
- Capitalize Each Word สำหรับชื่อ
- Lowercase สำหรับอีเมล หากต้องการ
การมาตรฐานชนิดข้อมูล:
- เลือกคอลัมน์วันที่
- ไปที่แท็บ Home >> เลือก Data Type >> เลือก Date
- หากต้องการ เพิ่มคอลัมน์:
- ไปที่แท็บ Add Column >> ขยาย Date
- เลือก Year, Month, Day, Quarter, Day of Week
- เลือกคอลัมน์ตัวเลข เช่น Quantity และ Price
- ขยายไอคอนหัวคอลัมน์ >> เลือก Decimal Number
- หรือไปที่แท็บ Transform >> เลือก Data Type >> เลือก Decimal Number หรือ Whole Number
การแปลงนี้จำเป็นเพราะทำให้การจัดกลุ่ม การรวม การกรอง และการค้นหา (Lookup) น่าเชื่อถือได้มากขึ้น
4. การลบข้อมูลซ้ำและจัดการค่า Null
ค่า Null พบได้บ่อยในข้อมูลอีคอมเมิร์ซ บางอย่างไม่เป็นอันตราย แต่บางอย่างทำลายการคำนวณ กระบวนการชำระเงินและซิงค์อาจสร้าง OrderID ซ้ำ และค่า Quantity หรือ Price ที่เป็น Null อาจทำให้ยอดรวมและภาพแสดงผล (Visuals) ผิดปกติ หาก Null ไม่ได้รับการจัดการอย่างถูกต้อง ผลลัพธ์อาจคลาดเคลื่อน
การลบข้อมูลซ้ำ:
- เลือก OrderID และ OrderDate เป็นคีย์ (สิ่งนี้ป้องกันไม่ให้ลบคำสั่งซื้อซ้ำในวันเดียวกันที่ถูกต้อง)
- ไปที่แท็บ Home >> เลือก Remove Duplicates
- ลบเฉพาะแถวที่แท้จริงเสียหาย:
- ไปที่แท็บ Home >> เลือก Remove Blank Rows (กรองแถวที่ OrderID เป็น null ก่อน)
การแทนที่ค่า:
- หากค่าส่วนลดว่างหมายถึงไม่มีส่วนลด ให้แทนที่ null ด้วย 0
- เลือกคอลัมน์ Quantity และ Price ทั้งหมด
- ไปที่แท็บ Transform >> เลือก Replace Values:
- Value to find: null
- Replace with: 0
การทำความสะอาดค่าข้อความ:
- ลบคำนำหน้าที่ไม่จำเป็น เช่น Stock_
- เลือกคอลัมน์ Size
- ไปที่แท็บ Transform >> เลือก Replace Values:
- Value to find: Stock_
- Replace with: (เว้นว่าง)
ตอนนี้ค่า Size จะดูสะอาดและอ่านง่าย
การ Fill Down ค่าที่ซ้ำ:
- บางครั้งเฉพาะแถวแรกของคำสั่งซื้อเท่านั้นที่มี OrderID หรือชื่อลูกค้า
- เลือกคอลัมน์
- ไปที่ Transform >> เลือก Fill >> เลือก Down
ข้อควรระวังสำคัญ: อย่าแทนที่ทุกค่า null แบบโง่ๆ ค่าที่หายไปอาจหมายถึง "ไม่เกี่ยวข้อง" "ไม่ทราบ" หรือ "ปัญหาข้อมูล" ต้องเข้าใจความหมายทางธุรกิจก่อนแทนที่ค่าเสมอ
5. การสร้างคอลัมน์แบบกำหนดเอง (Calculated Custom Columns)
จากข้อมูลอีคอมเมิร์ซ คุณสามารถสร้างคอลัมน์รายได้ (Revenue) หรือกำไรขั้นต้น (Margin) แม้จะทำได้ด้วยสูตร Excel แต่สูตรเหล่านั้นจะแตกเมื่อรีเฟรช (Refresh) คอลัมน์กำหนดเองของ Power Query จะคำนวณใหม่อัตโนมัติและยังคงเป็นส่วนหนึ่งของไปป์ไลน์ ETL ของคุณ
Net_Revenue (รายได้สุทธิ):
- ไปที่แท็บ Add Column >> เลือก Custom Column
- ใส่ชื่อ
- ใส่สูตรดังต่อไปนี้
[Unit_Price] * [Qty] * (1 - [Discount_Pct])
Gross_Margin (กำไรขั้นต้น):
[Net_Revenue] - ([Cost_Per_Unit] * [Qty])
Margin_Pct (%) (อัตราส่วนกำไร):
if [Net_Revenue] = 0 then 0 else [Gross_Margin] / [Net_Revenue]
- ตั้งค่าชนิดข้อมูลชัดเจน: Net_Revenue และ Gross_Margin เป็น Currency, Margin_Pct เป็น Percentage
สำคัญ: ต้องป้องกันสูตรการหารเสมอ ค่า 0 ในตัวส่วนอาจทำให้เกิดข้อผิดพลาดทั่วทั้งคอลัมน์ใน Power Query และอาจนำไปสู่การลบแถวระหว่างโหลด ใช้รูปแบบ if [X] = 0 then null else … สำหรับอัตราส่วนที่คำนวณได้ทุกครั้ง
6. การจัดกลุ่มและรวมข้อมูล (Grouping and Aggregating) สำหรับตารางสรุปประสิทธิภาพสูง
ไฟล์อีคอมเมิร์ซดิบที่มีล้านแถวอาจทำให้รายงานช้า การจัดกลุ่ม (Grouping) สร้างตารางรวมที่มีประสิทธิภาพสำหรับแดชบอร์ด ในขณะที่ยังคงคิวรีรายละเอียดไว้สำหรับการ Drill-through
ขั้นตอน:
- ไปที่ Transform >> เลือก Group By
- เลือก Category >> คลิก Add aggregation:
- Sum of Net_Revenue
- Average of Margin_Pct
- Count of Order_ID (rows)
- ตั้งชื่อคิวรีใหม่ว่า Sales_Summary และเก็บคิวรีต้นฉบับเป็น Sales_Detail สำหรับ Drill-through
เคล็ดลับมืออาชีพ: ใน Power BI ให้ปิด "Enable load" บนคิวรีรายละเอียด และโหลดเฉพาะตารางสรุปและตารางมิติ (Dimension Tables) เพื่อเพิ่มประสิทธิภาพ
ขั้นตอนสุดท้าย:
- ไปที่แท็บ Home >> เลือก Close & Apply เพื่อโหลดข้อมูล
สรุป
ตอนนี้คุณสามารถนำ 5 การแปลงข้อมูล Power Query ที่สำคัญนี้ไปใช้กับข้อมูลยอดขายอีคอมเมิร์ซได้แล้ว Power Query เป็นหนึ่งในเครื่องมือที่มีประสิทธิภาพที่สุดสำหรับทำความสะอาดชุดข้อมูลอีคอมเมิร์ซ เพราะจัดการการแปลงทั้งง่ายและซับซ้อนได้ในลักษณะที่ทำซ้ำได้ (Repeatable) มันสามารถลดเวลาเตรียมข้อมูลได้อย่างมาก นี่ไม่ใช่ขั้นตอนทำความสะอาดทั่วไป — พวกเขาแก้ปัญหาการรายงานอีคอมเมิร์ซโลกจริงโดยตรง และทำให้การวิเคราะห์ต่อเนื่อง (Downstream Analysis) มีประสิทธิภาพมากขึ้น เมื่อคุณคล่องแคล่วกับการแปลงเหล่านี้แล้ว คุณสามารถสร้างเทมเพลต Power Query ที่ใช้ซ้ำได้สำหรับการส่งออกจากตลาดทุกแห่งใหม่
Get FREE Advanced Excel Exercises with Solutions!
