ฟังก์ชันแบบสอบถามแบบกำหนดเองของ LuckyTemplates

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

นี่คือความต่อเนื่องของบทช่วยสอนที่ฉันทำเมื่อเร็วๆ นี้ ซึ่งเราได้จัดการกับรูปแบบข้อมูลสแต็กที่ยากต่อการจัดการทั่วไป และใช้ Modulo, Pivot และฟังก์ชันการทำความสะอาดบางอย่างด้วย Power Query เพื่อแปลงข้อมูลนั้นเป็นรูปแบบที่เป็นระเบียบและใช้งานได้

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

สารบัญ

• วิธีสร้างและดีบักฟังก์ชัน Query แบบกำหนดเองของ LuckyTemplates
• บทสรุป

วิธีสร้างและดีบักฟังก์ชัน Query แบบกำหนดเองของ LuckyTemplates

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

ถ้าเราต้องการใช้ซ้ำ เราก็แค่คลิกขวาที่ Data แล้วพูดว่าCreate Function

มันบอกว่าจะไม่อ้างอิงพารามิเตอร์ใด ๆ แต่ไม่เป็นไรที่จะสร้างฟังก์ชันโดยไม่มีพารามิเตอร์ เราจะตอบว่า ใช่ เพราะเรากำลังจะเพิ่มพารามิเตอร์เมื่อเราดำเนินการในโปรแกรมแก้ไขขั้นสูง เราจะเรียกฟังก์ชันนี้ว่าUnstack – 3 Elementsเนื่องจากเรามีสามแถวในรูปแบบดิบ ถ้าเรามีรูปแบบสองแถว เราน่าจะสร้างรูปแบบแยกจากกันที่มีสององค์ประกอบหรือสี่องค์ประกอบ

ตอนนี้เราได้ Invoke นี้แล้ว เราไม่มีพารามิเตอร์ และไปที่ Advanced Editor แล้วเริ่มทำงานกับอันนี้

ประการแรก เราไม่ต้องการแหล่งที่มานี้ เราจะใช้พารามิเตอร์ของฟังก์ชันเพื่อกำหนดแหล่งที่มาของเรา

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

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

เราจะเรียกมันว่าConvertStack และนั่นจะใช้ฟังก์ชันที่เรียกว่าTable.FromList เช่นเดียวกับฟังก์ชัน M หลายฟังก์ชัน มันทำตามที่พูดทุกประการ เพียงแค่ใช้รายการ ในกรณีนี้ รายการจะเป็นรายการสแต็กของเรา จากนั้นเราต้องแทนที่ Source ที่นี่ด้วย ConvertStack ของเรา ดูดีทีเดียว! เราไม่มีข้อผิดพลาดทางไวยากรณ์ ดังนั้นเรามากด Done กันเถอะ

ตอนนี้มันให้สิ่งที่เราต้องการ ซึ่งก็คือความสามารถในการเลือกคอลัมน์

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

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

แทนที่จะเรียกใช้สิ่งนี้เป็นฟังก์ชัน เราจะเรียกใช้เป็นแบบสอบถาม เรากำลังจะเริ่มการโทร Stack ด้วยตนเอง เราต้องกำหนดว่า Stack คืออะไร เพราะเราไม่ได้วาดมันผ่านการโต้ตอบของการเลือกใน Invoke ดังนั้นเราจึงมี Stack เท่ากับ TestData และคอลัมน์ Value ของ TestData

สิ่งที่ทำคือแบ่งมันออกเป็นขั้นตอนที่ใช้ สิ่งนี้จะช่วยให้เราทราบว่าเหตุใดจึงไม่ทำงาน สังเกตสิ่งที่น่าสนใจอย่างหนึ่งเมื่อเราดึง Stack เข้ามา เริ่มแรกจะดึง TestData[Value] แต่เมื่อเราแปลงเป็นตาราง แทนที่จะใช้ Value เป็นส่วนหัวของคอลัมน์ ตอนนี้ส่วนหัวของคอลัมน์กลายเป็น Column1

จดจำข้อความแสดงข้อผิดพลาด นั่นคือไม่พบคอลัมน์ค่า สาเหตุที่ไม่พบคอลัมน์ Value ก็คือฟังก์ชัน Table.FromList จะเปลี่ยนชื่อเป็น Column1

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

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

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

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

การสร้างฟังก์ชันการล้างข้อความแบบกำหนดเองใน
รหัส Power Query M ในคอลัมน์แบบกำหนดเองของ Power Query | แบบสอบถามฟังก์ชัน LuckyTemplates
และตัวดำเนินการในตัวแก้ไขแบบสอบถาม

บทสรุป

ในบทช่วยสอนนี้ เราได้สร้างฟังก์ชันแบบกำหนดเอง ดีบัก ทดสอบ และใช้งานได้ดี ดังนั้น เมื่อใดก็ตามที่เรามีสแต็กสามองค์ประกอบ เราก็สามารถเลือกตารางนั้น เลือกคอลัมน์นั้นภายในตาราง และเรียกใช้ฟังก์ชันแบบกำหนดเองนั้น จากนั้นใช้ Modulo, unpivot และทำความสะอาด

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

ดีที่สุด!