รหัส M ในคอลัมน์แบบกำหนดเองของ Power Query | พลัง BI

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

ความสามารถในการสร้างคอลัมน์ใน Power Query โดยใช้รหัส M ช่วยเพิ่มความสามารถในการแก้ปัญหาที่ซับซ้อนมากขึ้น

ก่อนที่เราจะเข้าสู่แนวทางแก้ไข เรามาทบทวนลักษณะของปัญหากันก่อน ฉันได้รับชุดข้อมูลขนาดใหญ่จาก Kaggle ซึ่งเป็นข้อมูลออสการ์ปี 93 เราต้องการดึงเอานักแสดงหญิงที่ดีที่สุด ผู้ชนะ และโดยเฉพาะอย่างยิ่งผู้หญิงที่ได้รับรางวัลนักแสดงหญิงยอดเยี่ยมมากกว่าหนึ่งรางวัล จากนั้นจึงแสดงภาพโดยใช้ Queryon Timeline ในปีที่พวกเธอได้รับรางวัลที่สอง

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

และในหนึ่งนี้มีสามคน

สิ่งแรกคือการเรียนรู้หรือพัฒนาแนวทางปฏิบัติด้วยการสร้าง Group By และ All Rows เป็นสิ่งที่ฉันให้ความสำคัญจริงๆ เพราะมันช่วยแก้ปัญหาต่างๆ ได้ค่อนข้างเรียบง่าย และบ่อยครั้งก็ขจัดความจำเป็นในการใช้ DAX ที่ซับซ้อนมากๆ

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

ตามบทหนึ่งในหนังสือของ Gil Raviv เกี่ยวกับ Power Query ประมาณ 40% ของปัญหาใน Power Query สามารถแก้ไขได้ผ่านทางอินเทอร์เฟซผู้ใช้เท่านั้น แต่ด้วยการเพิ่มโค้ด M พื้นฐานและความสามารถในการสร้างคอลัมน์แบบกำหนดเอง ไม่ใช่เรื่องยาก คุณสามารถเพิ่มเปอร์เซ็นต์นั้นได้มากกว่า 80% คุณสามารถเพิ่มความสามารถในการแก้ปัญหาของคุณเป็นสองเท่าด้วยทักษะพื้นฐานง่ายๆ

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

สารบัญ

• แนวทางการแก้ปัญหาประจำสัปดาห์ #9
• การใช้ Group By In Power Query
• การรวม Table.AddIndexColumn M Code
• การแสดงภาพโดยใช้ Queryon Timeline Custom Visual
• บทสรุป

แนวทางการแก้ปัญหาประจำสัปดาห์ #9

ในการคิดเกี่ยวกับปัญหานี้ ผมต้องการทดลองทางความคิด จะเกิดอะไรขึ้นถ้าฉันให้กระดาษ 93 แผ่นแทนไฟล์ CSV (หนึ่งแผ่นที่มีผู้ได้รับรางวัลนักแสดงนำหญิงยอดเยี่ยมประจำปี) วิธีแก้ปัญหาจะเป็นอย่างไร

โซลูชันของคุณอาจมีลักษณะเหมือนกับที่ฉันได้รับด้านล่าง

ฉันคิดว่าทุกคนจะมีวิธีแก้ปัญหาที่คล้ายกัน เมื่อคุณมีโซลูชันเชิงแนวคิดแล้ว ก็เป็นเพียงเรื่องของการค้นหาวิธีทำให้ Power Query ดำเนินการแต่ละขั้นตอนเหล่านั้น

ดังนั้น ฉันจะใช้สิ่งนี้เป็นโครงร่างกว้างๆ แล้วดูว่าพวกเขานำโซลูชันเชิงแนวคิดนี้ไปใช้ใน Power Query ได้อย่างไร แนวทางที่เราจะใช้คือสิ่งที่ Melissa ซึ่งเป็นกูรูด้าน Power Query ของเราเรียกว่าเรียนรู้โดย Google

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

