function query

4 ขั้นตอนในการสร้าง Function Query แบบที่มี UI ให้แก้เล่นได้ใน Power Query

ในบทความนี้ผมจะมาแนะนำวิธีสร้าง Function Query ขึ้นใน Power Query ซึ่งไม่ใช่แบบปกติ แต่จะเป็นแบบที่ยังยอมให้เราแก้ไข Step ต่างๆ ใน Function นั้นได้โดยง่าย แต่ถ้าใครยังไม่รู้ว่าผมพูดถึงเรื่องอะไร ลองอ่าน recap นี้ดูก่อนเล็กน้อย... Recap Power Query เป็นเครื่องมือสำหรับการดัดแปลงข้อมูลที่ทรงพลังและใช้ง่ายมากๆ อยากจะทำอะไรก็มีเมนูสำเร็จรูปเอาไว้ให้กดได้เลยโดยแทบไม่ต้องเขียนสูตร แต่ว่าหลายครั้งก็น่าเบื่อเวลาที่เราต้องทำ Step อะไรซ้ำๆ เหมือนเดิมกับหลายๆ Query จะดีกว่ามั้ยถ้าเราทำ Step พวกนั้นเป็นฟังก์ชันแล้วเรียกใช้ได้อย่างง่ายๆ เลย? ในหนังสือ Excel Power Up! ที่ผมเขียนไว้ ในบทหลังๆ ก็มีเขียนวิธีที่ทำให้ Query กลายเป็นฟังก์ชันได้ง่ายๆ โดยเปิด Advanced Editor แล้วใส่คำว่า (input1, input2)=> นำหน้า code เดิม ซึ่งมันจะเปลี่ยน…
วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query 1

วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query

ปกติเวลาเราสั่งให้ Power Query รวมทุกไฟล์ใน Folder เดียวกัน แม้มันจะเอาข้อมูลรวมกันได้ แต่ก็อาจจะมีปัญหาเรื่องชื่อคอลัมน์ได้ในอนาคต สาเหตุเพราะ มันมีการระบุชื่อคอลัมน์ที่แตกออกมาอย่างชัดเจนในสูตร M Code ทำให้ หากในอนาคตมีคอลัมน์เพิ่มขึ้นหรือลดลง ก็จะมีปัญหาทันที (อย่างเช่นที่ผมบอกไว้ในหนังสือ Excel Power Up ว่า เมื่อไหร่ก็ตามที่มีการระบุชื่อคอลัมน์ในสูตร M Code นั้น จะอันตรายต่อการ Refresh ทันที) ลองทำดู โหลดไฟล์ประกอบได้ที่นี่ ตัวอย่างเช่น ผมสั่งรวมข้อมูลจาก Folder ที่ชื่อ MyFolder และสร้าง Custom Column ดังนี้ เพื่อดึงข้อมูลจาก Excel ด้วยสูตรว่า =Excel.Workbook([Content],true) ผมแตกคอลัมน์ Custom ออกมาจะได้ดังนี้ เดี๋ยวผมลบคอลัมน์ที่ไม่ใช้ทิ้งก่อน โดยจะเก็บแค่ ชื่อไฟล์ ชื่อ Item…
การใช้ Power Query จัดการข้อมูลที่มีหัวตาราง 2 ชั้น 2

การใช้ Power Query จัดการข้อมูลที่มีหัวตาราง 2 ชั้น

