ฝึกปฏิบัติ Power Query

เราจะใช้ไฟล์ forPowerQuery.csv เป็นข้อมูลนำเข้า แล้วใช้ Power Query ในการทำความสะอาดและแปลงข้อมูลแล้วสร้างเป็น Worksheet

1. เปิด Notepad แล้วลากไฟล์มาวางเพื่อดูว่าข้อมูลหน้าตาเป็นอย่างไร
2. ให้ลองเปิดไฟล์ด้วยการ doble click ที่ไฟล์ forPowerQuery.csv Excel จะเปิดไฟล์นี้ได้ แต่จะ save เป็น format ใด?ถ้าเซฟธรรมดามันจะเซฟลงไปในไฟล์เดิมซึ่งเป็น format  CSV
3. import CSV ไฟล์ Data-->from Text/CSV เลือกไฟล์ที่ต้องการ หรือจะ import ไฟล์จากเว็บโดยตรงด้วยการระบุ URL ก็ได้โดยเข้าที่ตัวเลือกเมนูดังนี้ Data->Get Data ->From Other Sources->From Web แล้วใส่ URL (copy link address จากเว็บมา paste) จะเปิด dialog ให้ preview ข้อมูล ทำความเข้าใว่า dialog นี้มีตัวเลือกอะไรบ้าง ให้ scroll ลงมาดูจะเห็นว่าให้ดูเป็นตัวอย่างไม่กี่รายการ ถ้าอยากดูทั้งหมดให้เลือก "Based on entire dataset" ตรง dropdown "Data Type Detection" ให้ scroll จะเห็นข้อมูลทั้งหมด ด้านล่างของ dialog มีปุ่ม Load กับ Transform ปุ่ม Load สั่งให้บรรจุข้อมูลเข้า worksheet ทันที ส่วน Transform ใช้ในกรณีที่ต้องการแปลงข้อมูลก่อนบรรจุ
4. ให้คลิก Load ดูก่อน จะเห็นว่า ข้อมูลจะถูกนำไปบรรจุ (Load แปลว่าใส่เข้าไปหรือบรรจุ) ใน worksheet ใหม่ และ worksheet นี้จะอยู่ที่ริบบอน "Design" ของ "Table Tools" และมีริบบอน "Query"
5. ลองไปแก้ไขข้อมูลใน Notepad เปลี่ยน Sale amount รายการแรก 555.55 แล้ว save ไฟล์  อาจเซฟไฟล์ลงชื่อเดิมไม่ได้ เพราะ Excel ยงใช้ไฟล์นี้ใช้อยู่ ให้กลับไปที่  Excel แล้วเซฟไฟล์เป็นเป็นสกุล Excel เพื่อให้ Excel เลิกใช้ไฟล์เดิมที่มีสกุลเป็น CSV คราวนี้ควรจะเซฟไฟล์ด้วย Notepad ได้แล้ว
6. กลับไปที่ Excel ข้อมูลยังไม่เปลี่ยนตามเพราะมันโหลดมาก่อนที่จะมีการแก้ไข ให้คลิก Reflesh บนริบบอน Table Tools Design หรือที่ ... หรือ ... มันจะโหลดข้อมูลเข้ามาอีกครั้งได้เป็นข้อมูลใหม่ล่าหลังจาที่มีการแก้ไขแล้ว
7. ให้เลื่อนลูกศรไปชี้ที่ชื่อไฟล์ในแผง(pane) "Queries & Connections" จะขึ้นหน้าต่างที่แสดงข้อมูลเกี่ยวกับ query ของข้อมูลนี้ ดูรายละเอียดว่ามีอะไรบ้าง เช่น Last Refresh, Load Status, Data Sources
8. ถ้าต้องการ Transform ข้อมูลก่อน load ไม่ควรคลิก Load ให้คลิก Transform ให้เริ่ม import ไฟล์เดิมอีกครั้งแต่คราวนี้ให้คลิก Transform จะเปิดหน้าต่างใหม่ ซึ่งเรียกว่า Power Query Editor เมื่อเปิดหน้าต่างนี้ขึ้นมาแล้วจะทำอะไรกับ worksheet ไม่ได้จนกว่าจะปิดหน้าต่างนี้ก่อน
9. ดูที่แผงด้านขวาที่เรียกว่า Query Setting จะมี PROPERTIES กับ APPLIED STEPS ซึ่ง 2 หัวข้อนี้สามารถยุบยืดได้ ช่อง Name เป็นชื่อ Query สามารถเปลี่ยนจากชื่อ default เป็นชื่อที่เราต้องการได้ ให้เปลี่ยนชื่อเป็น Cleaning
10. ช่อง APPLIED STEPS จะแสดงรายการที่ดำเนินการไปแล้ว สามารถคลิกแต่ละรายการเพื่อดูว่าในแต่ละขั้นได้ทำอะไรไปถึงไหน ชื่อ step จะบอกความหมายว่าทำอะไร ลองคลิกไล่ดูตั้งแต่ลำดับแรก แล้วสังเกตตรง Formular bar ในช่องนี้จะเป็นคำสั่งภาษา M
11. ตรงขั้นตอน Change Type ซึ่ง Power Query จะวิเคราะห์ข้อมูลในแต่ละคอลัมน์ว่าควรเป็นชนิดใด แล้วมันจะเปลี่ยนคอลัมน์ให้เป็นชนิดนั้น สังเกตคอลัมน์ Sale Amount มันเปลี่ยนให้เป็นตัวเลข เนื่องจากมันวิเคราะห์แล้วว่าทุกบรรทัดเป็นตัวเลข แต่คอลัมน์ Call Total, Order และ Rank ซึ่งน่าจะเป็นตัวเลขเช่นกัน แต่มันไม่เปลี่ยนคอลัมน์เหล่านี้ให้เป็นตัวเลข แสดงว่ามีข้อมูลที่ไม่ใช่ตัวเลขปะปนอยู่ด้วย ลองเลื่อนลงไปสำรวจดูข้อมูลบรรทัดต่าง ๆ จะพบว่ามีบรรทัดว่างอยู่จำนวนหนึ่ง และมีค่าข้อมูล "Test Entry" ปะปนอยู่ในบางบรรทัด ซึ่งถือว่าเป็น "ขยะ" ที่ทำให้ข้อมูลสกปรก ขั้นตอนต่อไปเราจะทำความสะอาดข้อมูล (cleaning data)
12. คลิกที่สัญรูป Remove Rows เลือก Remove Blank Rows เพื่อลบบรรทัดว่าง
13. ต่อไปเราจะเปลียนคอลัมน์ Call Total ให้เป็นชนิดตัวเลข คลิกขวาที่หัวคอลัมน์ (เรียกว่า Column Context Menu) Call Total เลือก Change Type->Whole Number เพื่อเปลียนให้เป็นชนิดเลขจำนวนเต็ม จะเห็นว่าตัวเลขจะอยู่ชิดขวา ลองไล่ดูข้อมูลในบรรทัดที่ไม่ใช่ตัวเลขจะขึ้น Error ตัวสีเขียว ตรงเซลที่ไม่สามารถเปลี่ยนเป็นตัวเลขได้
14. ต่อไปเราจะเปลี่ยนคอลัมน์ Order และ Rank ให้เป็นชนิดตัวเลขไปพร้อมกัน ให้คลิกเลือกทั้ง 2 คอลัมน์นี้ (กด Ctrl ค้างไว้ก่อนคลิกเลือกคอลัมน์อื่นเพิ่ม) แล้วทำแบบเดียวกับขั้นตอนที่แล้ว ให้สังเกตุ query ที่ step Change Type เราเปลี่ยนชนิดช้อมูล 2 ครั้ง กับ 3 คอลัน์ แต่มี step change type เพียง step เดียว เป็นเพราะ Power Query ได้ยุบรวม 2 step ที่ทำไปแล้วไว้ใน step เดียว ลองดูใน Formular bar จะเห็นคำสั่งเดียวแต่สั่งให้ชนิดข้อมูลของ 3 คอลัมน์
15. ต่อไปเราจะลบบรรทัดที่มี error ออก ด้วยการเลือกเมนู Home->Remove Rows->Remove Errors บรรทัดใดที่มี Error ปะปนอยู่จะถูกลบออกไป
16. ต่อไปเราจะลอง Sort ข้อมูลบางคอลัมน์เพื่อสำรวจข้อมูลบางอย่าง การ Sort Column จะให้ดูว่าเมื่อเรา sort คอลัมน์หนึ่งแล้วจะเปลี่ยนไป sort คอลัมน์อื่นได้อย่างไร
    ให้ sort คอลัมน์ order โดยคลิกที่ แล้วสังเกตว่า คอลัมน์ order กับ order date เรียงไปด้วยกัน
