Power Query Editor

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

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

ในตอนนี้ จะขอแนะนำหน้าตาของ Power Query Editor ก่อนก็แล้วกัน ว่าส่วนไหนเรียกว่าอะไรบ้าง

Power Query Editor

เมื่อเข้ามาสู่หน้า Power Query Editor จะมีหน้าตาคล้าย ๆ กับ Excel ทั่วไปนั่นแหละ ด้านบนสุด จะมีชื่อของ Query นั้นอยู่ เอาไว้ดูว่าตอนนั้นเรากำลังจัดการกับ Query ตัวไหนอยู่ (กรณีที่มี Query มากกว่า 1)

ในหน้าต่างนี้ จะแบบเป็น 5 ส่วนย่อย คือ

Ribbon – ตรงนี้ก็เหมือนกับ Ribbon ของ Excel (เออ ต้องเหมือนกันแหละ มันเป็นซอฟต์แวร์เดียวกัน) คือเป็นกลุ่มคำสั่งในแต่ละประเภท แต่ว่า รายละเอียดของแต่ละริบบอนจะแตกต่างไปคือจะมี เมนู FILE | Home | Transform | Add Column | View เท่านั้น

Query List – พื้นที่หน้าต่างด้านซ้าย จะแสดงรายการ Query ทั้งหมดใน Worksheet ที่เปิดอยู่

Formula Bar – เป็นแถวเอาไว้เขียนสูตร แสดงสูตร

Data Preview –แสดงตัวอย่างข้อมูลพร้อมขั้นตอนการแปลงทั้งหมดที่ใช้อยู่ และใช้จัดการข้อมูลแต่ละคอลัมน์

Query Setting – แบ่งเป็น 2 ส่วนใหญ่คือ Properties ซึ่งจะแสดงชื่อ Query และคำอธิบายเพิ่มเติม (แก้ไขได้ตามสะดวก) และ Appled แสดงสิ่งที่จัดการเอาไว้ เรียงตามลำดับเวลา จะเรียกว่าเป็น History ก็ได้ ท่านสามารถทบทวนขั้นตอน ดูการเปลี่ยนแปลง ลบ แก้ไข หรือจัดลำดับขั้นตอนใหม่ก็ได้เช่นกัน

Ribbon

ในริบบอนจะมีเมนู 5 เมนู (หรือ 5 แท็บ) คือ File | Home | Transform | Add Column | View

FILE

เมนูแรก อยู่ด้านบนซ้ายมือสุด จะเป็นตัวที่น่าจะคุ้นเคยกันอยู่แล้ว นั่นคือการ save (บันทึก) หรือ เปิด หรือ ปิด ภายในเมนู File จะมีคำสั่งย่อยที่เกี่ยวกับการจัดการ Query ดังนี้

• Close & Load –บันทึก Query และ load ไปใส่ในตาราง Excel
• Close & Load To – บันทึก Query และ load ไปใส่ในตาราง Excel พร้อมตัวเลือกการ load ต่าง ๆ ให้เลือก ลองมาดูตรง Load To… ถ้าคลิกตรงนี้ จะมีอะไรให้เลือกอีกพอสมควร เมื่อคลิกเลือก Load To จะมีหน้าต่าง Import Data โผล่ขึ้นมา ซึ่งตรงนี้จะพูดถึงอีกครั้งในบล็อกต่อไป คำสั่ง Close & Load และ Close & Load To ก็มีให้ใช้งานในแท็บ Home เช่นกัน
• Discard & Close ละทิ้งการเปลี่ยนแปลงใด ๆ ที่ท่านทำใน Query ตอนนั้น และปิดตัวแก้ไข
• Option and Setting ภายในจะมีเมนูย่อยคือ Query Options กับ Data Source Settings

HOME

HOME จะเป็นแท็บรวมเมนูคำสั่งเกี่ยวกับการดำเนินการต่าง ๆ ที่ส่งผลต่อทั้งตาราง

กลุ่มคำสั่ง Close (ปิด) ประกอบด้วย Close & Load / Close & Load To… อันนี้ก็คือ การปิดและโหลดลง Excel แบบเดียวกับในเมนู File

