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

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

บทช่วยสอนนี้จัดทำขึ้นเพื่อตอบคำถามของ   สมาชิกสมาชิกถามถึงวิธีที่เขาสามารถปรับเปลี่ยนข้อมูลของเขาจากเค้าโครงประเภทปฏิทินเป็นรูปแบบตาราง

ก่อนที่เราจะเริ่ม เรามาตรวจสอบข้อมูลที่เรามีกันก่อน

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

อย่างไรก็ตาม เค้าโครงประเภทนี้ยากต่อการวิเคราะห์เนื่องจากมีคอลัมน์และแถวจำนวนมากที่ต้องพิจารณา

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

สารบัญ

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

การล้างข้อมูลสำหรับรูปแบบตารางที่เหมาะสม

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

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

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

ลองลบแถวดัชนี 14 ถึง 16 เนื่องจากเต็มไปด้วยค่า Null เท่านั้น อีกครั้ง ไปที่ แท็บ Remove Rowsจากนั้นคลิกRemove Blank Rows

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

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

ตอนนี้ตารางควรมีลักษณะพลิกตะแคงหลังจากดำเนินการแล้ว

จากนั้น เลือกคอลัมน์ทั้งหมดที่มีวันที่ คลิกขวาที่ส่วนหัวของคอลัมน์ จากนั้นเลือกเติมลง

สิ่งนี้ควรทำซ้ำวันที่และเติมช่องว่างในคอลัมน์ที่เลือก

ทำลายตาราง

เมื่อดูข้อมูลที่เรามีตอนนี้ เราจะเห็นว่ามีสี่ตารางติดกัน คอลัมน์ 1 ถึง 7 สร้างหนึ่งตาราง คอลัมน์ 8 ถึง 13 สร้างตารางอื่น และอื่น ๆ

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

ในการทำเช่นนั้น เราจะสร้างสูตรแรกสำหรับบทช่วยสอนนี้ ให้คลิก ปุ่ม fxจากนั้นเพิ่มสูตรที่เราจะใช้ เราจะใช้การดำเนินการTable.ToColumns สิ่งนี้จะสร้างรายการของรายการที่ซ้อนกันของค่าคอลัมน์จากตาราง แต่ละคอลัมน์จะกลายเป็นรายการ

นี่คือวิธีที่ฉันตั้งค่าสูตร

สูตรสร้างรายการของคอลัมน์ที่เรามีก่อนหน้านี้ ค่าแรกของรายการประกอบด้วยคอลัมน์ 1 ค่าที่สองประกอบด้วยคอลัมน์ 2 และอื่นๆ

การระบุรายการสำหรับตารางใหม่สำหรับรูปแบบตารางที่เหมาะสม

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

ในการทำเช่นนั้น ขั้นแรกให้แปลงรายการกลับเป็นตารางโดยคลิกที่ ปุ่ม To Table ที่มุมซ้ายบน

จากนั้นเพิ่มคอลัมน์ดัชนีข้างๆ

สิ่งต่อไปที่เราจะทำคือเพิ่มคอลัมน์ถัดจากคอลัมน์ดัชนี คอลัมน์นี้จะช่วยเรากำหนดว่าค่าใดในคอลัมน์ 1 มีค่าวันที่ ตั้งชื่อคอลัมน์นี้ว่า Index2

ในการทำเช่นนั้น เราจะรวมสูตรใน Index2 ซึ่งจะส่งคืนค่าดัชนีของ Column1 หากมีวันที่

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

เนื่องจากค่าแรกใน Column1 มีวันที่ ค่าแรกใน Index2 ควรมีดัชนีเป็นเลข 0 นอกจากนี้ เนื่องจากค่าที่สองใน Column1 ไม่มีวันที่ ดังนั้นค่าที่สองใน Index2 จึงควรมีเฉพาะค่า Null เท่านั้น

