Power Query Transform Column Challenge: ข้อมูลแบบเรียงซ้อนไปยังตารางที่สั่งซื้อ

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

ความท้าทายของ Excel BIนี้  เป็นโอกาสที่ดีในการฝึกฝนและฝึกฝนทักษะของคุณใน LuckyTemplates และ การใช้ เครื่องมือPower Query นอกจากนี้ยังมีเทคนิคดีๆ มากมายที่ใช้โดยผู้เชี่ยวชาญในสาขานั้น ซึ่งคุณสามารถเรียนรู้และค้นพบได้ มาเริ่มกันเลย!

สารบัญ

• การสร้างรายการ
• การกำหนดชื่อฟิลด์
• Power Query แปลงคอลัมน์เป็นตาราง
• Table.AddRankColumn
• Power Query แปลงคอลัมน์เป็นตารางที่จัดลำดับใหม่
• บทสรุป

การสร้างรายการ

คอลัมน์ที่เราจะสร้างจะต้องมีชื่อ ดังนั้นเราจะสร้างรายการสำหรับคอลัมน์นั้น เริ่มจากการเปิด  หน้าต่าง Advanced Editorและที่ด้านบน เราจะเพิ่มตัวแปรที่เรียกว่า list columns ( lCols = ) หลังจากนั้น เราจะป้อนรายการที่มีค่าทั้งหมดสำหรับตัวแปรนี้ตามที่ไฮไลต์ด้านล่าง แล้วกดเสร็จสิ้น

มีห้าค่าสำหรับแต่ละแถวของข้อมูล เราจะแยกออกโดยป้อน  List.Split  ในแถบสูตรตามด้วย  5 สำหรับขนาดหน้า จากนั้นเรากดเครื่องหมายถูกซึ่งจะส่งคืนรายการที่มีรายการซ้อนกัน

และเมื่อเราคลิกออกไปด้านข้างในพื้นที่สีขาวเพื่อดูตัวอย่างด้านล่างตาราง เช่นนี้:

การกำหนดชื่อฟิลด์

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

ภายในแถบสูตร เราจะเพิ่ม  List.Transform ที่จุดเริ่มต้นและแต่ละ  Record.FromList ที่ส่วนท้าย เราสามารถใช้ขีดล่างเพื่ออ้างถึง รายการและฟิลด์ภายในวงเล็บของ  Record.FromList

ดังนั้นเราจะเรียกตัวแปรในคอลัมน์รายการของเราว่าRecord.FromList( _, lCols ) )

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

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

Power Query แปลงคอลัมน์เป็นตาราง

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

Table.AddRankColumn

สำหรับการจัด อันดับประชากร เราจะใช้  ฟังก์ชัน M ที่ค่อนข้างใหม่ ที่เรียกว่า  Table.AddRankColumn ดังนั้นเราจะกด  fx ข้างเครื่องหมายถูกและเข้าสู่ฟังก์ชั่นนี้

เรายังคงสร้างฟังก์ชันของเราต่อ ไป โดยป้อนค่าข้อความที่เราจะเรียกว่า Rank by Population ภายในวงเล็บ เราตามด้วยเครื่องหมายจุลภาคและป้อนเกณฑ์การเปรียบเทียบของเรา ซึ่งก็คือคอลัมน์  2022 Population

Power Query แปลงคอลัมน์เป็นตารางที่จัดลำดับใหม่

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

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

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

จากนั้นเราจะต่อท้ายด้วยคอลัมน์รายการของเรา ( lCols ) และปิดด้วยวงเล็บ และสุดท้าย เรากดเครื่องหมายถูกนั้นเป็นครั้งสุดท้าย

เราได้ดำเนินการท้าทาย Power Query แบบเรียงซ้อนของ Excel BI สำเร็จแล้ว!

บทช่วยสอน Power Query แบบแยกคอลัมน์ LuckyTemplates: วิธีแยกเซลล์ Excel แบบหลายบรรทัดใน LuckyTemplates
ส่งออกรายการ Share Points ไปยัง Excel หรือไฟล์ CSV
การต่อท้ายหลายแผ่นใน Excel ไปยัง LuckyTemplates

บทสรุป

เราได้ดำเนินการ Stacked Data to Ordered Challenge จาก Excel BI เรียบร้อยแล้ว เราเปลี่ยนข้อมูลคอลัมน์เดียวให้เป็นตารางที่เหมาะสม ซึ่งข้อมูลแต่ละประเภทจะมีคอลัมน์อยู่แล้ว จากนั้นจะเพิ่มอันดับตามจำนวนประชากร

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

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