บทช่วยสอน Microsoft Power Query เกี่ยวกับวิธีแก้ไขปัญหาความกว้างคอลัมน์คงที่แบบผสม

Mudassir:สำหรับวันนี้ เรามีปัญหาที่น่าสนใจมากที่จะทำงานร่วมกัน ปัญหาของไฟล์นี้คือไฟล์ได้รับการแก้ไขโดยคั่นด้วยคอลัมน์ และฉันไม่ทราบวิธีแก้ปัญหานี้โดยใช้ Microsoft Power Query คุณสามารถดูวิดีโอทั้งหมดของบทช่วยสอนนี้ได้ที่ด้านล่างของบล็อกนี้

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

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

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

เมลิสซาจะแสดงให้เราเห็นว่าเธอแก้ปัญหาที่ซับซ้อนนี้ได้อย่างไร ฉันคิดว่าคนส่วนใหญ่จะจัดการกับปัญหาประเภทนี้และกำลังมองหาวิธีแก้ปัญหา

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

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

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

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

สารบัญ

• การเพิ่มคอลัมน์ดัชนี
• การสร้างรายการบัฟเฟอร์ใน Microsoft Power Query
• แยกส่วนหัวออกจากแถว
• การลบ Nulls และ Blanks ใน Microsoft Power Query
• แยกส่วน
• การตัดแต่งสตริงข้อความใน Microsoft Power Query
• บทสรุป

การเพิ่มคอลัมน์ดัชนี

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

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

เราจะต้องใช้กุญแจเพื่อนำสิ่งเหล่านั้นกลับมารวมกัน แต่ถ้าไม่มีคีย์อยู่ สิ่งที่ฉันต้องทำคือเพิ่มคอลัมน์ดัชนี ฉันจะคลิกที่ไอคอนตารางขนาดเล็ก เลือกAdd Index Columnจากนั้นเพิ่มFrom 0

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

จากนั้นเราต้องหาตำแหน่งที่ headers ของเราอยู่ ซึ่งเราสามารถทำได้ค่อนข้างง่าย เพราะ headers เหล่านั้นจะวนซ้ำๆ กันตลอดทั้งไฟล์

ในการเริ่มต้น ให้คัดลอกค่านี้:

เพิ่มข้อความค้นหาว่างใหม่ วางลงไป และเรียกสิ่งนี้ว่าHeaderID

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

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

ฉันไม่ต้องดำดิ่งลงไปในรหัส M เพื่อค้นหาสตริงที่เราต้องการ เราสามารถใช้สิ่งนี้เป็นพารามิเตอร์ได้

มาเปิดใช้งานการโหลดสำหรับสองข้อความค้นหานี้กันเถอะ

การสร้างรายการบัฟเฟอร์ใน Microsoft Power Query

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

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

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

ฉันจะเรียกสิ่งนี้ว่าBufferListและอ้างอิง Column1 หากต้องการโหลดลงในหน่วยความจำ ฉันจะเพิ่มขั้นตอนList.Buffer

นี่คือตัวแปรของฉันจนถึงด้านบนสุด ฉันสามารถอ้างอิงซ้ำแล้วซ้ำอีก

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

ฉันจะเขียนว่าถ้า Column1 เท่ากับ ID ส่วนหัวของเรา ฉันต้องการให้หมายเลขดัชนีเป็นโมฆะ

เป็นผลให้พบข้อความและส่งกลับ 5 และ 23

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

ในกรณีนี้ ฉันได้เพิ่มTable.FillDownและในสตริงข้อความ ฉันระบุว่าเราต้องการเติมคอลัมน์ใด (ส่วนหัว)

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

แยกส่วนหัวออกจากแถว

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

เราจะใช้ฟังก์ชันรายการสองสามรายการเพื่อดูแต่ละตำแหน่งและดูว่าตรงกับดัชนีหรือไม่

ฉัน จะเริ่มต้นด้วยคำสั่ง ifและใช้List.Containsเพื่อค้นหาตำแหน่งเฉพาะในBufferListและอ้างอิงการสืบค้นHeaderID

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

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

เราต้องระบุส่วนของแถวด้วย หากรายการไม่มีHeaderIDแต่มีDetailIDแสดงว่าเราอยู่ในส่วนแถว

ถ้าColumn1เป็นสตริงข้อความว่าง ฉันต้องการให้ว่างไว้ หากไม่เป็นเช่นนั้น ฉันต้องการให้มันเป็นโมฆะ

ฝ่ายค้านนี้ได้แถวส่วนหัวและส่งกลับ H จากนั้นพบแถวที่มีรายละเอียดและส่งกลับ R จากนั้นส่งคืน 0 สำหรับรายการทั้งหมดที่ใช้ร่วมกันภายในส่วนของแถวนั้น

