การแทนที่ข้อความจำนวนมากใน Power Query ด้วยฟังก์ชัน List.Accumulate

วิธีแทนที่ข้อความอย่างรวดเร็วและเป็นกลุ่มตามรายการอ้างอิงด้วยสูตร - เราได้จัดเรียงแล้ว ตอนนี้ ลองทำใน Power Query

เกิดขึ้นบ่อย ดำเนินการ งานนี้ง่ายกว่าการอธิบายมาก ทำไม ได้ผล แต่มาลองทำทั้งสองอย่างกันดีกว่า 🙂

ดังนั้นเราจึงมีตารางไดนามิก "อัจฉริยะ" สองตารางที่สร้างขึ้นจากช่วงปกติด้วยแป้นพิมพ์ลัด Ctrl+T หรือทีม หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง):

ฉันเรียกโต๊ะแรก ข้อมูล, ตารางที่สอง – ค้นหาสถานที่ใช้ฟิลด์ ชื่อตาราง (ชื่อโต๊ะ) แถบ นวกรรมิก (ออกแบบ).

งาน: แทนที่ที่อยู่ในตาราง ข้อมูล เหตุการณ์ทั้งหมดจากคอลัมน์ การค้นหา คู่มือ ไปยังคู่ที่ถูกต้องที่สอดคล้องกันจากคอลัมน์ แทน. ข้อความที่เหลือในเซลล์ไม่ควรถูกแตะต้อง

ขั้นตอนที่ 1 โหลดไดเร็กทอรีลงใน Power Query และเปลี่ยนเป็นรายการ

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

ตารางอ้างอิงจะถูกโหลดลงในตัวแก้ไขแบบสอบถาม Power Query:

เพื่อไม่ให้รบกวนขั้นตอนที่เพิ่มโดยอัตโนมัติ แก้ไขประเภท (เปลี่ยนประเภท) ในแผงด้านขวา สามารถลบขั้นตอนที่ใช้ได้อย่างปลอดภัย เหลือเพียงขั้นตอนเท่านั้น แหล่ง (ที่มา):

ตอนนี้ เพื่อดำเนินการแปลงและแทนที่เพิ่มเติม เราจำเป็นต้องเปลี่ยนตารางนี้เป็นรายการ (รายการ)

การพูดนอกเรื่องโคลงสั้น

• ตาราง เป็นอาร์เรย์สองมิติที่ประกอบด้วยหลายแถวและคอลัมน์
• บันทึก (บันทึก) – อาร์เรย์-สตริงหนึ่งมิติ ประกอบด้วยหลายฟิลด์-องค์ประกอบที่มีชื่อ เช่น [ชื่อ = “มาช่า” เพศ = “ฉ” อายุ = 25]
  
• รายการ – อาร์เรย์คอลัมน์หนึ่งมิติ ซึ่งประกอบด้วยหลายองค์ประกอบ เช่น {1, 2, 3, 10, 42} or { "ความเชื่อความหวังความรัก" }
  

เพื่อแก้ปัญหาของเรา เราจะสนใจประเภทเป็นหลัก รายการ.

เคล็ดลับที่นี่คือ ไอเท็มในรายการใน Power Query ไม่เพียงแต่จะเป็นตัวเลขหรือข้อความธรรมดาเท่านั้น แต่ยังรวมถึงรายการหรือเรกคอร์ดอื่นๆ ด้วย มันอยู่ในรายการที่ยุ่งยาก (รายการ) ซึ่งประกอบด้วยบันทึก (บันทึก) ที่เราจำเป็นต้องเปลี่ยนไดเรกทอรีของเรา ในสัญกรณ์วากยสัมพันธ์ของ Power Query (รายการในวงเล็บเหลี่ยม แสดงรายการในวงเล็บปีกกา) ลักษณะนี้จะมีลักษณะดังนี้:

{

    [ ค้นหา = “เซนต์. ปีเตอร์สเบิร์ก”, แทนที่ = “เซนต์. ปีเตอร์สเบิร์ก” ] ,

    [ ค้นหา = “เซนต์. ปีเตอร์สเบิร์ก”, แทนที่ = “เซนต์. ปีเตอร์สเบิร์ก” ] ,

    [ ค้นหา = “ปีเตอร์” แทนที่ = “เซนต์. ปีเตอร์สเบิร์ก” ] ,

เป็นต้น

}

การแปลงดังกล่าวดำเนินการโดยใช้ฟังก์ชันพิเศษของภาษา M ที่สร้างไว้ใน Power Query – ตาราง.ToRecords. หากต้องการใช้โดยตรงในแถบสูตร ให้เพิ่มฟังก์ชันนี้ลงในโค้ดขั้นตอนที่นั่น แหล่ง.

มันคือ:

หลังจากที่:

หลังจากเพิ่มฟังก์ชัน Table.ToRecords ลักษณะที่ปรากฏของตารางของเราจะเปลี่ยนไป - มันจะกลายเป็นรายการของเรกคอร์ด เนื้อหาของแต่ละเรคคอร์ดสามารถดูได้ที่ด้านล่างของบานหน้าต่างมุมมองโดยคลิกที่พื้นหลังของเซลล์ถัดจากคำใด ๆ บันทึก (แต่ไม่ใช่ในคำเดียว!)

นอกเหนือจากข้างต้น การเพิ่มจังหวะอีกครั้งหนึ่ง – เพื่อแคช (บัฟเฟอร์) รายการที่เราสร้างขึ้น การดำเนินการนี้จะบังคับให้ Power Query โหลดรายการค้นหาของเราหนึ่งครั้งในหน่วยความจำ และไม่คำนวณใหม่อีกเมื่อเราเข้าถึงรายการค้นหาเพื่อแทนที่ในภายหลัง เมื่อต้องการทำสิ่งนี้ ให้ห่อสูตรของเราในฟังก์ชันอื่น – รายการบัฟเฟอร์:

การแคชดังกล่าวจะทำให้ความเร็วเพิ่มขึ้นอย่างเห็นได้ชัด (หลายครั้ง!) โดยจะต้องล้างข้อมูลเริ่มต้นจำนวนมาก

เสร็จสิ้นการจัดทำคู่มือ

มันยังคงคลิกที่ หน้าแรก – ปิดและโหลด – ปิดและโหลดไปที่… (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่..), เลือกตัวเลือก เพียงแค่สร้างการเชื่อมต่อ (สร้างการเชื่อมต่อเท่านั้น) และกลับไปที่ Excel

ขั้นตอนที่ 2 กำลังโหลดตารางข้อมูล

ทุกอย่างซ้ำซากอยู่ที่นี่ เช่นเคยกับหนังสืออ้างอิง เราไปถึงที่ใดก็ได้ในตาราง คลิกที่แท็บ ข้อมูล ปุ่ม จากตาราง/ช่วง และโต๊ะของเรา ข้อมูล เข้าสู่ Power Query เพิ่มขั้นตอนโดยอัตโนมัติ แก้ไขประเภท (เปลี่ยนประเภท) คุณยังสามารถลบ:

ไม่จำเป็นต้องดำเนินการเตรียมการเป็นพิเศษ และเราไปยังสิ่งที่สำคัญที่สุด

ขั้นตอนที่ 3 ดำเนินการเปลี่ยนโดยใช้ฟังก์ชัน List.Accumulate

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

มันยังคงคลิกที่ OK – และเราได้รับคอลัมน์ที่มีการแทนที่:

โปรดทราบว่า:

• เนื่องจาก Power Query คำนึงถึงขนาดตัวพิมพ์ จึงไม่มีการแทนที่ในบรรทัดสุดท้าย เนื่องจากในไดเร็กทอรีเรามี “SPb” ไม่ใช่ “SPb”
• หากมีหลายสตริงย่อยที่จะแทนที่พร้อมกันในแหล่งข้อมูล (เช่น ในบรรทัดที่ 7 คุณต้องแทนที่ทั้ง “S-Pb” และ “หนังสือชี้ชวน”) จะไม่สร้างปัญหาใดๆ (ต่างจากแทนที่ด้วยสูตรจาก วิธีก่อนหน้านี้)
• หากไม่มีอะไรที่จะแทนที่ในข้อความต้นฉบับ (บรรทัดที่ 9) แสดงว่าไม่มีข้อผิดพลาดเกิดขึ้น (ไม่เหมือนอีกครั้งจากการแทนที่ด้วยสูตร)

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

ฟังก์ชัน List.Accumulate ทำงานอย่างไร

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

ไวยากรณ์สำหรับฟังก์ชันนี้คือ:

=รายการสะสม(รายการ, เมล็ดพันธุ์, ผู้สะสม)

ที่ไหน

• รายการ คือรายการที่มีองค์ประกอบที่เรากำลังทำซ้ำ 
• เมล็ดพันธุ์ – สถานะเริ่มต้น
• ผู้สะสม – ฟังก์ชันที่ดำเนินการบางอย่าง (คณิตศาสตร์ ข้อความ ฯลฯ) ในองค์ประกอบถัดไปของรายการและรวบรวมผลลัพธ์ของการประมวลผลในตัวแปรพิเศษ
  

โดยทั่วไป ไวยากรณ์สำหรับการเขียนฟังก์ชันใน Power Query จะมีลักษณะดังนี้:

(argument1, argument2, … argumentN) => การกระทำบางอย่างที่มีอาร์กิวเมนต์

ตัวอย่างเช่น ฟังก์ชันผลรวมสามารถแสดงเป็น:

(a, b) => a + b