กลุ่มคำสั่ง Query  ตัวจัดการ Query โดยจะมีทั้ง Refresh  (สำหรับ Query ปัจจุบันที่เปิดอยู่ หรือQuery ทั้งหมดที่เชื่อมต่ออยู่ก็ได้) การตั้งค่า Properties ปุ่มเรียก Advanced Editor (ตัวแก้ไขขั้นสูง) และมีตัวเลือกใต้ปุ่มจัดการเพื่อลบ ทำซ้ำ หรืออ้างอิงQueryปัจจุบัน

กลุ่มManage Columns เลือกคอลัมน์ที่ต้องการและลบคอลัมน์ออกจาก Query

กลุ่ม Row – ท่านสามารถจัดการแถวของข้อมูลจากส่วนนี้ได้ มีตัวเลือกมากมาย ทั้ง Keep Row (เก็บแถว) หรือ Remove Row (ลบแถว)  โดยจะเลือกเก็บหรือลบแถวบนสุด แถวล่าง ช่วงของแถวเฉพาะ แถวสลับ แถวซ้ำ หรือ แถวที่มีข้อผิดพลาด หรือลบแถวที่ว่าง

กลุ่ม Sort – เรียงลำดับคอลัมน์ใดก็ได้ จากน้อยไปมาก หรือ จากมากไปน้อย

กลุ่ม Transform  ส่วนนี้ประกอบด้วยตัวเลือกการแปลงที่มีประโยชน์หลายอย่างผสมกัน เช่น

• Split Columns – แยกข้อมูลในคอลัมน์ตามตัวคั่นหรือความยาวของอักขระ
• Group By – จัดกลุ่มและสรุปข้อมูลเป็นกลุ่ม
• Data Type – กำหนดประเภทของข้อมูลในแต่ละคอลัมน์
• Use First Row as Headers –เลื่อนข้อมูลแถวแรกเป็นส่วนหัวของคอลัมน์ หรือ ยกเลิกไม่เอาส่วนหัวคอลัมน์จากแถวของข้อมูล
• Replace Values – ค้นหาและแทนที่ค่า

กลุ่ม Combine – ส่วนนี้ประกอบด้วยเกี่ยวกับการรวม Query หรือไฟล์ เข้าด้วยกัน

กลุ่ม Parameters – Power Query ช่วยสร้างพารามิเตอร์สำหรับ Query เช่น เมื่อตั้งค่าการค้นหาจากโฟลเดอร์ ต้องการให้ path ของโฟลเดอร์เป็นพารามิเตอร์ เพื่อช่วยให้เปลี่ยน path ชี้ตำแหน่งได้อย่างง่ายดาย

กลุ่ม Data Sources –ตั้งค่าแหล่งข้อมูล รวมถึงการจัดการสิทธิ์สำหรับแหล่งข้อมูลใดๆ ที่ต้องใช้รหัสผ่านในการเข้าถึง

กลุ่ม New Query –สามารถสร้าง Query ใหม่จากแหล่งข้อมูลใหม่หรือแหล่งข้อมูลที่ใช้ก่อนหน้านี้ได้

Transform

ส่วนแท็บ Transform จะเป็นแท็บที่รวมคำสั่งเกี่ยวกับการจัดการข้อมูลต่าง ๆ มีกลุ่มคำสั่งดังนี้

Table – ส่วนนี้มีคำสั่งที่จะแปลงทั้งตาราง จัดกลุ่มและรวมข้อความ เลื่อนระดับแถวเป็นส่วนหัว ลดระดับส่วนหัวเป็นแถว ย้ายตำแหน่งข้อมูลของท่าน ย้อนกลับลำดับแถว และนับแถว

Any –ประกอบด้วยคำสั่งที่จะทำงานในคอลัมน์ต่าง ๆ ทั้งการเปลี่ยนประเภทข้อมูล การตรวจหาและเปลี่ยนประเภทข้อมูลโดยอัตโนมัติ เปลี่ยนชื่อส่วนหัวของคอลัมน์ ค้นหาและแทนที่ค่า เติมค่า คอลัมน์เพื่อแทนที่ช่องว่าง หรือ เติมค่าว่างด้วยค่าด้านบน (หรือด้านล่าง) , pivot หรือ unpivot คอลัมน์ ย้ายคอลัมน์ หรือแปลงคอลัมน์เป็นรายการ

