คงปฏิเสธไม่ได้ว่าเครื่องมือที่ร้อนแรงที่สุดของ Excel ยุคใหม่ ก็คือ Power Query ซึ่งเดิมเคยเป็น Add-in ตัวนึงที่ใช้ได้ตั้งแต่ Excel 2010 เป็นต้นไป แต่สำหรับ Excel Version ใหม่ๆ มันได้ถูกฝังตัวมาอยู่ใน Ribbon Data เลย
สำหรับคนที่ไม่เคยใช้ Power Query มาก่อนเลย ให้ลองอ่านบทความที่ผมเคยเขียนไว้ก่อน เพื่อที่จะได้เข้าใจภาพรวมมากขึ้นครับ
Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!!
Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ
ในบทความนี้ ผมจะมาแนะนำเทคนิคเจ๋งๆ ในการใช้ Power Query ให้รู้จักกันมากขึ้นครับ โดยจะขอแนะนำซัก 7 อันก่อนแล้วกันครับ
สำหรับไฟล์ประกอบที่ผมใช้ในบทความนี้ สามารถโหลดได้ที่นี่ครับ
สารบัญ
Tips 1 : เราไม่จำเป็นต้องเอา Data มาไว้ในไฟล์ Excel จริงๆ
ปกติแล้วเวลาเรา Import ข้อมูลจากข้างนอกมาก เราก็มักจะเอา Data มาไว้ในไฟล์ Excel จริงๆ ด้วย ซึ่งข้อเสียคือ จะทำให้ไฟล์ Excel มีขนาดใหญ่โดยไม่จำเป็น และยังมีข้อจำกัดที่ว่า Sheet ของ Excel มีได้แค่ 1 ล้านกว่าๆ แถวด้วย
ซึ่งข้อจำกัดนี้สามารถแก้ไขได้โดยการที่เราสามารถกด Import File แล้วเลือก Load to… ==> Only Create Connection ได้ครับ (แถมเราเอาเข้า PivotTable จาก Query ที่มีแต่ Connection ก็ได้นะ)
Only Create Connection จะได้ไม่เปลืองเนื้อที่เก็บข้อมูล
แล้วเลือก External Data Source -> Choose Connection
Tips 2 : ลำดับการคลิ๊ก Column มีผลต่อการจัดเรียง เวลากด Remove Other Columns
เมื่อเรา Edit Query แล้วเข้าสู่ Query Editor หากเราต้องการเอาคอลัมน์ในตารางออกยกเว้นแค่บางคอลัมน์ ปกติก็จะเลือก Column ที่ต้องการ แล้วกด Remove Other Columns ได้เลย แต่รู้หรือไม่ว่า ลำดับก่อนหลังที่เราคลิ๊ก Column นั้นมีผลต่อการจัดเรียงคอลัมน์ด้วยนะ
จากนั้นกด click ขวา -> Remove Other Columns
Tips 3 : ใน Power Query อักษรตัวพิมพ์เล็กพิมพ์ใหญ่ ถือว่าเป็นคนละคำกัน
เวลาเราเลือก Filter ข้อมูลใน Power Query จะต้องระมัดระวังเรื่องของข้อมูลที่มีตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย เช่น Filter ว่าเอาเฉพาะคำว่า Female แปลว่ามันจะตัดคำว่า female ทิ้งด้วยนะ ระวังให้ดี!!
ถ้าไม่อยากมีปัญหาเรื่องนี้ ผมแนะนำให้แปลงข้อมูลเป็นตัวพิมพ์เล็กทั้งหมดด้วย Transform–>Format–>Lower Case ก่อนทำการ Filter นะครับ
Tips 4 : แยกข้อความง่ายๆ ด้วย Split
หากในข้อมูลดิบ มีข้อความหลายตัวที่รวมกันอยู่ในคอลัมน์เดียว แต่คั่นด้วยตัวอักษรบางอย่างเช่น . / – หรือ ตัวอื่นๆ เราสามารถแยกคำเหล่านั้นให้ออกมาให้อยู่คนละคอลัมน์ได้อย่างง่ายดาย
ถ้าตัวคั่นเป็นอักษรอื่น ก็เลือก dropdown -> custom ได้
ซึ่งใครจะเปลี่ยนชื่อ column ก็กด Double Click ที่ชื่อ column ได้เลย
Tips 5 : Unpivot พลิกข้อมูลหลายคอลัมน์ให้มาอยู่ในคอลัมน์เดียว
งานนี้เป็นงานที่ยากสุดๆ เมื่อใช้ Excel แต่กลับง่ายแบบปอกกล้วยเข้าปากเมื่อใช้ Power Query ซะงั้น
สมมติเรามีข้อมูลตามตาราง ซึ่งมีคอลัมน์ Survived กับ Not Survived แยกกันอยู่
แล้ว transform -> Unpivot Columns
Tips 6 : เราสามารถดึงข้อมูลจาก ทุก Text File ใน Folder ได้เลย
ถ้าเรามี Text File หรือ CSV หลายๆ ไฟล์อยู่ใน Folder เดียวกัน เราสามารถสั่งให้ Excel ดึงข้อมูลทุกไฟล์ที่อยู่ใน Folder นั้นมา Append หรือเอาข้อมูลมาต่อตูดกันได้เลย
Tips 7 : เทคนิคพิเศษดึงข้อมูลจากไฟล์ Excel ทุก File ใน Folder
ถ้าเราต้องการ รวมไฟล์ Excel ใน Folder มันจะใช้วิธีปกติไม่ได้ครับ ทั้งนี้เพราะโครงสร้าง Excel มันสามารถมีได้หลาย Sheet ในไฟล์เดียว
โดยไม่มี Option ให้เอาทั้งหมดทุก Sheet มารวมกัน
ดังนั้นเราต้องใช้เทคนิคพิเศษ (ที่ค่อนข้างยุ่งหน่อย) ดังนี้แทนครับ
แล้วเอาแต่ละ Sheet มาต่อกันด้วย
ทำอย่างนี้ทุก File ใน Folder เลย เจ๋งสุดๆ
เป็นยังไงบางครับกับเทคนิคการใช้ Power Query ทำงานในการดัดแปลงข้อมูล จะเห็นว่าถ้าหากเราต้องทำเรื่องแบบนี้ใน Excel นี่จะต้องปวดหัวแน่นอน แต่ถ้าใช้ PowerQuery ก็จะสบายขึ้นเยอะ
ยังไงก็ลองหัดทำดูนะครับ ถ้าสงสัยตรงไหนก็ Comment ถามได้เลยครับ
ใครอ่านแล้วชอบ ก็ช่วยบอกต่อเพื่อนๆ ด้วยนะครับ
ยิ่งคนใช้เป็นเยอะก็ยิ่งดีใจครับ ^^
รวบรวม+ดัดแปลง Data เพื่อเตรียมข้อมูลก่อน Pivot ให้พร้อมแบบอัตโนมัติ ด้วย Power Query
ONSITE (2 วันเต็ม) – ได้เจอวิทยากรตัวจริง!
17-18 พ.ค. 68 | 
9:00 – 17:00 น. | 
Sindhorn Midtown Hotel
ราคาพิเศษ 7,000 – 7,500 บาท/ท่าน (รวม VAT) (ปกติ 8,500 บาท)
Early Bird ชำระก่อน 30 เม.ย. + ลูกค้าเก่า Workshop มีส่วนลดพิเศษ
LIVE ONLINE (6 วัน วันละ 2 ชม.) – เรียนจากที่ไหนก็ได้!
13-15 & 20-22 พ.ค. 68 | 
ผ่าน Zoom
สมัครเลย! ดูรายละเอียดเพิ่มเติมที่นี่ 
Leave a Reply