การใช้ Group By In Power Query

จากนั้น เราสามารถเข้าสู่ Power Query และเริ่มดำเนินการโซลูชันของเราได้ สิ่งที่ฉันทำคือในตารางรางวัลออสการ์นี้ ฉันเพิ่งเปิดดูและดึงไฟล์ CSV และกรองลงไป เหลือแต่ผู้ชนะรางวัลนักแสดงนำหญิงยอดเยี่ยมในแต่ละปี ดังนั้นเราจึงมีเพียง 93 แถวของชื่อและปีของภาพยนตร์

ถ้าเราเข้าไปใน Power Query และไปที่ตารางนั้น สิ่งแรกที่เราต้องการทำคือGroup Byนี้

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

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

เราจะมีอีกคอลัมน์ชื่อCountและเราต้องการมันเมื่อเรากำลังจะนับแถว เราต้องการหาจำนวนสลิปในแต่ละกอง จากนั้นเราก็คลิกตกลง

เราสามารถคลิกที่พื้นที่สีขาวในตารางนี้ และเราจะได้สลิปเพียงใบเดียว

แต่สำหรับผู้ที่มีจำนวนหลายรายการ คุณจะเห็นว่าเราได้รับบันทึกทั้งหมด

ต่อไปเราต้องการกรองรายการ (1) โดยพื้นฐานแล้วเราจะทิ้งกองทั้งหมดที่มีเพียงชื่อเดียว เลือก 1 แล้วคลิกตกลง

และตอนนี้เราก็เหลือผู้ได้รับรางวัลมากมายถึง 14 คน และตอนนี้เราก็เหลือผู้ได้รับรางวัลมากมายถึง 14 คน ถ้าเรารับและขยายออกไป เราสามารถเอาName ออกได้ เพราะเรามี Name อยู่ที่นี่แล้ว และเก็บyear_filmไว้

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

ตอนนี้เราจะไปยังขั้นตอนต่อไปของโมเดลแนวคิดของเรา ซึ่งก็คือการจัดเรียงสลิปในแต่ละกลุ่มตามวันที่จากน้อยไปมาก จากนั้นเลือกวันที่สองที่เกิดขึ้นภายในแต่ละกลุ่ม

การเรียงลำดับเป็นเรื่องง่าย เราสามารถกลับไปที่ Power Query และทำการเรียงลำดับจากน้อยไปมากตามนักแสดงหญิงและตามวันที่

และจะแสดงให้คุณเห็นหนึ่งและสอง

เราก็เรียงกันดีแล้ว ตอนนี้อยากได้แบบดัชนีหรืออันดับของแต่ละกอง แล้วเอาอันที่ 2 ในแต่ละกองได้

การรวม Table.AddIndexColumn M Code

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

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

ไปที่ Power Query แล้วจัดกลุ่มตารางของเราใหม่ตามนักแสดงหญิง

ด้วยเหตุนี้ เราจึงได้ผู้ชนะ 14 กลุ่มตามตาราง และถ้าเราไปที่ตารางใดในตารางเหล่านี้ที่มีคำว่า Frances McDormand เราจะเห็นว่าตารางนั้นเรียงตามวันที่

จากนั้น นี่คือขั้นตอนสำคัญ ซึ่งก็คือการเพิ่ม คอลัมน์แบบกำหนดเองของ Power Queryและป้อนโค้ด M ของเรา เราจะมีTable.AddIndexColumn จากนั้นเพิ่มฟิลด์AllData เราจะเรียกคอลัมน์ใหม่ของเรา (เป็นข้อความ) ในที่นี่ว่าIndexและเราจะเริ่ม Index ที่หนึ่ง (1) และเพิ่มทีละ (1)

และเราได้ดัชนีที่สมบูรณ์แบบตรงนี้

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

