LuckyTemplates Power Query: การลบแถวที่ซ้ำกัน

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

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

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

สถานการณ์ที่หนึ่งขึ้นอยู่กับสองแถว หากหักล้างกัน ควรลบทั้งสองแถวออก หากไม่เป็นเช่นนั้น ควรเก็บทั้งสองแถวไว้

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

โดยพื้นฐานแล้วเรากำลังใช้ตรรกะเดิมซ้ำแล้วซ้ำอีก เราควรจะสามารถออกแบบแนวทางเดียวเพื่อให้เหมาะกับสถานการณ์เหล่านี้ทั้งหมด

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

ฉันให้คะแนนสิ่งนี้ในระดับกลางเพราะการแปลงส่วนใหญ่ที่เราสามารถทำได้ที่นี่คือการใช้อินเทอร์เฟซผู้ใช้ จากที่กล่าวมา ไปที่ LuckyTemplates Power Query กัน

สารบัญ

• การเพิ่มคอลัมน์ที่มีปริมาณสัมบูรณ์ใน Power Query
• จัดกลุ่มตามดัชนี ID และปริมาณสัมบูรณ์
• การแปลงตารางที่ซ้อนกันใน LuckyTemplates Power Query
  • การเรียงลำดับประเภทการเคลื่อนไหว & วันที่โพสต์
  • สร้างผลรวมที่กำลังทำงานอยู่
  • การกรองแถวตามเงื่อนไข
• บทสรุป

การเพิ่มคอลัมน์ที่มีปริมาณสัมบูรณ์ใน Power Query

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

ฉันจะเปลี่ยนชื่อคอลัมน์นี้ในแถบสูตร

จัดกลุ่มตามดัชนี ID และปริมาณสัมบูรณ์

ตอนนี้เราสามารถจัดกลุ่มตามดัชนี ID และปริมาณสัมบูรณ์ เลือกID Indexจากนั้นกด shift หรือ control ค้างไว้เพื่อเลือกหลายรายการ จากนั้นคลิกที่Abs qty (ปริมาณสัมบูรณ์)

คุณจะพบGroup byบนแท็บ Transform

แต่คุณสามารถค้นหาได้ในแท็บหน้าแรก

คลิกที่มันและตอนนี้เรากำลังจะเพิ่มคอลัมน์ใหม่ ไม่ใช่ "การนับ" แต่จะเป็น "ผลรวม" ของปริมาณ ดังนั้นขอเปลี่ยนชื่อเป็นQty (ปริมาณ) ยอดคงเหลือ มันจะเป็นผลรวมของคอลัมน์ปริมาณ จากนั้น เราจะเพิ่มการรวมอีกครั้ง แต่จะเป็นการรวมแบบพิเศษ ดังนั้นเราจะเลือกแถวทั้งหมด ตั้งชื่อคอลัมน์นี้ด้วย ( AllRows ) จากนั้นกด ตกลง

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

หากเราดูตารางที่ซ้อนกันถัดไป (ดังนั้น ให้คลิกปิดด้านข้างในพื้นที่สีขาวของแถวถัดไป) เราจะเห็นว่าคอลัมน์ปริมาณไม่ตัดกันและยอดคงเหลือของปริมาณมีค่ามากกว่าศูนย์

มาเพิ่มคอลัมน์ที่กำหนดเองเพื่อให้เราสามารถใช้สำหรับการแปลงตารางที่ซ้อนกันในภายหลัง ดังนั้น เลือกเพิ่มคอลัมน์ที่กำหนดเอง

เปลี่ยนชื่อคอลัมน์นั้นเป็นTransformationsแล้วเราจะเพิ่มศูนย์ (0) เป็นตัวยึดตำแหน่ง จากนั้นคลิก ตกลง

การแปลงตารางที่ซ้อนกันใน LuckyTemplates Power Query

ตอนนี้เราพร้อมที่จะสร้างตรรกะสำหรับการแปลงตารางที่ซ้อนกันเหล่านั้นแล้ว แต่เราไม่ต้องการเขียนจำนวนมาก และเราไม่จำเป็นต้องเขียน

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

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

คลิกขวาที่ด้านข้างในพื้นที่สีขาวแล้วเลือกAdd as New Query

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

การเรียงลำดับประเภทการเคลื่อนไหว & วันที่โพสต์

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

ต่อไป เราจะเรียงลำดับวันที่โพสต์เป็นจากน้อยไปหามากตรวจสอบให้แน่ใจว่าถ้าเราละเว้นแถว มันจะทำตามหลักการ FIFO เสมอ (เข้าก่อนออกก่อน)

สร้างผลรวมที่กำลังทำงานอยู่

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

กลับไปที่คำถามของเรา เราต้องการสองสิ่งสำหรับ List.FirstN. อันดับแรก เราต้องการรายการนั้น และรายการนั้นคือคอลัมน์ปริมาณ ของเรา ถ้าฉันคลิกขวาที่ส่วนหัวของคอลัมน์ Volume เราจะมีตัวเลือกให้Add as NewQuery

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

ส่วนที่สองที่เราต้องการสำหรับ List.FirstN คือตัวเลขเพื่อระบุจำนวนที่จะเก็บจากรายการนั้น เราสามารถใช้ดัชนีสำหรับสิ่งนั้นได้ ดังนั้นฉันจะไปเพิ่มคอลัมน์ดัชนีจากหนึ่ง (1) สำหรับแถวนี้

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

มาเพิ่มคอลัมน์ที่กำหนดเอง และเรียกมันว่าRunning Total เราใช้ List.FirstN ที่นี่ และเราจะเห็นว่าพารามิเตอร์แรกคือรายการนั้น คอลัมน์ปริมาณ เราต้องชี้ไปที่ขั้นตอนสุดท้าย และขั้นตอนสุดท้ายนั้นคือAdded Index

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

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

ตอนนี้สิ่งที่เราต้องทำคือรวมปริมาณเหล่านี้และเราสามารถใช้Lists.Sumเพื่อทำสิ่งนั้นได้ ฉันจะเพิ่มสิ่งนั้นในแถบสูตร และฉันจะใส่Type ด้วย

การกรองแถวตามเงื่อนไข

เราสามารถระบุแถวที่เราต้องการเก็บไว้ได้ เราต้องการเก็บเฉพาะแถวที่มากกว่าศูนย์ ดังนั้นมาเพิ่มเงื่อนไขตัวกรองกัน

ตอนนี้เราสามารถลบคอลัมน์ตัวช่วยของเราได้แล้ว เลือก Index and Running Total จากนั้นคลิก Remove Columns

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

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

โค้ดที่ไฮไลต์ด้านบนชี้ไปที่ตารางซ้อนแบบขยายของเรา ซึ่งวางอยู่ภายในคอลัมน์แถวทั้งหมด ดังนั้นฉันจะชี้ไปที่นั้นโดยแทนที่โค้ดที่ไฮไลต์นี้เป็นAllRows

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

เราสามารถลบออกได้โดยการกรองในคอลัมน์ยอดคงเหลือของปริมาณ เราไม่ต้องการให้ดุลปริมาณเท่ากับศูนย์

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

บนแท็บ Transform เลือกData Type เท่านี้ก็เสร็จเรียบร้อย และนั่นคือวิธีที่คุณล้างข้อมูลธุรกรรม

Unpivot และ Pivot Basics In LuckyTemplates – Query Editor Review
My Practice Tips when using the LuckyTemplates Advanced Editor
การแปลงขั้นสูงใน LuckyTemplates

บทสรุป

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

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

ไชโย!

เมลิสซ่า