แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel

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

ดังนั้นผมจึงคิดว่า ทำไมเราไม่เขียนฟังก์ชันที่ตอบโจทย์ขึ้นมาเองซะเลยล่ะ! จะได้แก้ Pain Point และให้การใช้งาน Power Query ง่ายขึ้นด้วย

ในบทความนี้ผมก็จะมาแนะนำให้รู้จักว่าผมเขียนฟังก์ชันอะไรเจ๋งๆ ออกมาใน Package ThepExcel-Mfx บ้าง และความสามารถของมันคืออะไร? ซึ่งจะใช้ได้ทั้งกับ Excel และ Power BI เลยครับ สะดวกมากๆ ยกตัวอย่างสิ่งที่ผมทำไว้ เช่น

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

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

วิธีโหลด Package ThepExcel-Mfx

แต่ก่อนจะใช้ฟังก์ชันที่ผมเขียนได้ ก็ต้องมีฟังก์ชันที่ผมเขียนก่อนนะครับ

ใครสนใจไฟล์ Package ThepExcel-Mfx ที่ผมทำขึ้นมานี้สามารถโหลดไฟล์ได้ครับ
มีทั้งแบบฟรี และแบบ Premium (ใครที่อยากได้ฟังก์ชันเจ๋งๆ เยอะกว่า หรือ อยากสนับสนุนผมก็ซื้อแบบ Premium ได้ครับ)
ซึ่งทั้ง 2 แบบผมจะมีการเพิ่มฟังก์ชันให้เรื่อยๆ ตาม Feedback ของคนใช้งานด้วยครับ

• Version ฟรี สามารถโหลดไฟล์ได้ที่นี่ / หรือดู code ใน GitHub ที่นี่
• Version Premium สั่งซื้อได้ที่นี่ (ใช้ได้ตลอดชีวิต ในราคา 590 บาท )
  (หลังจากซื้อและได้รับการตรวจสอบแล้ว ผมจะ Add คุณเข้า Google Group ที่จะมีสิทธิ์โหลดไฟล์แบบ Premium ได้ครับ)

1. พอมีไฟล์ของผมแล้วทำดังนี้ ให้ Copy Query Folder ที่ชื่อ ThepExcel-Mfx
2. จากนั้นเอาไป Paste ใส่ในไฟล์ Excel หรือ Power BI ของตัวเองได้เลย

เพียงเท่านี้คุณก็จะสามารถใช้ฟังก์ชันพิเศษที่ผมเขียนเอาไว้ได้แล้วครับ

แนะนำฟังก์ชันใน Package ThepExcel-Mfx

ให้ตารางข้างบนเป็น Query ที่ชื่อว่า MyTable ซึ่งจะใช้อ้างอิงกับหลายๆ ตัวอย่างนะครับ

กลุ่มที่อยู่ใน Package Free (ใน Premium ก็มี)

ThepDatefromText : เปลี่ยนข้อความ ให้เป็นวันที่ แถมเลื่อนปีได้

=ThepDatefromText(DateText as text,DateFormat,optional offsetYear as number)as date

ThepExtractNumber : ดึงเฉพาะตัวเลขจากข้อความ เลือกได้ว่าจะเอาจุดกับ space หรือไม่

=ThepExtractNumber(OriginalText as text,optional Keepdot as logical, optional Keepspace as logical) as text

ThepGetColumnName : ดึงเอาชื่อคอลัมน์จากลำดับตัวเลข

=ThepGetColumnName(TableName as table,ColNumber as number) as text

ThepTrim : Trim แบบเดียวกับ Excel ที่เราคุ้นเคย แต่เลือกอักขระที่จะ Trim ได้ด้วย

=ThepTrim(OriginalText as text,optional TrimChar as text) as text

ThepGetMultipleListItem : ดึงข้อมูลจาก List หลายๆ ไอเท็มพร้อมกันได้

