---
title: 7 เทคนิค Power Query
url: https://www.thepexcel.com/7-tips-power-query-excel/
type: post
date: 2019-03-21
updated: 2019-05-26
author: Sira Ekabut
categories: ["Highlights : บทความแนะนำ", Power Query]
tags: [tips]
---

# 7 เทคนิค Power Query

คงปฏิเสธไม่ได้ว่าเครื่องมือที่ร้อนแรงที่สุดของ Excel ยุคใหม่ ก็คือ Power Query ซึ่งเดิมเคยเป็น Add-in ตัวนึงที่ใช้ได้ตั้งแต่ Excel 2010 เป็นต้นไป แต่สำหรับ Excel Version ใหม่ๆ มันได้ถูกฝังตัวมาอยู่ใน Ribbon Data เลย

 

สำหรับคนที่ไม่เคยใช้ Power Query มาก่อนเลย ให้ลองอ่านบทความที่ผมเคยเขียนไว้ก่อน เพื่อที่จะได้เข้าใจภาพรวมมากขึ้นครับ

 

[Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!!](https://www.thepexcel.com/install-powerquery/)  
[Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ](https://www.thepexcel.com/power-query-overview/)

 

ในบทความนี้ ผมจะมาแนะนำเทคนิคเจ๋งๆ ในการใช้ Power Query ให้รู้จักกันมากขึ้นครับ โดยจะขอแนะนำซัก 7 อันก่อนแล้วกันครับ

 

สำหรับไฟล์ประกอบที่ผมใช้ในบทความนี้ [สามารถโหลดได้ที่นี่ครับ](https://drive.google.com/open?id=1j97sXWdwKgh1BfcmwhHTTy3xKOj7dNXT)

 

### Tips 1 : เราไม่จำเป็นต้องเอา Data มาไว้ในไฟล์ Excel จริงๆ

 

ปกติแล้วเวลาเรา Import ข้อมูลจากข้างนอกมาก เราก็มักจะเอา Data มาไว้ในไฟล์ Excel จริงๆ ด้วย ซึ่งข้อเสียคือ จะทำให้ไฟล์ Excel มีขนาดใหญ่โดยไม่จำเป็น และยังมีข้อจำกัดที่ว่า Sheet ของ Excel มีได้แค่ 1 ล้านกว่าๆ แถวด้วย   
  
ซึ่งข้อจำกัดนี้สามารถแก้ไขได้โดยการที่เราสามารถกด Import File แล้วเลือก Load to… ==> Only Create Connection ได้ครับ (แถมเราเอาเข้า PivotTable จาก Query ที่มีแต่ Connection ก็ได้นะ)

 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips1-1.png)
*กดลูกศรข้างๆ ปุ่ม Load*
 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips1-2.png)
*Only Create Connection จะได้ไม่เปลืองเนื้อที่เก็บข้อมูล*
 ![1](https://www.thepexcel.com/wp-content/uploads/2019/03/tips1-3.png)
*จะออกมาเป็น Connection Only*
 ![2](https://www.thepexcel.com/wp-content/uploads/2019/03/tips1-4.png)
*ถ้าจะเอาไป Pivot ก็ได้ โดยเลือก Insert -> PivotTable   
แล้วเลือก External Data Source -> Choose Connection*
 ![3](https://www.thepexcel.com/wp-content/uploads/2019/03/tips1-5.png)
*เลือก Query ที่ต้องการ แล้วกด Open เพื่อทำ PIvot ตามปกติได้เลย*
 

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

 

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

 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips2-1-1024x381.png)
*เลือกคอลัมน์ที่ต้องการให้เหลือ ตามลำดับที่ต้องการให้เรียง (กด Ctrl ค้างไว้เพื่อเลือกหลายๆ อัน)  
จากนั้นกด click ขวา -> Remove Other Columns*
 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips2-2.png)
*สุดท้ายจะเลือกแต่คอลัมน์ที่เราเลือกไว้ โดยเรียงตามลำดับการเลือก*
 

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

 

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

 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips3-1.png)
*ลองกดปุ่ม Filter ดู*
 ![4](https://www.thepexcel.com/wp-content/uploads/2019/03/tips3-2.png)
*เห็นมั้ย? ว่ามันมองเป็น 4 ตัวที่ไม่เหมือนกัน เพราะติดเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่*
 ![5](https://www.thepexcel.com/wp-content/uploads/2019/03/tips3-3.png) 

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

 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips3-4.png)
*กด lowercase ให้แปลงข้อมูลเป็นตัวพิมพ์เล็กทั้งหมด*
 ![6](https://www.thepexcel.com/wp-content/uploads/2019/03/tips3-5.png)
*เหลือแค่ 2 แบบแล้ว*
 

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

 

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

 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips4-1.png)
*จะเห็นว่าจริงๆ มัน split ด้วย Pattern อย่างอื่นได้อีก เช่น แบ่งตัวหนังสือกับตัวเลข เป็นต้น*
 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips4-2.png)
*เลือกได้ว่าจะว่าจะ Split ทุกตัว หรือเอาเฉพาะตัวซ้ายสุด ขวาสุด (ในที่นี้ split ที่ตัวซ้ายสุด)  
ถ้าตัวคั่นเป็นอักษรอื่น ก็เลือก dropdown -> custom ได้*
 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips4-3.png)
