จากการศึกษา Power Query อย่างจริงจังในช่วง 4 ปีที่ผ่านมา ทำให้ผมคิดว่าหลังจากนี้วิธีใช้งาน MS Excel จะไม่เหมือนเดิมอีกต่อไป

คำว่า “ไม่เหมือนเดิม” คือเปลี่ยนจากหน้ามือเป็นหลังมือ ถ้าเทียบกับรถก็คือการเปลี่ยนแบบ Major Change

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

ทำไม?

เพราะ Power Query ใช้งานง่ายยิ่งกว่าปอกกล้วยเข้าปาก จากเดิมที่เคยนั่งทำแบบถึกๆเป็นชั่วโมงก็จะเสร็จเพียงไม่กี่คลิก

ถ้าข้อมูลเกิดการเปลี่ยนแปลงก็ทำซ้ำทุกขั้นตอนด้วยการคลิกรีเฟรช

ที่สำคัญคือไม่ต้องเขียนสูตรสักตัว!

ฟังดูเจ๋งมากเลยใช่ไหม งั้นมารู้จักกับ Power Query กัน ^__^

Power Query คืออะไร?

Power Query คือชุดคำสั่งที่มีใน Microsoft 365 และ Excel 2016/2019 อยู่ใน Ribbon Data/ Get & Transform Data

อันที่จริง Power Query สามารถใช้งานใน Excel 2010/ Excel 2013 ได้ด้วยเช่นกัน แต่ต้องดาวน์โหลด Add-Ins เพิ่มเติม ซึ่งดาวน์โหลดได้ฟรีจากเว็บของไมโครซอฟต์ หรือก็คือลิงค์นี้ครับ

https://www.microsoft.com/en-us/download/details.aspx?id=39379

Power Query ถือเป็นหนึ่งในเครื่องมือตระกูล Power Tools อันได้แก่ Power Query, Power Pivot, Power View และ Power Map ซึ่งออกแบบมาเพื่อรองรับ Big Data

Power Query ใช้ทำอะไร?

Power Query ใช้แปลงข้อมูลเพื่อให้ได้รูปแบบตามต้องการ (ภาษาอังกฤษใช้คำว่า Data Shaping)

คำว่า “แปลง” หมายรวมไปถึง การ Filter, Sort, Copy, Remove, Transpose หรือทำทุกอย่างเพื่อปรับข้อมูลให้ได้หน้าตาในแบบที่อยากให้เป็น

Power Query มีข้อดียังไง?

จุดเด่นของ Power Query มี 4 ข้อคือ

1. ใช้กับข้อมูลขนาดใหญ่ได้ คำว่า “ใหญ่” ในทีนี้คือได้ถึงระดับ 100,000,000 บรรทัด (อ่านไม่ผิดหรอกครับ ร้อยล้านบรรทัด) ซึ่ง Excel ทำงานกับข้อมูลได้เพียงระดับหนึ่งล้านบรรทัดเท่านั้น
2. ทำงานเร็วปรื๊ด แม้ว่าทำงานกับข้อมูลขนาดใหญ่แต่ใช้เวลาน้อยมาก ถ้าเป็น Excel อาจใช้เวลาหลายนาทีกว่าจะคำนวณเสร็จ แต่ Power Query อาจใช้เวลาเพียงไม่กี่วินาที
3. ใช้งานซ้ำและอัพเดตได้ตามต้องการ ถ้าข้อมูลเกิดการเปลี่ยนแปลงหรือมีข้อมูลใหม่เพิ่มเติม การจะทำให้ได้หน้าตาเหมือนเดิมอีกครั้งลำบากเพียงยกเมือเท่านั้น แค่คลิกขวาแล้วกด Refresh จบ!
4. ใช้งานง่าย ไม่จำเป็นต้องรู้สูตรหรือฟังก์ชันใดๆ แค่คลิกไม่กี่ทีก็เสร็จ พูดง่ายๆคือใครๆก็ใช้ได้

Power Query ทำอะไรได้บ้าง?

Power Query แม้จะเป็นเครื่องมือในเอ็กเซล แต่การใช้งานจะใช้ผ่านหน้าต่าง Power Query Editor ซึ่งผมเรียกเจ้าหน้าต่าง Power Query Editor นี้ว่า “โลกของพาวเวอร์คิวรี่” ^__^

การจะใช้งาน Power Query ในเอ็กเซลนั้น ทางที่ดีควรเปลี่ยนฟอร์แมตข้อมูลให้เป็น Table ก่อน (กด Ctrl+T)

