Blog

Power Query เป็นเครื่องมือสำหรับช่วยทำงานด้าน data analytics โดยตรง แต่ โดยส่วนตัวไม่ได้เอาเจ้าเครื่องมือนี้มาใช้ในงาน Data Analytics เท่าไหร่นัก มักจะเอามาจัดการข้อมูลที่จะนำเข้ามาใช้งานมากกว่าด้านอื่น ตัวนี้เป็นเครื่องมือที่มีประโยชน์มาก สำหรับการจัดการกับข้อมูลภายนอก อันนี้ต้องบอกก่อนว่า ความจริงเครื่องมือนี้เครื่องมือนี้ติดตั้งมาตั้งแต่ยุค Office 2016 ตอนแรกยังใช้ชื่อว่า Get & Transform ซึ่ง ถ้าใครใช้ Excel เวอร์ชันเก่ากว่านั้นจะยังไม่มีมาในตัว ต้องลง Add-In เอาเอง ดาวน์โหลดได้ที่ https://www.microsoft.com/en-us/download/details.aspx?id=39379

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

เราลองมองข้อมูลเป็น 2 แบบ แบบแรก คือ ข้อมูลที่อยู่ใน Workbook อยู่แล้ว อันนี้จัดการแก้ไขดัดแปลงอะไรก็ทำในนั้นเลยโดยตรง

กับอีกแบบหนึ่ง คือข้อมูลภายนอก เช่น พวก Text File ที่เดิมที่เราต้องใช้วิธี import เข้ามาอยู่ใน sheet ซึ่งถ้าข้อมูลต้นทางเปลี่ยน ก็ต้องนำเข้าใหม่ จัดการใหม่

แต่พอมี power Query แล้ว ปัญหานี้ลดน้อยถอยลงไป เพราะ เมื่อใดที่เราใช้ข้อมูลในลักษณะ data connection แล้ว (ไม่ใช่การ import เข้ามานิ่ง ๆ) ถ้าข้อมูลต้นทางมีการปรับปรุงแก้ไข เราก็จะได้ข้อมูลล่าสุดนั้นด้วย

Work สุด ๆ กับข้อดีข้อนี้

สมมติว่า (อันนี้ตัวอย่างจากการทำงานจริง) ได้รับบันทึกเป็นไฟล์ข้อความ (text file) จากหน่วยงานอื่นเป็นประจำ ซึ่งทุกครั้งที่มีการเปลี่ยนแปลงข้อมูล ก็ต้องไปที่โฟลเดอร์นี้ เก็บไฟล์ทุกไฟล์มา เอาไฟล์ที่เป็นข้อมูลเดียวกันมารวมกัน ต้องนำข้อมูลจากคนละไฟล์มาเชื่อมโยงเข้ากันด้วย เช่น ข้อมูลจากไฟล์ Transection (ซึ่งเก็บข้อมูลการเปลี่ยนแปลงทางธุรกรรม) เอามา XLOOKUP กับไฟล์จาก Condo_Reg (ซึ่งมีรายละเอียดของห้องชุด) เพื่อสรุปออกมาว่า อาคารชุด แต่ละแห่ง แต่ละเขต แต่ละชั้น มีการเปลี่ยนแปลงธุรกรรมอะไรกันบ้าง คำนวณราคาต่อตารางเมตรหักลบตามสูตรต่าง ๆ

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

พอต้องทำแบบนี้ซ้ำ ๆ ก็น่าเอาเวลาไปทำอย่างอื่น…

แต่ถ้าเราทำ data connection โดยใช้ Power Query สิ่งที่เราต้องทำคือแค่ refresh ข้อมูลจะเปลี่ยนให้โดยอัตโนมัติ

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

• Connect
• Data Shaping
• Load

หมายถึง เรา –connect– เชื่อมต่อกับแหล่งข้อมูลอย่างเช่น workbook แบบเดี่ยว ๆ หรือแม้กระทั่งหลายแหล่งข้อมูล เช่นจาก database ของ Access จากนั้นเราทำ –Data Shaping– ปรับแต่งข้อมูลเพื่ออำนวยความสะดวกในการวิเคราะห์ข้อมูล เช่น เปลี่ยนประเภทข้อมูลจาก Numbers เป็น Text ตัดบางคอลัมน์ทิ้ง แล้วเรายังดึงข้อมูลจากแหล่งอื่น เช่น ข้อมูลนี้จาก Database ของ Access อันนี้มาจาก Excel อันนี้มาจาก Text เราดึงทุกอย่างมา มาเข้าด้วยกัน และสุดท้าย –Load– เอาสิ่งที่เราทำไว้ไปใช้ต่อได้ เก็บ Query ที่สร้างไว้ใช้ในงานต่อไป (กรณีที่มีการใช้งานลักษณะเดิมซ้ำ ๆ) หรือ อาจจะทำสำเนา (duplicate) แล้วเปลี่ยนขั้นตอน Query บางขั้นตอน เพื่อทดสอบอะไรที่แตกต่างกันไป

