ฉันทำความสะอาดสเปรดชีตที่นำเข้ามายุ่งเหยิงได้ภายในไม่กี่นาทีโดยใช้เครื่องมือ Excel ที่ฉันเพิกเฉยมาหลายปี

ลองจินตนาการว่าคุณเป็นนักวิเคราะห์ข้อมูลที่มีหน้าที่สร้างแดชบอร์ดยอดขายหลัก แต่แทนที่จะเริ่มด้วยชุดข้อมูลที่สะอาด คุณได้รับโฟลเดอร์ Monthly_Logs ที่เต็มไปด้วยไฟล์ CSV 50 ไฟล์ ครอบคลุมหลายภูมิภาคและหลายเดือน มีไฟล์ PDF ที่บรรจุข้อมูลค่าขนส่ง และเข้าถึงฐานข้อมูล Supabase PostgreSQL ที่เก็บต้นทุนสินค้า คุณต้องเปลี่ยนทุกอย่างเหล่านั้นให้กลายเป็นตารางเดียวที่สะอาดและเป็นมาตรฐานที่สามารถใช้งานได้จริง

ในตอนแรก มันอาจดูเหมือนว่าทางเดียวที่จะจัดการกับโครงการแบบนี้คือการคัดลอกและวางข้อมูลด้วยมือข้ามหลายไฟล์ แต่ผมพบว่านั่นไม่จำเป็นเลย การใช้ Power Query ผมนำเข้าข้อมูลจากทั้งสามแหล่งและรวมทุกอย่างเป็นชุดข้อมูลรวมเพียงชุดเดียวภายในไม่กี่นาที ระหว่างทาง ผมได้ลบแถวขยะ แยกคอลัมน์ เพิ่มคอลัมน์ที่กำหนดเอง และทำความสะอาดรูปแบบข้อความที่ไม่สอดคล้องกันโดยไม่ต้องสร้างใหม่ตั้งแต่ต้น

ถ้าคุณต้องการติดตามด้วยตัวเอง ผมได้แนบไฟล์ทั้งหมดที่คุณต้องการเพื่อทดลองกระบวนการด้วยตัวเองไว้ที่นี่แล้ว

การทำความสะอาดและรวมไฟล์ CSV

การใช้ Power Query เพื่อรวม ทำความสะอาด แยก พลิกตาราง และทำให้ไฟล์ CSV 50 ไฟล์มีรูปแบบมาตรฐานในครั้งเดียว

ในกรณีนี้ โฟลเดอร์ Monthly_Logs ประกอบด้วยไฟล์ CSV แยกกันทั้งหมด 50 ไฟล์ โดยมีไฟล์หนึ่งสำหรับแต่ละภูมิภาคและแต่ละเดือน แต่ละไฟล์มีสามแถวขยะอยู่ด้านบน มีคอลัมน์วันที่ 26 คอลัมน์กระจายตามแนวนอน มีคอลัมน์ Store_Info ที่จริงแล้วรวมค่าต่างกันสามค่าเข้าด้วยกันในฟิลด์เดียวโดยใช้ค่าตัวอย่างเช่น 101-Chicago-60601 มีคอลัมน์ Internal_Notes ที่ไม่จำเป็น 15 คอลัมน์ และชื่อผลิตภัณฑ์ที่ดูเหมือนว่าจะถูกป้อนโดยทีมต่างกันโดยใช้รูปแบบการตั้งชื่อที่แตกต่างกัน

ในการเริ่มจัดระเบียบทุกอย่าง ให้ไปที่ ข้อมูล > ดึงข้อมูล -> จากไฟล์ -> จากโฟลเดอร์ ใน Excel เลือกโฟลเดอร์ Monthly_Logs และคลิก ตกลง ในกล่องโต้ตอบตัวอย่าง เลือก แปลงข้อมูล และ Power Query จะโหลดตารางที่แต่ละแถวแทนไฟล์แยกต่างหาก จากนั้น คลิกปุ่มรวมไฟล์ ซึ่งปรากฏเป็นไอคอนลูกศรคู่ในคอลัมน์เนื้อหา Power Query จะรวมไฟล์ CSV ทั้ง 50 ไฟล์เข้าด้วยกันเป็นตารางเดียวทันที