=ThepGetMultipleListItem(OriginalList as list,PosIndex as list) as list

โดยระบุ PosIndex เป็น List ที่เก็บ index ของ item ที่ต้องการไว้ (index ของ list เริ่มต้นที่เลข 0)

ThepRenameColumn : เปลี่ยนชื่อคอลัมน์โดยระบุตำแหน่ง (ระบุเป็น list ได้ )

=ThepRenameColumn(TableName as table,ColNumber as any,NewName as any) as table

ThepGenDateTableFromDate : สร้างตารางจากวันที่เริ่มต้นและสิ้นสุดที่กำหนด

=ThepGenDateTableFromDate(StartDate as date,EndDate as date) as table

สามารถกด Invoke ฟังก์ชันแล้วระบุวันที่จากปฏิทินได้เลย

ThepGenDateTableFromText : สร้างตารางจากข้อความวันที่เริ่มต้นและสิ้นสุดที่กำหนด

=ThepGenDateTableFromText(StartDateText as text,EndDateText as text) as table

คล้ายๆ กับฟังก์ชันก่อนหน้า แต่คราวนี้ ระบุ Date ให้เป็น text รูปแบบ yyyymmdd ได้เลย

ThepReplaceAllError : เปลี่ยน Error ทั้งตารางให้กลายเป็นตัวที่ต้องการ

=ThepReplaceAllError(TableName as table,optional ReplaceWith as text) as table

ThepRegExExtract : ดึงข้อความที่ตรงกับ RegEx Pattern ที่ระบุออกมา

ThepRegExMatchCount : นับได้ว่าเจอผลลัพธ์ตาม Pattern กี่ชุด (ไม่สนใจ capturing Group)

ThepRegExReplace : แทนที่ข้อความที่ตรงกับ Pattern ที่ระบุ

วิธีใช้ Regular Expression (RegEx) ใน Power Query

กลุ่มที่อยู่ใน Package Version Premium เท่านั้น

ThepReplaceTextList : แทนค่าในข้อความโดยใส่ค่าทีเดียวหลายๆ ตัวโดยใช้ List ได้

=ThepReplaceTextList(OriginalText as text,OldTextList as list,NewTextList as list) as text 

ThepFlattenHeader : เปลี่ยนหัวตารางหลายชั้นให้กลายเป็นชั้นเดียว แถมใส่ตัวคั่นได้

=ThepFlattenHeader(TableName as table,NumRowHeader as number,optional Seperator as text, optional reverse as logical) as table

ThepGetColumn : ดึงเอาคอลัมน์จากลำดับตัวเลขเริ่มและสิ้นสุด

=ThepGetColumn(TableName as table,StartColumnNum as number,optional EndColumnNum as number) as table

ThepMoveColumn : ย้ายคอลัมน์แบบไม่อ้างอิงชื่อคอลัมน์อื่นที่ไม่เกี่ยวข้อง

=ThepMoveColumn(TableName as table,ColumnName as any,optional MoveFirst as logical) as table

ThepExpandAllColumn : แตกคอลัมน์ออกมาจากทุกตารางแบบไม่ฝังชื่อคอลัมน์ กำหนด prefix ได้

=ThepExpandAllColumn(TableName as table,optional prefix as text) as table

ตารางที่จะ Expand ออกมาจะต้องอยู่ที่คอลัมน์ที่ชื่อว่า Data เท่านั้น เช่น มีข้อมูลก่อนจะ Expand ดังนี้

เราสามารถเรียกฟังก์ชันมา Expand คอลัมน์ทั้งหมดได้ (โดยมันจะ Scan คอลัมน์ทั้งหมดใหม่เสมอ) ดังนี้

หรือเราจะระบุ Prefix ของคอลัมน์ที่ Expand ออกมาก็ได้ เช่น

ThepMergeTableOffsetRow : ดึงข้อมูล 2 ตารางมาประกบกัน สามารถเหลื่อมแถวได้

