Power Query คืออะไร ? เจ้าเครื่องมือนี้ มีมานานแล้ว Microsoft เริ่มแนะนำให้ผู้ใช้งานรู้จัก Power Query ตั้งแต่เวอร์ชัน Excel 2010 ซึ่งเป็น Add-in ฟรี โดยดาวน์โหลดได้ที่ https://www.microsoft.com/en-us/download/details.aspx?id=39379)
แต่ Office 2016 เป็นต้นมา (รวมถึง Microsoft 365) Power Query โดนควบรวมเป็นส่วนหนึ่งของ Excel อย่างเป็นทางการกลุ่มคำสั่ง Get & Transform (แท็บ Data) และทำให้เป็นตัวช่วยสร้างการนำเข้าข้อมูลจากภายนอกโดยอัตโนมัติ (หากใครยังอยากใช้ตัวนำเข้าแบบเดิม ตั้งค่าที่ Select File > Options > Data > Show legacy data import wizards)
อย่างไรก็ดี ถ้าเป็นฝั่ง Mac จะใช้ได้เฉพาะเวอร์ชัน Microsoft 365 ถ้าใครใช้ Excel 2016 หรือ Excel 2019 for Mac จะยังใช้งานไม่ได้
ที่ผ่านมาก็มีการเปลี่ยนแปลงเกี่ยวกับฟังก์ชันการทำงานมาตลอด อย่างเช่น Data Catalog หายไป Facebook data connector ก็หายไป
DefExcel.com ก็เหมือนผู้ใช้งานทั่วไป เห็นตอนแรกก็รู้สึกว่ามันดูวุ่นวายเกินไป และไม่จำเป็นต้องใช้ แต่สุดท้าย กลายเป็นว่า นี่เป็นหนึ่งในเครื่องมือที่ใช้งานแทบจะทุกวัน เพราะช่วยแปลงข้อมูลแบบง่ายๆ ช่วยประหยัดเวลาได้หลายชั่วโมงโดยไม่ต้องเขียนโค้ดอะไรให้ยุ่งยาก
ดังนั้น ถ้าหากท่านจำเป็นต้องทำงานกับข้อมูลจำนวนมาก DefExcel ขอแนะนำให้ลองทำความรู้จักกับเครื่องมือที่จะช่วยให้งานลุล่วงแบบรวดเร็วตัวนี้
Power Query คืออะไร ?
ถ้าเกิดคำถามว่า Power Query คืออะไร ? บางทีคำตอบอาจจะง่าย ๆ ก็คือ เครื่องมือนำเข้าข้อมูลและดัดแปลงให้นำไปใช้งานต่อได้ง่าย ๆ
โดยวิธีการทำงานของเครื่องมือนี้ ให้นึกถึงคำ 3 คำ คือ
Connect
Transform / Combine
Load
เพราะว่าวิธีการทำงานของ Power Query ก็คือ การ -connect- เชื่อมต่อกับแหล่งข้อมูลอย่างเช่น workbook หรือ text file หรือ database อื่น ๆ จากนั้นเราทำ – Transform / Combine – ปรับแต่งข้อมูลเพื่ออำนวยความสะดวกในการวิเคราะห์ข้อมูล เช่น เปลี่ยนประเภทข้อมูลจาก Numbers เป็น Text ตัดบางคอลัมน์ทิ้ง แล้วเรายังดึงข้อมูลจากแหล่งอื่น เช่น ข้อมูลนี้จาก Database ของ Access อันนี้มาจาก Excel อันนี้มาจาก Text เราดึงทุกอย่างมา มาเข้าด้วยกัน และสุดท้าย -Load- เอาสิ่งที่เราทำไว้ไปใช้ต่อได้ เก็บ Query ที่สร้างไว้ใช้ในงานต่อไป (กรณีที่มีการใช้งานลักษณะเดิมซ้ำ ๆ) หรือ อาจจะทำสำเนา (duplicate) แล้วเปลี่ยนขั้นตอน Query บางขั้นตอน เพื่อทดสอบอะไรที่แตกต่างกันไป
connect – การเชื่อมต่อ
เคยเจอปัญหาแบบนี้หรือไม่? ต้องใช้ข้อมูลจากภายนอก นำมาคำนวณใน Excel แต่เมื่อข้อมูลภายนอกมีการเปลี่ยนแปลง ก็ต้องนำเข้าใหม่อีกรอบ เสียเวลาโดยใช่เหตุ
แต่เราสามารถจัดการปัญหานี้ได้ง่าย ๆ โดยใช้ power Query โดยการข้อมูลในลักษณะ data connection (ไม่ใช่การ import เข้ามานิ่ง ๆ) ถ้าข้อมูลต้นทางมีการปรับปรุงแก้ไข เราก็จะได้ข้อมูลล่าสุดนั้นด้วย
นี่คือข้อดีแบบที่ดีมาก และช่วยประหยัดเวลาได้เป็นอย่างมาก
สมมติว่า (อันนี้ตัวอย่างจากการทำงานจริง) ได้รับบันทึกเป็นไฟล์ข้อความ (text file) จากแหล่งข้อมูลภายนอกเป็นประจำ ซึ่งทุกครั้งที่มีการเปลี่ยนแปลงข้อมูล ก็ต้องไปที่โฟลเดอร์นี้ เก็บไฟล์ทุกไฟล์มา เอาไฟล์ที่เป็นข้อมูลเดียวกันมารวมกัน ต้องนำข้อมูลจากคนละไฟล์มาเชื่อมโยงเข้ากันด้วย เช่น ข้อมูลจากไฟล์ A (ซึ่งเก็บข้อมูลการเปลี่ยนแปลงทางธุรกรรม) เอามา XLOOKUP กับไฟล์ B เพื่อเอามาคำนวณตามหลักการต่าง ๆ
นั่นแค่ส่วนย่อย ในความเป็นจริงคือ มีไฟล์ที่เกี่ยวข้องกันเป็น 100 ไฟล์ ลองจินตนาการถึงการทำงานกับไฟล์ทั้ง 100 ไฟล์ ที่ต้องเปิด เชื่อมโยง ซึ่งไม่ใช่เรื่องยาก แต่เสียเวลาในการทำงานพอสมควร
พอต้องทำแบบนี้ซ้ำ ๆ ก็น่าเอาเวลาไปทำอย่างอื่น…
แต่ถ้าเราทำ data connection โดยใช้ Power Query สิ่งที่เราต้องทำคือแค่ refresh ข้อมูลจะเปลี่ยนให้โดยอัตโนมัติ และเราไม่ต้องทำงานเหล่านี้ซ้ำอีก (จนกว่ารูปแบบการทำงานจะเปลี่ยนไป)
Transform / Combine – ปรับแต่งข้อมูล
เมื่อเรา connect ข้อมูลภายนอกแล้ว สิ่งที่ต้องทำอีกอย่างคือ ข้อมูลที่นำเข้ามาใช้นั้น สามารถ นำไปใช้งานได้เลยหรือไม่ ถ้านำไปใช้ได้เลย ก็จะเข้าสู่การ Load
แต่ถ้าข้อมูลที่ได้รับมา มีอะไรบางอย่างที่ไม่สามารถนำไปใช้งานได้เลย เราก็ต้อง transform ข้อมูล เพื่อให้นำข้อมูลไปใช้ได้ตรงกับความต้องการด้านการวิเคราะห์ข้อมูล ตัวอย่างเช่น เอาคอลัมน์ที่ไม่จำเป็นออก เปลี่ยนชนิดข้อมูลจากตัวเลขเป็นข้อความ หรือกรองข้อมูล หรือ แม้แต่ นำข้อมูลจากแหล่งอื่นมารวมกันเพื่อนำข้อมูลไปใช้
Power Query จะมีเครื่องมือที่เรียกว่า Power Query Editor เป็นตัวช่วยในการดำเนินการต่าง ๆ ได้อย่างสะดวก ง่ายดาย และยังเรียกใช้ Power Query M Language ในเครื่องมือแก้ไขขั้นสูง เพื่อนำมาดัดแปลงปรับแต่งได้อีก โดยการการแปลงทั้งหมด แต่ละขั้นตอนจะทำงานอัตโนมัติ
และข้อดีอีกอย่างหนึ่งก็คือ เรารวม Query มากกว่า 1 Query เข้าหากันได้ ไม่ว่าจะเป็นการผนวก (นำไปต่อท้ายของเดิมที่มีอยู่) หรือจะผสานข้อมูลเข้าหากันก็ได้เช่นกัน
Load – นำไปใช้
นำข้อมูลไปใช้งานต่อเพื่อการวิเคราะห์ข้อมูล หรืออื่น ๆ
Power Query ในการทำงาน
แล้วงานลักษณะไหน ที่ควรใช้ power Query ? สมมติว่างานของท่านเป็นแบบนี้:-
สมมติว่าทุกวันคุณได้รับไฟล์ CSV พร้อมรายการซื้อขาย โดยปกติแล้ว ท่านจะเปิด CSV เพื่อคัดลอกคอลัมน์ที่เกี่ยวข้องลง Excel จากนั้นใช้สูตรซ้ายและขวาจำนวนมากเพื่อแยกฟิลด์ข้อความบางฟิลด์ และอาจจะใช้ lookup เพื่อเชื่อมโยงข้อมูลอื่น และ บางที อาจจะต้องใช้ PivotTable เพื่อนำเสนอข้อมูลในแบบสรุปสั้น ๆ
Power Query ทำแบบนี้ได้ และทำเพียงแค่ครั้งเดียว หลังจากนั้น ท่านเพียงแค่กดรีเฟรชก็เสร็จเรียบร้อย (อนุมานว่าไฟล์ CSV ใช้ชื่อเดิมและวางอยู่ในตำแหน่งเดิม
หรือ ในแต่ละเดือน ท่านต้องรับ Excel จากส่วนงานอื่น อาจจะ 78 ไฟล์ โดยที่ไฟล์ Excel เหล่านั้น มีโครงสร้างเหมือนกันทุกประการ ท่านเพียงแค่เปิดไฟล์เหล่านั้น แล้วคัดลอกข้อมูลต่าง ๆ มารวมกันเป็นไฟล์เดียว เพื่อจะนำไปสรุปผลงานของแต่ละส่วนงาน
ถ้าท่านใช้ Power Query ท่านไม่จำเป็นต้องเปิดไฟล์แต่ละไฟล์เพื่อมาคัดลอก เพียงแค่จัดการตั้งค่าให้เหมาะสมก็สามารถรวมทุกไฟล์เข้ากัน และทำซ้ำได้ตลอดเวลาเพียงแค่ “คลิก”
สองตัวอย่างนี้เป็นตัวอย่างง่าย ๆ สำหรับงานที่ทำซ้ำ ๆ แต่มีลักษณะงานเหมือนกัน ซึ่งงานมันอาจจะไม่ได้ยาก แต่เสียเวลาในการทำ อาจจะสัก 1 ชั่วโมง หรือ 2 ชั่วโมง แต่ ลองมาคิดว่า ถ้าเราจะทำงานเหล่านั้นได้ในเวลาไม่กี่นาที เพียงแค่คลิกไม่กี่ครั้ง มันจะดีขนาดไหน?
Power Query อยู่ที่ไหน
สำหรับ Excel เวอร์ชัน 2013 หรือเก่ากว่า ท่านจะต้องดาวน์โหลด Add-in แต่สำหรับเวอร์ชันใหม่กว่านั้น ไม่จำเป็นต้องติดตั้งตัว Add-in ใดใดทั้งสิ้น เพราะติดตั้งเอาไว้ให้แล้วในชื่อ Get Data อยู่ในแท็บ Data
ถึงแม้ว่า ทางไมโครซอฟต์จะให้ชื่อว่า Get and Transform แต่ดูเหมือนว่าผู้ใช้งานทั่วไปก็ยังสมัครใจเรียกรวม ๆ ว่า Power Query อยู่ดี
รองรับอะไรบ้าง
เวลาเปิดริบบอน Data ให้สังเกตทางด้านซ้ายมือ จะมีเมนู Get Data และจะมีทางลัดให้สำหรับ From Text/CSV, From Web และ From Table/Range ซึ่งตรงนี้น่าจะเป็นเมนูที่มีคนใช้งานบ่อย ทาง Excel เลยทำปุ่มลัดให้ แต่ถ้าเราคลิกที่ Get Data เราจะเห็นเป็นหัวข้อใหญ่ ๆ คือ
From File ในเมนูย่อยจะมีพวกไฟล์ Excel, Text File หรือ CSV, ไฟล์ XML และ JSON มีแม้กระทั่ง pdf แต่ต้องบอกก่อนว่า ถ้าเป็นภาษาไทย ยังรองรับไม่ดีเท่าที่ใจอยากได้ ยังมีปัญหากับภาษาไทยอยู่ แต่ถ้าเป็นภาษาอังกฤษกับตัวเลข จะช่วยได้มากทีเดียว และยังมีการนำเข้าทีละหลายไฟล์ที่อยู่ในโฟลเดอร์เดียวกันด้วย
From Databases ในเมนูย่อยจะเป็นพวกฐานข้อมูลต่าง ๆ เช่น Microsoft Access
From Microsoft Azure
From Other Sources รับข้อมูลจากแหล่งอื่นๆ เช่น Table หรือ Range จากไฟล์ที่เปิดอยู่ จากเว็บ Microsoft Query, Hadoop, ODBC, OLEDB, Sharepoint, Microsoft Exchange, Dynamics 365, Facebook และ Salesforce เป็นต้น
From Online Services like Sharepoint, Microsoft Exchange, Dynamics 365, Facebook and Salesforce.
Merge two queries (รวม 2 Query)
Append a query (ผนวก Query)
ตัวอย่างการนำเข้า Text File เข้า
ลองมาดูตัวอย่างการนำข้อมูล text file เข้ามาทำงาน
ขั้นแรก ไปที่ริบบอน Data คลิก Get Data คลิก From Files คลิก From Text/CSV
เลือกไฟล์ที่ต้องการแล้วกด import
หลังจากเลือกไฟล์ที่ท่านต้องการนำเข้าแล้ว จะมีหน้าต่างปรากฏขึ้นมา จะแสดงหน้าตาคร่าว ๆ ของข้อมูลที่ท่านเลือก
จะเห็นมุมซ้ายด้านบนว่า มี encoding ให้เลือก ถ้าตัวอย่างที่แสดงผลไม่มีปัญหาก็ไม่ต้องแตะอะไรตรงนี้
ตรงกลาง จะมี delimiter หรือตัวคั่น ว่าใช้ตัวไหน เช่น จุลภาค หรืออะไรอื่น เลือกให้ตรงกับที่ต้องการ ตามปกติ Excel จะคาดเดาไว้ให้แล้ว
มุมขวาด้านบน จะเป็น Data Type Detect หรือการตรวจจับรูปแบบของข้อมูลในแต่ละคอลัมน์ให้โดยอัตโนมัติ ซึ่งค่าตั้งต้นที่ให้มาคือ 200 แถวแรก (base on first 200 rows) แต่ถ้าไม่มั่นใจก็เลือก base on entires dataset หรือ ให้เช็คทั้งหมด หรืออีกตัวเลือกเลย คือไม่ต้อง detect (do not detect data types) ตรงนี้ถ้าคิดว่าจะเลือกประเภทข้อมูลด้วยตัวเองอยู่แล้วก็คลิกตรงนี้ไว้
ตรงนี้ต้องอธิบายสำหรับคนที่ชอบคลิกโดยไม่ทันระวังนิดหน่อย คือ บางคนมักจะไม่ได้สนใจและปล่อยค่า base on first 200 rows เอาไว้ แต่ลองคิดว่า ถ้าหาก 200 แถวแรก ข้อมูลในคอลัมน์นั้นเป็นตัวเลข Excel จะจัดประเภทให้เป็น numeric ไป แต่ถ้าหากข้อมูลในแถวที่ 201 ดันเป็นตัวอักษร จะกลายเป็นว่าข้อมูลนั้นไม่ได้นำเข้ามา เพราะผิดประเภท หรือขึ้น error อันนี้ก็ฝากไว้ให้คิดซักนิด
ถ้าเลือกได้ดังใจแล้ว จะเลือก Load หรือ Load To ก็ได้ แต่ ถ้ายังมีบางส่วนต้องจัดการก่อน ให้เลือก Transform
ซึ่งในตอนหน้า จะได้พูดถึงหน้าตาของ Power Query Editor ต่อไป