ปัญหาข้อมูลเน่าแบบ classic อันนึงเลยก็คือ ได้ข้อมูลเชิง Report มา และดันใส่หัวตาราง 2 ชั้นมาด้วย ทำให้ใช้ Unpivot ตรงๆ ไม่ได้ คราวนี้จะทำยังไงดี มาดูกันครับ Download ข้อมูลที่มีหัวตาราง 2 ชั้นได้ที่เว็บสำนักงานสถิติแห่งชาติ จำนวนนักเรียน นิสิต นักศึกษาในระบบโรงเรียน ในสถานศึกษาของรัฐบาลและเอกชน จำแนกตามระดับการศึกษา และชั้น ปีการศึกษา 2556 - 2560 หรือ http://statbbi.nso.go.th/staticreport/Page/sector/TH/report/sector_03_3_TH_.xlsx หลักๆ การจัดการข้อมูลใน Power Query จะมีแนวทางดังนี้ ข้อมูลอยู่ในรูปแบบ Report ยังไม่ใช่ข้อมูลเชิง Database ติดข้อมูลอื่นที่ไม่เกี่ยวมา เช่น header/footer -> filter, remove col, remove top/bottom rowsข้อมูลเว้นว่างแบบละไว้ในฐานที่เข้าใจ…
เทคนิคการเผชิญหน้ากับข้อมูลเน่าเฟะด้วย Power Query 3

เทคนิคการเผชิญหน้ากับข้อมูลเน่าเฟะด้วย Power Query

หากคุณกำลังเจอข้อมูลเน่าเฟะ อย่าเพิ่งสิ้นหวัง... ทำอย่างไรเมื่อเจอข้อมูลที่อยู่ในรูปแบบที่แย่และไม่ ok เอาซะเลย และดันมีความจำเป็นต้องแก้ด้วย Power Query ซะด้วยสิ ในบทความนี้เราจะมาเผชิญความเน่าเฟะไปด้วยกันครับ เตือนไว้ก่อนว่าบทความนี้ยาวหน่อยและมีหลายขั้นตอน ซึ่งเป็นเรื่องปกติที่การกำจัดข้อมูลที่เน่าเฟะนั้นต้องใช้เวลา ดังนั้นอย่าเพิ่งท้อถอยนะครับ รับรองว่าจะได้ความรู้ดีๆ กลับไปแน่นอน ข้อมูลเน่าเฟะที่ได้มา สมมติว่าเราได้ข้อมูลหุ้นในตลาด SET100 ทุกตัวหน้าตาแบบนี้มา ซึ่งผมใช้ Python ดึงมารวมกันแบบไม่ได้ Clean ดีๆ ตั้งแต่แรก (จะได้มาฝึกทำใน Power Query ไง 555) ซึ่งดาวน์โหลดไฟล์ได้ที่นี่ (Google Drive / GitHub) ความยาก1 มี Topic แทรกอยู่ระหว่างบรรทัดอยู่เป็นระยะๆ เช่น ทรัพย์สินรวม หนี้สินรวม ความยาก2 จำนวนตัวเลขต่อ 1 Topic มี 4 ตัวบ้าง 5…
Power BI ตอนที่ 07: เริ่ม Get Data ตั้งแต่ไฟล์ยังว่างเปล่า 4

Power BI ตอนที่ 07: เริ่ม Get Data ตั้งแต่ไฟล์ยังว่างเปล่า

เอาล่ะ ในที่สุดก็ถึงเวลาที่จะเรียนรู้การดึง Data เข้า Power BI ด้วยเครื่องมือ Get Data/Transform Data หรือก็คือ Power Query นั่นเอง (ใครได้อ่านหนังสือ Excel Power Up! มาก็น่าจะเข้าใจได้เร็วกว่านะครับ เพราะมันคือตัวเดียวกันเลย) ซึ่งในบทความนี้ผมมีไฟล์ Data ให้โหลดเป็นไฟล์ zip 1 อัน (โหลดจาก Google Drive / โหลดจาก GitHub) ซึ่งถ้าแตกออกมาจะมี Folder อยู่ 1 อันชื่อว่า Contoso ในนั้นจะมีไฟล์ Excel 1 ไฟล์ที่มีหลาย sheet โดยแต่ละ Sheet คือตาราง Reference ทั้งหมดที่ต้องใช้และมีไฟล์ CSV อยู่…
วิธีดึงข้อมูลจาก Excel ไฟล์ย่อยมาทำรายงานสรุปใน Excel หลัก (อีกไฟล์) /Power BI 5

