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

ตามปกติแล้ว ณ ตอนนี้ Power Query นั้นยังไม่ Support เรื่องการใช้ Regular Expression (RegEx) ทั้งใน Excel และ Power BI ก็ยังใช้ไม่ได้ แต่เจ้า Regular Expression นี้มีประโยชน์มากๆ จริงๆ จนหาสิ่งอื่นทดแทนไม่ได้เลย ดังนั้นผมก็เลยพยายามหาวิธีพลิกแพลงเพื่อทำมันให้ได้โดยเขียนฟังก์ชัน M Code ขึ้นมาใช้เองใน Power Query ซึ่งมีเทคนิคลับช่วยให้มันทำได้อยู่ครับ มาดูกันว่าทำได้ยังไง?

เผื่อใครที่ยังไม่รู้จัก Regular Expression

Regular Expression (RegEx) มีความสามารถจะช่วยให้เราสามารถค้นหา/แทนที่ข้อความที่ตรงกับ Pattern ที่เราระบุได้ครับ ซึ่งเรื่องของเรื่องคือ Pattern มันสามารถรองรับเคสที่ซับซ้อนได้มากๆ และดีกว่าการค้นหาปกติของ Excel ที่รองรับได้แค่ข้อความปกติ หรืออย่างมากก็มีแค่ Wildcard *กับ? เท่านั้น (ซึ่งมัน Simple มากเกินไป) แปลว่าการค้นหาด้วย RegEx จะทำให้เราค้นหาและดึงข้อความที่เราต้องการได้ดั่งใจ

ซึ่งตอนนี้ Excel กับ Power Query ยังไม่มี RegEx ในตัว แต่ในเครื่องมืออื่น เช่น Google Sheets, Python, R, Java เค้าก็มีกันหมดแล้ว ทำให้นี่เป็นจุดอ่อนสำคัญของ Excel และ Power Query/Power BI เลย

แม้ว่าใน Excel VBA ก็สามารถ add Library เกี่ยวกับ Regular Expression มาใช้ได้ แต่ในบางกรณีเราอาจใช้ VBA ไม่ได้ ดังนั้นผมก็เลยพยายามเขียนฟังก์ชันขึ้นมาใน Power Query ครับ ซึ่งผลลัพธ์ที่ได้ออกมาน่าพอใจสำหรับผมเองมากๆ

และในระหว่างที่รอให้เค้าพัฒนา RegEX อย่างเป็นทางการ ก็สามารถไปช่วยกันโหวตได้ที่ https://ideas.powerbi.com/ideas/idea/?ideaid=1395c308-7f92-4321-8e35-6cd04d7108bf

หัดใช้ Regular Expression

ผมเคยเขียนบทความอธิบายการใช้งาน RegEx เบื้องต้นไว้นิดหน่อยที่นี่

แนวทางของการใช้ Regular Expression ใน Power Query

การจะใช้ Regular Expression ใน Power Query ได้นั้น มีอยู่ 2 แนวทางใหญ่ๆ นั่นคือ

1. ใช้ Python มาช่วย (ทำได้แค่ใน Power BI เท่านั้น) ถ้าใครสนใจอ่านเพิ่มเติมได้ที่นี่
2. ใช้ JavaScript มาช่วย (ใช้ได้ทั้ง Excel และ Power BI)

ซึ่งแนวทางที่ผมจะใช้ในบทความนี้คือ การใช้ JavaScript มาช่วย ซึ่งใช้ได้ทั้ง Excel และ Power BI และไม่ต้องลงโปรแกรม Python แถมไม่ต้องต่อ Internet ด้วยซ้ำครับ และที่สำคัญ ความสามารถของมันมากกว่าที่ใช้ใน Google Sheets ด้วยนะครับ (อันนี้มันดึงได้แค่อันแรกที่เจอ แต่ของผมดึงได้ทุกอัน)

แนวทางการใช้ JavaScript มาช่วยทำ Regular Expression ใน Power Query

หลักการคือเราสามารถสร้าง Script ข้อความที่เป็นภาษา JavaScript แล้วใส่เข้าไปในคำสั่ง Web.Page() ของ Power Query ได้ ซึ่งจะได้ผลลัพธ์กลับมาเป็นตารางที่เก็บ Html ของเว็บนั้นๆ (แนวคิดนี้เอามาจากเว็บ biaccountant บทความนี้)