การจะทำให้ Table ในเอ็กเซลเข้าไปใน Power Query Editor ได้นั้น ให้คลิกที่ข้อมูลแล้วไปที่ Data/ From Table/ Range

หน้าต่างของ Power Query Editor หรือ “โลกของ Power Query” จะโผล่ขึ้นมา หน้าตาแบบนี้ครับ

Power Query มีเครื่องมือให้ใช้มากมาย

เครื่องมือหลักอยู่ใน 3 ริบเบิน (Ribbon) คือ Home

Transform

และ Add Column

อย่างไรก็ตาม ในการทำงานจริงเรามักใช้เครื่องมือโดยคลิกที่คอลัมน์แล้วคลิกขวาเพื่อเรียกใช้งานเมนูลัดมากกว่า

หรือถ้าต้องการแปลงข้อมูลในระดับทั้งตาราง อาจคลิกปุ่มซ้ายบนก็ได้

เมื่อคลิกปุ่มซ้ายบน จะแสดงเมนูคำสั่งที่ใช้จัดการกับทั้งตารางหน้าตาแบบนี้

ถ้าจะอธิบายว่า Power Query ทำอะไรได้บ้าง อาจต้องใช้ 10 บทความเพื่อเขียนให้เห็นถึงความสามารถทั้งหมด  ซึ่งผมคงอธิบายไม่ไหว T_T

เพื่อให้เข้าใจในภาพรวม ผมขออธิบายความสามารถของ Power Query โดยเปรียบเทียบกับความสามารถของ Excel น่าจะทำให้เข้าใจในภาพรวมได้ชัดเจนขึ้น ^__^

เมื่อเปรียบเทียบความสามารถของเอ็กเซล Power Query สามารถแบ่งได้เป็น 2 หมวดหลักๆ คือ

1. ความสามารถที่ Excel ทำได้
2. ความสามารถที่ Excel ทำได้ แต่เหนื่อย

(การเปรียบเทียบความสามารถแบบนี้เกิดจากมุมมองส่วนตัวของผม ซึ่งแต่ละคนอาจมีมุมมองไม่เหมือนกัน)

1 ความสามารถที่ Excel ทำได้

ความสามารถที่ Excel ทำได้ Power Query ก็ทำได้ และทำได้ดีกว่า!

ที่ว่าทำได้ดีกว่าก็คือ เมื่อข้อมูลเกิดการเปลี่ยนแปลงหรือมีข้อมูลใหม่เพิ่มเติม Power Query สามารถทำซ้ำทั้งหมดใหม่โดยการคลิกขวาแล้วกด Refresh ในขณะที่ Excel แบบดั้งเดิมจะต้องทำใหม่ทั้งหมดตั้งแต่สเต็ป 1, 2, 3, 4…

ความสามารถที่ Excel ทำได้และชื่อเหมือนกันทั้งใน Excel และ Power Query ก็คือ

• Filter
• Sort
• Remove Rows
• Remove Columns
• Remove Duplicates
• Move (ไปด้านซ้าย, ขวา, คอลัมน์แรก, คอลัมน์สุดท้าย)
• Transpose

ความสามารถที่ Excel ทำได้แต่ชื่อไม่เหมือนใน Power Query ก็คือ

• Text to Columns/ Flash Fills: ใน Power Query ใช้ชื่อว่า Split Column
• ฟังก์ชัน LEFT, RIGHT, MID: ใน Power Query ใช้ชื่อว่า Extract
• Consolidate: ใน Power Query ใช้ชื่อว่า Append Queries
• Replace (Ctrl+H) และฟังก์ชัน SUBSTITUTE: ใน Power Query ใช้ชื่อว่า Replace Values
• ฟังก์ชัน UPPER, LOWER, PROPER, TRIM, CLEAN: ใน Power Query ใช้ชื่อว่า Format (UPPERCASE, lowercase, Capitalize Each Word, Trim, Clean)
• ฟังก์ชันทางคณิตศาสตร์พื้นฐาน เช่น ABS, POWER, LOG, FACT: ใน Power Query ใช้ชื่อว่า Scientific (Absolute Value, Power, Logarithm, Factorial)
• ฟังก์ชัน ROUND, ROUNDUP, ROUNDDOWN: ใน Power Query ใช้ชื่อว่า Rounding (Round, Round Up, Round Down)
• ฟังก์ชัน ISEVEN, ISODD: ใน Power Query ใช้ชื่อว่า Information (Is Even, Is Odd)
• ฟังก์ชัน IF: ใน Power Query ใช้ชื่อว่า Conditional Column
• ฟังก์ชัน VLOOKUP: ใน Power Query ใช้ชื่อว่า Merge Queries Join Kind แบบ Left Outer
• ฟังก์ชัน SUMIFS: ใน Power Query ใช้ชื่อว่า Merge Queries Join Kind แบบ Left Outer และระบุเป็นแบบ Aggregate
• Record Macro: ใน Power Query ใช้ชื่อว่า Invoke Custom Function

