นิพจน์ทั่วไป (RegExp) ใน Power Query

หากคุณคุ้นเคยกับนิพจน์ทั่วไปอย่างน้อยก็ไม่จำเป็นต้องโฆษณานิพจน์ทั่วไป หากคุณไม่ค่อยอยู่ในหัวเรื่อง นิพจน์ทั่วไป (นิพจน์ทั่วไป = RegExp = “regexps” = “ปกติ”) เป็นภาษาที่ใช้อักขระพิเศษและกฎ สตริงย่อยที่จำเป็นจะถูกค้นหาในข้อความ พวกมันจะถูกแยกออก หรือแทนที่ด้วยข้อความอื่น นี่เป็นเครื่องมือที่ทรงพลังและสวยงามมาก ซึ่งเป็นลำดับความสำคัญที่เหนือกว่าวิธีอื่นๆ ในการทำงานกับข้อความ

ฉันได้อธิบายอย่างละเอียดแล้ว และด้วยตัวอย่างมากมายจากชีวิตที่คุณสามารถเพิ่มการสนับสนุนนิพจน์ทั่วไปใน Excel โดยใช้มาโครง่ายๆ หากคุณยังไม่ได้อ่านบทความนี้ เราขอแนะนำให้คุณอ่านก่อนดำเนินการต่อ คุณจะค้นพบสิ่งใหม่มากมายฉันรับประกัน🙂

อย่างไรก็ตาม คำถามยังคงเปิดอยู่ – จะเพิ่มความสามารถในการใช้นิพจน์ทั่วไปใน Power Query ได้อย่างไร แน่นอนว่า Power Query ทำงานได้ดีในตัวเองและสามารถทำอะไรได้มากมายกับข้อความ (การตัด การติด การทำความสะอาด ฯลฯ) แต่ถ้าคุณสามารถข้ามมันไปได้ด้วยพลังของนิพจน์ทั่วไป มันจะเป็นเพียงแค่ระเบิด

ขออภัย ไม่มีฟังก์ชันในตัวสำหรับการทำงานกับ RegExps ใน Power Query และความช่วยเหลืออย่างเป็นทางการของ Microsoft และฝ่ายสนับสนุนด้านเทคนิคจะตอบคำถามนี้ในทางลบ อย่างไรก็ตาม มีวิธีแก้ปัญหาข้อจำกัดนี้ 🙂

สาระสำคัญของวิธีการ

แนวคิดหลักนั้นง่ายต่อการทำให้อับอายขายหน้า

ในรายการความสามารถในตัวของ Power Query มีฟังก์ชัน หน้าเว็บ. คำอธิบายของฟังก์ชันนี้ในไซต์ช่วยเหลืออย่างเป็นทางการของ Microsoft นั้นกระชับอย่างยิ่ง:

แปลแล้วจะเป็น: “ส่งคืนเนื้อหาของเอกสาร HTML ที่แยกย่อยออกเป็นโครงสร้างส่วนประกอบ รวมถึงการเป็นตัวแทนของเอกสารที่สมบูรณ์และเนื้อหาหลังจากแท็กถูกลบออกแล้ว” คำอธิบายพอดูได้ตรงไปตรงมา

โดยปกติ ฟังก์ชันนี้จะใช้เมื่อนำเข้าข้อมูลจากเว็บและจะถูกแทนที่โดยอัตโนมัติ เช่น เมื่อเราเลือกบนแท็บ ข้อมูล คำสั่ง จากอินเทอร์เน็ต (ข้อมูล — จากเว็บ). เราให้ฟังก์ชันหน้าเว็บเป็นอาร์กิวเมนต์ และส่งคืนเนื้อหาให้เราในรูปแบบของตาราง โดยก่อนหน้านี้ได้ล้างแท็กทั้งหมดแล้ว