ซึ่งใน JavaScript นั้นเอง เราจะสั่งใน Run คำสั่งที่รองรับ Regular Expression ใน JavaScript เช่น String.Match, String.Replace, String.Search, RegExp.exec ได้ (ความเศร้าคือมันใช้ String.MatchAll ไม่ได้ เพราะ Internet Explorer มันไม่รองรับ แต่ผมมีวิธีพลิกแพลงได้ อยู่ตอนจบของบทความ)

ซึ่งสุดท้ายเราสามารถใช้คำสั่ง Document.Write ของ JavaScript เพื่อให้พ่นผลลัพธ์สุดท้ายที่เราต้องการออกมาในหน้า HTML ได้ แล้ว Power Query ก็เอาค่านั้นกลับมาใช้ด้วย Web.Page() นั่นเอง

พอเรารู้แบบนี้แล้ว เราก็สามารถสร้างฟังก์ชันใน Power Query เพื่อให้รับค่าข้อความ และ Pattern ของ RegEx ที่ต้องการ แล้วไปทำงานตาม Concept ที่ผมเล่าให้ฟังได้ ตัวอย่างดังนี้

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

(OriginalText as text,RegExPattern as text, optional RegExMode as text) as list

RegExMode

• ไม่ระบุ RegExMode (หรือ เป็นค่าว่าง) สามารถใช้ค้นหาข้อความแรกที่ตรง Pattern (เอา Capturing Group ด้วย)
• RegExMode เป็น “i” สามารถใช้ค้นหาข้อความแบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่
• RegExMode เป็น “g” สามารถใช้ค้นหาข้อความทุกตัวพร้อมกันได้ (ไม่เอา Capturing Group)
• RegExMode เป็น “gi” สามารถใช้ค้นหาข้อความทุกตัวพร้อมกันได้ (ไม่เอา Capturing Group) และ ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่

Function M Code ( Copy ใส่ Advanced Editor ได้เลย)

