บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ

กำจัดค่าว่างและค่า Error

ในบางครั้งข้อมูลในตารางที่เรามี มันก็ไม่ได้ดีขนาดที่จะเอาไปใช้ได้ทันที แต่ว่าต้องจัดการบางอย่างก่อน ดังนั้นความรู้ในส่วนนี้จึงเป็นเรื่องที่สำคัญมากครับ

ยกตัวอย่างเช่น สมมติผม Get Data ข้อมูลจาก Excel แบบนี้ ซึ่งมีข้อมูลหลากหลายรูปแบบ (โหลดไฟล์ประกอบได้ที่นี่)

ใน Power Query ก็จะตีความออกมาเป็นแบบนี้

จุดที่น่าสังเกต

• ช่องที่เป็นค่าว่างจริงๆ จะขึ้นเป็น null
• ช่องที่เป็น empty text (มาจากสูตร =””) จะกลายเป็นช่องว่างๆ ที่ไม่มีคำว่า null ซึ่งหากลองกด Filter ดูจะเห็นเป็นคำว่า (blank)
• ข้อมูล Error ใน Excel ไม่ว่าจะเป็นแบบใดก็ถูกตีความเป็น Error ทั้งหมด

การจัดการข้อมูล Error

ถ้าข้อมูลยังมี Error อยู่แบบนี้ จะ Transform ต่อแล้วเกิดปัญหา เช่น จะ Sort ก็ไม่ได้ จะ Load Data ออกมาก็ไม่ได้ ดังนั้นเราจะต้องมีวิธีจัดการข้อมูล Error ซะก่อน

ข้อมูลที่ Error มีวิธีการจัดการ 2 แบบใหญ่ๆ คือ 

1. ตัดข้อมูลแถวนั้นทิ้งโดยการ Remove Row ที่ Error ออกไปเลย
2. แทนที่ค่า Error ด้วยค่าอื่น ด้วยการ Replace Errors

ซึ่งมันก็แล้วแต่สถานการณ์ว่าจะจัดการแบบไหน แต่ในเคสส่วนใหญ่ผมคิดว่าแนวทาง Replace Errors น่าจะ Work กว่า เพราะข้อมูลในคอลัมน์อื่นก็จะไม่หายไปด้วย

ในที่นี้ผมขอ Replace Errors ด้วยคำว่า –Err– แล้วกัน จะได้เห็นชัดๆ

การ Filter

หนึ่งในเครื่องมือที่มีการใช้บ่อยมากที่สุดอันหนึ่งใน Power Query นั่นก็คือ เครื่องมือ Filter ที่เรากดได้ที่หัวตารางแต่ละคอลัมน์นั่นเอง

เครื่องมือ Filter นั้นใช้ง่ายและสะดวกมาก สามารถช่วยคัดเลือกข้อมูลบรรทัดที่ต้องการได้ทันที แบบเดียวกับที่เราลอง Filter กันไปก่อนหน้านี้ในตัวอย่างแรกๆ นั่นแหละ

อย่างไรก็ตาม มีประเด็นที่ควรจะต้องระวังมากๆ เวลาจะใช้งานเครื่องมือนี้อยู่ด้วยดังนี้

ประเด็น 1 : ใน Power Query ตัวพิมพ์ใหญ่/ตัวพิมพ์เล็ก มีผลต่างกัน (Case-Sensitive) เสมอ

หากเราลองกด Filter ว่าต้องการเอาเฉพาะบรรทัดที่มีค่า aa ก็จะได้แต่ aa จริงๆ (ไม่ได้บรรทัดที่มี AA ด้วย หรือ Aa หรือ aA ด้วย) เพราะมันมองว่าแต่ละค่าต่างกันโดยสิ้นเชิง

ดังนั้นหากเราต้องการได้ข้อมูล aa แบบไม่สนตัวพิมพ์เล็กตัวพิมพ์ใหญ่ ก็มีแนวทางที่เข้าใจง่ายคือ แปลงข้อมูลให้เป็นตัวพิมพ์เล็ก หรือ ตัวพิมพ์ใหญ่ ก่อนทำการ Filter นั่นเอง 555

วิธีทำตัวพิมพ์เล็กด้วย lowercase

ผมใช้คำสั่งในเมนู Add Column เพื่อให้สร้างเป็นคอลัมน์ใหม่ ตัวเดิมจะได้ไม่เปลี่ยนวิธีสะกดนะครับ

จากนั้นค่อยไป Filter คอลัมน์ใหม่ให้เอาเฉพาะ aa

จะได้ผลลัพธ์แบบนี้

เราก็จะเหลือบรรทัดที่คอลัมน์ Data เป็น aa ทุกรูปแบบแล้ว