Text– แบ่งคอลัมน์ด้วยตัวคั่น จัดรูปแบบตัวพิมพ์เล็ก ปรับแต่งข้อมูล รวมสองคอลัมน์ขึ้นไปเข้าด้วยกัน แยกข้อความ XML หรือ JSON

Number– พวกการคำนวณอย่างเช่น ผลรวมและค่าเฉลี่ย ดำเนินการพีชคณิตมาตรฐานหรือตรีโกณมิติ และ ปัดเศษขึ้นหรือลง

Date & Time–ข้อมูลวันที่ เวลา และระยะเวลา

Structured – ส่วนนี้มีคำสั่งสำหรับการทำงานกับโครงสร้างข้อมูลที่ซ้อนกัน

Add Column

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

General –เพิ่มคอลัมน์ใหม่ตามสูตรหรือฟังก์ชันแบบกำหนดเอง ท่านยังสามารถเพิ่มคอลัมน์ดัชนีหรือทำสำเนาคอลัมน์ได้จากที่นี่

From Text – คล้ายกับส่วนจากข้อความในแท็บการแปลง แต่คำสั่งเหล่านี้จะสร้างคอลัมน์ใหม่ที่มีการเปลี่ยนแปลง

From Number – คล้ายกับส่วน From Number ในแท็บ Transform แต่คำสั่งเหล่านี้จะสร้างคอลัมน์ใหม่ที่มีการเปลี่ยนแปลง

From Date & Time – คล้ายกับส่วนจากวันที่และเวลาในแท็บการแปลง แต่คำสั่งเหล่านี้จะสร้างคอลัมน์ใหม่ที่มีการเปลี่ยนแปลง

Transform VS Add Column

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

View

แท็บ View นี้บางท่านอาจจะไม่ค่อยได้ใช้งานเท่าไหร่ เพราะเป็นแค่การจัดการมุมมองเท่านั้น ไม่มีคำสั่งสำหรับทำงานปรับปรุงข้อมูล

Layout – ส่วนนี้ให้ท่านแสดงหรือซ่อน Formular Bar หน้าต่าง Query Setting

Data Preview– ส่วนนี้ปรับแต่งหน้าตาของหน้าต่าง Data Preview จะแสดงหรือซ่อน Whitespace เปลี่ยนแบบอักษรให้เป็นแบบอักษรโมโนสเปซในพื้นที่แสดงตัวอย่างข้อมูล

Columns – ช่วยให้ท่านไปที่และเลือกคอลัมน์ที่ต้องการในการแสดงตัวอย่างข้อมูล คำสั่งนี้มีอยู่ในแท็บ Home

Parameters – อนุญาตให้กำหนดพารามิเตอร์

Advanced –จะเปิดตัว Advance Power Query Editor

Dependencies –เปิดมุมมองไดอะแกรมการเกี่ยวพันกันของ Query ทั้งหมดใน Workbook ซึ่งจะมีประโยชน์มาก ในการดูกระบวนการโดยรวมของการปรับปรุงข้อมูล

Query List

Query List นอกจากจะแสดงรายชื่อ Query ที่มีใน workbook นั้นแล้ว เรายังจัดการอะไรได้อีกหลายอย่าง เช่น สลับการทำงานในแต่ละ Query โดยคลิกที่ชื่อ Query ที่ต้องการทำงาน

และถ้าเราคลิกขวาที่ชื่อ Query ก็จะเห็นรายการให้เลือกดังนี้

Copy / Paste – คัดลอก / วาง Query

Delete – ลบ Query ซึ่งขออนุญาตแนะนำไว้ก่อนว่า หากเผลอลบโดยไม่ตั้งใจ ให้ปิด Power Query Editor โดยไม่ save จะช่วยได้

Rename – เปลี่ยนชื่อ Query ของท่าน ซึ่งเหมือนกับ Properties ใน Query Setting

Duplicate – ทำสำเนาอีกชุด

Reference – ทำสำเนาไว้อ้างอิง