*จากคอลัมน์ Name อันเดียว คราวนี้กลายเป็น Name.1 กับ Name.2 แล้ว  
ซึ่งใครจะเปลี่ยนชื่อ column ก็กด Double Click ที่ชื่อ column ได้เลย*
 

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

 

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

 

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

 ![7](https://www.thepexcel.com/wp-content/uploads/2019/03/tips5-1.png) ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips5-2.png)
*เลือก 2 คอลัมน์ (อย่าลืมกด Ctrl) ที่ต้องการพลิกให้มาอยู่ในคอลัมน์เดียวกัน   
แล้ว transform -> Unpivot Columns*
 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips5-3.png)
*จะเห็นว่า Survived กับ Not Survived ถูกพลิกมาอยู่ในคอลัมน์เดียวกันแล้ว (ส่งผลให้จำนวนแถวมีมากขึ้น)*
 

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

 

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

 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips6-1.png)
*เลือก Get Data -> From File -> From Folder*
 ![8](https://www.thepexcel.com/wp-content/uploads/2019/03/tips6-2.png)
*เลือก Folder ที่ต้องการ*
 ![9](https://www.thepexcel.com/wp-content/uploads/2019/03/tips6-3-1024x364.png)
*กด Edit หรือ Transform Data (แล้วแต่ version)*
 ![10](https://www.thepexcel.com/wp-content/uploads/2019/03/tips6-4-1024x177.png)
*จะเห็นว่ามีไฟล์นามสกุลอื่นปนอยู่ (นอกจาก csv ที่เราอยากได้) ดังนั้นก็ Filter ทิ้งไปซะ*
 ![11](https://www.thepexcel.com/wp-content/uploads/2019/03/tips6-5.png)
*กด select all ทิ้งก่อน แล้วค่อยเลือก csv*
 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips6-6.png)
*กดแตกลูกศรตรง Content ออกมา แล้วกด ok จากหน้าต่างใหม่ที่โผล่มา*
 ![Power Query](https://www.thepexcel.com/wp-content/uploads/2019/03/tips6-7.png)
*คราวนี้มันจะเอาทุกไฟล์มาต่อกัน โดยบอกด้วยว่าเอามาจากไฟล์ไหน*
 

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

 

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

 ![12](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-1.png)
*สมมติเราทำแบบเดิม แต่ Filter ให้เหลือแต่ไฟล์ excel (แต่ละไฟล์มีหลาย sheet)*
 ![13](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-2.png)
*พอกดลูกศรตรง Content คราวนี้มันจะให้เลือกทีละ Sheet   
โดยไม่มี Option ให้เอาทั้งหมดทุก Sheet มารวมกัน*
 

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

 ![14](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-3.png)
*สร้าง Custom Column*
 ![Power Query from Excel in Folder](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-4.png)
*ใส่สูตรว่า =Excel.Workbook([Content],true)*
 ![15](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-5.png)
*จะมี Field ใหม่โผล่มา ซึ่งจะกด Expand ได้แล้ว*
 ![16](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-6.png)
*กด ok*
 ![17](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-7.png)
*มันจะ expand ข้อมูลออกมาอีกก้อนนึง คราวนี้เราต้องกด Expand อีกทีที่ DataExpand.Data*
 ![18](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-8.png)
*กด ok*
 ![19](https://www.thepexcel.com/wp-content/uploads/2019/03/tips7-9.png)
*คราวนี้มันก็จะแตกทุก Field ที่มีใน File Excel ได้แล้ว  
แล้วเอาแต่ละ Sheet มาต่อกันด้วย  
ทำอย่างนี้ทุก File ใน Folder เลย เจ๋งสุดๆ*
 

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

 

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

---

_Source: [https://www.thepexcel.com/7-tips-power-query-excel/](https://www.thepexcel.com/7-tips-power-query-excel/)_