นี่คือวิธีที่ฉันตั้งค่าสูตรสำหรับ Index2 เพียงตรวจสอบให้แน่ใจว่าไม่มีข้อผิดพลาดทางไวยากรณ์ในสูตรของคุณ

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

การพิจารณาว่าค่าใดอยู่ในตาราง

ตอนนี้ ค่าดัชนีเหล่านี้ภายใน Index2 ระบุจุดเริ่มต้นและจุดสิ้นสุดของตารางแยกต่างหากที่เรามี นั่นคือ 0 หมายถึงจุดเริ่มต้นของตารางแรก 7 หมายถึงการสิ้นสุดของตารางแรก และการเริ่มต้นของตารางที่สอง และอื่นๆ

เพื่อช่วยให้เราระบุว่าค่าใดในคอลัมน์ 1 อยู่ในตารางเดียวกัน ให้ใช้ การดำเนิน การเติมลงในคอลัมน์ใหม่ของเรา

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

การจัดกลุ่มข้อมูลสำหรับรูปแบบตารางที่เหมาะสม

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

เมื่อทำเช่นนั้น ตอนนี้เราจะเหลือเพียง 4 ค่าในคอลัมน์ Index2 ของเรา ขอให้สังเกตว่าคอลัมน์อื่นชื่อ Count อยู่ข้างคอลัมน์ Index2 ของเรา คอลัมน์นี้นับจำนวนแถวในแต่ละค่าของเราในคอลัมน์ Index2

อย่างไรก็ตาม นี่ไม่ใช่สิ่งที่เราต้องการจะทำ สิ่งที่เราต้องทำคือสร้างตารางใหม่โดยใช้คอลัมน์ที่เรามี

ในการทำเช่นนั้น เราจะใช้ การดำเนินการ Table.FromColumnsอ้างอิงคอลัมน์ที่มีค่ารายการของเราซึ่งก็คือ Column1 แล้วเปลี่ยนประเภทการส่งคืนเป็นตาราง

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

การทำเช่นนี้จะรวมค่าทั้งหมดใน Column1 ที่มีค่า Index2 เป็น 0 ลงในตารางหนึ่ง ตารางที่มีค่า Index2 เป็น 7 ลงในอีกตารางหนึ่ง และอื่นๆ

เราตรวจสอบทุกระเบียนเป็นค่า 0 จากนั้นดึงค่ารายการจาก Column1 สุดท้าย เรารวมรายการเหล่านั้นเข้าด้วยกันเพื่อสร้างตารางเดียวโดยใช้โค้ด M นี้

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

ต่อท้ายตารางแยก

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

การ ดำเนินการ Table.Combineจะผสานรายการของตาราง จากนั้นสร้างตารางเดียวจากตารางเหล่านั้น ที่นี่ เราจะอ้างอิงคอลัมน์ที่มีรายการตารางของเรา ซึ่งก็คือคอลัมน์จำนวน

ตอนนี้เรามีตารางที่รวมตารางที่ซ้อนกันทั้งหมดกลับเข้าไปในตารางเดียวขนาดใหญ่

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

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

จากนั้น ตั้งค่าประเภทที่ถูกต้องของคอลัมน์ทั้งหมดและติดป้ายกำกับให้ถูกต้อง

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

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

จากนั้น เลือกคอลัมน์ที่สองและสาม และตั้งค่าประเภทเป็นข้อความ

ทุกอย่างเสร็จเรียบร้อย! นี่คือรูปแบบตารางที่เหมาะสมของเราควรมีลักษณะดังนี้

บทสรุป

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

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

การทำความเข้าใจแนวคิดที่ใช้ในที่นี้มีความสำคัญมาก เนื่องจากสามารถนำไปใช้ในลักษณะอื่นๆ ได้มากมาย

หากแนวคิดยังไม่ชัดเจนสำหรับคุณ การตรวจสอบลิงก์ด้านล่างอาจช่วยคุณได้

สนุกกับการทำงานผ่านสิ่งนี้!

เมลิสซ่า