ปราณคิวรี่ พื้นฐาน 10 รูปแบบ : รวมกระบวนท่า Power Query พื้นฐานที่คุณควรรู้จัก

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

ถ้าเพื่อนๆ ชอบอ่านการ์ตูนคงเคยได้ยินเรื่องดาบพิฆาตอสูรใช่ไหมครับ? พระเอกที่ชื่อทันจิโร่มีท่าไม้ตาย “ปราณวารี” เราในฐานะผู้ใช้ Power Query ใน Excel/Power BI จะเรียกท่าไม้ตายของเราว่า “ปราณคิวรี่” กันบ้างดีไหมล่ะ 555

เอาล่ะ เพื่อไม่ให้เสียเวลา มาดูกันว่า Power Query มีกระบวนท่าจัดการข้อมูลยังไงบ้าง? บทความนี้ผมจะขอนำเสนอเรื่องพื้นฐานก่อนน้า ส่วนกระบวนท่าขั้น Advance เดี๋ยวจะมีในบทความต่อๆ ไป

เตรียมสูดลมหายใจลึกๆ แล้วไปพบกับปราณคิวรี่พื้นฐานทั้ง 10 รูปแบบกันเลยดีกว่า!

Version คลิปวีดีโอ

ปราณคิวรี่ รูปแบบที่ 1 : Fill Down

ท่านี้เจ๋งมากครับ! เพราะมันช่วยเติมช่องว่างด้วยข้อมูลด้านบนได้ ซึ่งเหมาะมากกับรายงานที่ข้อมูลชอบเว้นว่างไว้ เช่น ในตารางของเรา คอลัมน์ผลไม้อาจถูกเว้นว่างแบบที่มนุษย์เข้าใจ (แต่คอมพิวเตอร์เข้าใจว่ามันว่างจริงๆ)

ซึ่งเราสามารถแก้ไขช่องว่างที่เป็น null แบบง่ายๆ ด้วยวิธีนี้

• เลือกคอลัมน์ที่ต้องการ -> คลิ๊กขวา -> Fill -> Down หรือ
• เลือกคอลัมน์ที่ต้องการ -> Transform -> Fill -> Down

ปราณคิวรี่ รูปแบบที่ 2 : Replace

กระบวนท่านี้ยังมีความสามารถเปลี่ยนค่า Error เป็นค่าอื่นด้วย Replace Error หรือจะเปลี่ยนค่านึงเป็นอีกค่านึงด้วย Replace Values ก็ได้ แต่มันมีจุดต้องระวังนิดนึงครับ! ถ้าเราใช้มันกับข้อความ บางครั้งมันจะแทนที่ข้อความแค่บางส่วน เช่นถ้าเปลี่ยน “ส้ม” เป็น “มะนาว” มันก็จะเปลี่ยน “ส้ม”โอ เป็น “มะนาว”โอ ไปด้วยนะครับ

ทางแก้คือ ตอนใช้ Replace Value เพื่อนๆ ควรเลือก Advance แล้วติ๊ก Match Entire Cell Content ด้วย จะได้เป๊ะ!

ปราณคิวรี่ รูปแบบที่ 3 : Column From Example

กระบวนท่านี้เหมือนเป็นท่าไม้ตายที่อัจฉริยะ มันเลียนแบบกระบวนท่าอื่นๆ ได้เพียงใส่ผลลัพธ์ที่ต้องการให้ดูตัวอย่าง เหมือน Flash Fill ใน Excel ยังไงยังงั้นเลยครับ มันก็จะเลือกสูตรหรือคำสั่งที่เหมาะสมให้เราได้คอลัมน์ใหม่ตามที่ต้องการ

ตัวอย่างเช่น เราอาจจะแยกบางส่วนของคำ, เปลี่ยนตัวอักษรเป็นพิมพ์เล็กพิมพ์ใหญ่, เติม 0 หน้าตัวเลข, เขียนเงื่อนไข IF อะไรพวกนี้ เครื่องมือนี้ทำได้หมดเลยครับ!

เอาล่ะ ผมจะทำให้ดูเป็นตัวอย่างนะครับ

