คงปฏิเสธไม่ได้ว่าเครื่องมือที่ร้อนแรงที่สุดของ 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 ก็ได้นะ)

Power Query
กดลูกศรข้างๆ ปุ่ม Load
Power Query

Only Create Connection จะได้ไม่เปลืองเนื้อที่เก็บข้อมูล
จะออกมาเป็น Connection Only
ถ้าจะเอาไป Pivot ก็ได้ โดยเลือก Insert -> PivotTable
แล้วเลือก External Data Source -> Choose Connection
เลือก Query ที่ต้องการ แล้วกด Open เพื่อทำ PIvot ตามปกติได้เลย

Tips 2 : ลำดับการคลิ๊ก Column มีผลต่อการจัดเรียง เวลากด Remove Other Columns

เมื่อเรา Edit Query แล้วเข้าสู่ Query Editor หากเราต้องการเอาคอลัมน์ในตารางออกยกเว้นแค่บางคอลัมน์ ปกติก็จะเลือก Column ที่ต้องการ แล้วกด Remove Other Columns ได้เลย แต่รู้หรือไม่ว่า ลำดับก่อนหลังที่เราคลิ๊ก Column นั้นมีผลต่อการจัดเรียงคอลัมน์ด้วยนะ

Power Query
เลือกคอลัมน์ที่ต้องการให้เหลือ ตามลำดับที่ต้องการให้เรียง (กด Ctrl ค้างไว้เพื่อเลือกหลายๆ อัน)
จากนั้นกด click ขวา -> Remove Other Columns
Power Query
สุดท้ายจะเลือกแต่คอลัมน์ที่เราเลือกไว้ โดยเรียงตามลำดับการเลือก

Tips 3 : ใน Power Query อักษรตัวพิมพ์เล็กพิมพ์ใหญ่ ถือว่าเป็นคนละคำกัน

เวลาเราเลือก Filter ข้อมูลใน Power Query จะต้องระมัดระวังเรื่องของข้อมูลที่มีตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย เช่น Filter ว่าเอาเฉพาะคำว่า Female แปลว่ามันจะตัดคำว่า female ทิ้งด้วยนะ ระวังให้ดี!!

Power Query
ลองกดปุ่ม Filter ดู
เห็นมั้ย? ว่ามันมองเป็น 4 ตัวที่ไม่เหมือนกัน เพราะติดเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่

ถ้าไม่อยากมีปัญหาเรื่องนี้ ผมแนะนำให้แปลงข้อมูลเป็นตัวพิมพ์เล็กทั้งหมดด้วย Transform–>Format–>Lower Case ก่อนทำการ Filter นะครับ

Power Query
กด lowercase ให้แปลงข้อมูลเป็นตัวพิมพ์เล็กทั้งหมด
เหลือแค่ 2 แบบแล้ว

Tips 4 : แยกข้อความง่ายๆ ด้วย Split

หากในข้อมูลดิบ มีข้อความหลายตัวที่รวมกันอยู่ในคอลัมน์เดียว แต่คั่นด้วยตัวอักษรบางอย่างเช่น . / – หรือ ตัวอื่นๆ เราสามารถแยกคำเหล่านั้นให้ออกมาให้อยู่คนละคอลัมน์ได้อย่างง่ายดาย

Power Query
จะเห็นว่าจริงๆ มัน split ด้วย Pattern อย่างอื่นได้อีก เช่น แบ่งตัวหนังสือกับตัวเลข เป็นต้น
Power Query
เลือกได้ว่าจะว่าจะ Split ทุกตัว หรือเอาเฉพาะตัวซ้ายสุด ขวาสุด (ในที่นี้ split ที่ตัวซ้ายสุด)
ถ้าตัวคั่นเป็นอักษรอื่น ก็เลือก dropdown -> custom ได้
Power Query
จากคอลัมน์ Name อันเดียว คราวนี้กลายเป็น Name.1 กับ Name.2 แล้ว
ซึ่งใครจะเปลี่ยนชื่อ column ก็กด Double Click ที่ชื่อ column ได้เลย

Tips 5 : Unpivot พลิกข้อมูลหลายคอลัมน์ให้มาอยู่ในคอลัมน์เดียว

