การใช้ตัวแก้ไขขั้นสูงของ Power Query เพื่อแยกค่าก่อนข้อความเฉพาะ

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

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

สารบัญ

• การใช้ Power Query Advanced Editor และฟังก์ชัน M
• บทสรุป

การใช้ Power Query Advanced Editor และฟังก์ชัน M

ฉันจะสร้างการอ้างอิงของแบบสอบถามตัวอย่าง และเพิ่มคอลัมน์ที่กำหนดเองซึ่งเราสามารถสร้างตรรกะของเราได้

สิ่งแรกที่ฉันจะทำคือแยกคอลัมน์ข้อความนั้น ดังนั้นเราจะใช้ฟังก์ชันText.Splitแล้วใส่วงเล็บเปิด มันต้องการข้อความ ดังนั้นเราสามารถชี้ไปที่คอลัมน์ข้อความของเรา ถัดไป มันต้องการตัวคั่นเป็นข้อความ ดังนั้นในระหว่างชุดของเครื่องหมายคำพูดคู่ เราสามารถเพิ่มเครื่องหมายอัฒภาค จากนั้นปิดวงเล็บ แล้วกดตกลง

และด้วยเหตุนี้จึงทำให้เรามีรายการที่มีค่าข้อความที่แยกจากกัน

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

ดังนั้น ในแถบสูตร ฉันจะเพิ่ม List.Transform มันต้องการรายการและเราได้ให้แล้วใส่เครื่องหมายจุลภาค ต่อไปเป็นการแปลง ดังนั้นเราต้องการตัดแต่งข้อความเหล่านั้น ดังนั้น ฉันจะเพิ่มText.Trimแล้วใส่วงเล็บปิด แล้วกด ตกลง

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

เราสามารถสร้างชุดตัวแปรที่คั่นด้วยเครื่องหมายจุลภาคภายในนิพจน์ปล่อย ให้ ฉันจะเรียกขั้นตอนแรกเป็นListTexts วงเล็บปิดสุดท้ายนั้นเป็นของตารางของเราที่เพิ่มฟังก์ชันคอลัมน์ ดังนั้นให้ย้ายไปยังบรรทัดใหม่ด้วย ในตอนท้ายของนิพจน์แรกนั้น เราต้องใส่เครื่องหมายจุลภาค เพื่อให้เราสามารถเพิ่มนิพจน์ใหม่ตรงนั้นได้

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

ต่อไป เราใส่ค่าที่เราต้องการ และนั่นคือ A ตัวใหญ่สองตัว คู่ของ let คือ the ในประโยค ดังนั้นเราต้องเพิ่มสิ่งนั้นและส่งกลับไปที่FindPositions จากนั้นกด เสร็จสิ้น

ฟังก์ชันนี้ใช้พารามิเตอร์ที่สามซึ่งเป็นทางเลือก ดังนั้นในแถบสูตร เราจึงสามารถเพิ่มพารามิเตอร์ที่สามนั้นได้ และด้วยเหตุนี้เราจึงได้รับรายการ

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

เรายังต้องถอยกลับไปตำแหน่งหนึ่ง ดังนั้นในโปรแกรมแก้ไขขั้นสูง เราสามารถใช้List.Transformเพื่อช่วยเราได้

ต่อไป เราจะใช้ List.Transform เพื่อรับสตริงและเข้าถึงรายการภายในรายการของเรา

ขั้นตอนสุดท้ายคือการรวมค่าข้อความทั้งหมดในรายการเป็นสตริงเดียว ดังนั้น ผมจะเพิ่มอีกบรรทัดในตรรกะของเราสำหรับผลลัพธ์ของเราด้วยฟังก์ชันText.Combine

และนั่นคือวิธีที่คุณสามารถแยกสตริงก่อนข้อความใดข้อความหนึ่งด้วยโซลูชันที่มีโค้ดทั้งหมด

ข้อมูลที่มีตัวคั่น – การแยกเรกคอร์ดโดยใช้ LuckyTemplates
แยกค่าจากเรกคอร์ดและรายการใน Power Query
Unpivot หลายคอลัมน์แบบไดนามิกใน LuckyTemplates

บทสรุป

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

ดีที่สุด!

เมลิสซ่า