วิธีดึงข้อมูลจาก Excel ไฟล์ย่อยมาทำรายงานสรุปใน Excel หลัก (อีกไฟล์) /Power BI

ผมเองก็ได้นำเสนอข้อมูลเกี่ยวกับ Power Query มาเยอะพอสมควรว่ามันสามารถรวบรวมข้อมูลจากหลายแหล่ง และยังสามารถดัดแปลงหน้าตาข้อมูลให้อยู่ในรูปแบบที่เหมาะสมมากขึ้นได้ แต่เหมือนว่ายังมีเพื่อนๆ หลายท่านอาจยังไม่เห็นภาพว่ามันดึงข้อมูลจาก Excel มารวมกันได้ยังไง ดังนั้นในบทความนี้ผมจะสรุปวิธีดึงข้อมูลจากไฟล์ Excel ให้ดูครับ การดึงข้อมูลจาก Excel นั้นจริงๆ มีหลากหลายรูปแบบ มีความซับซ้อนหลายระดับ (แบบยากสุดจริงๆ จะรองรับแบบสถานการณ์แบบง่ายสุดได้ด้วยครับ) ดึงข้อมูลจาก Excel 1 ไฟล์ ต้องการแค่ 1 Table จาก Excel 1 ไฟล์ การดึงข้อมูลจาก Table เดียวจากไฟล์ Excel ที่ต้องการนั้นง่ายที่สุดเลย นั่นก็คือ กด Get Data -> From File -> From Workbook แล้วเลือกไฟล์ Excel ที่ต้องการ และเลือก Table…
วิธีเปรียบเทียบข้อมูล 2 ตาราง ว่ามีรายการไหนตรงกัน ไม่ตรงกัน? 6

วิธีเปรียบเทียบข้อมูล 2 ตาราง ว่ามีรายการไหนตรงกัน ไม่ตรงกัน?

สำหรับเรื่องการเปรียบเทียบข้อมูล 2 ตารางนี้ เป็นอีกเรื่องที่มีคนถามมาเยอะ แต่ในความเป็นจริงหลักการไม่มีอะไรไปกว่าการใช้สูตรกลุ่มพวก lookup ในการตรวจสอบแค่นั้นเอง และเพื่อให้มันเจ๋งยิ่งขึ้น ผมจะแสดงวิธีที่ 2 วิธีเลยละกัน นั่นคือ 1. ใช้สูตร 2. ใช้ Power Query ก่อนอื่น สมมติว่าผมมีข้อมูลดังนี้ วิธีการใช้สูตรในการเปรียบเทียบข้อมูล Q1: คนไหนมีชื่ออยู่ทั้ง 2 ตาราง? หากเราอยากรู้ว่าคนไหนบ้างที่มีชื่ออยู่ใน 2 ตาราง เราจะทำยังไง? แนวคิดในการเปรียบเทียบข้อมูลก็คือ เราจะหาชื่อในตาราง Math ทีละตัว โดยเอาแต่ละตัววิ่งหาในตาราง Excel ว่าเจอรึเปล่า? ถ้ามันเจอ ก็แปลงว่าตัวนั้นอยู่ทั้ง 2 ตาราง จริงมะ? ดังนั้นเราจะเขียนสูตรใน D4 เพื่อเอาคะแนนในอีกตารางได้ว่า =VLOOKUP(B4,G:H,2,FALSE) ปล. สูตรข้างบนเป็นฉบับคนขี้เกียจ ซึ่งเลือกทั้งคอลัมน์เลย เพื่อนๆ อาจเลือกเฉพาะที่มีข้อมูลก็ได้นะครับ…
วิธีใช้ Power Query ดึงข้อมูลจากไฟล์ Excel เดียวกัน โดยไม่ต้องแปลงเป็น Table ก่อน 7

วิธีใช้ Power Query ดึงข้อมูลจากไฟล์ Excel เดียวกัน โดยไม่ต้องแปลงเป็น Table ก่อน