สำหรับ List.Accumulate ฟังก์ชัน accumulator นี้มีสองอาร์กิวเมนต์ที่จำเป็น (สามารถตั้งชื่ออะไรก็ได้ แต่ชื่อปกติคือ รัฐ и ปัจจุบันในความช่วยเหลืออย่างเป็นทางการสำหรับฟังก์ชันนี้ โดยที่:

• รัฐ – ตัวแปรที่สะสมผลลัพธ์ (ค่าเริ่มต้นคือค่าที่กล่าวข้างต้น เมล็ดพันธุ์)
  
• ปัจจุบัน – ค่าวนซ้ำถัดไปจากรายการ รายการ
  

ตัวอย่างเช่น ลองมาดูขั้นตอนของตรรกะของโครงสร้างต่อไปนี้:

=รายการสะสม({3, 2, 5}, 10, (สถานะปัจจุบัน) => รัฐ + ปัจจุบัน)

นี่คือการสะสมล่าสุด รัฐ ค่าคือฟังก์ชัน List.Accumulate ของเราและผลลัพธ์ที่ได้:

หากคุณเพ้อฝันเพียงเล็กน้อย โดยใช้ฟังก์ชัน List.Accumulate คุณสามารถจำลองฟังก์ชัน Excel CONCATENATE ได้ (ใน Power Query จะเรียกอะนาล็อกของฟังก์ชันนั้น ข้อความรวม) โดยใช้นิพจน์:

หรือแม้แต่ค้นหาค่าสูงสุด (เลียนแบบฟังก์ชัน MAX ของ Excel ซึ่งใน Power Query เรียกว่า รายการสูงสุด):

อย่างไรก็ตาม คุณสมบัติหลักของ List.Accumulate ก็คือความสามารถในการประมวลผลไม่เพียงแต่ข้อความธรรมดาหรือรายการตัวเลขเป็นอาร์กิวเมนต์เท่านั้น แต่ยังรวมถึงอ็อบเจ็กต์ที่ซับซ้อนมากขึ้นด้วย เช่น list-from-lists หรือ lists-from-records (hello, Directory!)

มาดูการก่อสร้างที่ทำการแทนที่ในปัญหาของเราอีกครั้ง:

รายการสะสม (ค้นหาสถานที่, [ที่อยู่], (สถานะปัจจุบัน) => Text.Replace(สถานะ ปัจจุบัน[ค้นหา] ปัจจุบัน[แทนที่]) )

นี่มันเกิดอะไรขึ้นกันแน่เนี่ย?

1. เป็นค่าเริ่มต้น (เมล็ดพันธุ์) เราใช้ข้อความเงอะงะแรกจากคอลัมน์ [ที่อยู่] ตารางของเรา: 199034, เซนต์ปีเตอร์สเบิร์ก, str. เบอริงก้า, d. 1
2. จากนั้น List.Accumulate จะวนซ้ำองค์ประกอบของรายการทีละรายการ – คู่มือ. แต่ละองค์ประกอบของรายการนี้เป็นระเบียนที่ประกอบด้วยช่อง "สิ่งที่ต้องค้นหา - สิ่งที่จะแทนที่ด้วย" หรือกล่าวอีกนัยหนึ่งคือบรรทัดถัดไปในไดเร็กทอรี
3. ฟังก์ชันตัวสะสมทำให้เป็นตัวแปร รัฐ ค่าเริ่มต้น (ที่อยู่แรก 199034, เซนต์ปีเตอร์สเบิร์ก, str. เบอริงก้า, d. 1) และดำเนินการฟังก์ชันตัวสะสม - การดำเนินการเปลี่ยนโดยใช้ฟังก์ชัน M มาตรฐาน ข้อความแทนที่ (คล้ายกับฟังก์ชัน SUBSTITUTE ของ Excel) ไวยากรณ์ของมันคือ:

   Text.Replace (ข้อความต้นฉบับ สิ่งที่เรากำลังมองหา สิ่งที่เรากำลังแทนที่ด้วย )

   และที่นี่เรามี:

• รัฐ เป็นที่อยู่สกปรกของเราซึ่งอยู่ใน รัฐ (ไปที่นั่นจาก เมล็ดพันธุ์)
  
• ปัจจุบัน[ค้นหา] – ค่าฟิลด์ การค้นหา จากรายการวนซ้ำถัดไปของรายการ ค้นหาสถานที่ซึ่งอยู่ในตัวแปร ปัจจุบัน
• ปัจจุบัน[แทนที่] – ค่าฟิลด์ แทน จากรายการวนซ้ำถัดไปของรายการ ค้นหาสถานที่นอนอยู่ใน ปัจจุบัน

ดังนั้น สำหรับแต่ละที่อยู่ จะมีการรันรอบการแจงนับของบรรทัดทั้งหมดในไดเร็กทอรีในแต่ละครั้ง โดยแทนที่ข้อความจากฟิลด์ [Find] ด้วยค่าจากฟิลด์ [Replace]

หวังว่าคุณจะมีความคิด🙂

• ข้อความแทนที่จำนวนมากในรายการโดยใช้สูตร
  
• นิพจน์ทั่วไป (RegExp) ใน Power Query