---
title: เทคนิคการเผชิญหน้ากับข้อมูลเน่าเฟะด้วย Power Query
url: https://www.thepexcel.com/bad-format-data/
type: post
date: 2020-02-22
updated: 2025-12-22
author: Sira Ekabut
categories: [Power Query]
tags: [format, bad, Table.AddIndexColumn]
---

# เทคนิคการเผชิญหน้ากับข้อมูลเน่าเฟะด้วย Power Query

หากคุณกำลังเจอข้อมูลเน่าเฟะ อย่าเพิ่งสิ้นหวัง…

 

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

 

ในบทความนี้เราจะมาเผชิญความเน่าเฟะไปด้วยกันครับ

 

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

 

## ข้อมูลเน่าเฟะที่ได้มา

 

สมมติว่าเราได้ข้อมูลหุ้นในตลาด SET100 ทุกตัวหน้าตาแบบนี้มา ซึ่งผมใช้ Python ดึงมารวมกันแบบไม่ได้ Clean ดีๆ ตั้งแต่แรก (จะได้มาฝึกทำใน Power Query ไง 555)

 

ซึ่งดาวน์โหลดไฟล์ได้ที่นี่ ([Google Drive](https://drive.google.com/open?id=173ZcG6eV5HbTMu8K_3t_M0f6d14drqmU) / [GitHub](https://github.com/siraekabut/siraekabut.github.io/blob/master/SET100.xlsx))

 

**ความยาก1** มี Topic แทรกอยู่ระหว่างบรรทัดอยู่เป็นระยะๆ เช่น ทรัพย์สินรวม หนี้สินรวม

 ![1](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ001-fix.png) 

**ความยาก2** จำนวนตัวเลขต่อ 1 Topic มี 4 ตัวบ้าง 5 ตัวบ้าง ไม่แน่นอน  
ซึ่งความเป็นจริงคือข้อมูลย้อนหลังจนถึงปีปัจจุบัน Y-4, Y-3, Y-2, Y-1, Y0 ซึ่งบาง Topic มี 4 บาง Topic มี 5

 ![2](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ002-fix.png) 

## หน้าตาผลลัพธ์ที่ต้องการ

 

ผลลัพธ์ที่อยากได้ เราอยากเอา Topic ทั้งหมดขึ้นเป็นหัวตารางไล่ไปทางด้านขวา และหุ้นตัวนึง 1 บรรทัดคือข้อมูล 1 ปี ดังนั้นถ้ามีทั้งหมด 5 ปี หุ้นตัวนึงจะมี 5 บรรทัด

 ![3](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ002-2-1024x397.png) 

## เริ่มกำจัดความเน่าเฟะ

 

### สร้าง Topic แยกออกมาให้ได้

 

หลังจากสั่ง Get Data from Table/Range เพื่อเอาข้อมูลเข้าสู่ Power Query

 

ก่อนอื่น เราต้องคิดว่า ถ้าเราอยากเอา Topic ไปทำเป็นหัวตาราง เราต้องสั่ง Pivot เอา Topic ขึ้นไป

 

และการจะทำ Pivot ได้ ก็ต้องมีคอลัมน์ Topic ขึ้นมาโดยเฉพาะซะก่อน โดย Topic จะต้องถมค่าแบบนี้ให้ได้

 ![4](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ003-1024x873.png) 

การจะถมค่าได้ เราต้องเอาหัว Topic แต่ละบรรทัดมาให้ได้ก่อน แล้วค่อย Fill Down ลงมา

 

แล้วการจะได้หัว Topic มาเราก็ต้องรู้ให้ได้ว่าอันไหนคือหัว Topic อันไหนไม่ใช่

 

จากที่ลองไล่ดูแล้ว การจะได้หัว Topic มา น่าจะต้องดูว่าข้อมูลเป็นข้อความรึเปล่า? ถ้าเป็นข้อความน่าจะเป็นหัว Topic

 

ข้อมูลในคอลัมน์ Data จริงๆ มันก็เป็นข้อความหมดแหละ แค่เป็นตัวที่หน้าตาแบบข้อความจริงๆ กับข้อความที่หน้าตาเหมือนตัวเลข ดังนั้นเราจะลองบังคับขืนใจให้มันเป็นตัวเลขดูว่ามันยอมมั้ย? ถ้ายอมแสดงว่าหน้าตามันเป็นตัวเลขได้

 

ดังนั้นเราจะสั่ง Duplicate Column Data ออกมาอันนึงก่อน โดยคลิ๊กขวาที่คอลัมน์ Data แล้ว Duplicate Column จากนั้นสั่งแปลงเป็น Decimal Number ซะ

 ![5](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ004-1024x708.png) 

จะพบว่าอันไหนพอจะแปลงเป็นตัวเลขได้ มันก็จัดให้เรียบร้อย อะไรที่ทำไม่ได้ก็ค้างเป็น Error

 

ถ้าไล่ดูจะเห็นว่ายังมีประเด็นค้างอยู่อีก 2 ตัว คือ วันที่ กับค่า N/A

 ![6](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ005.png) 

เนื่องจากค่า N/A ยังไงก็ใช้ไม่ได้ ดังนั้นผมจะย้อน Step กลับไปก่อนจะ Duplicated Column แล้วสั่ง Replace Value (Insert Step) แล้วทำให้ N/A ให้เป็น null ซะ

 ![7](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ006-1024x353.png) 

จากนั้นสั่ง Duplicate คอลัมน์ Data ออกมา เพื่อจัดการแปลงเป็นวันที่ซะ โดยใช้ Using Locale เป็น Thai (เพราะวันที่ที่เป็น text นั้นเป็นรูปแบบไทย)

 ![8](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ007-1024x473.png) 

จะเห็นว่ามันออกมาเป็นวันที่ได้แล้ว แต่ว่าดันติดวันที่เกินๆ มาด้วย (ด้วยความฉลาดที่มากเกินไป ดันมองว่าทศนิยมบางตัวเป็นวันที่ได้ 555)

 ![9](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ008-1024x643.png) 

เพื่อให้จัดการค่าง่ายขึ้นผมจะ Replace Error ใน Data-Copy และ Data-Copy.1 ด้วย null ทั้งหมด

 ![10](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ009-1024x575.png) 

จากนั้นผมสั่ง Conditional Column เพื่อให้เลือกเอา Data ให้เหลือคอลัมน์เดียว นั่นคือ ได้ Data Copy ว่าง ให้เอาจาก Data-Copy.1 แทน

 ![11](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ010-1024x439.png) 

จะได้แบบนี้ ซึ่งดีขึ้นมาก

 ![12](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ011-1024x546.png) 

ดังนั้นบรรทัดที่ยังเป็น null ในคอลัมน์ NumData จะตรงกับ Topic ที่อยากได้ในคอลัมน์ Data พอดีเลย ดังนั้นเราจะใช้ Conditional Column ได้

 ![13](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ012-1024x428.png) 

สุดท้ายจะได้แบบนี้

 ![14](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ013-1024x519.png) 

คราวนี้เราก็ Fill Down คอลัมน์ Topic ได้

 

จากนั้นเลือกเก็บไว้แค่คอลัมน์ Symbol, Topic และ NumData พอ ด้วยการคลิ๊กเลือก 3 คอลัมน์ที่ต้องการแล้ว Remove Other Column

 ![15](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ014.png) 

จากนั้นเราสามารถ Filter เอาบรรทัด null ใน NumData ทิ้งได้เลย

 

แต่จากการตรวจดู Data พบว่า บาง Topic ดันมี 4 ตัวบ้าง 5 ตัวบ้าง (ซึ่งไม่แปลก เพราะบางค่าของปีล่าสุดจะยังไม่มี) แต่ดันมี Topic ที่มี 3 ตัวด้วย! ซึ่งน่าจะเกิดจากการที่เดิมเป็น N/A แล้วผมสั่งให้เป็น Null

 

### กลับไปแก้ไข Step เก่าให้รัดกุมขึ้น

 ![16](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ015-1.png) 

ดังนั้นการ Replace ให้ N/A เป็น null ก่อนหน้านี้อาจไม่ใช่วิธีที่ดีแล้ว ดังนั้นเราจะไปแก้ให้ Replace N/A เป็นเลขซักตัวนึงที่เรามั่นใจว่าไม่มีทางใช่ข้อมูลจริงดีกว่า เช่น -99999

 ![17](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ016-1024x515.png) 

คราวนี้เราจะได้บรรทัดกลับมาครบตามที่ควรจะเป็นแล้ว (และเรียงถูกต้องตาม sequence เดิม)

 ![18](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ017-1024x430.png) 

แล้วเราค่อย Replace -99999 ของเราด้วย null ใหม่อีกที

 ![19](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ018-1024x713.png) 

### สร้างเลข Running ตาม Topic

 

คราวนี้เราต้องการจะสร้างเลข Running 1,2,3,4,5 ตาม Topic แต่ละตัว (โดยบางตัวอาจมี 4, บางตัวอาจมี 5)

 

ถ้าเรากด Add Index คอลัมน์ตรงๆ มันจะไม่ Reset ใหม่ตาม Topic แต่เราจะได้ Code ออกมาตามนี้

 

```
= Table.AddIndexColumn(#"Replaced Value1", "Index", 1, 1)
= Table.AddIndexColumn(ชื่อ Table ,ชื่อคอลัมน์,เลขตั้งต้น,เพิ่มทีละ)
```

 

ซึ่งเดี๋ยวเราจะจำเอาไว้เลียนแบบตอนทำเรื่อง Group by ต่อไป

 

เพื่อให้เราสามารถ Add Index เฉพาะกลุ่มได้ ดังนั้นเราจะใช้ Group By แบบ All Row มาช่วย ดังนี้

 ![20](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ019-1024x738.png) 

เราจะได้ผลลัพธ์ออกมาเป็น Table ซึ่งถ้าคลิ๊กดูจะเป็น Table ที่มีครบทุกคอลัมน์เลย แต่มีการ Group ให้เหลือตามที่เรา Group By เท่านั้น

 ![21](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ020.png) 

ซึ่งที่เราจะทำคือเราจะ Add Index คอลัมน์ โดยการใส่ Custom Function แบบนี้

 ![22](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ021-1024x548.png) 

```
=Table.AddIndexColumn(ชื่อ Table ,ชื่อคอลัมน์,เลขตั้งต้น,เพิ่มทีละ)
```

 

```
=Table.AddIndexColumn([Data],"running",1,1)
```

 

ซึ่งในคอลัมน์ NewData เราจะได้ Table ใหม่ ซึ่งมีคอลัมน์ running เพิ่มขึ้นมา

 ![23](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ022-1024x671.png) 

แต่ถ้าเราอยากให้ไล่จาก -4,-3,-2,-1,0 แทน 0,1,2,3,4 ก็ต้องแก้สูตรเป็น

 

```
=Table.AddIndexColumn([Data],"running",-4,1)
```

 ![24](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ023-fix-1024x652.png) 

คราวนี้เราจะลบคอลัมน์ Data ทิ้ง เพราะไม่ใช้แล้ว

 

หลังจากนั้นกด Expand คอลัมน์ NewData ออกมาโดยเอาแค่ NumData กับ Running ดังรูป

 ![25](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ024.png) ![26](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ025-fix-1024x400.png) 

เราจะสั่ง Pivot คอลัมน์ Topic ไปเป็นหัวตาราง แต่ว่าข้อมูลมันจะยัง Sum ไม่ได้เพราะยังไม่ได้เปลี่ยน NumData ให้เป็นตัวเลข

 

ดังนั้นให้**เปลี่ยนประเภทข้อมูล NumData ให้เป็นตัวเลขก่อน**ครับ (ถึงจะเลือกการ Sum ใน Step ถัดไปได้)

 

จากนั้นค่อยเลือกคอลัมน์ Topic แล้วสั่ง Pivot Column

 ![27](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ027-fix-1024x571.png) 

ผลลัพธ์จะออกมาเป็นแบบที่อยากได้แล้ว

 ![28](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ028-1024x544.png) 

### ความเน่าเฟะที่ยังหลงเหลือ

 

แต่ถ้าตรวจดูความเรียบร้อย ดันมีคอลัมน์งอกเกินมา ซึ่งเป็นผลมาจาก Symbol บางตัว

 ![29](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ029-1024x539.png) 

ดังนั้นต้องย้อน Step กลับไปดูว่าพลาดตรงไหน

 ![30](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ030-1024x436.png) 

จะพบว่า ดันมี Text จำพวก N/A ที่เราตั้งใจแปลงเป็น -99999 แต่ดันมี Text อื่นอีก เช่น N.A. และ –

 

ดังนั้นเราต้องย้อน Step กลับไป Replace Value ให้ N.A. และ – เป็น -99999 ด้วย (แบบ Insert Step นะ)

 ![31](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ031-fix-1024x420.png) 

จากนั้นก็ Replace – ด้วย -99999 อีกที แต่ให้ติ๊กว่าต้องเจอแบบ Entire Cell Content ด้วยนะ ไม่งั้นมันแทนเครื่องหมายติดลบด้วยซวยเลย

 ![32](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ032-1024x446.png) 

ถ้าเรากลับไปดู Step สุดท้าย จะพบว่าคอลัมน์ที่งอกเกินมาได้หายไปแล้ว

 ![33](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ033-1024x423.png) 

### จัดการวันที่

 ![34](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ034-1024x421.png) 

เหลือปัญหาสุดท้ายคือ Data Type ของวันที่งบการเงิน แค่กดเปลี่ยน Data Type เป็น Date ก็จบเลย เพราะเลขเป็น 4 หมื่นกว่าอยู่แล้ว

 ![35](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ035-1024x515.png) 

### แก้คอลัมน์ Running ให้เป็น Year

 

เดิม Running เราเป็น -4,-3,-2… สมมติว่าเราจะทำให้เป็น Y-4, Y-3, Y-2 ก็ทำได้

 

ให้ไปที่คอลัมน์ Running -> Transform -> Format -> Add Prefix แล้วใส่คำว่า Y

 ![36](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ036-1024x389.png) 

จากนั้นเปลี่ยนชื่อคอลัมน์เป็น Year ซะก็จบละ

 

## จบแล้ว สำเร็จ!!

 

ในที่สุด เราก็ได้ฐานข้อมูลที่ต้องการแล้ว ดังนั้นกด Close & Load to.. ออกมาเป็น Table หรือจะเอาเข้า Pivot Table เลยก็ย่อมได้

 ![37](https://www.thepexcel.com/wp-content/uploads/2020/02/Stock-PQ037-1024x518.png) 

เป็นอย่างไรบ้างกับการต่อสู้อันยาวนานกับข้อมูลขยะอันเน่าเฟะ อาจมีขั้นตอนเยอะซักหน่อย แต่คิดว่าเป็นประโยชน์กับทุกคนแน่นอนครับ ใครสงสัยตรงไนก็ post ถามได้เลยนะครับ

---

_Source: [https://www.thepexcel.com/bad-format-data/](https://www.thepexcel.com/bad-format-data/)_