ตอนนี้ผมคิดว่าหลายๆ ท่านเริ่มใช้ Power Query กันมากขึ้นแล้ว เลยอยากจะทำบทความเพื่อสรุปกระบวนท่าจัดการข้อมูลพื้นฐานของ Power Query ให้เพื่อนๆ ได้ทบทวนกันด้วย และเป็นการแนะนำให้คนที่ไม่เคยใช้มาลองใช้ด้วยครับ
ถ้าในการ์ตูนสุดฮิตแห่งยุคนี้อย่างเรื่องดาบพิฆาตอสูร พระเอกที่ชื่อทันจิโร่มีท่าไม้ตายว่า “ปราณวารี” งั้นคนใช้ Power Query ใน Excel/Power BI อย่างพวกเราก็คงมีท่าไม้ตายที่ชื่อว่า “ปราณคิวรี่” ได้เหมือนกันล่ะนะ 555
เพื่อไม่ให้เสียเวลา เราลองมาดูกันว่า Power Query มีกระบวนท่าจัดการข้อมูลยังไงบ้าง? ซึ่งบทความนี้จะเป็นกระบวนท่าพื้นฐานครับ ในบทความต่อๆ ไปจะเป็นกระบวนท่าที่ Advance มากขึ้นอีกที
ดังนั้น เตรียมตัวสูดลมหายใจลึกๆ แล้วไปพบกับ ปราณคิวรี่พื้นฐานทั้ง 10 รูปแบบ ได้เลย!
สารบัญ
Version คลิปวีดีโอ
ปราณคิวรี่ รูปแบบที่ 1 : Fill Down
ท่านี้มีความสามารถในการถมช่องว่างด้วยข้อมูลด้านบน ซึ่งเหมาะกับใช้จัดการข้อมูลเชิง Report ที่ชอบมีลักษณะที่เว้นว่างข้อมูลไว้ในฐานที่เข้าใจ เช่นในรูปข้างล่างนี้คอลัมน์ผลไม้ถูกเว้นว่างไว้ในฐานที่มนุษย์เข้าใจ (แต่คอมพิวเตอร์เข้าใจว่าช่องว่างก็คือว่าง)
ซึ่งแก้ไขปัญหาได้ถมช่องว่างที่เป็น 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 หน้าตัวเลขให้มีจำนวน Digit ตามต้องการ, การเขียน Condition IF อะไรพวกนี้ เครื่องมือนี้ทำได้หมดเลย
ผมจะขอทำให้ดูเป็นตัวอย่างดังนี้
เนื่องจาก Power Query เป็นโปรแกรมที่ให้ความสำคัญเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย แปลว่ามันมองเรื่องพิมพ์เล็กพิมพ์ใหญ่ไม่ตรงกันเป็นคนละคำกันโดยสิ้นเชิง ดังนั้นหลายๆ ครั้งเราจะต้องแก้เรื่องตัวพิมพ์ให้เหมือนกันทั้งหมดก่อน ซึ่งถ้าเรารู้จักเครื่องมือ ว่าสามารถใช้คำสั่งใน Format มาช่วยได้ ซึ่งมีทั้ง lowercase, UPPERCASE, Capitalize Each Word ก็สามารถแก้ไขปัญหาได้ดังนี้
แต่ถ้าเราไม่รู้จักเครื่องมือ เราก็สามารถใช้ Column From Example มาช่วยได้อยู่ดี โดยเรียกใช้ 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 อัน เฉพาะกล้วยกับมะนาว
ในขณะที่มีผลไม้ 5 แบบ ผมกดติ๊กเลือกเอาออกไป 3 อัน เหลือ 2 อัน สูตรที่มันบันทึกจะบันทึกว่าเอา 2 อัน เฉพาะ ส้มโอ กับแอปเปิ้ล
ดังนั้นวิธีบันทึกสูตรไม่เหมือนกัน ต้องดูดีๆ ว่ามันทำอย่างที่เราคิดจริงๆ รึเปล่าด้วยนะครับ
ปราณคิวรี่ รูปแบบที่ 5 : Split
กระบวนท่านี้มีความสามารถในการแยกข้อมูลใน 1 ช่อง ให้กลายเป็นหลายๆ ช่องได้ คล้ายๆ กับ Text to Column ใน Excel ซึ่งจะแบ่งด้วยตัวคั่น (Delimiter) แบ่งด้วยจำนวนตัวอักษร (Number of Character) แบ่งด้วยตำแหน่งของตัวอักษร (แบ่งได้หลายจุด) ก็ได้
ที่เจ๋งกว่าใน Excel ปกติ ก็เช่นสามารถเลือกได้ว่าจะแบ่งทีเดียวจากทางซ้าย ทางขวา หรือจะแบ่งทุกครั้งที่เจอตัวคั่นก็ได้
นอกจากนั้น ที่เด็ดสุดก็คือ นอกจากจะแบ่งเป็นหลายๆ คอลัมน์ได้แล้ว ยังสามารถแบ่งออกข้อมูลเป็นหลายๆ แถวได้ด้วย ซึ่งเหมาะมากกับกรณีที่จำนวนข้อมูลที่จะแบ่งมีไม่แน่นอน
ปราณคิวรี่ รูปแบบที่ 6 : Group By
กระบวนท่านี้มีความสามารถในการรวบข้อมูลที่เหมือนกันเข้าเป็นบรรทัดเดียว (คล้ายกับการ Remove Duplicates) แต่ที่แตกต่างก็คือ Group By นั้นนอกจากจะรวบข้อมูลแล้ว ยังทำการสรุปข้อมูลได้ด้วย เช่น สรุปแบบ Sum, Count Row, Count Distinct, Average, Median, Max, Min เช่น
จะเห็นว่าจำนวนแถวลดลงไป คอลัมน์จะเหลือแค่ตัวที่เอามา Grouping และคอลัมน์สรุปเท่านั้น คอลัมน์อื่นๆ จะหายไปหมดเลย
จริงๆ แล้ว Group By เป็นกระบวนท่าที่ต่อยอดไปใช้ท่าแบบ Advance ได้ นั่นคือ Operation ที่เรียกว่า All Rows ซึ่งเอาไว้ตอนต่อๆ ไปจะแนะนำให้รู้จักอีกทีครับ
ปราณคิวรี่ รูปแบบที่ 7 : Pivot
กระบวนท่านี้มีความสามารถ คือ เปลี่ยนข้อมูลหลาย item ในคอลัมน์เดียว ให้หลายเป็นหัวตารางหลายๆ คอลัมน์ แล้วยังสามารถกำหนดคอลัมน์ที่จะหลายมาเป็นตัวเลขสรุปได้ด้วย
ปราณคิวรี่ รูปแบบที่ 8 : Unpivot
กระบวนท่านี้มีความสามารถ ตรงข้ามกับการ Pivot คือ เปลี่ยนหัวตารางหลายๆ คอลัมน์ ให้กลายมาเป็น item ในคอลัมน์เดียว ชื่อคอลัมน์ว่า Attribute แล้วยังเปลี่ยนตัวเลขที่เคยอยู่ใต้คอลัมน์เหล่านั้นให้กลายมาเป็นตัวเลขคอลัมน์เดียวที่เรียกว่า Value ได้
ในชีวิตจริง เรามักจะเจอสถานการณ์ที่ต้องใช้กระบวนท่า Unpivot บ่อยมากๆ (เพราะคนชอบส่งข้อมูลเชิง Report มาให้เรา ไม่ใช่ส่งข้อมูลเชิง Database) ดังนั้นฝึกใช้ให้ช่ำชองล่ะ
จากในรูป คอลัมน์เรื่องพนักงานขาย sales ก-ง ดันกระจายอยู่คนละคอลัมน์กัน ทั้งๆ ที่จริงๆ ควรจะถูกพลิกลงมารวมในคอลัมน์เดียวกัน ซึ่งวิธีการที่เหมาะสมคือ ให้เลือกคอลัมน์ที่จะอยู่กับที่ (คือ สินค้า กับ วิธีการชำระเงิน) แล้วคลิ๊กขวา -> Unpivot Other Columns
สังเกตว่าในสูตรจะบันทึกไว้ว่าให้พลิกคอลัมน์อื่นที่ไม่ใช่สินค้ากับวิธีการชำระเงินลงมาให้หมด
Tips : ถ้าเรายืนยันว่าจะพลิกแค่ sales ก-ง ลงมาเสมอ ก็ให้เลือกคอลัมน์ sales ก-ง แล้วเลือก Unpivot Only Selected Columns แทนครับ
ปราณคิวรี่ รูปแบบที่ 9 : Append Query
กระบวนท่านี้มีความสามารถในการรวมเอาข้อมูล 2 Query มาต่อแถวกัน โดยยึดจากชื่อคอลัมน์เป็นหลัก ไม่ได้สนใจเรื่องการเรียงลำดับคอลัมน์ ถ้ามีคอลัมน์ที่ชื่อไม่เหมือนกันจะถูกสร้างเป็นคอลัมน์ใหม่ให้ทันที
พอจับ Append กันจะได้แบบนี้ ซึ่งจะเห็นว่ามันยึดตามคอลัมน์ในตารางแรกเป็นหลักก่อน แล้วค่อยเอาคอลัมน์ที่มีเพิ่มในตารางที่สองมาเพิ่มต่อท้าย แล้วตารางไหนที่ไม่มีคอลัมน์นั้นก็จะหลายเป็น null ว่างๆ ไป
ปราณคิวรี่ รูปแบบที่ 10 : Merge Query
กระบวนท่านี้มีความสามารถในการรวมเอาข้อมูล 2 Query มารวมคอลัมน์โดยระบุได้ว่าเชื่อมโยงกันด้วยคอลัมน์ใด เชื่อมโยงแบบไหน ซึ่งจะคล้ายๆ กับการใช้ VLOOKUP ใน Excel แต่ว่าจะเจ๋งกว่าตรงที่ Merge Query ได้ฟลลัพธ์กลับมาครบทุก Row ในขณะที่ VLOOKUP ได้แค่ item ตัวแรกสุดอันเดียว อีกทั้งยังรองรับการเชื่อมที่สะกดไม่เหมือนกัน 100% ด้วย เรียกว่า Fuzzy Merge ด้วย
สมมติว่าเอาตารางที่ Append แล้วข้างบนมา Merge ด้วยตารางในรูป โดยต้องเลือกด้วยว่าเชื่อมกันด้วยคอลัมน์ใด (เลือกได้หลายคอลัมน์) จะได้ดังนี้
จากนั้นกด Expand Column ที่ต้องการออกมา
จะได้แบบนี้
สังเกตว่าเราได้ผลลัพธ์กลับมาทุกบรรทัด ทำให้ผลลัพธ์สุดท้ายมีจำนวนแถวมากขึ้นไปด้วยนั่นเอง ส่วนอะไรที่หาไม่เจอก็จะได้ null กลับมา
ตอนต่อไปกระบวนท่า Power Query ระดับสูง
ตอนต่อไปเตรียมพบกับกระบวนท่า ปราณคิวรี่ขั้นสูง ซึ่งจะยากขึ้นกว่านี้ แต่รับรองว่าเจ๋งแน่นอน
