การสร้างค่าสำหรับแต่ละวันที่โดยใช้สูตร Power Query

ในบทช่วยสอนนี้ คุณจะได้เรียนรู้วิธีสร้างค่าในแต่ละวันโดยใช้ สูตร Power Queryใน LuckyTemplates

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

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

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

สารบัญ

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

ตั้งค่าเพิ่มเติม

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

เมื่อต้องการทำเช่นนั้น ไปที่ Power Query เพื่อดูตารางของคุณ

คุณต้องโหลดสำเนาข้อมูลของคุณลงในหน่วยความจำ เพื่อให้เกิดขึ้น คุณต้องใช้การวัดTable.Buffer

หากคุณคลิก TableBuffer คุณจะเห็นว่าการวัดทำอะไร

จากนั้น กลับไปที่ชุดข้อมูลของคุณแล้วคลิกfxในแถบสูตร คุณต้องเพิ่ม Table.Buffer ในหน่วยวัด

จากนั้น เปลี่ยนชื่อขั้นตอนนี้เป็นBufferedTable

ถัดไป เพิ่มคอลัมน์ที่กำหนดเอง

สร้างเงื่อนไขตัวกรองสำหรับรหัส

ในสูตร Power Query นี้ ให้เปลี่ยนขั้นตอนอ้างอิงจากBufferedTableเป็นRenameColumnsเนื่องจากควรตรวจสอบ BufferedTable ก่อน

หากต้องการตรวจสอบ BufferedTable ให้ใช้Table.SelectRowsซึ่งจะส่งคืนตารางที่ตรงกับเงื่อนไขที่เลือก

ดังนั้นสำหรับแต่ละนิพจน์ในสูตร ให้ใช้ Table.SelectRows แล้วใช้ BufferedTable

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

สิ่งนี้จะให้ตารางที่กรองซึ่งมีเฉพาะ ID ที่ตรงกับระเบียนปัจจุบัน 

เพิ่มเงื่อนไขตัวกรองสำหรับวันที่

คุณต้องกรองคอลัมน์วันที่ เนื่องจากคุณต้องการเฉพาะวันที่ที่เท่ากับเรกคอร์ดปัจจุบันเท่านั้น

ใช้ฟังก์ชันเหล่านี้เพื่อตรวจสอบคอลัมน์วันที่ BT:

คอลัมน์นี้ต้องมากกว่าหรือเท่ากับวันที่จากบันทึกปัจจุบัน

ด้วยเหตุนี้ ตารางจะมีสองแถวแทนที่จะเป็นสามแถว:

ตรวจสอบและจัดเรียงคอลัมน์วันที่

คุณต้องตรวจสอบว่าการเรียงลำดับคอลัมน์วันที่ถูกต้องหรือไม่โดยใช้Table.Sort ฟังก์ชัน Table.Sort จัดเรียงตารางในหนึ่งคอลัมน์ขึ้นไป ขึ้นอยู่กับเกณฑ์ที่คุณต้องการ

ไวยากรณ์ในตัวอย่างนี้ค่อนข้างซับซ้อน ดังนั้นคุณต้องทำการเจาะลึกในหัวข้อตาราง

คุณต้องคลิกขวาที่ตารางและเพิ่มเป็นแบบสอบถามใหม่

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

จากนั้น คลิกที่คอลัมน์วันที่ แล้วเลือก เรียงลำดับจากน้อยไปหามาก

คัดลอกสูตรทั้งหมด:

กลับไปที่แบบสอบถามหลักของคุณแล้ววางลงในแถบสูตร:

คุณต้องลบการอ้างอิงของ Table.Sort เนื่องจากตารางที่ต้องผ่านพารามิเตอร์ควรเป็นผลลัพธ์จาก Table.SelectRows

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

ใช้ฟังก์ชัน Table.FirstN

สำหรับขั้นตอนนี้ คุณต้องการเพียง 2 ระเบียนแทนที่จะเป็น 3

หากต้องการแก้ไขปัญหานี้ ให้กลับไปที่ข้อความค้นหาที่กำหนดเอง คลิก Keep Rows จากนั้น Keep Top Rows

ป้อน 2 ในช่องว่างสำหรับจำนวนแถว

คุณจะสังเกตเห็นว่าฟังก์ชัน Table.FirstN ปรากฏในแถบสูตร Table.FirstN ส่งคืนแถวแรกของตารางโดยขึ้นอยู่กับค่าของ countOrCondition

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

ทุกวัตถุตารางในชุดจะมี 1 หรือ 2 แถว

รับความแตกต่างของวันระหว่างค่าวันที่ 2 ค่า

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

เริ่มต้นด้วยการเพิ่มคอลัมน์ที่กำหนดเอง ตั้งชื่อวัน จากนั้น ทำตามสูตร Power Query นี้:

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

จากนั้นเพิ่มประเภทตัวเลขในสูตรเช่นนี้

อย่างที่คุณเห็น คุณจะได้รับความแตกต่าง 385 วันจากวันที่ 14 กันยายน 2017 ถึง 4 ตุลาคม 2018

กำหนดขั้นตอนน้ำหนัก

ต่อไปคุณต้องคำนวณว่าน้ำหนักในแต่ละวันควรเป็นเท่าใด

คุณต้องเพิ่มคอลัมน์แบบกำหนดเองอีกครั้ง ตั้งชื่อเป็น WeightStep และคัดลอกสูตร Power Query นี้:

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

อีกครั้ง ใส่เลขพิมพ์ในสูตร เพื่อดูน้ำหนักในแต่ละวัน

เติมช่องว่าง

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

ขั้นแรก เพิ่มคอลัมน์ที่กำหนดเองและเรียกมันว่าDateKey จากนั้น ทำตามสูตร Power Query นี้:

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

นี่คือผลลัพธ์:

หัวเรื่องตารางที่ไม่มีความแตกต่างใด ๆ จะส่งคืนวันที่ ตารางที่ทำ มีรายการวันที่

หากคุณขยาย DateKey เป็นแถวใหม่ คุณจะเห็นบันทึกของวันทั้งหมด

รับการคาดการณ์น้ำหนักของวันที่

หลังจากสร้างรายการวันที่แล้ว คุณต้องได้รับการคาดคะเนน้ำหนักสำหรับวันที่

ขั้นแรก เพิ่มคอลัมน์ที่กำหนดเอง และตั้งชื่อว่าWeightProjection จากนั้นป้อนสูตร Power Query นี้:

ไวยากรณ์จะรับค่าน้ำหนัก เพิ่มลงในค่า DateKey ลบออกจากค่าวันที่ และคูณด้วย WeightStep

เพิ่มหมายเลขประเภท อีกครั้ง ที่ส่วนท้ายของสูตร

ขณะนี้คุณมีการคาดการณ์น้ำหนักสำหรับแต่ละวัน

ลบคอลัมน์ตัวช่วย: Custom, Days และ WeightStep โดยใช้สูตรด้านล่าง และเปลี่ยนรูปแบบของคอลัมน์ DateKey เป็น Date

บทสรุป

ในบทช่วยสอนนี้ คุณได้เรียนรู้วิธีเติมช่องว่างในชุดข้อมูลของคุณโดยการสร้างค่าในรายการวันที่แต่ละรายการ

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

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

สิ่งที่ดีที่สุด

เมลิสซ่า