Data Connection

เราสามารถดูว่า ในขณะนี้ excel ที่กำลังทำงานอยู่ ได้มีการเชื่อมต่อกับแหล่งข้อมูลภายนอกหรือยัง โดยคลิกที่แท็บ Data Source Setting จะมีบอกว่าเราเชื่อมต่อข้อมูลภายนอกอะไรไว้บ้าง

เรื่องแรกที่อยากกล่าวถึง ก็คือเรื่องการนำข้อมูลจากแหล่งอื่น เช่น Text File SQL เว็บเพจ ฯลฯ สะดวกรวดเร็วมาก แถมยังปรับแต่งข้อมูลเพื่อให้ใช้งานได้สะดวกด้วย

เชื่อมต่อกับอะไรได้บ้าง?

• text file (รวมทั้ง csv)
• Excel table (หรือ range)
• Excel file
• web page (เช่น Facebook, Wikipedia)
• SQL Server
• ODBC data source
• Access database
• XML file
• Analysis Services
• JSON file
• PDF file
• Oracle
• SharePoint list
• OData feed
• OLE DB
• MySQL
• Exchange
• Active Directory
• SAP HANA
• IBM DB2
• PostgreSQL
• Common Data Service for Apps
• Teradata
• SAP Sybase SQL Anywhere
• Microsoft Azure Blob Storage
• Azure HDInsight

อันนี้คือที่ Microsoft บอกมานะ แต่โดยส่วนตัวใช้อยู่ไม่กี่อย่าง จะยกตัวอย่างให้ดูสักสองสามแบบ ที่ใช้บ่อย

Connect

มาดูวิธีการเชื่อมต่อกับข้อมูลภายนอก ว่าทำยังไงบ้าง จะยกสักสองสามแบบ

นำข้อมูล text file เข้า excel

เคยเขียนไปครั้งหนึ่งแล้ว เรื่องการ import text file เข้า excel โดยใข้ Power Query ลองย้อนกลับไปอ่านกัน

เปิด Excel แล้วไปที่ Data > Get Data

ตรง กลุ่มคำสั่งจะมีปุ่มลัดสำหรับไฟล์ประเภทที่พบกันบ่อย ๆ อยู่แล้ว เช่น text file ในตัวอย่างนี้จะนำไฟล์ csv เข้า ก็กดตรงนี้ เลือกไฟล์ที่ต้องการ หรือจะนำเข้าจากทางอื่นก็แล้วแต่สะดวก เช่น อันนี้ ใช้

Get Data > From File > From Text/CSV

เชื่อมต่อกับ table หรือ range ใน excel

ถ้าเป็นข้อมูลที่มีอยู่ใน workbook นั้นอยู่แล้ว แต่อยากใช้ power query ก็ทำได้ โดย คลิกเลือก cell ทั้งหมดที่ต้องการ แล้วไปที่ แท็บ Data เลือกFrom Table/Range

หรือ คลิกขวา เลือก Get Data from Table/Range

ถ้าได้ตั้งชื่อช่วง (named range) หรือ Format as Table ไว้เรียบร้อยแล้ว จะดึงข้อมูลทั้งหมดตามที่กำหนดไว้แล้ว

เชื่อมต่อกับไฟล์ excel อื่น

การเชื่อมต่อกับไฟล์ excel อื่น ทำได้ง่าย ๆ เพียงแค่ คลิก Data > Get Data > from File > workbook

เมื่อเลือกไฟล์แล้ว จะมีหน้าต่าง navigator ขึ้นมาว่า จะเอา sheet ไหนบ้าง? คลิกตรงชื่อ sheet จะเห็นภาพตัวอย่างของ sheet นั้น ๆ ถ้าต้องการมากกว่า 1 sheet ให้ติ๊กตรง select multiple items แล้วเลือกที่ต้องการทั้งหมด

เชื่อมต่อกับโฟลเดอร์

การเชื่อมต่อกับโฟลเดอร์ จะดึงไฟล์ทุกไฟล์มาเชื่อมต่อให้ ดังนั้น ก่อนจะใช้ โปรดตรวจสอบให้มั่นใจก่อนว่า ในโฟลเดอร์ที่จะเชื่อมต่อ

คลิก Data > Get Data > from File > Folder

เมื่อหน้าต่างเปิดขึ้นมา ให้เลือก ตำแหน่งของโฟลเดอร์ที่เก็บไว้ เลือกได้แล้วกด OK จะขึ้นหน้าต่างรายชื่อไฟล์ต่าง ๆ ที่อยู่ในโฟลเดอร์นั้นมาให้ เลือกโหลดเลย หรือ Combine and Transform หรือ  Combine & Load หรืออะไรก็แล้วแต่ใจปรารถนา

