เค้าโครงปฏิทินเป็นรูปแบบตารางโดยใช้ Power Query

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

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

สารบัญ

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

การเตรียมไฟล์และเค้าโครงใน Power Query

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

ตอนนี้ ไปที่ Power Query และรับไฟล์ เลือก แหล่งข้อมูลใหม่ แล้วคลิก เพิ่มเติม จากนั้นคลิกทั้งหมดแล้วเลือกโฟลเดอร์

เลือกพารามิเตอร์แล้วกดตกลง

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

จากนั้น เลือกไฟล์ใดก็ได้ในโฟลเดอร์เป็นไฟล์ตัวอย่าง แล้วเลือกรายการที่คุณต้องการเริ่ม

เมื่อดูที่ Query1 คุณจะเห็นข้อมูลจากทั้ง File1 และ File2

การกำหนดสูตรการคำนวณใน Power Query

รูปแบบของข้อมูลใน Query1 ยังคงไม่ถูกต้อง คุณสามารถเปลี่ยนแปลงได้โดยใช้แบบสอบถาม Transform Example File

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

ลบ 2 แถวแรกและ Column1

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

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

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

เพิ่มขั้นตอนด้วยตนเองโดยกดf(x)ในแถบสูตร จากนั้นป้อนTable.ToColumns ซึ่งจะเปลี่ยนแต่ละคอลัมน์เป็นรายการที่มีรายการ

เปลี่ยนรายการเป็นตารางและเพิ่มหมายเลขดัชนี คุณสามารถเปลี่ยนจาก 0 หรือ 1

เติมช่องว่างในวันที่

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

เลือกคอลัมน์ดัชนีแล้วคลิกจัดกลุ่มตาม แทนที่ Count เป็น ToTable เพื่อเปลี่ยนชื่อคอลัมน์ เปลี่ยน RowCount เป็น FromColumns และชี้ไปที่ Column1 ซึ่งวางรายการไว้ จากนั้นเปลี่ยนเป็นตารางประเภท

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

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

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

จากนั้นตรวจสอบว่าเป็นวันที่หรือไม่ ถ้าใช่ ให้เติมคอลัมน์นั้นลงไปด้วย Table.Filldown ภายในฟังก์ชันนั้น ให้ป้อน ToTable และ Column1 แต่ถ้าไม่ใช่วันที่ ให้ป้อน ToTable เพื่อส่งคืนทั้งตาราง

นี่จะทำให้คุณมีคอลัมน์ใหม่ที่มีตารางเต็ม

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

หลังจากฟังก์ชัน Table.FillDown และ Else ในแถบสูตร ให้อ้างอิง Column1

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

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

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

การจัดกลุ่มตารางที่แยกจากกัน

ใช้ Group by ในคอลัมน์ Index2 จัดกลุ่มตารางที่มีค่า Index2 เหมือนกันเป็นตารางเดียว

คุณจะไม่นับแถว ดังนั้น เปลี่ยน RowCount เป็น FromColumns และเพิ่มคอลัมน์ FillDown จากนั้นเปลี่ยนประเภทเป็นตาราง

หากคุณคลิกที่ตาราง คุณจะเห็นคอลัมน์ภายในนั้น

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

เลือกคอลัมน์ 1 และคอลัมน์ 2 คลิก Transform แล้วเลือก Unpivot คอลัมน์อื่น จากนั้นจะแสดงให้คุณเห็น 4 คอลัมน์

ลบคอลัมน์ Attribute เนื่องจากไม่จำเป็น จากนั้น เปลี่ยนชื่อคอลัมน์เป็นDate , MovementsและName เปลี่ยนประเภทข้อมูลของคอลัมน์ Date เป็น Date และคอลัมน์ Name เป็น Text

คุณได้อัปเดตการแปลงเป็นไฟล์ตัวอย่างการแปลงแล้ว

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

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

ตอนนี้คุณได้อัปเดตและเปลี่ยนรูปแบบของโมเดลข้อมูลเป็นฟอร์มตารางแล้ว

ปฏิทินแบบกำหนดเองของ LuckyTemplates: การคำนวณสำหรับเดือนที่เปลี่ยนเดือน – 445 ปฏิทิน
เทคนิคการจัดรูปแบบตามเงื่อนไขแบบกำหนดเองใน LuckyTemplates
วิธีทำงานกับวันที่หลายวันใน LuckyTemplates

บทสรุป

ในรายงานข้อมูลของคุณ คุณต้องเติมช่องว่างในตารางวันที่ของคุณโดยเปลี่ยนเค้าโครงปฏิทินเป็นรูปแบบตาราง

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

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

เมลิสซ่า