เนื่องจากทุกไฟล์เริ่มต้นด้วยแถวขยะ คุณจึงไม่สามารถรวมไฟล์เหล่านั้นได้โดยไม่ทำความสะอาดก่อน ในแผง Queries ทางด้านซ้าย Power Query จะสร้างคิวรีตัวช่วยโดยอัตโนมัติที่เรียกว่า Transform Sample File คิวรีนี้ทำหน้าที่เป็นแม่แบบสำหรับวิธีการประมวลผลไฟล์ CSV ทุกไฟล์ก่อนที่จะเกิดการรวม เปิดคิวรีนี้แล้วใช้ขั้นตอนการทำความสะอาดโดยไปที่ Home -> Remove Rows -> Remove Top Rows -> 3 จากนั้นเลือก Home -> Use First Row as Headers เมื่ากลับไปที่คิวรีหลัก Monthly_Logs Power Query จะใช้ขั้นตอนการทำความสะอาดแบบเดียวกันนี้กับไฟล์ CSV ทั้ง 50 ไฟล์ก่อนทำการรวม ซึ่งจะลบแถวขยะออกจากชุดข้อมูลทั้งหมดในครั้งเดียว

ประเด็นถัดไปคือโครงสร้างของข้อมูลการขายเอง การมีคอลัมน์เดือนแยกกัน 26 คอลัมน์พร้อมหัวข้อเช่น Jan-2023 และ Feb-2023 ทำให้การวิเคราะห์ยากโดยไม่จำเป็น โดยเฉพาะอย่างยิ่งถ้าคุณวางแผนที่จะสร้าง Pivot Tables หรือแดชบอร์ดใน Excel ในภายหลัง ในการทำให้ข้อมูลเป็นปกติ ให้กด Ctrl และเลือกคอลัมน์เดือนทั้ง 26 คอลัมน์ คลิกขวา และเลือก Unpivot Columns Power Query จะเปลี่ยนคอลัมน์กว้างเหล่านั้นให้เป็นสองฟิลด์ที่สะอาดกว่า: คอลัมน์หนึ่งที่มีชื่อเดือนและอีกคอลัมน์หนึ่งที่มีค่าการขาย จากนั้นคุณสามารถเปลี่ยนชื่อคอลัมน์ให้ชัดเจนขึ้น เช่น Month และ Sales_Amount.

คอลัมน์ Store_Info ก็ต้องได้รับความสนใจเช่นกันเพราะมันรวมค่าหลายค่าเข้าไว้ในฟิลด์เดียว เลือกคอลัมน์แล้วไปที่ หน้าแรก -> แยกคอลัมน์ -> โดยตัวคั่น เลือกตัวคั่นเป็นเครื่องหมายขีดกลาง (-) และตั้งค่าให้แยกใน ทุกตำแหน่งที่เจอ Power Query จะทำการแยกฟิลด์ออกเป็นคอลัมน์แต่ละคอลัมน์ทันที ซึ่งคุณสามารถเปลี่ยนชื่อคอลัมน์เป็น Store_ID, City, และ Zip ได้

ในจุดนี้ คอลัมน์ 15 Internal_Notes เป็นเพียงความรกเท่านั้น กด Ctrl ขณะเลือกคอลัมน์ที่ไม่จำเป็นแต่ละคอลัมน์ คลิกขวา แล้วเลือก ลบคอลัมน์ เพื่อเอาออกจากชุดข้อมูล

คอลัมน์ Product_Name ก็ต้องได้รับการปรับมาตรฐานด้วยเช่นกัน เนื่องจากการตั้งชื่อที่ไม่สอดคล้องกันสร้างปัญหาในการรายงาน เริ่มต้นด้วยการเลือกคอลัมน์แล้วไปที่ Transform -> Format -> Trim เพื่อลบช่องว่างเกินก่อนหรือหลังชื่อผลิตภัณฑ์ จากนั้นใช้ Transform -> Format -> Capitalize Each Word เพื่อปรับมาตรฐานตัวอักษร หลังจากนั้นใช้ Home -> Replace Values เพื่อรวมความแตกต่างของการตั้งชื่อ ตัวอย่างเช่น คุณสามารถแทนที่ “milk ” ด้วย “Whole Milk ” และ “Milk-W ” ด้วย “Whole Milk.” นอกจากนี้ควรตรวจสอบการซ้ำที่เกิดขึ้นโดยไม่ตั้งใจระหว่างการแทนที่ เช่น “Whole Whole Milk,” เพื่อที่คุณจะสามารถล้างข้อมูลเหล่านั้นได้เช่นกัน

ในการทำให้กระบวนการทำความสะอาดเสร็จสมบูรณ์ ให้ลบธุรกรรมที่ซ้ำโดยการเลือกคอลัมน์ Transaction_ID คลิกขวา และเลือก ลบรายการซ้ำ Power Query จะเก็บรายการแรกและลบรายการที่ซ้ำ จากนั้นกรองคอลัมน์ Quantity และยกเลิกการเลือกค่า null เพื่อลบแถวที่มีปริมาณหายไป

