Unpivot หลายคอลัมน์แบบไดนามิกใน LuckyTemplates

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

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

จากที่กล่าวมา ไปที่ Power Query กัน

สารบัญ

• ข้อมูลในรูปแบบตาราง
• การแก้ไขโค้ดเพื่อยกเลิกการหมุนหลายคอลัมน์แบบไดนามิก
• Unpivot หลายคอลัมน์แบบไดนามิก: การเพิ่มตรรกะของคอลัมน์ที่กำหนดเอง
• บทสรุป

ข้อมูลในรูปแบบตาราง

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

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

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

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

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

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

จากนั้น List.Zip จะนำองค์ประกอบจากแต่ละรายการจากสามรายการเหล่านี้มารวมเข้าด้วยกันตามตำแหน่งในรายการใหม่ ตัวอย่างเช่น องค์ประกอบแรกแต่ละรายการจากรายการเหล่านั้นจะรวมกันเป็นรายการใหม่ สิ่งเดียวกันนี้เกิดขึ้นกับค่าทั้งหมดที่แสดงรายการที่สองในรายการนั้นและต่อๆ ไป

ค่าฮาร์ดโค้ดอาจเป็นปัญหาได้หากแอตทริบิวต์ไม่น่าจะเปลี่ยนแปลงเมื่อเวลาผ่านไป ดังนั้นหากครั้งหน้าไม่มีไอเท็มทั้งหมดหรือมีไอเท็มใหม่ คุณอาจประสบปัญหาได้

การแก้ไขโค้ดเพื่อยกเลิกการหมุนหลายคอลัมน์แบบไดนามิก

แล้วเราจะสร้างไดนามิกนี้ได้อย่างไร?

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

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

คลิกขวาที่ส่วนหัวนั้น และด้านล่างเลือกAdd as new query

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

เริ่มต้นด้วยการลบโมฆะเหล่านั้นออก ดังนั้นภายในแถบสูตร เราจะเพิ่มList.RemoveNulls อย่างที่คุณเห็นในตอนนี้ รายการทั้งหมดซ้ำกันและนั่นเป็นเพราะคู่ค่า มันแสดงครั้งเดียวสำหรับชั่วโมง

และอีกครั้ง สำหรับผู้ติดต่อ หากต้องการลบรายการที่ซ้ำกันออก เราสามารถเพิ่มList.Distinctได้ ตอนนี้เราได้สร้างตรรกะนี้แล้ว และทั้งหมดที่เราต้องทำคือคัดลอกและวางกลับไปที่แบบสอบถามตัวอย่างของเรา

ในแบบสอบถามตัวอย่างให้เปิดตัวแก้ไขขั้นสูงและสร้างชื่อตัวแปรใหม่ เรียกมันว่าAttributeList จากนั้นเราวางรหัสและใส่เครื่องหมายจุลภาค

ตอนนี้ให้กลับไปที่ขั้นตอนที่กำหนดเอง ของเรา ภายใน List.Zip เราสามารถแทนที่รายการแรกด้วยชื่อตัวแปรของเรา (AttributeList)

ทีนี้มาดูรายการที่สองให้ละเอียดยิ่งขึ้น เรารู้ว่ามันอ้างอิงชื่อคอลัมน์ และชื่อคอลัมน์แต่ละชื่อจะขึ้นต้นด้วยแอตทริบิวต์เดียวกันกับที่เรามีอยู่แล้วในรายการนั้น ตามด้วยช่องว่างและข้อความ "hours"

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

Unpivot หลายคอลัมน์แบบไดนามิก: การเพิ่มตรรกะของคอลัมน์ที่กำหนดเอง

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

ในการเปลี่ยนค่าในรายการ เราสามารถใช้ฟังก์ชันList.Transform เราจำเป็นต้องแปลงค่าในAttributeList ของเรา ดังนั้นฉันจะวางมันลงไป

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

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

ด้านล่างของหน้า เราเห็นตัวอย่างรายการที่เราสร้างขึ้น ดังนั้นคุณจะเห็นว่าตอนนี้เรามีส่วนหัวของคอลัมน์เหล่านั้นแล้ว ซึ่งตรงกับส่วนหัวของคอลัมน์ที่เราเห็นที่นี่พร้อมกับรายการนี้

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

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

แต่เราสนใจเฉพาะค่าของฟิลด์เรกคอร์ดเท่านั้น ดังนั้นเราต้องดึงข้อมูลนั้นออกมา กลับไปที่กล่องโต้ตอบคอลัมน์ที่กำหนดเองของเราและเพิ่มRecord.FieldsValues

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

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

บนแถบสูตรของเรา เราเลือกรายการที่สองและแทนที่ด้วยตรรกะของเรา

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

จากนั้นแทนที่จะเป็น ชั่วโมงมาเปลี่ยนเป็น รายชื่อติดต่อกัน

ไม่มีข้อผิดพลาดและดูดี นี่เป็นโซลูชันไดนามิกในการเลิกทำ pivot หลายคอลัมน์

คอลัมน์ Unpivot ของ LuckyTemplates – บทช่วยสอน Power Query
วิธียกเลิกการเปลี่ยนคอลัมน์ใน LuckyTemplates
Unpivot และ Pivot Basics ใน LuckyTemplates – รีวิวตัวแก้ไขแบบสอบถาม

บทสรุป

นั่นเป็นวิธีที่คุณยกเลิกการเปลี่ยน Pivot หลายคอลัมน์แบบไดนามิกในโดยใช้ List.Zip

หากมีแอตทริบิวต์ใหม่เข้ามา AttributeList จะเลือกโดยอัตโนมัติ และเมื่อไม่มีรายการนั้นแล้ว รายการนั้นก็จะไม่ปรากฏในรายการนั้น

ฉันหวังว่าคุณจะสนุกกับสิ่งนี้ ดูวิดีโอแบบเต็มด้านล่างสำหรับรายละเอียดเพิ่มเติมเกี่ยวกับบทช่วยสอนนี้ และดูลิงก์ด้านล่างสำหรับเนื้อหาเพิ่มเติมเกี่ยวกับการเลิกทำ pivot หลายคอลัมน์ใน LuckyTemplates

ทั้งหมดที่ดีที่สุด

เมลิสซ่า