Tips : อย่าลืมว่าหากมีข้อมูลที่ Error อยู่ด้วย ผลลัพธ์จะติด Error ออกมาอีก แล้วจะโหลดข้อมูลออกมาไม่ได้ ดังนั้นต้องจัดการข้อมูลที่ Error ก่อนนะครับ

ประเด็น 2 : Filter ด้วยการติ๊ก เอา/ไม่เอาตัวไหน ต้องสังเกตสูตรที่ออกมาด้วย 

เวลาเราติ๊ก Filter ว่าเอาไม่เอาตัวไหน ให้ดูสูตรด้วยว่ามันใช้วิธีตัดบางตัวทิ้ง หรือ เลือกบางตัวไว้ เพราะมันอาจไม่ได้ผลอย่างที่เราคิด โดยอาจจะมีปัญหาเวลามีข้อมูลใหม่มาเพิ่มอีก

หลักการในการคิดสูตรของมันคือ 

• หากเราติ๊กเลือกไว้จำนวนน้อยกว่าครึ่งนึง มันจะใช้สูตรแบบ เอาสิ่งที่เราเลือกไว้
• หากเราติ๊กเลือกไว้จำนวนมากกว่าครึ่ง มันจะใช้สูตรแบบ ตัดตัวที่เราไม่เลือกทิ้ง

ดังนั้นหากผลลัพธ์ออกมาไม่ใช่อย่างที่ต้องการ (โดยเฉพาะเวลา List มันน้อยๆ) ก็มีแนวทางแก้ไข คือ ควรระบุเงื่อนไขแบบชัดเจนไปเลย เช่น More Than, Less Than, Contain, Not Contain, Equal, Not Equal

ซึ่งเมนูพวกนี้จะโผล่มาก็ต่อเมื่อเรามีการกำหนด Data Type ของหัวตารางคอลัมน์นั้นๆ แล้วเท่านั้น ดังนี้

ถ้า Data Type เป็นตัวเลข

ถ้า Data Type เป็น วันที่/เวลา

Data Type เป็น Text

Remove Duplicates

Remove Duplicates คือเครื่องมือที่ใช้กำจัดข้อมูลซ้ำ โดยให้เหลือแต่ตัวบนสุดไว้ ประเด็นสำคัญที่ต้องระวัง คือ Power Query มันมองตัวพิมพ์เล็กตัวพิมพ์ใหญ่เป็นคนละตัวกันนะ (แต่ใน Excel เครื่องมือ Remove Duplicates จะมองเป็นตัวเดียวกัน)

สมมติถ้าเราอยากลอง Remove Duplicates แบบทื่อๆ เลย ไม่ได้ระวังเรื่อง Case Sensitive ก็ให้เลือกคอลัมน์ Name แล้วไปที่ Home → Remove Rows → Remove Duplicates 

จะเห็นว่ามีบางรายการหายไป เช่นรายการที่ 3, 9, 12, 14 เพราะมันซ้ำแบบเป๊ะๆ จริงๆ

อย่างไรก็ตาม รายการที่ 4 ที่เป็นชื่อ SIRA EKABUT ไม่หายไป เพราะมันเป็นตัวพิมพ์ใหญ่ ถือว่าไม่ซ้ำกัน

ดังนั้นหากเราอยากให้ตัดตัวซ้ำแบบไม่สนใจตัวพิมพ์เล็กพิมพ์ใหญ่ใน Power Query ก็อย่าลืมจัดการแปลงข้อมูลด้วยพวก lowercase ด้วย (คล้ายๆ ตอนเรา Filter นั่นแหละ) ดังนั้นเราจะลบ Step Removed Duplicates ไปก่อน แล้วเลือกคอลัมน์ Name แล้วไปที่ Add Column → Format → lowercase

จากนั้นค่อยสั่ง Remove Duplicates จากคอลัมน์ lowercase ที่เพิ่งสร้างมาใหม่

คราวนี้จะเห็นว่าผลลัพธ์เหลือน้อยลงแล้ว

หากอยากให้เหลือชื่อไม่ซ้ำ แต่อยากได้ Transaction ล่าสุดล่ะ?

หากอยากได้ Transaction ล่าสุด เราก็ต้องจัดเรียง Transaction ล่าสุดให้อยู่ข้างบนก่อน โดยกด Sort ที่หัวตาราง Transaction แล้วเลือก Sort Descending ก่อน

จากนั้นค่อยไปเลือกคอลัมน์ lowercase แล้วสั่ง Remove Duplicates ครับ 

จะเห็นว่าเราได้ SIRA EKABUT ที่ Transaction 4 มาแทนเดิมที่เป็นเลข 2 แล้วครับ