เนื่องจากชุดข้อมูลสุดท้ายที่รวมกันมีขนาดค่อนข้างใหญ่ จึงควรหลีกเลี่ยงการโหลดเข้าไปในเวิร์กชีต Excel ปกติโดยตรง แทนที่จะเป็นเช่นนั้น ให้ไปที่ หน้าแรก -> ปิด & โหลด -> ปิด & โหลดไปยัง... เลือก สร้างการเชื่อมต่อเท่านั้น และทำเครื่องหมายที่ เพิ่มข้อมูลนี้ไปยังแบบจำลองข้อมูล

สิ่งนี้จะผลักดันชุดข้อมูลเข้าสู่ Power Pivot ซึ่งจัดการชุดข้อมูลขนาดใหญ่ได้อย่างมีประสิทธิภาพมากขึ้นและช่วยป้องกันไม่ให้ไฟล์งานของคุณช้าลงหรือค้าง

รวมข้อมูลมากขึ้นจากทุกที่

Power Query รวบรวมทุกอย่างเข้าด้วยกัน ไม่ว่าจะเป็นฐานข้อมูล ไฟล์ PDF หรือสเปรดชีต

เมื่อไฟล์ CSV ถูกทำความสะอาดและรวมกันแล้ว ขั้นตอนต่อไปคือการดึงข้อมูลที่เหลือซึ่งจำเป็นสำหรับแดชบอร์ดการขายหลัก ในกรณีนี้ นั่นหมายถึงการนำเข้าข้อมูลต้นทุนผลิตภัณฑ์จากฐานข้อมูล Supabase PostgreSQL และอัตราค่าขนส่งที่เก็บอยู่ในไฟล์ PDF แม้ว่าข้อมูลเหล่านั้นจะมาจากแหล่งข้อมูลที่แตกต่างกันโดยสิ้นเชิง แต่ Power Query จัดการทั้งสองอย่างได้โดยไม่ยากนัก

สำหรับการเชื่อมต่อฐานข้อมูล ผมพบว่าวิธีที่ง่ายที่สุดคือการเชื่อมต่อ Supabase ผ่าน ODBC ก่อน แล้วค่อยนำเข้าข้อมูลไปยัง Power Query โดยใช้การเชื่อมต่อนั้น โดยไปที่ ข้อมูล -> รับข้อมูล -> จากแหล่งอื่น -> จาก ODBC ใส่ข้อมูลรับรองของคุณ เลือกตาราง product_cost ในหน้าต่าง Navigator จากนั้นเลือก โหลดไปยัง... คุณยังสามารถติดตั้งไดรเวอร์ PostgreSQL และเชื่อมต่อโดยตรงผ่าน ข้อมูล -> รับข้อมูล -> จากฐานข้อมูล -> จากฐานข้อมูล PostgreSQL ไม่ว่าจะด้วยวิธีใด ให้โหลดข้อมูลเข้าไปใน Data Model โดยตรงเหมือนกับวิธีที่คุณจัดการชุดข้อมูล CSV

เมื่อโหลดตารางราคาสินค้าเสร็จแล้ว คุณสามารถรวมตารางนี้กับข้อมูลการขายได้ เปิดคิวรี Monthly_Logs และไปที่ หน้าแรก -> รวมคิวรี เลือก Product_Name เป็นคอลัมน์ที่ตรงกันจากข้อมูลการขายของคุณ เลือกคิวรี product_cost จากเมนูแบบเลื่อนลง และจับคู่กับคอลัมน์ product_name ในฐานข้อมูล การใช้การรวมแบบ Left Outer จะเก็บทุกแถวของการขายไว้พร้อมดึงข้อมูลค่าใช้จ่ายเข้ามาเมื่อมีสินค้าที่ตรงกัน หลังจากการรวมเสร็จสิ้น ให้คลิกไอคอนขยายบนคอลัมน์ที่รวมและเลือกฟิลด์ที่คุณต้องการนำเข้า เช่น cost_per_unit, retail_price, gross_margin_pct และ SKU จากนั้นใช้ ปิด & โหลด... เพื่อใช้การเปลี่ยนแปลง

การนำเข้า PDF ทำงานในลักษณะคล้ายกัน ไปที่ ข้อมูล -> รับข้อมูล -> จากไฟล์ -> จาก PDF เรียกดูไปยัง Shipping_Costs_Invoice.pdf และ Power Query จะสแกนเอกสารเพื่อค้นหาตารางโดยอัตโนมัติ ในแผง Navigator ให้เลือกตารางที่มีข้อมูลการจัดส่งและโหลดเข้าไปในโมเดลข้อมูล