ใน Power Query จะไม่ใช้ VLOOKUP (หรือ INDEX + MATCH) แต่จะใช้ Merge Queries แบบ Left Outer ซึ่งทำงานเร็วกว่านับร้อยเท่า (ยิ่งข้อมูลเยอะ ยิ่งเห็นชัด) แถมจะดึงข้อมูลทีเดียวกี่คอลัมน์ก็ได้!

เช่น จากเดิมรู้แค่โค้ดของสาขา (StoreKey) แต่ไม่รู้ว่าสาขานี้อยู่ประเทศใด ทวีปใด หรือจัดหมวดหมู่เป็นร้านค้าแบบใด

ถ้าจะรู้ ต้องดูจากอีกตารางนึง คือตารางนี้

การจะดึงข้อมูลของแต่ละสาขามาใส่นั้นง่ายสุดๆ ไม่ต้องใช้สูตรอะไรเลย แค่คลิก Home/ Merge Queries

ระบุว่าสองตารางนี้สัมพันธ์กันด้วยข้อมูลใด (ในที่นี้คือคอลัมน์ StoreKey) แล้วคลิก OK

จะมีคอลัมน์ใหม่เพิ่มขึ้นมา และมีปุ่ม (Expand) ให้เลือกว่าต้องการดึงข้อมูลอะไรบ้าง

เลือกคอลัมน์ที่ต้องการ เช่น ในที่นี้เลือกทีเดียว 5 คอลัมน์ (StoreType, StoreName, Continent, RegionalCountry, Status) แล้วกด OK

ได้ข้อมูลที่ต้องการแล้ว (ในที่นี้คือ 5 คอลัมน์) !

Power Query มีความสามารถที่ชื่อว่า Conditional Column ซึ่งผมมองว่าใช้แทนฟังก์ชัน IF ได้เลย

เช่น ต้องการเพิ่มคอลัมน์เพื่อบอกว่าแต่ละสาขาจัดเป็นร้านค้าขนาดใด คลิก Add Column/ Conditional Column แล้วใส่เงื่อนไขที่ต้องการ

เงื่อนไขนี้จะมีกี่ชั้นก็ได้ ให้ผลเป็นยังไงก็ได้ เสร็จแล้วกด OK

ได้คอลัมน์ที่ต้องการแล้ว !

ความเจ๋งมากๆของ Power Query ก็คือ สามารถทำทุกอย่างในการรันครั้งเดียว ขั้นตอนการแปลงข้อมูลทั้งหมดจะถูกบันทึกไว้ (Applied Steps) สามารถแก้ไข เปลี่ยนแปลง และเรียกใช้ซ้ำได้ตามต้องการ

2 ความสามารถที่ Excel ทำได้ แต่เหนื่อย

ความสามารถบางอย่างที่ Excel ทำตรงๆไม่ได้ เช่น ทำได้แต่ต้องใช้ VBA ทำได้แต่ต้องเขียนสูตรอาร์เรย์ (Array Formula) หรือทำได้แต่ต้องมีหลายขั้นตอน Power Query ทำได้ง่ายๆโดยการคลิกเพียง 1-2 ครั้ง

ความสามารถที่ว่านั้น เช่น

