คลังสูตรExcel 80 · DAX 95 · M 15
หน้าแรก › Power Query (M)

15 ฟังก์ชัน · เฟส M1 · ใช้ได้ทั้ง Excel และ Power BI

Power Query (ภาษา M)

ทุกปุ่มที่กดใน Power Query Editor เขียนโค้ด M ให้หนึ่งขั้น ทุกหน้ามีตัวอย่างที่แก้ได้ เห็นตารางก่อนและหลังแต่ละขั้น เทียบกับวิธีทำใน Excel และ DAX พร้อมไฟล์ฝึกที่ตรวจคำตอบเอง

อ่านโค้ด M ให้ออกใน 4 เรื่อง

เปิด Home › Advanced Editor จะเห็นโค้ดทั้ง query หน้าตาแบบนี้

let … in คือรายการขั้นตอน

แต่ละบรรทัดคือหนึ่ง Applied Step รูปแบบ ชื่อขั้น = สูตร ขั้นถัดไปอ้างชื่อขั้นก่อนหน้า ส่วนหลัง in คือขั้นที่เป็นผลลัพธ์

#"ชื่อที่มีช่องว่าง"

ชื่อขั้นอย่าง Changed Type มีช่องว่าง จึงเขียนเป็น #"Changed Type" ส่วนชื่อคำเดียวอย่าง Source เขียนได้เลย

each กับ [คอลัมน์]

each คือฟังก์ชันที่ทำทีละแถว [จำนวน] คือค่าของคอลัมน์จำนวนในแถวนั้น เหมือน [@จำนวน] ใน Excel Table

ตัวพิมพ์ใหญ่เล็กมีผลเสมอ

Table.SelectRows ต้องสะกดตรงทุกตัว และข้อความ "Online" ไม่เท่ากับ "online" ต่างจาก Excel ที่ไม่สนตัวพิมพ์

หัวตารางและชนิดข้อมูล

Table.PromoteHeadersใช้แถวแรกของตารางเป็นชื่อคอลัมน์ แทนชื่อ Column1, Column2 ที่ได้จากข้อมูลที่ไม่มีหัวตารางM · หัวตารางและชนิดข้อมูลพื้นฐานTable.TransformColumnTypesกำหนดชนิดข้อมูลของแต่ละคอลัมน์ เช่น ข้อความ ตัวเลข วันที่ ค่าที่แปลงไม่ได้กลายเป็น Error ในช่องนั้นM · หัวตารางและชนิดข้อมูลพื้นฐานTable.RenameColumnsเปลี่ยนชื่อคอลัมน์ ระบุเป็นคู่ {ชื่อเดิม, ชื่อใหม่} ได้หลายคู่ในขั้นเดียวM · หัวตารางและชนิดข้อมูลพื้นฐาน

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

Table.SelectColumnsเลือกเฉพาะคอลัมน์ที่ต้องการ คอลัมน์อื่นหายไป และคอลัมน์เรียงตามลำดับใน listM · จัดการคอลัมน์พื้นฐานTable.RemoveColumnsลบคอลัมน์ที่ระบุออกจากตาราง คอลัมน์อื่นอยู่ครบในลำดับเดิมM · จัดการคอลัมน์พื้นฐาน

จัดการแถว

Table.Skipข้ามแถวบนสุดของตารางตามจำนวนที่กำหนด หรือข้ามไปเรื่อย ๆ ตราบที่เงื่อนไขยังเป็นจริงM · จัดการแถวพื้นฐานTable.SelectRowsเก็บเฉพาะแถวที่เงื่อนไขเป็นจริง เงื่อนไขเขียนด้วย each และอ้างคอลัมน์ของแถวนั้นด้วย [ชื่อคอลัมน์]M · จัดการแถวพื้นฐานTable.Sortเรียงแถวตามคอลัมน์ที่กำหนด ใส่ได้หลายคอลัมน์ คอลัมน์แรกเรียงก่อน ค่าที่เท่ากันจึงเรียงด้วยคอลัมน์ถัดไปM · จัดการแถวพื้นฐานTable.Distinctลบแถวที่ซ้ำ เหลือแถวแรกของแต่ละชุดค่า เทียบทั้งแถวหรือเฉพาะคอลัมน์ที่ระบุM · จัดการแถวพื้นฐานTable.FirstNเก็บแถวบนสุดตามจำนวนที่กำหนด หรือเก็บไปเรื่อย ๆ ตราบที่เงื่อนไขยังเป็นจริง ใช้ต่อจาก Table.Sort เพื่อหา Top NM · จัดการแถวพื้นฐาน

ล้างและแปลงค่า

Table.ReplaceValueแทนค่าที่ตรงกับค่าเดิมด้วยค่าใหม่ในคอลัมน์ที่เลือก ทั้งแบบแทนทั้งช่องและแบบแทนบางส่วนของข้อความM · ล้างและแปลงค่าพื้นฐานTable.FillDownเติมช่อง null ด้วยค่าล่าสุดที่อยู่ด้านบนในคอลัมน์เดียวกัน ใช้กับรายงานที่พิมพ์หัวกลุ่มไว้แถวแรกของกลุ่มเท่านั้นM · ล้างและแปลงค่าพื้นฐานTable.TransformColumnsแปลงค่าทุกช่องในคอลัมน์ที่เลือกด้วยฟังก์ชัน เช่น ตัดช่องว่าง เปลี่ยนเป็นตัวพิมพ์ใหญ่ โดยไม่ต้องเพิ่มคอลัมน์ใหม่M · ล้างและแปลงค่ากลาง

เพิ่มคอลัมน์

Table.AddColumnเพิ่มคอลัมน์ใหม่ที่คำนวณจากคอลัมน์อื่นในแถวเดียวกัน (Custom Column)M · เพิ่มคอลัมน์พื้นฐานTable.AddIndexColumnเพิ่มคอลัมน์เลขลำดับ เริ่มจากค่าที่กำหนดและเพิ่มทีละขั้นตามลำดับแถวปัจจุบันM · เพิ่มคอลัมน์พื้นฐาน

ทำที่ไหนดี: Power Query, DAX หรือ Excel

งานเดียวกันทำได้หลายที่ เลือกตามว่าผลต้องเปลี่ยนเมื่อไร

MPower Query

ล้างและจัดรูปข้อมูลก่อนโหลด ทำครั้งเดียวต่อการ Refresh เช่น ตัดหัวรายงาน แทนค่า ลบแถวซ้ำ เปลี่ยนชนิดข้อมูล

DAXDAX ใน Power BI

ตัวเลขที่ต้องเปลี่ยนตาม Slicer และตัวกรองของรายงาน เช่น ยอดขาย สัดส่วน เทียบปีก่อน

XLสูตร Excel

งานคำนวณในชีตที่ต้องเห็นทีละช่อง หรือแก้ไขด้วยมือได้ทันที