Load

 เมื่อ connect แล้ว ก็มีสองตัวเลือกให้ตัดสินใจ จะ Load เลย หรือว่าจะแก้ไขก่อน ดูด้านล่างจะเห็นว่า มีตัวเลือกให้ load ซึ่งจะมีสองตัวเลือกคือ

Load = จะนำเข้าเป็น Table

Load to = จะเลือกว่า นำเข้าเป็น Table หรือทำเป็น Connection Only

อันนี้ โดยส่วนตัวเลือกเป็น connection

กับอีกอย่างที่อยู่ข้าง ๆ Load ก็คือ Transform คือเข้าไปจัดการข้อมูลก่อนดึงข้อมูลมาใช้ เช่น แก้ชื่อคอลัมน์ , เปลี่ยนประเภทข้อมูล เรียงลำดับ หรือ คัดกรองบางข้อมูล เพิ่มการคำนวณ ฯลฯ ซึ่ง ถึงแม้เราจะ load ข้อมูลไปแล้ว ก็ยังเรียกกลับมาปรับปรุงแก้ไขภายหลังได้

Data Shaping

เมื่อเปิดหน้าต่างนำเข้ามา จะเห็นว่าต้องปรับแต่งอะไรมากมายเอาการ ตั้งแต่ encoding ไปจนถึง format ของคอลัมน์ จึงเปลี่ยน encoding ให้อ่านภาษาไทยได้ชัด ๆ (Windows 874) แล้วกด data transform เพื่อปรับเปลี่ยนแก้ไขให้ตรงกับที่ต้องการ

Power Query Editor

เมื่อกด Data Transform ขึ้นมา จะเข้าสู่หน้าต่าง Power Query Editor ซึ่งจะเป็นตัวจัดการกับข้อมูลต่าง ๆ ให้เป็นอย่างที่เราต้องการ จะเห็นว่าตรง ribbon จะมีเครื่องมือ 4 กลุ่มใหญ่ให้เราทำงาน นั่นคือ

• Home
• Transform
• Add Column
• View

แต่เวลาทำงาน เราคลิก (ขวา) ที่ตารางตรงบริเวณที่ต้องการเปลี่ยนแปลงเลยก็ได้ เช่น

ที่ ribbon Home มีคำสั่ง Remove Column เราแค่คลิกที่คอลัมน์ที่เราต้องการแล้วคลิก Home > Remove Column นี้ คอลัมน์นั้นจะโดนยกออกไป แต่อาจจะง่ายกว่า ถ้าเราคลิกขวาแล้วเลือก Remove แบบนี้เป็นต้น ผลลัพธ์แบบเดียวกัน คำสั่งเดียวกัน แต่มีทางเลือกให้เข้าถึง

TIP: กรณีเลือกบางคอลัมน์ ถ้าเราคลิก Home > Choose Columns จะมีหน้าต่างรายขื่อคอลัมน์ใน Query นั้นขึ้นมาทั้งหมด เราแค่ติ๊กถูกตรงคอลัมน์ที่เราจะเอาไว้ จัดการทีเดียวตรงนี้เลยว่าจะเลือกใช้คอลัมน์ไหนบ้าง อาจจะสะดวกกว่า

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

ดูตรงหัวคอลัมน์ จะมีประเภทของข้อมูลกำกับอยู่ทุกคอลัมน์ ซึ่งตรงนี้กดเปลี่ยนได้ตามใจ เวลากดเปลี่ยน จะมีหน้าต่างถามว่า แทนที่ของเดิมเลยมั้ย ถ้าตอบว่าไม่ จะสร้างอีกคอลัมน์ขึ้นมาแทน หรือไปที่ Transform > data type (จะขึ้นชื่อประเภทของข้อมูล เมื่อเราคลิกเลือกคอลัมน์ไว้โดยอัตโนมัติ) แล้วเปลี่ยนตรงนี้ก็ได้เหมือนกัน

 ถ้าไม่อยากใช้คอลัมน์ไหน คลิกคอลัมน์นั้น แล้วคลิก Transform > Remove Column ได้เลย

ปรับแต่งได้อย่างที่ต้องการแล้ว คลิก close and load

Tip: ถ้าเลือกประเภทข้อมูลผิด เช่น คอลัมน์ที่ควรจะเป็น Number ดันกลายเป็น text ก็ไม่ต้องกังวล เราสามารถคลิก edit กลับไปแก้ไขใหม่ได้ นี่คือข้อดีอย่างหนึ่งของ Power Query

-เราสามารถดึงข้อมูลเข้ามาเพื่อ “รวม” เข้ากับของเดิมที่มีอยู่ อย่างเช่น มี sheet ที่เก็บข้อมูลที่จะต้องนำเข้ามาต่อ

กรณีที่เราเลือกหลาย sheet ถ้าเราไม่ได้กำหนดพิเศษ มันจะออกมาหลาย sheet เหมือนกัน