แนวทางปฏิบัติที่ดีที่สุดเมื่อทํางานกับ Power Query

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

เลือกตัวเชื่อมต่อที่เหมาะสม

Power Query มีตัวเชื่อมต่อข้อมูลจํานวนมาก ตัวเชื่อมต่อเหล่านี้มีตั้งแต่แหล่งข้อมูล เช่น TXT, CSV และไฟล์ Excel ไปจนถึงฐานข้อมูล เช่น Microsoft SQL Server และซอฟต์แวร์ยอดนิยมในฐานะผลิตภัณฑ์บริการ (SaaS) เช่น Microsoft Dynamics 365 และ Salesforce ถ้าตัวเชื่อมต่อที่สร้างขึ้นตามวัตถุประสงค์ไม่พร้อมใช้งานในหน้าต่าง รับข้อมูล ให้ใช้ตัวเชื่อมต่อทั่วไปเช่น ODBC หรือ OLE DB

เลือกตัวเชื่อมต่อที่สร้างขึ้นตามวัตถุประสงค์สําหรับแหล่งข้อมูลของคุณเมื่อตัวเชื่อมต่อพร้อมใช้งาน ตัวอย่างเช่น ตัวเชื่อมต่อ SQL Server ให้ประสบการณ์รับข้อมูลดีกว่าตัวเชื่อมต่อ ODBC ทั่วไปเมื่อคุณเชื่อมต่อกับฐานข้อมูล SQL Server ตัวเชื่อมต่อ SQL Server ยังสนับสนุนคุณลักษณะด้านประสิทธิภาพการทํางาน เช่น Query Folding เมื่อต้องการเรียนรู้เพิ่มเติม ไปที่ภาพรวมของการประเมินผลคิวรีและการพับคิวรีใน Power Query

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

กรองข้อมูลตั้งแต่ต้นเพื่อปรับปรุงประสิทธิภาพการทํางาน

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

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

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

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

การดําเนินการที่มีราคาแพงเพื่อปรับปรุงประสิทธิภาพการทํางาน

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

การดําเนินการอื่น ๆ (เช่น ตัวกรอง) ไม่จําเป็นต้องอ่านข้อมูลทั้งหมดก่อนที่จะส่งกลับผลลัพธ์ใดๆ แต่ฟังก์ชันเหล่านี้ทํางานผ่านข้อมูลในสิ่งที่เรียกว่ารูปแบบ "การสตรีม" แทน ข้อมูล "สตรีม" โดย และผลลัพธ์จะถูกส่งกลับไปพร้อมกัน ในตัวแก้ไข Power Query การดําเนินการดังกล่าวจําเป็นต้องอ่านเพียงพอของข้อมูลต้นฉบับเพื่อรวบรวมตัวอย่าง

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

ใช้ชุดย่อยข้อมูลขณะพัฒนาคิวรี

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

ใช้ชนิดข้อมูลที่ถูกต้อง

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

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

สิ่งสําคัญคือคุณต้องทํางานกับชนิดข้อมูลที่ถูกต้องสําหรับคอลัมน์ของคุณเสมอ เมื่อคุณทํางานกับแหล่งข้อมูลที่มีโครงสร้าง เช่น ฐานข้อมูล ข้อมูลชนิดข้อมูลจะถูกนําเข้าจาก Schema ของตารางที่พบในฐานข้อมูล แต่สําหรับแหล่งข้อมูลที่ไม่มีโครงสร้างเช่นไฟล์ TXT และ CSV เป็นสิ่งสําคัญที่คุณต้องตั้งค่าชนิดข้อมูลที่ถูกต้องสําหรับคอลัมน์ที่มาจากแหล่งข้อมูลนั้น ตามค่าเริ่มต้น Power Query นําเสนอการตรวจหาชนิดข้อมูลอัตโนมัติสําหรับแหล่งข้อมูลที่ไม่มีโครงสร้าง คุณสามารถอ่านเพิ่มเติมเกี่ยวกับคุณลักษณะนี้และวิธีที่สามารถช่วยคุณใน ชนิดข้อมูลได้

โปรไฟล์และสํารวจข้อมูลของคุณ

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