• Unpivot Columns: แปลงข้อมูลแนวขวาง (Crosstab) ให้เป็นข้อมูลแนวตั้ง หรือแปลงให้เป็นฐานข้อมูล (Tabular) ซึ่งเดิมทีการทำแบบนี้ในเอ็กเซลต้องใช้ VBA เขียนคำสั่งวนลูป 2 ชั้น แต่ถ้าทำด้วย Power Query บอกเลย คลิกเดียวจบ! (ดูตัวอย่างด้านล่าง)
• Pivot Columns: แปลงข้อมูลในแนวคอลัมน์เป็นแนวขวาง (Crosstab)
• Unpivot Only Selected Columns: เพิ่มข้อมูลหนึ่งคอลัมน์ คอลัมน์ที่เพิ่มแสดงค่าเป็นชื่อคอลัมน์ที่เลือก และมีข้อมูลเหมือนกันทุกบรรทัด
• Duplicate Column: ก็อปปี้ทั้งคอลัมน์เป็นอีกคอลัมน์นึง
• Index Column: เพิ่มคอลัมน์ที่แสดงลำดับที่ของข้อมูล เช่น 1, 2, 3, … , เลขบรรทัดสุดท้าย มักใช้ในการเพิ่ม Primary Key
• Fill (Up, Down): เติมข้อมูลที่ว่างโดยกำหนดได้ว่าข้อมูลที่เติมคือข้อมูลที่เหมือนกับด้านบน หรือเหมือนกับด้านล่าง
• Reverse Rows: สลับลำดับข้อมูลจากล่างขึ้นบนเรียงตามลำดับ โดยข้อมูลบรรทัดสุดท้ายจะถูกนำไปไว้บรรทัดแรก ข้อมูลบรรทัดรองสุดท้ายจะถูกนำไปไว้ลำดับที่สอง สลับแบบนี้ไปเรื่อยๆ จนข้อมูลลำดับแรกถูกนำไปไว้สุดท้าย
• Remove Alternate Rows: ลบข้อมูลแบบสลับบรรทัด เช่น ลบข้อมูลบรรทัดเว้นบรรทัด หรือบรรทัดเว้นสองบรรทัด ทั้งนี้สามารถกำหนดได้ด้วยว่าจะเริ่มลบข้อมูลตั้งแต่บรรทัดที่เท่าไร
• Remove Errors: ลบข้อมูลที่เป็นค่าความผิดพลาด (เช่น #NAME?, #DIV/0!, #N/A) ออก
• Replace Errors: แทนที่ข้อมูลที่เป็นค่าความผิดพลาดด้วยค่าอื่น เช่น แทนที่  #NAME?, #DIV/0!, #N/A ด้วยคำว่า Unavailable
• Group By: ใช้หา Unique List หรือข้อมูลที่ไม่ซ้ำ ทั้งนี้สามารถเพิ่มคอลัมน์เพื่อสรุปตัวเลขของข้อมูลที่ไม่ซ้ำนั้นได้ด้วย ซึ่งถ้าใช้สูตรอาร์เรย์ (Array Formula) ขอบอกว่ายากมาก
• Merge Queries แบบ Left Outer: ใช้ดึงข้อมูลแบบ 2 เงื่อนไขได้ และทำได้ง่ายสุดๆ ซึ่งเดิมทีการใช้ VLOOKUP (หรือ INDEX) ดึงข้อมูลแบบ 2 เงื่อนไขนั้นถือว่ายาก แถม Merge Queries แบบ Left Outer นั้นยังใช้ดึงข้อมูลแบบที่เรียกว่า Multiple Match ได้อีกต่างหาก (กรณีค่าในตารางอ้างอิงมีค่าซ้ำ และต้องการดึงทั้งสองค่า) ซึ่งถ้าใช้ฟังก์ชันแบบปกติ บอกเลยว่ายากสุดๆ!
• Get Data/ From Folder: ดึงข้อมูลจากโฟลเดอร์ที่ต้องการ โดยสามารถดึงข้อมูลจากทุกไฟล์ได้พร้อมกัน หรือเลือกได้ว่าต้องการดึงข้อมูลจากไฟล์ประเภทใด หรือเพิ่มเงื่อนไขได้ว่าต้องการไฟล์ที่ชื่อไฟล์มีคำว่าอะไร เมื่อดึงข้อมูลแล้วสามารถเพิ่มคอลัมน์ได้ด้วยว่าข้อมูลนี้ดึงจากไฟล์ชื่ออะไร หรือดึงจากชีตชื่อใดของไฟล์เอ็กเซล ซึ่งเดิมทีการทำแบบนี้ต้องใช้ VBA และเขียนโค้ดยากมาก T_T

ขอยกตัวอย่างการใช้ Unpivot Columns (อาจใช้ Unpivot Other Columns แทนก็ได้) เช่น จากข้อมูลเดิมที่หน้าตาเป็นแนวขวางแบบนี้

แปลงให้เป็นแนวตั้งได้ง่ายมากๆ โดยคลิกคอลัมน์ที่ต้องการเปลี่ยนจากแนวขวางเป็นแนวตั้ง (ในที่นี้คือ Thailand, Indonesia, Malaysia, Philippines, Singapore, Vietnam) แล้วคลิกขวา เลือก Unpivot Columns

เสร็จแล้ว !

(เคสนี้อาจเลือก ProdGroup, Product แล้วคลิกขวาเลือก Unpivot Other Columns ก็ได้เช่นกัน)

เดิมทีการจะทำแบบนี้ได้ต้องใช้ VBA และเขียนคำสั่งวนลูป 2 รอบ

แต่ถ้าใช้ Power Query แค่คลิก Unpivot Columns จบเลย!

เจ่งไหมล่ะ ^__^

อันที่จริงแล้ว Power Query ก็มีโค้ดหรือมีฟังก์ชันเช่นกัน ซึ่งโค้ดที่ใช้ใน Power Query มีชื่อว่า M Code

ในการใช้งานทั่วไปเราอาจไม่จำเป็นต้องใช้หรือต้องรู้ M Code ก็ได้ เพราะการคลิกคำสั่งต่างๆผ่านเมาส์ นั่นคือการสั่งให้ Power Query เขียน M Code แบบอัตโนมัติ

อย่างไรก็ตาม ในการใช้งานระดับสูงหรือกรณีเจอเคสซับซ้อน การรู้และเข้าใจ M Code จะช่วยแก้ปัญหาได้

นอกจากนี้ Power Query ยังทำงานได้ดีกับ Pivot Table หรือ Power Pivot

เมื่อแปลงข้อมูลใน Power Query เสร็จแล้ว สามารถเลือกได้ว่าจะโหลดข้อมูลออกมาในเอ็กเซล (Home/ Close & Load To) เป็น Table, Pivot Table, Pivot Chart หรือจะสร้างความสัมพันธ์ไว้ก่อน แล้วค่อยใช้ร่วมกับตารางอื่นก็ได้ (Only Create Connection)

หรือจะโหลดข้อมูลนี้เข้าไปใน Data Model เพื่อใช้งานร่วมกับ Power Pivot ก็ยังได้ (Add this data to the Data Model)

ยิ่งไปกว่านั้น Power Query คือความสามารถที่มีในโปรแกรม Power BI Desktop ด้วย ดังนั้นถ้าใช้ Power Query ใน Excel เป็น ก็เท่ากับใช้ Power Query ใน Power BI Desktop เป็นด้วยเช่นกัน

เรียกได้ว่า “ยิงปืนนัดเดียว ได้นกสองตัว” ^__^

Power Query เจ๋งขนาดนี้ แล้วเรายังต้องใช้ Excel แบบเดิมอยู่ไหม?

ผมคิดว่าเรายังจำเป็นต้องใช้งาน Excel แบบเดิมอยู่ ด้วยเหตุผล 2 ข้อ

1. Power Query เหมาะกับการจัดการข้อมูลที่มีรูปแบบตายตัว หรือมีแพทเทิร์นที่ชัดเจน ซึ่งในชีวิตจริงอาจเจอเคสที่แพทเทิร์นไม่ชัดเจน
   (เช่น การแยกคำนำหน้าชื่อออกจากชื่อ-นามสกุล)
2. ไม่สามารถรีเฟรชแบบออโต้ได้ ต่างจากการเขียนสูตรแบบดั้งเดิมที่สามารถอัพเดตได้ทันทีเมื่อมีการเปลี่ยนแปลง (แต่ถ้าข้อมูลเยอะก็จะคำนวณช้า)

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

แต่.. ความรู้พื้นฐาน Excel ก็ยังเป็นสิ่งจำเป็น

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

ทางออกก็คงไม่พ้นการใช้เอ็กเซลแบบดั้งเดิม ซึ่งจะค่อนไปทางการใช้เอ็กเซลขั้นสูงไม่ว่าจะเป็น สูตรอาร์เรย์ หรือ VBA

ดังนั้นการชำนาญทั้ง Power Query และเอ็กเซลแบบดั้งเดิมคือวิธีที่ดีที่สุด ข้อสำคัญคืออย่ายึดติดกับเครื่องมือ แต่ให้ยึดติดกับผลลัพธ์ที่ต้องการ เรียกว่าขอให้ผลลัพธ์ถูกต้องก็พอ จะใช้เครื่องมือใดก็ไม่สำคัญ

“กระบี่อยู่ในใจ” ขุนเขาถล่มทลายปานใดก็มิหวั่น คุณว่าจริงไหม ^__^

.

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟซบุ๊กแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^