=ThepMergeTableOffsetRow(TableName1 as table,TableName2 as table, RowOffset as number) as table

ThepSearchColumn : เลือกเอาเฉพาะคอลัมน์ที่มีคำที่กำหนด แถมปะเพิ่มคอลัมน์ที่ต้องการได้

=ThepSearchColumn(TableName as table,FindColText as text,optional MatchType as text,optional ColtoKeep as list) as table

ตัว parameter MatchType สามารถระบุ option เป็นข้อความได้ดังนี้

• contain : หาที่มีคำนั้นอยู่ข้างใน (ค่า Default กรณีไม่ระบุ MatchType)
• start: หาที่มีคำนั้นนำหน้า
• end : หาที่มีคำนั้นลงท้าย

ThepRegExExtractAll : ดึงผลลัพธ์ทุกตัวทุก SubGroup ออกมาเป็น List

วิธีใช้ Regular Expression (RegEx) ใน Power Query

เทคนิคที่ทำให้ใช้สูตรของผมได้ง่ายขึ้น (ไม่จำเป็นต้องทำก็ได้)

ปกติแล้ว การ Copy Paste Query ข้ามไฟล์ ถ้า Code ใน Query นั้นๆ ดันไม่ได้เริ่มด้วย let…in… ตัวโปรแกรมจะใส่ Code let…in…เพิ่มมาให้เองอัตโนมัติ

ซึ่งมีข้อเสียคือ เวลาเรียกใช้ฟังก์ชันใน Formula Bar มันจะมองไม่เห็น ToolTips เช่นในรูป (แต่ถ้ากด Invoke Function จะเห็นปกตินะ)

ซึ่งถ้าอยากให้ตอนเขียนสูตรใน Formula Bar เห็น Tooltips ตามปกติตามรูปข้างล่าง ก็มีอยู่ 3 way ที่จะแนะนำ คือ

• Way 1: ให้เอาไฟล์ของผม copy paste เป็นไฟล์ตั้งต้นของคุณเวลาจะเขียน Query ไปเลย (โดยไม่ต้องใช้การ copy query)
• Way 2 : เปิด Advanced Editor แล้ว copy M code จากไฟล์ผมไป Paste ใน Blank Query ของคุณตรงๆ
• Way 3 : ใช้การ Copy Query นี่แหละ แต่ให้เข้า Advanced Editor แล้วเอาคำว่า let…in… source ที่ มาครอบอยู่ออกไปซะครับ

เดี๋ยวขออธิบาย Way3 เพิ่มเติม… เช่น สูตรในฟังก์ชัน ThepTrim หลังจาก Copy Query มาจะเป็นแบบนี้ (มีสีแดงๆ เพิ่มมาโดยอัตโนมัติ ให้คุณเอามันออกไปซะ)

ดู code แบบนี้ได้ใน View -> Advanced Editor นะครับ

let
     Source = (OriginalText as text,optional TrimChar as text) as text=>
 let
     TrimCharFix = if TrimChar is null then " " else TrimChar,
     SplitStep = Text.Split(OriginalText,TrimCharFix),
     RemoveBlankStep = List.Select(SplitStep, each _ <> ""),
     CombineStep = Text.Combine(RemoveBlankStep,TrimCharFix)
 in
 CombineStep
 in
     Source

พอเอาสีแดงๆ ออกไปจะเหลือเป็นแบบนี้ ซึ่งจะใช้สูตรได้ดีขึ้น

(OriginalText as text,optional TrimChar as text) as text=>
 let
     TrimCharFix = if TrimChar is null then " " else TrimChar,
     SplitStep = Text.Split(OriginalText,TrimCharFix),
     RemoveBlankStep = List.Select(SplitStep, each _ <> ""),
     CombineStep = Text.Combine(RemoveBlankStep,TrimCharFix)
 in
 CombineStep