Power Query ให้ความสำคัญเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่เสมอ หมายความว่ามันแยกแยะตัวอักษรที่พิมพ์ต่างกันได้หมด ดังนั้นเราควรทำตัวพิมพ์ให้ตรงกันทั้งหมดก่อนนะ เพื่อนๆ สามารถใช้คำสั่งใน Format ช่วยได้ มีทั้ง lowercase, UPPERCASE, Capitalize Each Word สบายใจได้เลยครับ

แต่ถ้าเพื่อนๆ ยังไม่รู้จักคำสั่งพวกนี้ดีพอ ก็ใช้ Column From Example ช่วยได้เหมือนกัน เรียกใช้งานแล้วใส่ตัวอย่างผลลัพธ์ที่ต้องการไว้ในคอลัมน์ด้านขวา มันจะคิดสูตรให้เหมือน Flash Fill แต่เจ๋งกว่าตรงที่ Power Query มัน Refresh ได้ด้วยนั่นแหละ

ปราณคิวรี่ รูปแบบที่ 4 : Filter

กระบวนท่า Filter คือการคัดเลือกข้อมูลเฉพาะที่เราต้องการ ฟังดูอาจจะง่ายๆ แต่จริงๆ มันละเอียดอ่อนมากครับ มีหลายจุดที่ต้องระวังดังนี้

• ถ้าในคอลัมน์มีข้อมูล Error อยู่มันจะติด Error มาด้วย ต้องจัดการ Error ให้หายไปก่อน (เช่นใช้กระบวนท่า Replace Error)
• ทุกคำสั่งใน Power Query ให้ความสำคัญเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย Filter ก็เช่นกัน ดังนั้นอย่าลืมคิดถึงประเด็นนี้ด้วย
• ที่สำคัญสุดคือ การติ๊กเลือก item ใน filter ให้สังเกตสูตรที่ออกมาด้วยว่าตรงตามที่ต้องการมั้ย?

ยกตัวอย่างให้เห็นชัดๆ

มีผลไม้ 5 แบบ ผมกดเลือกเอาออก 2 อัน เหลือ 3 อัน สูตรที่บันทึกจะบอกว่า ไม่เอา 2 อัน ซึ่งก็คือกล้วยกับมะนาว

แต่ถ้าผมกดเลือกเอาออก 3 อัน เหลือ 2 อัน สูตรที่บันทึกจะกลายเป็น เอา 2 อัน คือส้มโอกับแอปเปิ้ล

เพราะฉะนั้น วิธีบันทึกสูตรจะไม่เหมือนกัน ต้องตรวจสอบให้แน่ใจว่าเราทำตามที่คิดว่าจะได้ผลจริงๆ รึเปล่าด้วยนะครับ!

ปราณคิวรี่ รูปแบบที่ 5 : Split

กระบวนท่าแยกข้อมูลจาก 1 ช่องให้กลายเป็นหลายๆ ช่องได้ คล้าย Text to Column ใน Excel มากๆ เพียงแต่เทคนิคการแบ่งมีหลากหลายกว่า เช่น ใช้ตัวคั่น (Delimiter), จำนวนอักษร (Number of Character), หรือแบ่งตามตำแหน่งอักษรก็ยังได้

และที่เด็ดกว่า Excel ก็คือสามารถเลือกแบ่งทีเดียวจากทางซ้าย ทางขวา หรือทุกครั้งที่เจอตัวคั่นตามที่ต้องการได้เลย

นอกจากนั้นยังมีความสามารถ แบ่งเป็นหลายๆ แถว ได้ด้วยนะครับ เพื่อนๆ ซึ่งเหมาะมากกับกรณีที่จำนวนข้อมูลที่จะถูกแบ่งมีไม่แน่นอน

ปราณคิวรี่ รูปแบบที่ 6 : Group By

กระบวนท่า Group By จะรวบข้อมูลที่ซ้ำเข้าด้วยกันเป็นบรรทัดเดียว (คล้าย Remove Duplicates) แต่ที่เจ๋งกว่าคือมันสามารถสรุปข้อมูลไปพร้อมกันได้ด้วย เช่น Sum, Count, Average และอื่นๆ