สิ่งที่ไม่ได้ช่วยคือนอกเหนือจากภาษามาร์กอัป HTML ฟังก์ชัน หน้าเว็บ รองรับสคริปต์จาวาสคริปต์ซึ่งขณะนี้มีอยู่ทั่วไปในเว็บไซต์บนอินเทอร์เน็ต และในทางกลับกัน JavaScript สามารถทำงานกับนิพจน์ทั่วไปได้เสมอและมีฟังก์ชันในตัวสำหรับ RegExps! ดังนั้นเพื่อใช้นิพจน์ทั่วไปใน Power Query เราจะต้องป้อนฟังก์ชัน Web.Page เป็นอาร์กิวเมนต์ของโปรแกรม JavaScript ขนาดเล็กที่จะทำงานทั้งหมดสำหรับ Power Query

ดูเหมือนว่าใน JavaScript ล้วนๆ

มีบทช่วยสอนโดยละเอียดมากมายเกี่ยวกับการทำงานกับนิพจน์ทั่วไปใน JavaScript บนอินเทอร์เน็ต (เช่น หนึ่ง สอง)

โดยย่อและเรียบง่าย โค้ด JavaScript จะมีลักษณะดังนี้:

ที่นี่:

• var str = 'จ่ายบิล 123 และ 789 สำหรับไส้กรอก'; – สร้างตัวแปร Str และกำหนดข้อความต้นฉบับที่เราจะวิเคราะห์
• รูปแบบวาร์ = /d+/gi; – สร้างนิพจน์ทั่วไปและใส่ลงในตัวแปร Belt hold .

  นิพจน์เริ่มต้นด้วยเครื่องหมายทับ (/)

  นิพจน์ในที่นี้ เช่น is d+ ย่อมาจากลำดับของตัวเลขใดๆ

  ผ่านเศษส่วนหลังนิพจน์ มีพารามิเตอร์การค้นหาเพิ่มเติม (ตัวแก้ไข) – สามารถระบุในลำดับใดก็ได้:
  

  • g – หมายถึงการค้นหาทั่วโลก กล่าวคือ หลังจากพบการจับคู่แล้ว คุณไม่ควรหยุด แต่ให้ค้นหาต่อไปจนกว่าจะสิ้นสุดข้อความ หากไม่ได้ตั้งค่าตัวปรับแต่งนี้ สคริปต์ของเราจะคืนค่าเฉพาะการจับคู่แรก (123)
  • i – ค้นหาโดยไม่คำนึงถึงกรณีของตัวอักษร
  • m – การค้นหาแบบหลายบรรทัด (ใช้เมื่อข้อความต้นฉบับแบ่งออกเป็นหลายบรรทัด)
• var result = str.match(pattern).join(';'); – ทำการค้นหาในข้อความต้นฉบับ (Str) โดยนิพจน์ทั่วไปที่กำหนด (Belt hold ) และใส่ผลลัพธ์ลงในตัวแปร ผลเชื่อมด้วยเครื่องหมายอัฒภาคโดยใช้คำสั่ง ร่วม
• เอกสาร.เขียน(ผลลัพธ์); – แสดงเนื้อหาของตัวแปรผลลัพธ์

นอกจากนี้ โปรดทราบด้วยว่าสตริงข้อความ (ไม่รวมนิพจน์ทั่วไป) ใน JavaScript อยู่ในเครื่องหมายอะพอสทรอฟี ไม่ใช่เครื่องหมายคำพูดเหมือนใน Power Query หรือ VBA

ที่ผลลัพธ์ สคริปต์นี้จะให้ผลลัพธ์เป็นตัวเลขทั้งหมดที่พบในข้อความต้นฉบับ:

123, 789

หลักสูตรระยะสั้นของ JavaScript สิ้นสุดลงแล้ว ขอบคุณทุกท่าน หวังว่าคุณจะเข้าใจตรรกะ🙂

ยังคงต้องถ่ายโอนโครงสร้างนี้ไปยัง Power Query

ค้นหาและแยกฟังก์ชันข้อความด้วยนิพจน์ทั่วไปใน Power Query

เราทำสิ่งต่อไปนี้:

1. เปิด Excel และสร้าง Power Query ใหม่ที่ว่างเปล่าในแท็บ ข้อมูล – รับข้อมูล / สร้างคำขอ – จากแหล่งอื่น – คำขอว่างเปล่า (ข้อมูล — รับข้อมูล / คิวรีใหม่ — จากแหล่งอื่น — คิวรีว่าง). หากคุณมี Excel 2010-2013 เวอร์ชันเก่าและ Power Query คุณไม่มีในตัว แต่ได้รับการติดตั้งเป็น Add-in แยกต่างหาก ทั้งหมดนี้ก็จะอยู่บนแท็บ Power Queryและไม่ ข้อมูล.