ตามปกติแล้ว เวลาเราใช้ Power Query เพื่อGet Data จากไฟล์ Excel เดียวกันกับตัว Power Query ที่กำลังสร้าง เราจะต้อง Get Data from Table/Range เท่านั้น ซึ่งหากใช้วิธีนี้ Excel จะบังคับให้แปลงข้อมูลเป็น Table ก่อนเสมอ และนั่นก็เป็นวิธีมาตรฐานที่ทาง Microsoft ออกแบบเอาไว้ แต่ในหลายๆ สถานการณ์ เราอาจจะไม่ได้อยากแปลงข้อมูลเป็น Table ก็ได้ (ไม่ว่าด้วยเหตุผลอะไรก็ตาม) วิธีแก้ปัญหาสำหรับสถานการณ์นี้จริงๆ มีอยู่ 2 วิธี คือ ใช้ Get Data From Excel Workbook แล้วมา Browse หาไฟล์ปัจจุบันที่ทำงานอยู่ วิธีนี้ทำง่าย แต่ผมไม่แนะนำ เพราะว่าวิธีนี้มีข้อเสียสำคัญคือ ต้อง…
เรื่องประหลาดเกี่ยวกับวันที่ของ Excel vs Power Query 8

เรื่องประหลาดเกี่ยวกับวันที่ของ Excel vs Power Query

หลายๆ คนน่าจะพอรู้อยู่ว่าใน Excel นั้นจะมองวันที่เป็นแค่ Format ของค่าที่แท้จริงที่เป็นเลขจำนวนเต็มธรรมดา โดยเริ่มจากเลข 1 คือวันที่ 1/1/1900 (ค.ศ.) พอวันเพิ่มขึ้น 1 วัน ค่าที่แท้จริงก็จะเพิ่มขึ้น 1 หน่วยด้วย โดยวันที่สุดท้ายที่ Excel รองรับคือ 31/12/9999 (ค.ศ.) แต่ว่าใน Power Query นั้นรองรับวันที่วันแรกได้เก่าถึง 1/1/0001 (ค.ศ.) ซึ่งจะไม่สามารถแสดงออกมาเป็นวันที่หรือตัวเลขใน Excel ได้เลย ส่วนวันที่สุดท้ายก็รองรับถึง 31/12/9999 (ค.ศ.) เช่นเดียวกับ Excel ความผิดเพี้ยนอยู่ที่ต้นปี 1900 แต่ที่แปลกคือ ช่วงวันแรกๆ ของปี ค.ศ. 1900 นี่แหละ เจ้า Excel กับ Power Query จะมองเลขจำนวนเต็มเดียวกันเป็นคนละวัน!!…
แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 9

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก

บทความนี้เป็นภาคต่อจากบทความที่ผมเคย post ในปี 2014 (เกือบ 6 ปีมาแล้วเหรอเนี่ย!! ) ซึ่งเกี่ยวกับเรื่องลับที่หลายคนไม่รู้ใน Excel ซึ่งได้รับความสนใจอย่างสุดๆ (ใครยังไม่เคยอ่านก็ลองเข้าไปดูนะ) แฉ 10 ความลับของ EXCEL ที่คุณอาจยังไม่เคยรู้มาก่อน!แฉ 10 ความลับของ EXCEL ภาค 2 : เรื่องง่ายๆ ที่หลายคนไม่รู้ ตอนนี้ก็ขึ้นปี 2020 แล้ว ผมคิดว่าได้เวลาที่จะ post ภาค 3 เป็นของขวัญปีใหม่ให้กับทุกท่านในปี 2020 นี้เลยละกัน แล้ว 10 เรื่องที่ผมคิดว่าเจ๋ง แต่หลายคนไม่รู้ในช่วงนี้ มีอะไรบ้าง? มาดูกันเลย!! (บอกเลยว่าหลายอันนี่ Advance มากๆ นะ และบางอันอาจมีใน Excel version ใหม่ๆ หน่อยนะครับ)…