ก่อนที่คุณจะสามารถรวมข้อมูลการจัดส่งเข้ากับข้อมูลยอดขายได้ ทั้งสองชุดข้อมูลต้องมีคอลัมน์ร่วม ตารางการจัดส่งจัดอัตราตามภูมิภาค ในขณะที่ข้อมูลยอดขายมีเพียงชื่อเมืองเท่านั้น เพื่อเชื่อมช่องว่างนั้น ให้สร้างคอลัมน์ Region ใหม่ภายในคำสั่ง Monthly_Logs โดยไปที่ เพิ่มคอลัมน์ -> คอลัมน์ตามเงื่อนไข จากนั้นสร้างกฎเช่น “ถ้าเมืองเท่ากับชิคาโก ให้เป็นภาคตะวันออกเฉียงเหนือ” ทำซ้ำขั้นตอนนี้กับทุกเมืองในชุดข้อมูลของคุณ และปล่อยเงื่อนไข Else สำหรับสิ่งใดก็ตามที่ไม่ตรงกันไว้ด้านล่าง

หากรายการเมืองของคุณมีขนาดใหญ่ การสร้างกฎเงื่อนไขด้วยตนเองจะกลายเป็นเรื่องน่าเบื่อ วิธีที่มีประสิทธิภาพมากกว่าคือการสร้างตารางค้นหาง่าย ๆ สองคอลัมน์ใน Excel ที่ประกอบด้วยค่าเมืองและภูมิภาค แปลงช่วงข้อมูลให้เป็นตาราง Excel ที่ตั้งชื่อแล้วด้วย Ctrl + T, นำเข้าตารางนั้นไปยัง Power Query ผ่าน Data -> From Table/Range และโหลดเข้าไปใน Data Model จากนั้น ใช้ Merge Queries ภายใน Monthly_Logs เพื่อจับคู่คอลัมน์เมืองจากทั้งสองตารางและดึงข้อมูลภูมิภาคที่ตรงกันมาโดยอัตโนมัติ

เมื่อมีคอลัมน์ Region อยู่แล้ว การรวมข้อมูลการจัดส่งจะกลายเป็นเรื่องง่าย ไปที่ Home -> Merge Queries เลือกตารางการจัดส่ง จับคู่คอลัมน์ Region ในทั้งสองชุดข้อมูล เลือกการเชื่อมแบบ Left Outer และขยายฟิลด์ที่คุณต้องการรวม เช่น Shipping Zone, Carrier, Avg Weight, Rate/lb, Fuel Surcharge และ Handling Fee

เมื่อสิ้นสุดกระบวนการ คุณจะได้แบบจำลองข้อมูลเพียงชุดเดียวที่สะอาด ครอบคลุมข้อมูลยอดขายทั้ง 50 เดือนที่ถูกจัดเรียงและยกเลิกการหมุน คอลัมน์ Store_ID, City และ Zip ถูกแยกอย่างเหมาะสม ชื่อผลิตภัณฑ์ถูกปรับมาตรฐานและลบค่าซ้ำและค่าที่เป็น null ข้อมูลต้นทุนและกำไรของผลิตภัณฑ์ถูกดึงโดยตรงจาก Supabase และข้อมูลค่าขนส่งถูกสกัดจาก PDF จากนั้น การสร้างแดชบอร์ดจะง่ายขึ้นมากเพราะงานทำความสะอาดและรวมข้อมูลที่ยากทั้งหมดได้เสร็จสิ้นแล้ว สิ่งที่เหลือเพียงแค่ไปที่แท็บ แทรก และเลือก ตารางสรุป > จากแบบจำลองข้อมูล.

อีกทางเลือกหนึ่ง คุณสามารถคลิกขวาที่การสืบค้น Monthly_Logs เลือก โหลดไปที่… และแทรกชุดข้อมูลลงในตารางบนแผ่นงาน Excel ใดก็ได้ที่คุณต้องการ

คุณสร้างเวิร์กโฟลว์เพียงครั้งเดียวเท่านั้น

งานที่เคยใช้เวลาทั้งวันทำงาน สามารถตั้งค่าได้ในเวลาเพียงไม่กี่นาที ในครั้งถัดไปที่ไฟล์ CSV ใหม่มาถึงในโฟลเดอร์ Monthly_Logs สิ่งที่ฉันต้องทำคือไปที่ Data -> Refresh All และ Power Query จะรันการแปลงทุกขั้นตอนโดยอัตโนมัติ ตราบใดที่ไฟล์ใหม่มีโครงสร้างเดียวกันกับหัวข้อและจำนวนคอลัมน์ที่ตรงกัน กระบวนการทำความสะอาดทั้งหมดจะเกิดขึ้นอีกครั้งในไม่กี่วินาที