17. ต่อไปเราจะลอง sort คอลัมน์ Rank ดูบ้าง ปรากฏว่า sort ไม่ไป ทั้งนี้เนื่องจากมีการ sort คอลัมน์อื่นไปก่อนแล้ว จะไม่ยอมให้ sort คอลัมน์อื่นอีก ถ้าเราเปลี่ยนใจจะไป sort คอลัมน์อื่นต้องยกเลิกคอลัมน์เดิมที่เคย sort ไปแล้วเสียก่อนโดยการคลิกตรงเครื่องหมาย x ตรง step ที่ต้องการ เพื่อยกเลิก step นั้น เมื่อยกเลิกการ sort ออกไปแล้ว จึงจะ sort คอลัมน์ใหม่ได้ คราวนี้หลังจาก sort คอลัมน์ Rank แล้ว สังเกตุดูว่า ค่าของ Rank ไม่ซ้ำกันเลย และตรงกับลำดับของ Sale Amount แสดงว่าเป็นการจัดอันดับตามค่าของ Sale Amount
18. จะเห็นว่า Order และ Rank เป็นคอลัมน์ที่ซ้ำซ้อนกับข้อมูลอื่น ถึงค่าจะไม่เท่ากันแต่สัมพันธ์กันซึ่งสามารถหาใหม่ได้ในภายหลังถ้าต้องการ จึงไม่ควรเก็บ 2 คอลัมน์นี้ไว้ให้สิ้นเปลืองเนื้อที่ในการจัดเก็บ ถ้าจำเป็นต้องใช้สามารถคำนวณขึ้นมาใหม่ในภายหลังได้ จึงไม่ควรนำ 2 คอลัมน์นี้มาบรรจุไว้ใน worksheet ลบคอลัมน์ด้วยการคลิกขวาที่หัวคอลัมน์ที่ต้องการลบแล้วคลิกเลือก Remove
19. คอลัมน์ SKU_ID เป็นรหัสสินค้า เราเก็บแต่ชื่อสินค้าก็พอ จึงลบ คอลัมน์นี้ออกไปด้วย
20. ย้ายคอลัมน์ Order Date ไปไว้เป็นคอลัมน์แรกด้านซ้ายสุด ด้วยการคลิกขวาที่หัวคอลัมน์แล้วเลือก Move--> To Begining สังเกตว่ามีการเพิ่มคำสั่งลงในช่อง Applied Steps
21. เราทำความสะอาดข้อมูลจนเป็นที่พอใจแล้ว ก็คลิก Close & Load เพื่อเซฟ query แล้วปิดหน้าต่าง Power Query Editor ต่อจากนั้นจะทำการบรรจุข้อมูลเข้า worksheet ใหม่ ให้ rename worksheet ใหม่ที่ได้เป็นชื่อ Cleaned Sale Data ส่วน worksheet อื่นที่เหลือไม่จำเป็นต้องใช้อีกต่อไปให้ลบทิ้งไป แล้วเซฟไฟล์ คลิก Close and Load เพื่อให้ทำการ clean และ load เข้า Excel workbook