กำจัดค่าว่างและค่า 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 แล้วครับ

บทความนี้มีที่มายังไง?

บทความนี้เป็นส่วนหนึ่งของตัวอย่างจาก หนังสือ Excel Power Up! เพิ่มพลังการใช้ Excel ของคุณด้วย Power Query ซึ่งผมได้ตีพิมพ์ไปเมื่อสิ้นปี 2562 ที่ผ่านมา โดยผมเอาเนื้อหาบทแรกๆ ของหนังสือ ซัก 25-30% มาลงในเว็บให้อ่านกันฟรีๆ เลย คนอ่านจะได้ตัดสินใจได้ว่าอยากจะรู้เรื่องราวหลังจากนั้นอีกมั้ย? ซึ่งก็น่าจะช่วยงานคุณได้เยอะพอสมควรแล้วล่ะ

หากสนใจอ่านตัวอย่างบทอื่นๆ ของหนังสือ ลองดูที่สารบัญข้างล่างได้เลยครับ ^^

Facebook Group : Power Query Thailand

ผู้ที่สนใจ Power Query อย่างคุณที่มาอ่านบทความนี้ ผมขอเชิญชวนเข้ากลุ่มปิด Power Query Thailand ได้ตาม Link นี้ครับ

สารบัญ Power Query

บทนำ : ทำไมต้องเรียนรู้ Power Query? [ไฟล์ประกอบ]
บทที่ 1 : เข้าใจขั้นตอนการทำรายงานสรุป / วิเคราะห์ข้อมูล [ไฟล์ประกอบ]
บทที่ 2 : ก้าวสู่การเตรียมข้อมูลยุคใหม่ด้วย Power Query [ไฟล์ประกอบ]
บทที่ 3 : ภาพรวมการทำงานกับ Power Query [ไฟล์ประกอบ]
บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query [ไฟล์ประกอบ]
บทที่ 5 : การจัดการหัวตาราง [ไฟล์ประกอบ]
บทที่ 6 : การคำนวณเบื้องต้น [ไฟล์ประกอบ]
บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ [ไฟล์ประกอบ]
บทที่ 8 : การสร้างคอลัมน์ใหม่แบบกำหนดเองด้วย Custom Column [ไฟล์ประกอบ]
บทที่ 9 : การสร้างคอลัมน์ใหม่ตามเงื่อนไข [ไฟล์ประกอบ]
บทที่ 10 : การรวมกลุ่มข้อมูลด้วย Group By [ไฟล์ประกอบ]
บทที่ 11 : การพลิกคอลัมน์เป็นหัวตารางด้วย Pivot Column [ไฟล์ประกอบ]
บทที่ 12 : การยุบหัวตารางหลายคอลัมน์ให้เหลือคอลัมน์เดียวด้วย Unpivot [ไฟล์ประกอบ]
บทที่ 13 : การแยกข้อมูลในคอลัมน์เดียวออกจากกันด้วย Split Column [ไฟล์ประกอบ]
บทที่ 14 : การใช้ Query เป็นตัวแปร [ไฟล์ประกอบ]
บทที่ 15 : การรวมข้อมูลจากหลาย Query [ไฟล์ประกอบ]
บทที่ 16 : การดึงข้อมูลจาก Excel ไฟล์อื่น [ไฟล์ประกอบ]
บทที่ 17 : การดึงข้อมูลจาก Text File/ CSV File [ไฟล์ประกอบ]
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder [ไฟล์ประกอบ]
บทที่ 19 : การดึงข้อมูลจากแหล่งอื่นๆ
บทที่ 20 : การเตรียม Data เพื่อทำ Dashboard
บทที่ 21 : การทำ Pivot Table เพื่อสร้าง Dashboard
บทที่ 22 : เจาะลึก M Code หัวใจของ Power Query
บทที่ 23 : Function คือ ขุมพลังที่แท้จริงของ M Code [ไฟล์ประกอบ]
บทที่ 24 : ตัวอย่างการสร้าง Custom Function [ไฟล์ประกอบ]
บทที่ 25 : การวน Loop [ไฟล์ประกอบ]
บทส่งท้าย : เทพที่แท้จริง

อยากรู้เนื้อหาทั้งหมดนี้ไวๆ ทำยังไง?

นอกเหนือจากบทความที่ทยอยลงและหนังสือที่กำลังจะพิมพ์แล้ว ตอนนี้ผมมีคอร์ส In-House Training ที่สอนเรื่อง Power Query ที่ใช้เวลาอบรม 2 วันด้วยครับ บริษัทไหนสนใจก็ลองคลิ๊กดูรายละเอียดได้เลย

........

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