ช่องว่างหรือค่าว่างเหล่านี้มีความสำคัญเนื่องจากช่วยให้คุณสามารถกรอกข้อมูลได้ การเติมข้อมูลจะไม่เคลื่อนผ่านเซลล์ว่างเหล่านั้น ดังนั้น เราจึงสามารถกำจัดเซลล์เหล่านั้นได้ในภายหลัง

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

ตอนนี้เรามีค่า H และ R ซ้ำแล้วซ้ำอีกในคอลัมน์นี้ ซึ่งหมายความว่าเราสามารถแยกส่วนหัวออกจากส่วนรายละเอียดได้

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

การลบ Nulls และ Blanks ใน Microsoft Power Query

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

แยกส่วน

เมื่อเราลบช่องว่างและค่าว่างเหล่านั้นออก เราจะเหลือทุกอย่างที่เราต้องการ ณ จุดนี้เราสามารถแยกส่วนได้ เราสามารถโฟกัสที่แถวส่วนหัวและเลือกออกได้เนื่องจากมีระยะห่างแยกต่างหากจากแถวรายละเอียดทั้งหมด (ซึ่งมีระยะห่างแยกต่างหากเช่นกัน)

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

ตอนนี้ฉันมีแถวส่วนหัวทั้งหมดที่นี่

ฉันสามารถเลือกColumn1ไปที่แถบสูตร เลือกSplit columnจากนั้นแยกตามตำแหน่ง

จากนั้นให้แบบสอบถามพลังงานค้นหาสิ่งนี้ออกมา มันจะแนะนำสองสามตำแหน่ง คลิกตกลงเพื่อรับตำแหน่งเหล่านั้น

สิ่งเดียวที่เราสนใจจากส่วนหัวคือรายการและหมายเลขงาน

ภายในแถบสูตรที่นี่ ฉันสามารถเปลี่ยนชื่อด้วยItem และ Job # สิ่งนี้จะช่วยฉันจากขั้นตอนการเปลี่ยนชื่อคอลัมน์อื่น

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

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

ดังนั้นเราจึงเปิดสิ่งนี้ขึ้นและยกเลิกการเลือกเครื่องหมายขีดกลางและรายการต่างๆ

ตอนนี้ส่วนหัวทั้งหมดเสร็จแล้ว

เราต้องทำกระบวนการเดียวกันสำหรับDetailIDด้วย ฉันจะต้องเปลี่ยนชื่อขั้นตอนเหล่านั้นเพื่อให้ฉันสามารถย้อนกลับไปในภายหลังได้ง่ายขึ้น

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

ฉันจะคัดลอกสิ่งนี้และเพิ่มลงในตัวกรองของฉัน คราวนี้ ฉันไม่ได้เลือก H แต่ฉันเลือก R

จากนั้นฉันจะเลือก Column1 ไปที่คอลัมน์ Split แยกตามตำแหน่ง จากนั้นให้ power query คิดออก

นี่คือสิ่งที่ Power Query แนะนำ มาลองกัน

นี้ดูดีทีเดียว แม้แต่แถวทั้งหมดก็แยกออกจากกันได้อย่างสมบูรณ์แบบ แน่นอนว่ามีที่ว่างมากมายเพราะเรามีการเยื้องนั้น

การตัดแต่งสตริงข้อความใน Microsoft Power Query

ผมจะเลือกคอลัมน์แรก จากนั้นกด Down + Shift เพื่อเลือกจนถึงคอลัมน์ 1.10 ไปที่การแปลงเลือกรูปแบบแล้วตัดแต่ง การตัดแต่งจะลบเฉพาะช่องว่างด้านหน้าหรือส่วนท้ายของสตริงเท่านั้น ไม่ใช่ระหว่างนั้น

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

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

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

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

แทนที่จะเป็น AllDetails ฉันต้องการให้ AllHeaders เป็นตารางเริ่มต้นที่ฉันต้องการรวมเข้าด้วยกัน

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

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

เราจะลบคอลัมน์สุดท้ายออกจากที่นี่ จากนั้นเราจะแก้ไขความกว้างของคอลัมน์คงที่แบบผสมใน Microsoft power query

บทสรุป

ในบทช่วยสอนนี้ เราได้นำเสนอวิธีแก้ไขปัญหาความกว้างคอลัมน์คงที่แบบผสมโดยใช้ Microsoft Power Query หากคุณชอบเนื้อหาที่ครอบคลุมในบทช่วยสอนนี้ โปรดอย่าลืมสมัครรับข้อมูลจากช่อง LuckyTemplates TV

เรามีเนื้อหาจำนวนมากออกมาตลอดเวลาจากตัวฉันเองและผู้สร้างเนื้อหาจำนวนมาก ซึ่งทุกคนทุ่มเทให้กับการปรับปรุงวิธีที่คุณใช้ LuckyTemplates และ Power Platform

เมลิสซ่า