ข้อจำกัดของ Column From Examples และแนวทางแก้ไข

Column From Examples ใน Power Query เป็นเครื่องมือที่ช่วยให้ผู้ใช้สร้างคอลัมน์ใหม่ได้โดยไม่ต้องเขียนโค้ด M Language ซึ่งช่วยให้การแปลงข้อมูลทำได้ง่ายและสะดวกขึ้น โดยเฉพาะสำหรับการ แยกข้อความ, ดึงค่าจากวันที่, หรือ รวมข้อมูลจากหลายคอลัมน์ ซึ่งเราได้กล่าวถึงใน บทความ เริ่มต้นใช้งาน Column From Examples ใน Power Query – แปลงข้อมูลง่าย ๆ โดยไม่ต้องใช้โค้ด และบทความ เทคนิคการใช้ Column From Examples เพื่อสร้างคอลัมน์ตัวอย่างโดยกำหนดค่าเอง

อย่างไรก็ตาม ฟีเจอร์นี้มีข้อจำกัด เช่น ไม่สามารถใช้งานกับข้อมูลที่ต้องอ้างอิงค่าจากหลายแถว หรือข้อมูลที่ต้องใช้เงื่อนไขซับซ้อน หากต้องการคำนวณที่ซับซ้อนกว่านี้ อาจต้องใช้ M Language ใน Power Query หรือ DAX ซึ่งทำงานใน Data Model ของ Power BI

ในบทความนี้ เราจะสำรวจข้อจำกัดหลักของ Column From Examples พร้อมนำเสนอแนวทางแก้ไข เช่น Merge Queries, Custom Column พร้อม M Language และ DAX ใน Power BI ซึ่งผู้ใช้สามารถเลือกใช้ตามความถนัดหรือให้เหมาะกับลักษณะของงาน

ตัวอย่างที่ไม่สามารถกำหนดค่าตัวอย่างใน Column From Examples ได้

1. ข้อมูลที่ต้องใช้ค่าจากหลายแถว (Row-dependent Data)

ตัวอย่างข้อมูล

วันที่	ยอดขาย	ยอดขายสะสม (ต้องการสร้าง)
01/01	100	100
02/01	150	250
03/01	200	450
04/01	50	500

ปัญหา:

Column From Examples ทำงานในระดับ แถวต่อแถว (Row-by-Row) เท่านั้น ไม่สามารถอ้างอิงค่าจากแถวก่อนหน้าเพื่อคำนวณผลรวมสะสมได้

แนวทางแก้ไข:

• ใช้ Custom Column และเขียนโค้ด M Language เช่น

