เรื่องพื้นฐานที่ควรจะรู้เลยคือ การจัดการหัวตาราง ไม่ว่างจะเป็นการ Promote บรรทัดแรกให้กลายเป็นหัวตาราง หรือการปรับหัวตารางให้กลับมาเป็น Data บรรทัดแรก รวมถึงการจัดการบรรทัดข้อมูลที่ไม่ต้องการออกไปก่อน กรณีหัวตารางไม่ได้อยู่บรรทัดบนสุด
บทนี้ให้ Get Data → From Table/Range จากไฟล์ตัวอย่าง (โหลดที่นี่) เพื่อเอาข้อมูลเข้าไปใน Power Query แต่คราวนี้เราไม่ต้องติ๊ก My table has headers เนื่องจากเพราะบรรทัดแรกมันไม่ใช่หัวตาราง
จะได้ Query หน้าตาแบบนี้ ซึ่งจะเห็นว่าหัวตารางจะชื่อ Column1 Column2… ซึ่งไม่ใช่ชื่อหัวตารางที่ควรจะเป็น
สิ่งที่เราควรทำคือ พยายามทำให้ชื่อหัวตารางที่แท้จริงมาอยู่ที่บรรทัดแรกให้ได้ ซึ่งทำได้ 2 วิธี แล้วแต่ความเหมาะสม คือ
- เลือก Home→ Remove Rows→ Remove Top Rows แล้วใส่เลข 2 จะพบว่า Power Query จะเอา 2 แถวบนออกไปเลย
- เลือกคอลัมน์ที่ข้อมูลในตารางจริงมีครบ แต่ข้างบนว่าง เช่น Column2 แล้วกดที่หัว Filter → Remove Empty (ถ้าไม่มีให้เลือก ต้องกด Filter แล้วเลือกแบบ Not Equal แล้วใส่ว่า null)
ผลลัพธ์จะได้หน้าตาออกมาเหมือนกัน คือ ชื่อหัวตารางที่แท้จริงมาอยู่บรรทัดแรกแล้ว
จากนั้นสิ่งที่เราควรทำคือ Promote ให้ข้อมูลแถวแรกกลายเป็นหัวตารางซะ โดยไปที่ Home → Use First Row as Headers จะได้ดังรูป ซึ่งพบว่ามีการ Detect ประเภทข้อมูลให้อัตโนมัติด้วย (ดูที่ icon ของหัวตาราง)
ซึ่งสิ่งที่ได้เกือบจะ Perfect แล้ว เหลือแค่คอลัมน์ผลไม้ มันยังมีช่องว่างๆ อยู่ ซึ่งจะเห็นว่าเราควรจะเอาค่าผลไม้ต่างๆ ที่อยู่ด้านบนมาถมลงช่องว่างเหล่านั้นให้หมด เพื่อให้เราสามารถนำข้อมูลผลลัพธ์ไปวิเคราะห์ต่อได้ง่ายขึ้น ไม่ว่าจะเป็นการ Filter/Sort/SUMIFS/Pivot Table เป็นต้น
สารบัญ
การถมช่องว่าง
วิธีการถมช่องว่างให้เลือกคอลัมน์ผลไม้ แล้วไปที่ Transform → Fill → Down (เพราะเราจะถมข้อมูลจากบนลงล่าง) แค่นั้นจบเลย ช่างง่ายดายกว่าการใช้ Excel ธรรมดามากนัก 555
แล้วถ้าจะถมไปด้านขวาล่ะ?
Power Query มีแต่ Fill Down กับ Fill Up ซึ่งทำง่ายมาก แต่ไม่มีคำสั่ง Fill Right หรือ Fill Left ให้เราใช้…
หากเราจะต้องการจะถมข้อมูลไปด้านขวา ก็จะมีเทคนิคในการพลิกข้อมูลจากคอลัมน์เป็นแถวด้วยการ Transpose ซะก่อน แล้วค่อย Fill Down ครับ เช่น ผมมีข้อมูลแบบนี้
พอเอาเข้า Power Query (ไม่ต้องติ๊กว่า Table มี Header นะ) ช่องที่ Merge ก็จะเห็นเป็น null
ซึ่งจะเห็นว่าเราควรจะ Fill Right เพื่อเอาข้อมูลผลไม้ไปถมช่อง null ด้านขวา แต่มันไม่มีให้เลือก
ดังนั้นเราจะพลิกตารางก่อน โดย ไปที่ Transform → Transpose เราจะได้ผลลัพธ์หน้าตาคล้ายๆ ตัวอย่างก่อนหน้านี้ ซึ่งเราก็จะ Fill Down ได้แล้ว และเราควรจะ Promote Header ด้วยโดยไปที่ Home → Use First Row as Headers
ถ้าเราพอใจกับผลลัพธ์นี้จริงๆ ก็จบได้เลยนะ แต่ถ้ายังอยากจะพลิกให้ผลไม้ไปอยู่ที่คอลัมน์เหมือน Data Source เราก็ควรทำให้หัวตารางไม่ซ้ำกัน เราจะเอาข้อมูลในคอลัมน์ผลไม้กับเกรด มา Merge รวมกัน เช่น แอปเปิ้ล A มันจะได้ไม่ซ้ำกัน
จากนั้นเราค่อยเลือกทั้งสองคอลัมน์ แล้วไปที่ Transform → Merge Columns แล้วเราสามารถเลือกตัวคั่นได้ ในที่นี้ผมเลือกเป็น Space
เราจะได้ข้อมูลในคอลัมน์ Merged โดยที่แต่ละตัวไม่ซ้ำกันแล้ว
แล้วเราค่อย Transform → Transpose กลับไปให้หน้าตาเหมือนเดิม
จากนั้นก็ Home → Use First Row as Headers เป็นอันจบครับ
ระวังเรื่องการอ้างอิงชื่อคอลัมน์ในสูตร M Code
อย่างไรก็ตาม จะเห็นว่าการเปลี่ยน Data Type รวมถึงการถมช่องว่างมันมีการอ้างอิงชื่อคอลัมน์ในสูตรด้วย (ซึ่งการ Transform หลายๆ ตัวจะเป็นแบบนี้เช่นกัน)
การเปลี่ยน Data Type
การ Fill Down
ดังนั้นถ้าหากในอนาคตชื่อคอลัมน์ในตารางต้นทางเปลี่ยนไป Query นี้ก็จะมีปัญหาทันทีครับ
เช่น ผมลองเปลี่ยนชื่อคอลัมน์ ผลไม้ หรือแม้กระทั่งเกรด เป็นชื่ออื่น มันก็จะพัง Refresh ไม่ได้ทันที
หากลองเข้าไป Edit ใน Query ดู จะเก็นว่ามัน Error ตั้งแต่ Step Change Type เลยครับ
จะแก้ไขก็ต้องลบ Step นั้นทิ้งไปซะ และอาจต้องแก้บางอย่างอีก ซึ่งยุ่งยากพอสมควร ดังนั้นการทำงานกับ Power Query โดยทั่วไปแล้วผมแนะนำอย่างยิ่งว่าอย่าไปแก้ชื่อหัวตารางเล่นเด็ดขาดครับ
การเปลี่ยนประเภทข้อมูลใน Power Query
เวลาเราเอาข้อมูลเข้า Power Query ปกติแล้วมันจะ Detect ประเภทข้อมูลของหัวตารางให้เราโดยอัตโนมัติ ซึ่งส่วนใหญ่แล้วมันก็จะทำได้ถูกต้อง อย่างไรก็ตามหากมันทำผิด เราก็ต้องแก้ไขประเภทข้อมูลให้เป็น
สมมติผมเอาข้อมูล Table แบบนี้เข้าใน Power Query โดย Get Data → From Table/Range
จะเห็นว่ามันเปลี่ยน Data Type ให้เราโดยอัตโนมัติ (สังเกตที่สัญลักษณ์ที่ซ้ายมือของชื่อคอลัมน์)
ที่ Applied Step ก็จะมี Change Type โดยอัตโนมัติด้วย
ตรงนี้หากเราอยากให้ ID สินค้า เป็น Text แทนที่จะเป็นตัวเลข ก็สามารถกดเปลี่ยนที่ icon ด้านซ้ายของหัวตารางแต่ละคอลัมน์ที่ต้องการได้เลย
ซึ่งจะเห็นว่าจริงๆ แล้วมีข้อมูลประเภทอื่นๆ อีกเต็มเลย!!
กลุ่มตัวเลขจะมี 4 แบบ คือ
- Decimal ทศนิยมแบบละเอียด
- Currency สกุลเงิน โดยมีทศนิยมสูงสุดแค่ 4 ตำแหน่ง
- Whole Number จำนวนเต็ม
- Percentage ก็คือ แสดงให้เห็นเป็นเปอร์เซ็นต์ใน Preview (แต่โหลดออกมาก็เหมือน Decimal)
วันที่และเวลา มี 5 แบบ คือ
- Date/Time มีทั้งวันที่/เวลา
- Date มีแต่วันที่
- Time มีแต่เวลา
- Date/Time/Timezone จะมีทั้งวันที่/เวลา/timezone เช่น +7:00 คือ GMT+7
- Duration เป็นระยะเวลา แสดงเป็น วัน:ชม:นาที:วินาที (มักเกิดจากการคำนวณ)
นอกนั้นมีอย่างละแบบ
- Text ข้อความ
- True/False เป็นค่า Logic จริง เท็จ
- Binary คือ ตัวข้อมูลกลุ่มโครงสร้างพิเศษ ไม่ได้มีตัวเดียว
- Using Locale เอาไว้จัดการเวลาเจอข้อมูลที่มี Format แปลกๆ ที่ขึ้นอยู่กับแต่ละประเทศ เช่น วันที่ หรือ สกุลเงิน ซึ่งจะอธิบายละเอียดในบทหลังๆ ไปครับ
กรณีที่ Step ก่อนหน้าเคยเปลี่ยน Data Type ไปแล้ว และเรากดเปลี่ยน Data Type อีก (เช่น เปลี่ยน ID สินค้าเป็น Text หลังจาก Changed Type แบบ Auto เป็นตัวเลขไปแล้ว) มันจะขึ้นมาถามว่า จะสร้าง Step ใหม่ หรือ แก้ไข Step เดิม
ถ้าเราตอบว่า Add New Step มันจะมีการแปลงประเภทข้อมูลโดย Add Step ใหม่เพิ่มเข้ามา
แต่ถ้ากด Replace Current มันจะแก้สูตรใน Step เดิม ให้ ID สินค้าเป็น Text โดยไม่เพิ่ม Step ใหม่
สังเกตว่าสูตรเดิมมีการเปลี่ยน
จาก
= Table.TransformColumnTypes(Source,{{“ID สินค้า”, Int64.Type}…
เป็น
= Table.TransformColumnTypes(Source,{{“ID สินค้า”, type text}…
ส่วนตัวผมขอแนะนำให้ Replace Current จะดีกว่าครับ เพราะบางครั้งการแก้ Data Type ไปแล้วมันอาจสูญเสียข้อมูลบางอย่างไปเรียบร้อย เช่น แก้ให้เป็นจำนวนเต็มก่อน แล้วปรับเป็นทศนิยมทีหลังก็จะไม่มีผล เพราะค่าทศนิยมมันก็หายกลายเป็น 0 ไปหมดแล้ว
บทความนี้มีที่มายังไง?
บทความนี้เป็นส่วนหนึ่งของตัวอย่างจาก หนังสือ Excel Power Up! เพิ่มพลังการใช้ Excel ของคุณด้วย Power Query โดยผมเอาเนื้อหาบทแรกๆ ซัก 25-30% มาลงในเว็บให้อ่านกันฟรีๆ เลย คนอ่านจะได้ตัดสินใจได้ว่าอยากจะรู้เรื่องราวหลังจากนั้นอีกมั้ย? ซึ่งแค่นี้ก็น่าจะช่วยงานคุณได้เยอะพอสมควรแล้วล่ะ
หากสนใจอ่านตัวอย่างบทอื่นๆ ของหนังสือ ลองดูที่สารบัญข้างล่างได้เลยครับ ^^
สารบัญ Power Query
บทนำ : ทำไมต้องเรียนรู้ Power Query? [ไฟล์ประกอบ]
บทที่ 1 : เข้าใจขั้นตอนการทำรายงานสรุป / วิเคราะห์ข้อมูล [ไฟล์ประกอบ]
บทที่ 2 : ก้าวสู่การเตรียมข้อมูลยุคใหม่ด้วย Power Query [ไฟล์ประกอบ]
บทที่ 3 : ภาพรวมการทำงานกับ Power Query [ไฟล์ประกอบ]
บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query [ไฟล์ประกอบ]
บทที่ 5 : การจัดการหัวตาราง [ไฟล์ประกอบ]
บทที่ 6 : การคำนวณเบื้องต้น [ไฟล์ประกอบ]
บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ [ไฟล์ประกอบ]
บทที่ 8 : การสร้างคอลัมน์ใหม่แบบกำหนดเองด้วย Custom Column [ไฟล์ประกอบ]
บทที่ 9 : การสร้างคอลัมน์ใหม่ตามเงื่อนไข [ไฟล์ประกอบ]
บทที่ 10 : การรวมกลุ่มข้อมูลด้วย Group By [ไฟล์ประกอบ]
บทที่ 11 : การพลิกคอลัมน์เป็นหัวตารางด้วย Pivot Column [ไฟล์ประกอบ]
บทที่ 12 : การยุบหัวตารางหลายคอลัมน์ให้เหลือคอลัมน์เดียวด้วย Unpivot [ไฟล์ประกอบ]
บทที่ 13 : การแยกข้อมูลในคอลัมน์เดียวออกจากกันด้วย Split Column [ไฟล์ประกอบ]
บทที่ 14 : การใช้ Query เป็นตัวแปร [ไฟล์ประกอบ]
บทที่ 15 : การรวมข้อมูลจากหลาย Query [ไฟล์ประกอบ]
บทที่ 16 : การดึงข้อมูลจาก Excel ไฟล์อื่น [ไฟล์ประกอบ]
บทที่ 17 : การดึงข้อมูลจาก Text File/ CSV File [ไฟล์ประกอบ]
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder [ไฟล์ประกอบ]
บทที่ 19 : การดึงข้อมูลจากแหล่งอื่นๆ
บทที่ 20 : การเตรียม Data เพื่อทำ Dashboard
บทที่ 21 : การทำ Pivot Table เพื่อสร้าง Dashboard
บทที่ 22 : เจาะลึก M Code หัวใจของ Power Query
บทที่ 23 : Function คือ ขุมพลังที่แท้จริงของ M Code [ไฟล์ประกอบ]
บทที่ 24 : ตัวอย่างการสร้าง Custom Function [ไฟล์ประกอบ]
บทที่ 25 : การวน Loop [ไฟล์ประกอบ]
บทส่งท้าย : เทพที่แท้จริง
อ่านเนื้อหาบท 22 เป็นต้นไปแบบปรับปรุงใหม่ได้ฟรี ที่นี่ (อัปเดทเรื่อยๆ)
Facebook Group : Power Query Thailand
ผู้ที่สนใจ Power Query อย่างคุณที่มาอ่านบทความนี้ ผมขอเชิญชวนเข้ากลุ่มปิด Power Query Thailand ได้ตาม Link นี้ครับ