สุดท้าย เราสามารถทำในสิ่งที่เราทำกับ Count ซึ่งจะกรองสิ่งที่อยู่ในการชนะครั้งที่สองออกไป

และนั่นเราไป มีทางออกของเรา

การแสดงภาพโดยใช้ Queryon Timeline Custom Visual

มีอีก 2-3 สิ่งที่เราอยากทำที่นี่ ซึ่งทั้งหมดนี้ขึ้นอยู่กับการนำQueryon Visual ไปใช้งาน เรากำลังจะทำการรวมและเพิ่ม URL ของรูปภาพ

ดังนั้นเราจะรวมข้อความค้นหา ดึง ตาราง นักแสดงหญิงขึ้น มา แล้วเราจะรวมในชื่อ เราได้ 14 ถึง 14 แถวที่ตรงกัน ซึ่งถือว่าดี

จากนั้นในคอลัมน์นักแสดงหญิง เราจะขยายออก และเราจะขยายไปยัง URL

และตอนนี้เรามีคอลัมน์นี้พร้อม URL

มีอีกสิ่งหนึ่งที่มาพร้อมกับความรู้เกี่ยวกับ Queryon Timeline Queryon ทำงานได้ดี ที่สุดเมื่อต้องจัดการกับวันที่เต็มแทนที่จะเป็นปี นี่คือสิ่งที่เพิ่งมาพร้อมกับการทดลอง แต่เราสามารถใช้ คอลัมน์ Yearและสร้างColumn From Examplesได้ สิ่งที่เราต้องการทำที่นี่ก็แค่เปลี่ยนปีให้เป็นวันที่

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

ตอนนี้ มาดูแง่มุมของการสร้างภาพข้อมูลกัน และผมต้องการเน้นการเข้ามาของหนึ่งในสมาชิก LuckyTemplates, Jose ซึ่งทำงานได้อย่างยอดเยี่ยมในการแสดงภาพ

ย้อนกลับไปที่ Power Query สำหรับการแสดงภาพ เป็นเรื่องที่ควรค่าแก่การกล่าวถึงว่าMicrosoft เพิ่งนำการเปลี่ยนแปลงบางอย่างไปใช้ในการรับรองวิชวลแบบกำหนดเอง ในส่วนหนึ่งของการรับรองนั้น พวกเขาได้เข้มงวดกับการใช้รูปภาพ URL ภายนอก

หากคุณใช้ Queryon Timeline เวอร์ชันที่อยู่ใน App Source (เวอร์ชัน 1.05) จริงๆ คุณจะไม่สามารถดึงอิมเมจ URL เหล่านี้ได้ ดังนั้น ตอนนี้คุณมีตัวเลือกในการใช้ 1.04 นี้ โดยเก็บอิมเมจ URL ซึ่งมีวิธีฝังอิมเมจไบนารี 64 ลงในไฟล์ PBIX โดยตรงบนไซต์

ตอนนี้ฉันพูดถึงการเปลี่ยนปีนั้นเป็นฟิลด์วันที่ ดังนั้นเราจำเป็นต้องเชื่อมต่อข้อมูลนั้นกับโมเดลข้อมูลของเรา

ลองเปลี่ยนจากหนึ่งเป็นหนึ่งเป็นหนึ่งเป็นหลายและ ทิศทาง เดียวในตัวกรองข้าม แล้วทำให้ความสัมพันธ์ นี้ เป็นctive

ถัดไป สำหรับช่อง URL รูปภาพ เราต้องแน่ใจว่าในหมวดหมู่ข้อมูล ไม่แสดงเป็น Uncategorized แต่แสดงเป็นURL รูปภาพ

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

ตาราง Power Query: วิธีผสานคอลัมน์แบบไดนามิก
วิธีผสาน Query ใน LuckyTemplates
แยกค่าจากบันทึกและรายการใน Power Query

บทสรุป

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

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

ดีที่สุด!