ปฏิทินวันหยุดต่อเนื่อง – POTW #12 (โซลูชัน Power Query)

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

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

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

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

สารบัญ

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

จุดเริ่มต้นสำหรับการสร้างปฏิทินถาวร

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

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

การดึงปีจากตารางวันที่

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

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

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

เมื่อเพิ่มDate.Monthเราจะเหลือเพียงหมายเลขเดือนสำหรับวันที่ที่ระบุ สำหรับเดือนมกราคม จะส่งกลับ 1 สำหรับเดือนกุมภาพันธ์ จะส่งกลับ 2 ไปเรื่อยๆ

รับตัวเลขจากคอลัมน์วัน

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

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

เราจะใช้วิธีการที่คล้ายกันเพื่อรับชื่อวันที่ แต่ใช้ฟังก์ชันList.PositionOf วิธีนี้จะส่งกลับค่าออฟเซ็ตที่เป็นศูนย์ของค่าในรายการที่ส่งเป็นอาร์กิวเมนต์แรก และจะคืนค่า -1 หากค่าไม่ปรากฏในรายการนั้น

สำหรับระเบียนแรก จะส่งผ่านค่า 1 ซึ่งไม่มีอยู่ในรายการของเรา ด้วยเหตุนี้จึงส่งคืนค่า -1 สำหรับเรกคอร์ดที่ สองจะส่งคืนค่าMonday

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

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

การเรียกใช้ฟังก์ชันแบบกำหนดเองสำหรับปฏิทินวันหยุดถาวร

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

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

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

สร้างรายการวันที่

ตอนนี้มาสร้างรายการวันที่กัน เราจะกำหนดวันที่เริ่มต้นสำหรับเดือนนั้น กำหนดจำนวนวันในเดือนนั้น และใช้List.Datesเพื่อสร้างรายการที่มีวันทั้งหมดในเดือนนั้น

ต่อไป เราจะใช้List.Selectเพื่อเก็บเฉพาะวันที่ที่อยู่ในวันธรรมดาที่ระบุเท่านั้น

เมื่อเราคลิกไปที่ด้านข้างของรายการเหล่านี้ เราจะเห็นว่ามีเพียงสี่รายการเหล่านี้เท่านั้น

การใช้ตรรกะแบบมีเงื่อนไข

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

หากตัวเลขเท่ากับค่าสุดท้าย เราสามารถใช้ ฟังก์ชัน List.Reverseเพื่อย้อนกลับลำดับในรายการและดึงค่าแรกออกมา

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

สำหรับ กรณีอื่นๆ เราใช้List.Skip

เราสามารถแยกค่าตามตำแหน่งนี้ในรายการ สำหรับวันขอบคุณพระเจ้า ตัวเลขคือสี่ และสี่จะส่งกลับ 3 ซึ่งจะข้ามค่าสามค่าแรกในรายการ และจะส่งกลับรายการที่สี่เสมอ

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

ตรวจสอบรหัส M สำหรับปฏิทินวันหยุดต่อเนื่อง

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

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

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

ขั้นตอนต่อไปคือขยายรายการและเพิ่มCelebratedOnDate

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

ขั้นตอนสุดท้ายคือการเปลี่ยนประเภท

บทสรุป

ขอเตือนสั้นๆ ว่าเป็นซีรีส์ต่อเนื่องที่จะเกิดขึ้นทุกวันพุธที่ 1 และ 3 ในฟอรัม LuckyTemplates ปัญหาที่โพสต์ในสัปดาห์ที่ 1 ต้องการโซลูชันที่ใช้ DAX ในขณะที่ปัญหาในสัปดาห์ที่ 3 จะถามหาโดยใช้ Power Query

หากคุณชอบเนื้อหาที่ครอบคลุมใน POTW นี้ โปรดสมัครรับข้อมูลเพิ่มเติมจากช่อง LuckyTemplates TV เรามีเนื้อหาจำนวนมากออกมาตลอดเวลาจากตัวฉันเองและผู้สร้างเนื้อหาจำนวนมาก ทั้งหมดนี้ทุ่มเทให้กับการปรับปรุงวิธีที่คุณใช้ LuckyTemplates และ Power Platform

เมลิสซ่า