Move to Group – เอาไปวางใน group เพื่อการจัดการให้เป็นระเบียบ

Move Up / Move Down – จัดเรียงลำดับเลื่อนขึ้นเลื่อนลง!

Create Function – เปลี่ยน Query ให้เป็น Function อนุญาตให้ส่งพารามิเตอร์ไปยัง Query และส่งคืนผลลัพธ์ตามพารามิเตอร์ที่ส่งผ่าน.

Convert to Parameter –แปลง Query เป็นพารามิเตอร์

Advance Editor – ตัวจัดการระดับสูงกว่าเดิมหน่อย อันนี้ไว้ค่อยเขียนถึงอีกครั้งภายหลัง

Properties –เปลี่ยนชื่อ Query เพิ่มข้อความอธิบาย และเปิดใช้ตัวเลือก Fast Data Load สำหรับ Query (เหมือนกับ Properties ใน Query Setting)

หากท่านคลิกขวาที่พื้นที่ว่างใดๆ ใน Query Lists สามารถสร้าง Query หรือ Group ใหม่ได้

Formular Bar

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

Data Preview

พื้นที่หลัก และเป็นพื้นที่ส่วนใหญ่ใน Power Query Editor ก็คือ Data Preview ที่จะแสดงข้อมูลให้เห็นว่ามีอะไรเกิดขึ้นบ้าง หน้าตาก็จะเหมือนกับหน้าตาของ Excel ทั่วไป

บริเวณมุมบนด้านซ้ายมือ จะเห็นสัญลักษณ์รูปตาราง ตรงนี้กดเพื่อจัดการเรื่องต่าง ๆ เกี่ยวกับข้อมูลในตารางได้

การจัดการคอลัมน์

หัวคอลัมน์ จะแบ่งเป็นสามส่วน

ด้านซ้าย จะเป็น ประเภทของข้อมูลในคอลัมน์นั้น แสดงเป็นสัญลักษณ์ต่าง ๆ เป็นตัวแทน เพียงแค่คลิกไปที่ไอคอนสัญลักษณ์นั้น จะเห็นประเภทอื่น ๆ ซึ่งเราคลิกเลือกเพื่อเปลี่ยนประเภทข้อมูลของคอลัมน์นั้นได้เลย จะมี decimal numbers (ตัวเลขทศนิยม) currency (สกุลเงิน) whole numbers (จำนวนเต็ม) percentages (เปอร์เซ็นต์) date and time (วันที่และเวลา) dates (วันที่) times (เวลา) timezone (เขตเวลา) duration (ระยะเวลา) text (ข้อความ) Boolean (ผลลัพธ์ตรรกะ จริง ไม่จริง)  และ binary (ไบนารี)

ตรงกลาง จะเป็นชื่อคอลัมน์ ซึ่งท่านสามารถดับเบิลคลิก เพื่อเปลี่ยนชื่อคอลัมน์ได้โดยตรง

ด้านขวา จะเป็นตัวกรอง สำหรับกรองเพื่อเลือกข้อมูลต่าง ๆ มาจัดการได้ ใช้งานเหมือนกับตัวกรองใน Excel ปกติทั่วไป

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

เลือกหลายคอลัมน์ แต่คอลัมน์ไม่ได้อยู่ติดกัน กด Ctrl ค้างไว้ จากนั้นคลิกซ้ายที่ส่วนหัวของคอลัมน์ที่ท่านต้องการเลือก

Query Setting

สำหรับ Query Setting จะแบ่งเป็นสองส่วนคือ Properties กับ Applied

Properties จะมีชื่อของ Query ที่กำลัง Active แสดงอยู่ ซึ่งเราเปลี่ยนชื่อได้จากตรงนี้เลย และเพิ่มคำอธิบายโดยคลิกที่ All Properties ได้

หน้าต่าง Applied ก็เป็นเหมือน history หรือเก็บประวัติว่าท่านได้จัดการเปลี่ยนแปลงอะไรกับข้อมูลเหล่านั้นมาแล้วหรือไม่ โดยจะแสดงเป็นลำดับตามขั้นตอนการทำ

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

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

สำหรับการทำงานใน Power Query Editor จะอธิบายในบล็อกต่อไป