Power Query มีเครื่องมือการสร้างโพรไฟล์ข้อมูลสามอย่าง:

คุณยังสามารถโต้ตอบกับคุณลักษณะเหล่านี้ซึ่งช่วยให้คุณเตรียมข้อมูลของคุณ

บันทึกงานของคุณเป็นเอกสาร

จัดทําเอกสารโซลูชัน Power Query โดยให้ขั้นตอน คิวรี ่และกลุ่มชื่อและคําอธิบายที่มีความหมาย รายละเอียดเหล่านี้ทําให้วัตถุประสงค์ของการแปลงข้อมูลแต่ละครั้งง่ายต่อการทําความเข้าใจและบํารุงรักษา

ในขณะที่ Power Query สร้างชื่อขั้นตอนสําหรับคุณโดยอัตโนมัติในบานหน้าต่างขั้นตอนที่ใช้ คุณยังสามารถเปลี่ยนชื่อขั้นตอนของคุณ หรือเพิ่มคําอธิบายไปยังขั้นตอนใด ๆ ได้

แยกคิวรีขนาดใหญ่ออกเป็นโมดูล

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

ตัวอย่างเช่น คิวรีต่อไปนี้มีเก้าขั้นตอนและมีขั้นตอน ตารางผสานกับราคา

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

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

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

จัดระเบียบคิวรีลงในกลุ่ม

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

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

พยายามตั้งชื่อที่สื่อความหมายให้กับกลุ่มของคุณซึ่งเหมาะสมกับคุณและกรณีของคุณ

คิวรีที่ป้องกันในอนาคต

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

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

การแปลงต่อไปนี้สามารถช่วยให้คิวรียังคงยืดหยุ่นต่อการเปลี่ยนแปลงได้:

ใช้พารามิเตอร์

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

• อาร์กิวเมนต์ขั้นตอน: ใช้พารามิเตอร์เป็นอาร์กิวเมนต์ของการแปลงหลายรายการซึ่งขับเคลื่อนจากส่วนติดต่อผู้ใช้

  

• อาร์กิวเมนต์ฟังก์ชันแบบกําหนดเอง: สร้างฟังก์ชันใหม่จากคิวรี และพารามิเตอร์การอ้างอิงเป็นอาร์กิวเมนต์ของฟังก์ชันแบบกําหนดเองของคุณ

  

ประโยชน์หลักของการสร้างและการใช้พารามิเตอร์คือ:

• มุมมองแบบรวมศูนย์ของพารามิเตอร์ทั้งหมดของคุณผ่านหน้าต่างจัดการพารามิเตอร์

  

• การใช้พารามิเตอร์ในหลายขั้นตอนหรือหลายคิวรีกลับมาใช้ใหม่

• ทําให้การสร้างฟังก์ชันแบบกําหนดเองเป็นไปอย่างง่ายดายและตรงไปตรงมา

คุณยังสามารถใช้พารามิเตอร์ในอาร์กิวเมนต์บางอย่างของตัวเชื่อมต่อข้อมูล ตัวอย่างเช่น คุณสามารถสร้างพารามิเตอร์สําหรับชื่อเซิร์ฟเวอร์ของคุณเมื่อเชื่อมต่อกับฐานข้อมูล SQL Server ของคุณได้ จากนั้นคุณสามารถใช้พารามิเตอร์นั้นภายในกล่องโต้ตอบฐานข้อมูล SQL Server

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

สร้างฟังก์ชันที่นํามาใช้ใหม่ได้

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

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

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

คุณเริ่มต้นด้วยการมีพารามิเตอร์ที่มีค่าที่ทําหน้าที่เป็นตัวอย่าง

จากพารามิเตอร์นั้น คุณสร้างคิวรีใหม่ที่คุณนําการแปลงที่คุณต้องการไปใช้ สําหรับกรณีนี้ คุณต้องการแยกรหัส PTY-CM1090-LAX ออกเป็นหลายคอมโพเนนต์:

• จุดเริ่มต้น = PTY
• ปลายทาง = LAX
• สายการบิน = CM
• รหัสเที่ยวบิน = 1090

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

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