งานนี้เป็นงานที่ยากสุดๆ เมื่อใช้ Excel แต่กลับง่ายแบบปอกกล้วยเข้าปากเมื่อใช้ Power Query ซะงั้น

สมมติเรามีข้อมูลตามตาราง ซึ่งมีคอลัมน์ Survived กับ Not Survived แยกกันอยู่

Power Query
เลือก 2 คอลัมน์ (อย่าลืมกด Ctrl) ที่ต้องการพลิกให้มาอยู่ในคอลัมน์เดียวกัน
แล้ว transform -> Unpivot Columns
Power Query
จะเห็นว่า Survived กับ Not Survived ถูกพลิกมาอยู่ในคอลัมน์เดียวกันแล้ว (ส่งผลให้จำนวนแถวมีมากขึ้น)

Tips 6 : เราสามารถดึงข้อมูลจาก ทุก Text File ใน Folder ได้เลย

ถ้าเรามี Text File หรือ CSV หลายๆ ไฟล์อยู่ใน Folder เดียวกัน เราสามารถสั่งให้ Excel ดึงข้อมูลทุกไฟล์ที่อยู่ใน Folder นั้นมา Append หรือเอาข้อมูลมาต่อตูดกันได้เลย

Power Query
เลือก Get Data -> From File -> From Folder
เลือก Folder ที่ต้องการ
กด Edit หรือ Transform Data (แล้วแต่ version)
จะเห็นว่ามีไฟล์นามสกุลอื่นปนอยู่ (นอกจาก csv ที่เราอยากได้) ดังนั้นก็ Filter ทิ้งไปซะ
กด select all ทิ้งก่อน แล้วค่อยเลือก csv
Power Query
กดแตกลูกศรตรง Content ออกมา แล้วกด ok จากหน้าต่างใหม่ที่โผล่มา
Power Query
คราวนี้มันจะเอาทุกไฟล์มาต่อกัน โดยบอกด้วยว่าเอามาจากไฟล์ไหน

Tips 7 : เทคนิคพิเศษดึงข้อมูลจากไฟล์ Excel ทุก File ใน Folder

ถ้าเราต้องการ รวมไฟล์ Excel ใน Folder มันจะใช้วิธีปกติไม่ได้ครับ ทั้งนี้เพราะโครงสร้าง Excel มันสามารถมีได้หลาย Sheet ในไฟล์เดียว

สมมติเราทำแบบเดิม แต่ Filter ให้เหลือแต่ไฟล์ excel (แต่ละไฟล์มีหลาย sheet)
พอกดลูกศรตรง Content คราวนี้มันจะให้เลือกทีละ Sheet
โดยไม่มี Option ให้เอาทั้งหมดทุก Sheet มารวมกัน

ดังนั้นเราต้องใช้เทคนิคพิเศษ (ที่ค่อนข้างยุ่งหน่อย) ดังนี้แทนครับ

สร้าง Custom Column
Power Query from Excel in Folder
ใส่สูตรว่า =Excel.Workbook([Content],true)
จะมี Field ใหม่โผล่มา ซึ่งจะกด Expand ได้แล้ว
กด ok
มันจะ expand ข้อมูลออกมาอีกก้อนนึง คราวนี้เราต้องกด Expand อีกทีที่ DataExpand.Data
กด ok
คราวนี้มันก็จะแตกทุก Field ที่มีใน File Excel ได้แล้ว
แล้วเอาแต่ละ Sheet มาต่อกันด้วย
ทำอย่างนี้ทุก File ใน Folder เลย เจ๋งสุดๆ

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

ยังไงก็ลองหัดทำดูนะครับ ถ้าสงสัยตรงไหนก็ Comment ถามได้เลยครับ
ใครอ่านแล้วชอบ ก็ช่วยบอกต่อเพื่อนๆ ด้วยนะครับ
ยิ่งคนใช้เป็นเยอะก็ยิ่งดีใจครับ ^^


Comments

One response to “7 เทคนิค Power Query”

  1. chareerat Avatar

    เราจะดึงข้อมูลจากไฟล์ Excel ทุก File ทุกsheet ใน Folder ได้ยังไงคะ
    แล้วถ้าข้อมูลที่เราอยากให้เป็นหัวตารางเริ่มต้นที่ row24 ได้รึเปล่าคะ

    ขอบคุณค่ะ

Leave a Reply

Your email address will not be published. Required fields are marked *