List.Sum(List.FirstN(#”Previous Step”[ยอดขาย], [Index]))

• ใช้ DAX (ใน Data Model ของ Power BI)

ยอดขายสะสม = CALCULATE(SUM(ตาราง[ยอดขาย]), FILTER(ALL(ตาราง), ตาราง[วันที่] <= EARLIER(ตาราง[วันที่])))

2. ข้อมูลที่ต้องรวมค่าจากหลายแถวเป็นหนึ่งค่า (Aggregated Data)

ตัวอย่างข้อมูล

แผนก	ชื่อพนักงาน	รวมชื่อพนักงาน (ต้องการสร้าง)
IT	สมชาย	สมชาย, สมหญิง, เดชา
IT	สมหญิง	สมชาย, สมหญิง, เดชา
IT	เดชา	สมชาย, สมหญิง, เดชา
HR	อารีย์	อารีย์, สุดา
HR	สุดา	อารีย์, สุดา

ปัญหา:

Column From Examples ไม่สามารถดึงค่าหลายแถวที่อยู่ในกลุ่มเดียวกันมาเชื่อมต่อกันในคอลัมน์ใหม่

แนวทางแก้ไข:

• ใช้ Group By ใน Power Query
  • ไปที่ Transform → Group By → เลือก แผนก เป็นกลุ่ม
  • ใช้ All Rows แล้วเพิ่ม Custom Column เพื่อรวมค่าชื่อพนักงาน
    
• ใช้ DAX (ใน Data Model ของ Power BI)

รวมชื่อพนักงาน = CONCATENATEX(VALUES(ตาราง[ชื่อพนักงาน]), [ชื่อพนักงาน], “, “)

3. ข้อมูลที่ต้องอ้างอิงค่าจากอีกตารางหนึ่ง (Lookup Data)

ตัวอย่างข้อมูล

ตารางที่ 1: รายการคำสั่งซื้อ

Order ID	รหัสสินค้า	ราคาขาย (ต้องการสร้าง)
1001	P001	300
1002	P002	450
1003	P003	150

ตารางที่ 2: ราคาสินค้า

รหัสสินค้า	ราคา
P001	300
P002	450
P003	150

ปัญหา:

Column From Examples ไม่สามารถดึงข้อมูลจากอีกตารางเพื่อเติมค่าในคอลัมน์ใหม่

แนวทางแก้ไข:

• ใช้ Merge Queries ใน Power Query
  • ไปที่ Home → Merge Queries
  • เชื่อมตารางตาม รหัสสินค้า
  • เลือกดึงคอลัมน์ ราคา มาใช้งาน
• ใช้ DAX (Power BI)
  • ราคาขาย = RELATED(ตารางราคาสินค้า[ราคา])

4. ข้อมูลที่ต้องแยกค่าโดยใช้เงื่อนไขซับซ้อน

ตัวอย่างข้อมูล

รายละเอียดสินค้า	รหัสสินค้า (ต้องการสร้าง)
[P001] โทรศัพท์มือถือ	P001
(P002) โน้ตบุ๊ก	P002
P003 – หูฟังบลูทูธ	P003

ปัญหา:

Column From Examples อาจไม่สามารถแยกเฉพาะรหัสสินค้าออกมาได้อย่างถูกต้องเสมอ เนื่องจากรูปแบบข้อความในแต่ละแถวไม่เหมือนกัน

แนวทางแก้ไข:

• ใช้ Custom Column + M Language
  • Text.BetweenDelimiters([รายละเอียดสินค้า], “[“, “]”)
  • Text.BeforeDelimiter([รายละเอียดสินค้า], ” “)
• ใช้ DAX (Power BI) สำหรับแยกข้อความ
  • รหัสสินค้า = LEFT(ตาราง[รายละเอียดสินค้า], FIND(” “, ตาราง[รายละเอียดสินค้า])-1)

5. ข้อมูลที่ต้องใช้ฟังก์ชันพิเศษ เช่น การเข้ารหัสหรือแปลงข้อมูลแบบเฉพาะเจาะจง

ตัวอย่างข้อมูล

หมายเลขโทรศัพท์	หมายเลขที่เข้ารหัส (ต้องการสร้าง)
0812345678	XXXXXX5678
0911112222	XXXXXX2222

ปัญหา:

Column From Examples ไม่สามารถกำหนดให้ซ่อนบางส่วนของข้อมูล (เช่น แสดงเฉพาะ 4 ตัวท้าย) ได้โดยอัตโนมัติ

แนวทางแก้ไข:

• ใช้ Custom Column + M Language
  • “XXXXXX” & Text.End([หมายเลขโทรศัพท์], 4)
• ใช้ DAX (Power BI) สำหรับ Masking ข้อมูล
  • หมายเลขที่เข้ารหัส = “XXXXXX” & RIGHT(ตาราง[หมายเลขโทรศัพท์],4)

จากแนวทางแก้ปัญหามีกล่าวถึงการใช้ Power Query และ DAX ในการแก้ปัญหา ซึ่ง Power Query และ DAX อยู่คนละส่วนเครื่องมือกันดังนี้

• Power Query ใช้ M Language สำหรับการแปลงและจัดรูปแบบข้อมูลก่อนโหลดเข้า Data Model
• DAX ใช้ใน Data Model ของ Power BI สำหรับคำนวณค่า เช่น Measures และ Calculated Columns

บทสรุป

แม้ว่า Column From Examples จะช่วยให้สร้างคอลัมน์ใหม่ได้ง่ายโดยไม่ต้องเขียนโค้ด แต่ก็มีข้อจำกัดในบางกรณี เช่น ไม่สามารถใช้กับข้อมูลข้ามแถว หรือดึงค่าจากอีกตารางได้

หากพบข้อจำกัดเหล่านี้ ผู้ใช้สามารถเลือกแนวทางที่เหมาะสม เช่น Merge Queries, Custom Column พร้อม M Language, หรือ DAX ใน Power BI เพื่อให้การประมวลผลและการแปลงข้อมูลเป็นไปตามที่ต้องการ

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

หลักสูตรที่เกี่ยวข้อง

Microsoft 365 for End User Training
Microsoft SharePoint Online For Front-End
Microsoft Power Apps (Canvas App) Workshop
Microsoft Power Automate (Cloud)
Microsoft Copilot Studio (Former Power Virtual Agent)
Power BI for Beginners

สนใจสอบถามรายละเอียดเพิ่มเติม

Tel: 021198405

Line: @M365th

Email: Sales@m365.co.th