Thep Excel
SUBSTITUTEWITHINDEXแทนคอลัมน์ร่วมด้วยดัชนี (0-based) เพื่อทำ mapping
คืนตารางที่เป็นผลแบบ semi-join ระหว่าง 2 ตาราง พร้อมแทนคอลัมน์ร่วมด้วยดัชนี 0-based ซึ่งคำนวณจากลำดับแถวของตารางอ้างอิงหลังจัดเรียง
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| Table | table | ตารางหลักที่ต้องการปรับ โดยจะถูกคืนกลับมาในรูปแบบตารางผลลัพธ์ | |
| Name | string | ชื่อคอลัมน์ดัชนีใหม่ที่จะถูกเพิ่มลงในผลลัพธ์ | |
| SemiJoinIndexTable | table | ตารางอ้างอิงสำหรับสร้างดัชนีและใช้จับคู่กับ Table (หลังจัดเรียงตาม Expression/Order ที่ระบุ) | |
| Expression | expression | นิพจน์สำหรับ “จัดลำดับ” ตารางอ้างอิง (SemiJoinIndexTable); สามารถระบุได้หลายตัว | |
| [Order]ไม่บังคับ | keyword | ASC | ทิศทางการจัดเรียงของแต่ละ Expression เช่น ASC หรือ DESC |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
SUBSTITUTEWITHINDEX เป็นฟังก์ชันเชิงเครื่องมือที่คืน “ตารางผลลัพธ์แบบจับคู่บางส่วน (semi-join)” ระหว่าง 2 ตาราง โดยคอลัมน์ที่ใช้จับคู่ร่วมกันจะถูกแทนด้วยคอลัมน์ดัชนีแบบ 0-based (เริ่มจาก 0) ตามลำดับแถวของตารางอ้างอิงที่ถูกจัดเรียงตามนิพจน์ที่กำหนด ฟังก์ชันนี้พบได้บ่อยในงานที่ต้องทำ mapping ค่าจากคิวรีไปวางในแกนคอลัมน์ของ visual (เช่น matrix)
How it works
ทำ mapping ค่าไปยังแกนคอลัมน์ใน matrix/visual
ใช้สร้าง “หมายเลขคอลัมน์” จากรายการหัวคอลัมน์ที่จัดลำดับแล้ว เพื่อวางค่าลงตำแหน่งที่ถูกต้อง
แทนคีย์หลายคอลัมน์ด้วยดัชนีเพื่อทำงานต่อ
เมื่อมีคอลัมน์ร่วมหลายตัว การสร้างดัชนีช่วยให้การจัดรูปตาราง/ส่งต่อข้อมูลทำได้ง่ายขึ้น
Examples
ตัวอย่างที่ 1: แนวคิดสร้างคอลัมน์ดัชนีจากตารางอ้างอิงแนวคิด (โครง) = SUBSTITUTEWITHINDEX( <ResultTable>, "ColIndex", <IndexTable>, <SortExpression>, ASC )
จุดสำคัญคือ IndexTable ต้องเป็นชุดค่าที่กำหนดลำดับไว้ชัดเจน แล้วจึงนำไปแทนคอลัมน์ร่วมด้วยดัชนี
ตัวอย่างที่ 2: ใช้ร่วมกับ SUMMARIZECOLUMNS เพื่อเตรียม ResultTable (แนวคิด)R (แนวคิด) = SUMMARIZECOLUMNS( 'Product'[แบรนด์], 'Date'[ปี], "Amount", [Sales Amount] ) Result (แนวคิด) = SUBSTITUTEWITHINDEX( R, "ColIndex", <IndexTable…
มักใช้ในงาน mapping ค่าไปวางตามลำดับหัวคอลัมน์ที่กำหนด
ตัวอย่างที่ 3: ระวังเรื่อง data lineage/ชื่อคอลัมน์แนวคิด: ถ้าตารางอ้างอิงมีคอลัมน์ชื่อชนกับคอลัมน์ที่มี lineage อาจเกิดข้อผิดพลาด ให้พิจารณาใช้ SELECTCOLUMNS เพื่อสร้างคอลัมน์ใหม่จากนิพจน์แทน
ในงานจริงมักต้องจัดรูปคอลัมน์ให้ชัดเจนก่อนทำการจับคู่/แทนดัชนี
FAQs
SUBSTITUTEWITHINDEX เริ่มดัชนีที่เลขอะไร?+
เริ่มที่ 0 (0-based index) เช่น 0, 1, 2, …
ฟังก์ชันนี้เหมาะกับการเขียน measure ทั่วไปไหม?+
โดยมากเป็นฟังก์ชันเชิงเครื่องมือที่ใช้ในงานจัดรูปผลลัพธ์/การทำงานภายในมากกว่างานโมเดลปกติ แต่สามารถใช้ได้ถ้าเข้าใจโครงสร้างข้อมูลและการจับคู่คอลัมน์
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ SUBSTITUTEWITHINDEX จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
NATURALINNERJOIN ทำ inner join ระหว่าง LeftTable และ RightTable โดยใช้คอลัมน์ชื่อเดียวกันเป็นคีย์การจับคู่ คืนผลลัพธ์เฉพาะแถวที่จับคู่ได้ทั้งสองตาราง และมักใช้ร่วมกับ SELECTCOLUMNS เพื่อจัดชื่อคอลัมน์ให้ตรงกัน
NATURALLEFTOUTERJOIN ทำ left outer join ระหว่าง LeftTable และ RightTable โดยใช้คอลัมน์ชื่อเดียวกันเป็นคีย์ เก็บทุกแถวของตารางซ้ายไว้ทั้งหมด และเติมคอลัมน์จากตารางขวาเมื่อจับคู่ได้ (ไม่จับคู่จะเป็น BLANK)
สร้างตารางใหม่โดยเลือกเฉพาะคอลัมน์ที่ต้องการจากตารางต้นฉบับและเพิ่มคอลัมน์ที่คำนวณได้ ต่างจาก ADDCOLUMNS ตรงที่ SELECTCOLUMNS เริ่มจากตารางว่างแล้วเพิ่มเฉพาะคอลัมน์ที่ระบุ ทำให้สามารถปรับโครงสร้างตารางและเลือกข้อมูลที่จำเป็นได้อย่างยืดหยุ่น
SUMMARIZE สร้างตารางสรุปโดยจัดกลุ่มข้อมูลตามคอลัมน์ที่กำหนด คล้าย GROUP BY ใน SQL หรือ Pivot Table ใน Excel คืนค่าตารางที่มีหนึ่งแถวต่อหนึ่ง unique combination ของคอลัมน์ที่เลือก สามารถอ้างถึงคอลัมน์จาก related table ได้โดยตรงโดยไม่ต้องใช้ RELATED มักใช้สร้าง virtual table ใน measure เพื่อทำ intermediate calculations ก่อนใช้ iterator functions อย่าง SUMX, AVERAGEX คำนวณต่อ ⚠️ Best Practice: ใช้ ADDCOLUMNS ครอบ SUMMARIZE แทนการใส่ extension columns ตรงๆ เพื่อ performance และ filter context control ที่ดีกว่า สำหรับ calculated table แนะนำใช้ SUMMARIZECOLUMNS แทนเนื่องจากมี performance ดีกว่าอย่างมาก
SUMMARIZECOLUMNS เป็นฟังก์ชันหลักสำหรับสร้างตารางสรุปผล โดยจัดกลุ่มตามคอลัมน์ที่กำหนด พร้อมเพิ่มคอลัมน์คำนวณจากเมเชอร์ และสามารถใส่เงื่อนไขกรองได้ทันที
TREATAS เป็นเสมือนการสร้าง "virtual relationship" โดยไม่ต้องแก้โมเดล ช่วยให้ส่งเงื่อนไขข้ามตารางที่ไม่เชื่อมกัน หรือเมื่อต้องการส่งหลายคีย์พร้อมกัน
VALUES ดึงค่าที่ไม่ซ้ำจากคอลัมน์ หรือดึงแถวทั้งหมดจากตาราง โดยรวมแถว BLANK ที่เกิดจากข้อผิดพลาดของความสัมพันธ์ (Referential Integrity Violations) ส่วน DISTINCT จะไม่รวมแถว BLANK นี้
INDEX ดึงแถวจากตำแหน่งที่ระบุ (Position) ภายในกลุ่มข้อมูลที่เรียงแล้ว โดยสามารถใช้ ORDERBY และ PARTITIONBY เพื่อควบคุมลำดับและการแบ่งกลุ่ม เหมาะสำหรับงาน window function เช่นดึงค่าอันดับที่ N
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่