ตารางมิติในไฟล์ข้อความ: โซลูชัน Power Query

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

ในวันพุธแรกของแต่ละเดือน จะมีความท้าทายของ DAXและในวันพุธที่สาม จะมีความท้าทายของ Power Query

นี่เป็นโอกาสที่ดีในการสำรวจ ค้นพบ และเรียนรู้สิ่งใหม่ๆ เกี่ยวกับภาษาเหล่านี้ที่คุณจำเป็นต้องใช้ใน LuckyTemplates

คุณจะพบหมวดหมู่เกี่ยวกับปัญหาประจำสัปดาห์ที่ฟอรัม LuckyTemplates

โฟกัสหลักคือทั้งหมดที่เกี่ยวกับกระบวนการและไม่มากที่ผลลัพธ์

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

เมลิซซา เดอ คอร์ต

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

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

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

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

ฉันมีประสบการณ์เกี่ยวกับ Excel อยู่ บ้างและหนึ่งในคุณสมบัติที่ทรงพลังที่สุดในมุมมองของฉันคือpivot table

สารบัญ

• Pivot Table ใน Excel
• การใช้ Power Query เพื่อสร้างตารางไดเมนชัน
• ฟังก์ชันการล้างข้อความแบบกำหนดเอง
• การหมุนข้อมูล
• บทสรุป

Pivot Table ใน Excel

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

จากนั้นคุณสามารถแบ่งส่วนนั้นเพิ่มเติมได้โดยการลากช่องต่างๆ ลงในส่วนคอลัมน์

ฉันได้สร้างตัวอย่างว่ามีลักษณะอย่างไร ที่นี่ เรามีข้อมูลค่อนข้างมากเหมือนกับที่เรามีในไฟล์ข้อความ

และในหน้าถัดไป ฉันได้สร้าง pivot table เปล่าๆ

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

ฉันจะลากส่วนของฉันไปที่แถว

เนื่องจากคอลัมน์ 1 มีชื่อฟิลด์ ฉันจะลากไปที่ส่วนคอลัมน์

คอลัมน์ 2 มีค่าฟิลด์ทั้งหมด ดังนั้นฉันจะลากค่านั้นลงในส่วนค่า

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

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

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

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

การใช้ Power Query เพื่อสร้างตารางไดเมนชัน

มาดู Power Query และทบทวนวิธีแก้ปัญหาของฉันกัน

โดยส่วนตัวแล้วฉันคิดว่า UI ทำงานได้ดีมากในการเขียนโค้ด M จำนวนมากสำหรับเรา ดังนั้นฉันจึงมักจะออกแบบข้อความค้นหาโดยใช้ส่วนต่อประสานกับผู้ใช้ให้มากที่สุดเท่าที่จะทำได้

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

นี่คือกลุ่มฐานของฉันที่สร้างขึ้นโดยใช้อินเทอร์เฟซผู้ใช้

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

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

มาเปิดตัวแก้ไขขั้นสูงกัน

เราเห็นว่าแบบสอบถามนี้มี 31 ขั้นตอน

ฉันยังเพิ่มความคิดเห็นในแบบสอบถามเดียวกันนี้ที่มี 31 ขั้นตอน แต่ฉันแยกมันออกเป็นส่วนๆ

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

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

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

ฟังก์ชันการล้างข้อความแบบกำหนดเอง

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

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

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

มาดูรหัส M สำหรับฟังก์ชันล้างข้อความกัน

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

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

หากคุณไม่คุ้นเคยกับฟังก์ชันเหล่านี้ คุณสามารถค้นหาฟังก์ชัน M ทั้งหมด ในคู่มือสูตร M ทางออนไลน์ นี่คือลิงค์ที่คุณต้องไป

คุณจะพบส่วนที่ทุ่มเทให้กับ Power Query และฟังก์ชันต่างๆ

หากคุณเลื่อนลงมา คุณจะพบส่วนเกี่ยวกับฟังก์ชันข้อความ และแต่ละส่วนจะเริ่มต้นด้วยภาพรวม มีรายการฟังก์ชันข้อความทั้งหมดภายใน Power Query และสูตรภาษา M

หากคุณกำลังมองหาการแปลงที่เฉพาะเจาะจง คุณสามารถดูได้ที่นี่

การหมุนข้อมูล

ส่วนที่สองที่ฉันต้องการเน้นในขณะที่สร้างตารางส่วนข้อมูลนี้คือการหมุนข้อมูลเอง ลองมาดูเรื่องนี้กันดีกว่า

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

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

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

ถ้า Excel สามารถจัดการกับค่าข้อความได้ Power Query ก็สามารถทำได้เช่นกันเนื่องจากการตั้งค่าตัวเลือกขั้นสูง สิ่งที่เราต้องทำคือเลือกDon't Aggregateเพื่อให้สามารถจัดการกับค่าข้อความได้

เมื่อเราคลิกตกลง เราจะเห็นว่าข้อมูลของเราถูกหมุนแล้ว

บทสรุป

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

หากคุณชอบบล็อกโพสต์นี้ โปรดสมัครรับข้อมูลจากช่อง LuckyTemplates เพื่อไม่ให้พลาดเนื้อหาใหม่ๆ

ฉันหวังว่าจะได้พบคุณทุกคนในการท้าทายปัญหาประจำสัปดาห์ในอนาคต

เมลิสซ่า