2. ในหน้าต่างว่างของตัวแก้ไขแบบสอบถามที่เปิดขึ้น ในแผงด้านขวา ให้ป้อนชื่อฟังก์ชันในอนาคตของเราทันที (เช่น fxRegExpExtract)

3. ไปที่แท็บกัน ดู – ตัวแก้ไขขั้นสูง (ดู — ตัวแก้ไขขั้นสูง)เราลบ M-code ทั้งหมดของคำขอที่ว่างเปล่าและวางรหัสของ superfunction ของเราที่นั่น:

ระวังมือของคุณ:

ในบรรทัดแรก เราบอกว่าฟังก์ชันของเราจะมีอาร์กิวเมนต์ข้อความสามอาร์กิวเมนต์: TXT – ข้อความต้นฉบับที่กำลังวิเคราะห์ นิพจน์ทั่วไป – รูปแบบนิพจน์ทั่วไป เดลิม — ตัวคั่นสำหรับแสดงผล

ต่อไปเราจะเรียกฟังก์ชัน หน้าเว็บสร้างโค้ด JavaScript ที่อธิบายไว้ข้างต้นในอาร์กิวเมนต์ เราวางและแทนที่อาร์กิวเมนต์ตัวแปรของเราลงในโค้ด

ส่วน:

[ข้อมูล]{0}[เด็ก]{0}[เด็ก]{1}[ข้อความ]{0}

… จำเป็นต้อง “ล้ม” ลงในตารางด้วยผลลัพธ์ที่เราต้องการ ประเด็นคือฟังก์ชัน หน้าเว็บ เป็นผลให้สร้างตารางที่ซ้อนกันหลายตารางที่ทำซ้ำโครงสร้างของหน้าเว็บ หากไม่มี M-code นี้ ฟังก์ชันของเราจะแสดงผลดังนี้:

…และเราจะต้องคลิกคำนั้นหลายครั้ง ตาราง, ต่อเนื่อง "ล้ม" ลงในตารางที่ซ้อนกันย่อยในคอลัมน์ เด็ก:

แทนที่จะเป็นใบเสนอราคาทั้งหมด เราจะระบุในโค้ดของฟังก์ชันของเราทันทีซึ่งตารางและคอลัมน์ที่ซ้อนกัน (ข้อความ) พวกเราต้องการ.

อันที่จริงที่นี่เป็นความลับทั้งหมด มันยังคงกดปุ่ม เสร็จสิ้น ในหน้าต่าง บรรณาธิการขั้นสูงที่เราแทรกรหัสของเราและคุณสามารถดำเนินการที่อร่อยที่สุดได้ - ลองใช้ฟังก์ชันของเราในที่ทำงาน

ต่อไปนี้คือตัวอย่างเมล็ดพันธุ์สองสามตัวอย่าง

ตัวอย่างที่ 1 การดึงหมายเลขบัญชีและวันที่จากคำอธิบายการชำระเงิน

เรามีใบแจ้งยอดจากธนาคารพร้อมคำอธิบาย (วัตถุประสงค์) ของการชำระเงิน โดยคุณจะต้องดึงตัวเลขและวันที่ของใบแจ้งหนี้ที่ชำระแล้วออกเป็นคอลัมน์แยกกัน:

เราโหลดตารางลงใน Power Query ด้วยวิธีมาตรฐานผ่าน ข้อมูล – จากตาราง/ช่วง (ข้อมูล — จาก Tสามารถ / Rนางฟ้า).

จากนั้นเราเพิ่มคอลัมน์จากการคำนวณด้วยฟังก์ชันของเราผ่าน เพิ่มคอลัมน์ – เรียก Custom Function (เพิ่มคอลัมน์ — เรียกใช้ฟังก์ชันแบบกำหนดเอง) และป้อนอาร์กิวเมนต์:

เป็นนิพจน์ทั่วไป (อาร์กิวเมนต์ นิพจน์ทั่วไป) เทมเพลตที่เราใช้:

(ง{3,5}|ง{2}.d{2}.d{4})

… แปลเป็นภาษามนุษย์ความหมาย: 

ตัวเลขตั้งแต่ 3 ถึง 5 หลัก (หมายเลขบัญชี)

or

ชิ้นส่วนของแบบฟอร์ม “ตัวเลข 2 บิต – จุด – หมายเลข 2 บิต – จุด – หมายเลข 4 บิต”นั่นคือวันที่ในรูปแบบ DD.MM.YYYY

เป็นตัวคั่น (argument เดลิม) ป้อนอัฒภาค

หลังจากคลิกที่ OK ฟังก์ชันเวทย์มนตร์ของเราจะวิเคราะห์ข้อมูลเริ่มต้นทั้งหมดตามนิพจน์ทั่วไปของเราและสร้างคอลัมน์สำหรับเราด้วยตัวเลขและวันที่ของใบแจ้งหนี้ที่พบ:

มันยังคงคั่นด้วยเครื่องหมายอัฒภาคโดยใช้คำสั่ง หน้าแรก — แยกคอลัมน์ — โดยตัวคั่น (หน้าแรก — แยกคอลัมน์ — ตามตัวคั่น) และเราได้สิ่งที่เราต้องการ:

ความงาม!

ตัวอย่างที่ 2: แยกที่อยู่อีเมลออกจากข้อความ

สมมติว่าเรามีตารางต่อไปนี้เป็นข้อมูลเริ่มต้น:

… จากตำแหน่งที่เราต้องดึงที่อยู่อีเมลที่พบ (เพื่อความชัดเจน ฉันเน้นสีแดงในข้อความ)

ในตัวอย่างก่อนหน้านี้ เราโหลดตารางลงใน Power Query ด้วยวิธีมาตรฐานผ่าน ข้อมูล – จากตาราง/ช่วง (ข้อมูล — จาก Tสามารถ / Rนางฟ้า).

จากนั้นเราเพิ่มคอลัมน์จากการคำนวณด้วยฟังก์ชันของเราผ่าน เพิ่มคอลัมน์ – เรียก Custom Function (เพิ่มคอลัมน์ — เรียกใช้ฟังก์ชันแบบกำหนดเอง) และป้อนอาร์กิวเมนต์:

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

[w|.|-]*@w*.[w|.]*

เป็นตัวคั่น (เดลิม) คุณสามารถป้อนเครื่องหมายอัฒภาคและช่องว่าง

คลิกที่ OK และเราได้รับคอลัมน์ที่มีที่อยู่อีเมลที่ดึงมาจากข้อความต้นฉบับ "โจ๊ก":

มายากล!

PS

ดังคำกล่าวที่ว่า “ไม่มีสิ่งดีใดที่ไม่สามารถทำให้ดีขึ้นได้” Power Query นั้นยอดเยี่ยมในตัวมันเอง และเมื่อรวมกับนิพจน์ทั่วไป มันทำให้เรามีพลังและความยืดหยุ่นที่ไม่สมจริงอย่างสมบูรณ์ในการประมวลผลข้อมูลข้อความใดๆ ฉันหวังว่าสักวันหนึ่ง Microsoft จะเพิ่มการสนับสนุน RegExp ในการอัปเดต Power Query และ Power BI และการเต้นรำทั้งหมดข้างต้นด้วยแทมบูรีนจะกลายเป็นเรื่องในอดีต สำหรับตอนนี้ใช่

ฉันต้องการเพิ่มด้วยว่าสะดวกในการเล่นกับนิพจน์ทั่วไปบนเว็บไซต์ https://regexr.com/ – ในตัวแก้ไขออนไลน์ ในส่วนนั้น รูปแบบชุมชน มีฤดูกาลปกติสำเร็จรูปจำนวนมากสำหรับทุกโอกาส การทดลอง – พลังของนิพจน์ทั่วไปอยู่ที่บริการของคุณใน Power Query แล้ว!

• นิพจน์ทั่วไป (RegExp) คืออะไรและจะใช้งานอย่างไรใน Excel
• การค้นหาข้อความคลุมเครือใน Power Query
• การประกอบตารางจากไฟล์ต่างๆ โดยใช้ Power Query