Power BI

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

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

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

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

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

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

สารบัญ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

บทสรุป

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

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

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

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

เมลิสซ่า

More in Power BI