(OriginalText as text,RegExPattern as text, optional RegExMode as text) as any=>
 let 
    // MCode created by ThepExcel.com
    MyTextFix=Text.Replace(OriginalText,"\","\\"),
    MyTextFix2=Text.Replace(Text.Replace(MyTextFix,"#(lf)","\n"),"#(cr)","\r"),
    //แปลงให้ข้อความที่มี \ หรือการขึ้นบรรทัดใหม่ ไปใช้ใน Javascript ได้ถูกต้อง
    re= if RegExMode is null then "/"&RegExPattern&"/" else "/"&RegExPattern&"/"&RegExMode,
    //สามารถใส่ RegExMode เป็น "g", "i" หรือ "gi" ได้
    //ถ้าไม่ใส่ Mode อะไรก็จะถือเป็นการ Single Match และ case sensitive
    getWeb=Web.Page(
    "<meta http-equiv='X-UA-Compatible' content='IE=edge'>
    <script>
    var MyText='"&MyTextFix2&"';
    var MyMatches=MyText.match("&re&");
    var MyResult=JSON.stringify(MyMatches);
    document.write(MyResult);
    </script>"),
    // ใช้ JavaScript ดึงผลลัพธ์ออกมาเป็น JSON String
    getWeb2=getWeb[Data]{0}[Children]{0}[Children]{1}[Text],
    finalResult=try Json.Document(Text.Combine(getWeb2)) otherwise null
    // ให้ PQ อ่านผลลัพธ์จาก JSON string ให้กลายเป็น List
in  
    finalResult

ซึ่งเวลาใช้งานสามารถเรียกใช้งานได้ผลลัพธ์ออกมาเป็น List ซึ่งเอาไว้รองรับกรณีใช้ Capturing Group ได้ด้วย

ตัวอย่างการใช้งาน

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

ในทำนองเดียวกันเราก็สามารถประยุกต์สร้างอีกฟังก์ชันขึ้นมาเพื่อนับได้ว่าเจอผลลัพธ์กี่ตัว ดังนี้

(OriginalText as text,RegExPattern as text, optional RegExMode as text) as number

RegExMode

• RegExMode เป็นค่าว่าง จะหาแบบสนใจพิมพ์เล็กพิมพ์ใหญ่
• RegExMode เป็น “i” สามารถใช้ค้นหาข้อความแบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่

Function M Code

(OriginalText as text,RegExPattern as text, optional RegExMode as text) as number=>
 let 
    // MCode created by ThepExcel.com
    MyTextFix=Text.Replace(OriginalText,"\","\\"),
    MyTextFix2=Text.Replace(Text.Replace(MyTextFix,"#(lf)","\n"),"#(cr)","\r"),
    //แปลงให้ข้อความที่มี \ หรือการขึ้นบรรทัดใหม่ ไปใช้ใน Javascript ได้ถูกต้อง
    re= if RegExMode is null then "/"&RegExPattern&"/g" else "/"&RegExPattern&"/g"&RegExMode,
    //สามารถใส่ RegExMode เป็น "i" ได้
    //ถ้าไม่ใส่ Mode อะไรก็จะถือเป็นการ Single Match และ case sensitive
    getWeb=Web.Page(
    "<meta http-equiv='X-UA-Compatible' content='IE=edge'>
    <script>
    var MyText='"&MyTextFix2&"';
    var MyMatches=MyText.match("&re&");
    document.write(MyMatches.length);
    </script>"),
    getWeb2=getWeb[Data]{0}[Children]{0}[Children]{1}[Text],
    finalResult=try Number.From(Text.Combine(getWeb2)) otherwise 0
    // ให้ PQ อ่านผลลัพธ์จาก JSON string ให้กลายเป็น List
in  
    finalResult

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

(OriginalText as text,RegExPattern as text,NewText as text,optional RegExMode as text) as text

RegExMode

• ไม่ระบุ RegExMode (หรือ เป็นค่าว่าง) สามารถใช้แทนที่ข้อความแรกที่ตรง Pattern แบบสนใจพิมพ์เล็กพิมพ์ใหญ่
• RegExMode เป็น “i” สามารถใช้แทนที่ข้อความแทนที่ข้อความแรกที่ตรง Pattern แบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่
• RegExMode เป็น “g” สามารถใช้แทนที่ข้อความทุกตัวที่ตรง Pattern แบบสนใจพิมพ์เล็กพิมพ์ใหญ่
• RegExMode เป็น “gi” สามารถใช้แทนที่ข้อความทุกตัวที่ตรง Pattern แบบไม่สนใจพิมพ์เล็กพิมพ์ใหญ่

(OriginalText as text,RegExPattern as text,NewText as text,optional RegExMode as text) as text=>
 let 
    // MCode created by ThepExcel.com
    MyTextFix=Text.Replace(OriginalText,"\","\\"),
    MyTextFix2=Text.Replace(Text.Replace(MyTextFix,"#(lf)","\n"),"#(cr)","\r"),
    NewTextFix=Text.Replace(NewText,"\","\\"),
    re= if RegExMode is null then "/"&RegExPattern&"/" else "/"&RegExPattern&"/"&RegExMode,
    //สามารถใส่ RegExMode เป็น "g", "i" หรือ "gi" ได้
    //ถ้าไม่ใส่ Mode อะไรก็จะถือเป็นการ Single Match และ case sensitive
    getWeb=Web.Page(
    "<meta http-equiv='X-UA-Compatible' content='IE=edge'>
    <script>
    var MyText='"&MyTextFix2&"';
    var MyNewText='"&NewTextFix&"';
    var MyMatches=MyText.replace("&re&",MyNewText);
    var MyResult=JSON.stringify(MyMatches);
    document.write(MyResult);
    </script>"),
    getWeb2=getWeb[Data]{0}[Children]{0}[Children]{1}[Text],
    finalResult=Json.Document(Text.Combine(getWeb2))
in  
    finalResult

ตัวอย่างการใช้งาน

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

อย่างไรก็ตาม ตัวที่ท้าทายที่สุดก็คือ การ Extract ข้อมูลทุกตัวที่ตรงกับ Pattern และเอา Capturing Group ด้วย ซึ่งเราใช้ฟังก์ชัน String.MatchAll ของ JavaScript ตรงๆไม่ได้ เพราะ Internet Explorer มันไม่ Support

แนวทางนึงที่ทำได้คือ “ใช้การวน Loop มาช่วย” ซึ่งเดิมทีผมลองใช้ List.Generate ของ Power Query ในการวน Loop ซึ่งแม้จะได้ผลลัพธ์ออกมาถูกต้อง แต่ Performance มันช้ามาก ทำให้สุดท้ายผมเปลี่ยนไปใช้อีกวิธีซึ่ง Performance ออกมาดีมากๆ เพราะไป Loop ใน JavaScript จนจบเลยแล้วค่อยส่งผลลัพธ์ออกมา

ตัวอย่างการใช้งาน

ใครสนใจอยากได้ฟังก์ชัน ThepRegExExtractAll ที่เต็มเปี่ยมไปด้วยความสามารถที่สุดยอดแบบนี้ (หรืออยากช่วยสนับสนุนผม) สามารถสั่งซื้อ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel แบบ Premium ได้นะครับ

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