4

ปราณควิ รี่ พ้ืนฐาน 10 รปู แบบ : รวมกระบวนทา Power Query พื้นฐานทีค่ ณุ ควรรจู ัก by Sira Ekabut | Dec 3, 2020 | Power Query ปราณรูปแบบท่ี 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 กลับมา

อา งองิ Sira Ekabut. ปราณคิวร่ี พื้นฐาน 10 รูปแบบ : รวมกระบวนทา Power Query พื้นฐานที่ คุณควรรจู กั [ออนไลน]. 2020, แหลงท่ีมา : https://www.thepexcel.com/10-power- query-basic-transform/ [ 12 กนั ยายน 2564]