ถ้าสังเกตดูจะเห็นว่าจำนวนแถวลดลง คอลัมน์จะเหลือแค่ตัวที่ Group ไว้ และคอลัมน์สรุป คอลัมน์ที่เหลือหายไปหมด

จริงๆ แล้ว Group By เป็นกำลังหลักที่จะพัฒนาไปใช้กับท่าแบบ Advance ที่เรียกว่า All Rows ในตอนต่อไป ซึ่งผมจะอธิบายให้ฟังอย่างละเอียดนะครับ

ปราณคิวรี่ รูปแบบที่ 7 : Pivot

กระบวนท่านี้เปลี่ยนข้อมูลหลาย Item ในคอลัมน์เดียวให้กลายเป็นหัวตารางหลายคอลัมน์ และสามารถกำหนดตัวเลขสรุปในคอลัมน์ได้ด้วย เอาล่ะ!

ปราณคิวรี่ รูปแบบที่ 8 : Unpivot

กระบวนท่านี้ทำตรงข้ามกับ Pivot เปลี่ยนหัวตารางหลายๆ คอลัมน์ ให้กลายมาเป็น Item ในคอลัมน์เดียว ชื่อว่า Attribute แถมตัวเลขที่อยู่ในคอลัมน์เดิมจะมาอยู่ในคอลัมน์ Value อีกด้วย

ในชีวิตจริง เรามักจะเจอสถานการณ์ที่ต้องใช้กระบวนท่า Unpivot บ่อยมากๆ (เพราะคนชอบส่งข้อมูลเชิง Report มาให้เรา ไม่ใช่ส่งข้อมูลเชิง Database) ดังนั้นฝึกใช้ให้ช่ำชองล่ะ

จากตัวอย่าง คอลัมน์พนักงานขาย sales ก-ง กระจายอยู่คนละคอลัมน์ ทั้งที่จริงควรรวมลงในคอลัมน์เดียวกัน ซึ่งการเลือกคอลัมน์ที่อยู่กับที่ (สินค้า กับ วิธีการชำระเงิน) แล้วคลิกขวา -> Unpivot Other Columns เป็นวิธีที่ดีที่สุดครับ

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

Tips: ถ้ามั่นใจว่าจะพลิกแค่ sales ก-ง ให้เลือกคอลัมน์นี้แล้วใช้ Unpivot Only Selected Columns แทนนะครับ

ปราณคิวรี่ รูปแบบที่ 9 : Append Query

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

หลังจาก Append จะเป็นแบบนี้ สังเกตว่าระบบยึดคอลัมน์ตารางแรกเป็นหลักก่อน แล้วถ้ามีคอลัมน์เพิ่มเติมในตารางที่สองก็จะมาเพิ่มต่อท้าย ตารางไหนที่ไม่มีคอลัมน์นั้นก็จะเป็น null ว่างๆ ครับ

ปราณคิวรี่ รูปแบบที่ 10 : Merge Query

กระบวนท่านี้คล้ายการใช้ VLOOKUP ใน Excel แต่ดีกว่าตรงที่ Merge Query สามารถได้ผลลัพธ์ทุก Row ในขณะที่ VLOOKUP ได้แค่เพียง Item แรก อีกทั้งยังมีฟีเจอร์ที่เรียกว่า Fuzzy Merge ที่อนุญาตให้เชื่อมข้อมูลผิดพลาดเล็กน้อยได้ด้วย

สมมุติว่าใช้ตารางที่ Append แล้วด้านบนมา Merge กับตารางในรูป โดยระบุการเชื่อมที่คอลัมน์ใด (เลือกได้หลายคอลัมน์) จะได้ผลดังนี้

จากนั้นสามารถกด Expand Column ที่ต้องการได้

ผลลัพธ์จะออกมาแบบนี้ครับ

สังเกตได้ว่าเราจะได้ข้อมูลกลับมาทุกบรรทัด ทำให้จำนวนแถวเพิ่มขึ้น ส่วนอะไรที่หาไม่เจอจะได้ null กลับมาแทนครับ