ข้อมูลการส่งออกของอีคอมเมิร์ซมีความยุ่งเหยิงเกือบตลอดเวลา ไฟล์หนึ่งอาจมี SKU หลายรูปแบบ ข้อมูลลูกค้าที่ไม่สอดคล้องกัน ช่องที่อยู่รวม ค่าที่ซ้ำกันหรือเป็นค่าว่าง และรายละเอียดราคาที่กระจัดกระจาย หากคุณเคยลองวิเคราะห์ข้อมูลดังกล่าวใน Excel หรือ Power BI คุณจะรู้ว่าสิ่งต่างๆ มีความซับซ้อนได้เร็วเพียงใด นี่คือจุดที่ Power Query มีประโยชน์อย่างเหลือเชื่อ ไม่ว่าคุณจะใช้เครื่องมือใด - Excel หรือ Power BI - Power Query จะช่วยล้างและปรับรูปแบบข้อมูลดิบอีคอมเมิร์ซโดยไม่ต้องเขียนสูตรที่ซับซ้อน
ในบทช่วยสอนนี้ เราจะเรียนรู้การแปลง Power Query ที่สำคัญห้าประการสำหรับข้อมูลการขายอีคอมเมิร์ซที่มีประโยชน์อย่างยิ่งสำหรับชุดข้อมูลร้านค้าออนไลน์
การเลิกหมุนคอลัมน์รูปแบบ SKU
การส่งออกอีคอมเมิร์ซจำนวนมากจัดเก็บสินค้าที่สั่งซื้อในหลายคอลัมน์ เช่น Stock_S, SKU 1, SKU 2, SKU 3, ตัวแปร 1, ตัวแปร 2 และตัวแปร 3 ซึ่งแต่ละรายการมีปริมาณ โครงสร้างนี้วิเคราะห์ได้ยากเนื่องจากแต่ละคำสั่งซื้อจะกระจายรายละเอียดสินค้าไปยังหลายคอลัมน์ ตาราง Pivot, หน่วยวัด DAX และสูตร SUM ไม่สามารถรวมในรูปแบบกว้างนี้ได้อย่างมีประสิทธิภาพ Unpivoting จะสร้างตารางข้อเท็จจริงที่ยาวและเป็นมาตรฐานซึ่งเหมาะสำหรับการวิเคราะห์
ขั้นตอน:
- โหลดข้อมูลของคุณลงใน Power Query
- เลือกคอลัมน์ SKU (สต็อก S, M, L, XL)
- ไปที่การเปลี่ยนแปลง แท็บ>> เลือก ยกเลิกการหมุนคอลัมน์
- Power Query แปลงคอลัมน์ SKU เหล่านี้เป็นสองคอลัมน์ใหม่และเปลี่ยนชื่อ:
- แอตทริบิวต์: ขนาด
- ค่า: ปริมาณ
ใช้การแปลงนี้เมื่อการส่งออกของคุณจัดเก็บผลิตภัณฑ์ที่สั่งซื้อหลายรายการในคอลัมน์แยกกันแทนที่จะแยกแถว
การแยกสตริงที่อยู่จัดส่งออกเป็นหลายช่อง
ไฟล์อีคอมเมิร์ซดิบประมาณ 90% จัดเก็บที่อยู่สำหรับจัดส่งทั้งหมดไว้ในสตริงเดียว การแบ่งฟิลด์นี้จะทำให้สามารถรายงานภาษีระดับรัฐ การเพิ่มประสิทธิภาพต้นทุนการจัดส่ง การวิเคราะห์ประสิทธิภาพระดับภูมิภาค และการทำแผนที่ได้
ขั้นตอน:
- เลือก ที่อยู่สำหรับจัดส่ง คอลัมน์
- ไปที่หน้าแรก แท็บ>> เลือก แยกคอลัมน์>> เลือก ตามตัวคั่น
- ตามข้อมูลของคุณ ให้ใช้ตัวคั่น เช่น:
- จุลภาค
- ยัติภังค์
- ตัวแบ่งบรรทัด
- ตัวคั่นแบบกำหนดเอง
- สำหรับข้อมูลที่มีอยู่ ให้เลือก จุลภาค เป็นตัวคั่น
- คลิก ตกลง
- เปลี่ยนชื่อคอลัมน์ผลลัพธ์เป็นป้ายกำกับที่มีความหมาย:ถนน เมือง รหัสไปรษณีย์ และประเทศ
เมื่อแยกที่อยู่แล้ว คุณสามารถวิเคราะห์คำสั่งซื้อตามเมือง ประสิทธิภาพการจัดส่งตามโซน ยอดขายตามภูมิภาค และลูกค้าซ้ำจากสถานที่เฉพาะ สิ่งนี้มีประโยชน์อย่างยิ่งสำหรับธุรกิจจัดส่งในท้องถิ่นและการรายงานยอดขายตามภูมิภาค
หมายเหตุขั้นสูง: ที่อยู่ทั้งหมดไม่ได้มีรูปแบบเดียวกัน บางส่วนอาจมีชิ้นส่วนเพิ่มเติม ชิ้นส่วนที่ขาดหายไป หรือรายละเอียดอพาร์ตเมนต์ ในกรณีเหล่านี้ คุณสามารถ:
- ตัดช่องว่างหลังจากแยก
- รวมส่วนที่เลือกกลับเข้าด้วยกัน
- ใช้ แยกข้อความก่อน/หลังตัวคั่น
- สร้างคอลัมน์ที่กำหนดเองสำหรับการจัดการข้อยกเว้น
การตัดแต่ง การทำความสะอาด และการกำหนดมาตรฐานประเภทข้อมูล
ในชุดข้อมูลอีคอมเมิร์ซขนาดใหญ่ ชื่อลูกค้า SKU ที่อยู่อีเมล และหมวดหมู่ผลิตภัณฑ์มักจะมีช่องว่างนำหน้าหรือต่อท้าย อักขระที่ไม่สามารถพิมพ์ได้ และการใช้อักษรตัวพิมพ์ใหญ่ไม่สอดคล้องกัน ประเภทข้อมูลอาจไม่ตรงกัน เช่น ค่าตัวเลขที่จัดเก็บเป็นข้อความ
ปัญหาเล็กๆ น้อยๆ เหล่านี้อาจทำให้เกิดปัญหาร้ายแรงได้ ข้อมูลเดียวกันอาจปรากฏเป็นค่าที่แตกต่างกัน ล้มเหลวในการดำเนินการผสาน หรือสร้างกลุ่มที่ซ้ำกัน
การล้างข้อมูล:
- ตัดช่องว่าง:
- เลือกคอลัมน์ข้อความ
- ไปที่ การแปลง>> เลือก รูปแบบ>> เลือก ตัดแต่ง
- การดำเนินการนี้จะลบช่องว่างเพิ่มเติมจากจุดเริ่มต้นและจุดสิ้นสุด
- ล้างอักขระที่ซ่อนอยู่:
- ด้วยการเลือกคอลัมน์เดียวกัน
- เลือก รูปแบบ>> เลือก ทำความสะอาด
- การดำเนินการนี้จะลบอักขระที่ไม่สามารถพิมพ์ได้
- ทำให้เคสเป็นมาตรฐาน:
- ไปที่ การแปลง>> เลือก รูปแบบ>> เลือก:
- ใช้ตัวพิมพ์ใหญ่ในแต่ละคำ สำหรับชื่อ
- ตัวพิมพ์เล็ก สำหรับอีเมลหากจำเป็น
การกำหนดประเภทข้อมูลให้เป็นมาตรฐาน:
- เลือกคอลัมน์วันที่
- ไปที่หน้าแรก แท็บ>> เลือก ประเภทข้อมูล>> เลือก วันที่
- หากจำเป็น ให้เพิ่มคอลัมน์:
- ไปที่ เพิ่มคอลัมน์ แท็บ>> ขยาย วันที่
- เลือก ปี , เดือน , วัน , ไตรมาส , วันในสัปดาห์
- เลือกคอลัมน์ตัวเลข เช่น ปริมาณและราคา
- ขยายไอคอนส่วนหัวคอลัมน์>> เลือก เลขทศนิยม
- หรือไปที่การเปลี่ยนแปลง แท็บ>> เลือก ประเภทข้อมูล>> เลือก เลขทศนิยม หรือ จำนวนเต็ม
การเปลี่ยนแปลงนี้มีความสำคัญเนื่องจากทำให้การจัดกลุ่ม การรวม การกรอง และการค้นหามีความน่าเชื่อถือมากขึ้น
การลบรายการที่ซ้ำกันและการจัดการค่า Null
ค่าว่างเป็นเรื่องปกติในข้อมูลอีคอมเมิร์ซ บางตัวไม่เป็นอันตราย ในขณะที่บางตัวทำลายการคำนวณ เกตเวย์การชำระเงินและกระบวนการซิงค์อาจสร้าง OrderID ที่ซ้ำกัน และปริมาณหรือราคาที่เป็นโมฆะอาจทำให้ผลรวมและภาพเสียหายได้ หากค่าว่างไม่ได้รับการจัดการอย่างถูกต้อง ผลลัพธ์อาจทำให้เข้าใจผิดได้
การลบรายการที่ซ้ำกัน:
- เลือก รหัสคำสั่งซื้อ และ วันที่สั่งซื้อ เป็นกุญแจ (ซึ่งจะช่วยป้องกันการลบคำสั่งซื้อซ้ำที่ถูกต้องในวันเดียวกัน)
- ไปที่หน้าแรก แท็บ>> เลือก ลบรายการที่ซ้ำกัน
- ลบเฉพาะแถวที่เสียหายจริงๆ:
- ไปที่หน้าแรก แท็บ>> เลือก ลบแถวว่าง (กรองแถวที่ OrderID เป็นโมฆะออกก่อน)
การแทนที่ค่า:
- หากส่วนลดว่างหมายความว่าไม่มีส่วนลด ให้แทนที่ null ด้วย 0
- เลือกคอลัมน์ปริมาณและราคาทั้งหมด
- ไปที่การเปลี่ยนแปลง แท็บ>> เลือก แทนที่ค่า :
- ค่าที่ต้องการค้นหา:null
- แทนที่ด้วย:0
การทำความสะอาดค่าข้อความ:
- ลบคำนำหน้าที่ไม่จำเป็น เช่น Stock_
- เลือกคอลัมน์ขนาด
- ไปที่การเปลี่ยนแปลง แท็บ>> เลือก แทนที่ค่า :
- มูลค่าที่ต้องการค้นหา:หุ้น_
- แทนที่ด้วย:(เว้นว่างไว้)
ตอนนี้ค่าขนาดดูสะอาดตาและอ่านง่าย
การกรอกค่าที่ซ้ำกัน:
- บางครั้งเฉพาะแถวแรกของคำสั่งซื้อเท่านั้นที่มี OrderID หรือชื่อลูกค้า
- เลือกคอลัมน์
- ไปที่ การแปลง>> เลือก เติม>> เลือก ลง
ข้อควรระวังที่สำคัญ: อย่าสุ่มสี่สุ่มห้าแทนที่ทุกค่าว่าง ค่าที่หายไปอาจบ่งชี้ว่า "ไม่เกี่ยวข้อง" "ไม่ทราบ" หรือปัญหาด้านข้อมูล เข้าใจความหมายทางธุรกิจเสมอก่อนที่จะแทนที่ค่า
การสร้างคอลัมน์ที่กำหนดเองจากการคำนวณ
จากข้อมูลอีคอมเมิร์ซ คุณสามารถรับคอลัมน์รายได้หรือส่วนต่างกำไรได้ แม้ว่าสิ่งนี้สามารถทำได้โดยใช้สูตร Excel แต่สูตรเหล่านั้นจะหยุดการรีเฟรช คอลัมน์แบบกำหนดเองของ Power Query จะคำนวณใหม่โดยอัตโนมัติและยังคงเป็นส่วนหนึ่งของไปป์ไลน์ ETL ของคุณ
รายได้สุทธิ:
- ไปที่ เพิ่มคอลัมน์ แท็บ>> เลือก คอลัมน์ที่กำหนดเอง
- ป้อนชื่อ
- แทรกสูตรต่อไปนี้
[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 เป็น สกุลเงิน , Margin_Pct ถึง เปอร์เซ็นต์
สำคัญ: คอยรักษาสูตรการแบ่งตัวอยู่เสมอ ศูนย์ในตัวส่วนอาจทำให้เกิดข้อผิดพลาดทั่วทั้งคอลัมน์ใน Power Query และอาจทำให้แถวถูกละทิ้งระหว่างการโหลด ใช้รูปแบบ ถ้า [X] =0 จะเป็นโมฆะ มิฉะนั้น … สำหรับอัตราส่วนที่คำนวณได้
การจัดกลุ่มและการรวมกลุ่มสำหรับตารางสรุปประสิทธิภาพสูง
ไฟล์อีคอมเมิร์ซดิบที่มีหลายล้านแถวอาจทำให้รายงานช้าลง การจัดกลุ่มจะสร้างตารางรวมที่มีประสิทธิภาพสำหรับแดชบอร์ด ในขณะที่ยังคงรักษาการสืบค้นโดยละเอียดสำหรับการวิเคราะห์แบบเจาะลึก
ขั้นตอน:
- ไปที่ การแปลง>> เลือก จัดกลุ่มตาม
- เลือก หมวดหมู่>> คลิก เพิ่มการรวมกลุ่ม :
- ผลรวมของรายได้สุทธิ
- ค่าเฉลี่ยของ Margin_Pct
- จำนวน Order_ID (แถว)
- ตั้งชื่อแบบสอบถามใหม่ Sales_Summary และเก็บต้นฉบับเป็น Sales_Detail สำหรับการเจาะลึก
เคล็ดลับมือโปร: ใน Power BI ให้ปิดการใช้งาน "เปิดใช้งานการโหลด" ในการสืบค้นรายละเอียด และโหลดเฉพาะตารางสรุปและตารางมิติเพื่อปรับปรุงประสิทธิภาพ
ขั้นตอนสุดท้าย:
- ไปที่หน้าแรก แท็บ>> เลือก ปิด &ใช้ เพื่อโหลดข้อมูล
บทสรุป
ตอนนี้คุณสามารถใช้การแปลง Power Query ที่จำเป็นทั้งห้านี้กับข้อมูลการขายอีคอมเมิร์ซได้แล้ว Power Query เป็นหนึ่งในเครื่องมือที่มีประสิทธิภาพสูงสุดในการล้างชุดข้อมูลอีคอมเมิร์ซ เนื่องจากสามารถจัดการการแปลงทั้งแบบง่ายและซับซ้อนในลักษณะที่ทำซ้ำได้ สามารถลดเวลาในการเตรียมข้อมูลได้อย่างมาก สิ่งเหล่านี้ไม่ใช่ขั้นตอนการล้างข้อมูลทั่วไป แต่จะจัดการกับความท้าทายในการรายงานอีคอมเมิร์ซในโลกแห่งความเป็นจริงโดยตรง และทำให้การวิเคราะห์ขั้นปลายมีประสิทธิภาพมากขึ้น เมื่อคุณคุ้นเคยกับการเปลี่ยนแปลงเหล่านี้แล้ว คุณสามารถสร้างเทมเพลต Power Query ที่นำมาใช้ซ้ำได้สำหรับการส่งออกตลาดใหม่ทุกครั้ง
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!