มารู้จัก Power Query ความสามารถใหม่ของ MS Excel กัน

สวัสดีครับ พอดีผมได้นำความสามารถของ Power Query มาช่วยแก้ไขปัญหางานได้อย่างมีประสิทธิภาพและเห็นว่ามีประโยชน์กับคนอื่นจำนวนมากจึงนำมาแบ่งปันกันครับ Power Query ของ Microsoft Excel นี้เป็นโปรแกรมเสริมตัวใหม่ที่เพิ่มเติมใน Excel ขึ้นมาเพื่อทลายข้อจำกัดในการดึงข้อมูลที่ซับซ้อนอย่าง MS Access ที่ทำได้อยู่แล้วมานาน โดย concept นั้นจะคล้ายคลึงกับการใช้ Query ใน database เลยครับ คือสามารถทำ Sub-Query แล้วมาเชื่อม Query เข้ากันทีหลังก็ได้ มาเชิญชมกันเลยดีกว่าครับ

Power Query คืออะไร

หน้าที่ของ Power Query นั้นสามารถทำเรื่อง ETL(Extract,Transform,Load) แบบย่อมๆได้เลยครับ คือ
1. สามารถดึงข้อมูลจากหลายๆแหล่ง เช่น Text file, Excel, CSV, Database หรือข้อมูล online sharepoint, onedrive, และกระทั่ง website ต่างๆได้(Extract)
2. แก้ไขข้อมูลจากหลายๆแหล่ง และหลายรูปแบบให้อยู่ในรูปแบบเดียวกันได้(Transform)
3. เรียกดูข้อมูลด้วยเงื่อนไขที่ซับซ้อน แล้วแสดงผลลงในไฟล์ Excel ได้ทันที(Load)

วิธีการใช้งานเบื้องต้น

1. หาก Excel คุณเป็น version 2010 หรือ 2013 คุณจำเป็นต้องโหลด add-in ตาม link นี้
https://www.microsoft.com/en-us/download/details.aspx?id=39379
แต่ถ้าเป็น version office 365 หรือ 2016 เป็นต้นไป ก็ข้ามส่วนนี้ไปได้เลยครับ
2.ใน tab data จะมีส่วนที่ชือว่า Get & Transform ให้เลือก New Query แล้วเลือก data source ที่ต้องการ
3.เมื่อเลือก source แล้ว สามารถเลือกว่าจะ Load มาแสดงผลที่ Excel ทันทีเลยหรือเลือก edit เพื่อไปแก้ไขรูปแบบข้อมูลต่างๆได้ ตัวอย่างนี้จะพาไปเลือก edit กันครับ
4. ในส่วน menu ต่างๆในหน้า Query Editor จะเป็นหน้าไว้สำหรับทำลำดับการ transform ต่างๆ ผมจะแนะนำส่วนที่สำคัญๆสำหรับในหน้านี้ก่อนนะครับ

4.1 Tab Home

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

Merge Query จะเป็นการนำ Query สองตัวมา join กัน หรือนำมาเชื่อมต่อกันแล้วนำ column จากตารางอื่นมาเพิ่มในตารางตนเองได้ โดยใช้ Key จากสองตารางเป็นตัวเชื่อม (คล้ายๆกับการใช้ Vlookup นั่นเองครับ)

Append Query เป็นการเอารวม Query สองตัวมาต่อท้ายกัน

4.2 Transform

จะเป็นส่วนที่ใช้สำหรับแปลงรูปแบบข้อมูลต่างๆ ที่น่าสนใจมีดังนี้ครับ

Group by ใช้สำหรับ group column และเพื่อใช้สูตรรวมพื้นฐานเช่น sum, count, average และอื่นๆ(คล้ายๆ Pivot table)

Transpose ใช้สำหรับสลับจาก row เป็น column และ column ไปเป็น row

Split Column ใช้สำหรับแตกข้อมูลให้เป็นหลาย column ด้วยตัวแบ่งเช่น , $ ; tab และอื่นๆ หรือจะแบ่งด้วยจำนวนตัวอักษรก็ได้ครับ

Replace Value ใช้สำหรับการ clean data ให้เปลี่ยนค่าเก่าให้ไปเป็นค่าที่กำหนด

4.3 Add Column

ใช้เพิ่ม column ใหม่ ในตาราง ซึ่งยืดหยุ่นในการทำ transform มากๆเพราะเราสามารถใส่สูตรต่างๆเพื่อแปลงข้อมูลได้ โดยสิ่งที่น่าสนใจมีดังนี้ครับ

Conditional Column
เป็น column ที่ใช้เงื่อนไข IF ELSE เทียบกับข้อมูลในบรรทัดเดียวกัน เพื่อใช้สร้างข้อมูลใหม่ เช่น ถ้าข้อมูลเพศมีหลากหลายคือ ชาย, ผู้ชาย, male, M โดย column ใหม่ที่สร้างก็ให้แสดงค่าเป็น M อย่างเดียว เพื่อจัดระเบียบให้เป็นรูปแบบเดียวกัน

Custom Column
ใช้สร้าง Column ตามสูตรที่เราต้องการ

เช่นเราอยากเอาข้อมูลจากหลาย column มาต่อกัน เพื่อสร้างเป็น Key ใหม่ไว้เพื่อ join กับ query อื่นที่ต้องใช้หลาย key ได้

4.4 View

เป็นเครื่องมือเพื่อให้เราเห็นภาพรวมและ flow ของแต่ละ query ซึ่งทำให้เราตรวจสอบและเข้าใจได้ง่ายขึ้น

เนื้อหาต่อไปเริ่มจะลงรายละเอียดแล้ว ผมจึงขอจบในส่วน Introduction ไว้เท่านี้ก่อนนะครับ หากมีข้อสงสัยสามารถพูดคุยสอบถามใน facebook page ได้เลยนะครับ แล้วติดตามบทความใหม่ๆถัดไปนะครับ สวัสดีครับ

No Comments

Leave a Comment