Thep Excel
Excel.CurrentWorkbookเข้าถึง Table และ Named Range ใน Workbook ปัจจุบัน
Excel.CurrentWorkbook เป็น function ที่ใช้เข้าถึง Table, Named Range และ Dynamic Array ทั้งหมดที่อยู่ใน Excel workbook ปัจจุบัน โดย return เป็น table ที่มี metadata และเนื้อหาของแต่ละ object ทำให้สามารถอ้างอิงข้อมูลภายใน workbook ได้อย่างยืดหยุ่น เหมาะสำหรับการสร้าง query ที่ portable และไม่ต้องพึ่งพา file path
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| [(ไม่มี parameter)]ไม่บังคับ | none | N/A | ฟังก์ชันนี้ไม่รับพารามิเตอร์ใดๆ เรียกใช้งานโดยใส่วงเล็บว่างเปล่า () ระบบจะทำงานโดยอัตโนมัติในการสแกนหาออบเจ็กต์ทั้งหมดที่มีชื่อภายในเวิร์กบุ๊กปัจจุบัน |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
Excel.CurrentWorkbook เป็นฟังก์ชันพื้นฐานที่สำคัญมากใน Power Query สำหรับการเข้าถึงข้อมูลภายใน Excel workbook ที่กำลังทำงานอยู่
ฟังก์ชันนี้ช่วยให้เราสามารถดึงข้อมูลจาก Table, Named Range และ Dynamic Array ที่ถูกกำหนดไว้ในไฟล์ โดยไม่ต้องระบุเส้นทางไฟล์หรือชื่อไฟล์แบบเต็ม
ทำให้การเขียนคิวรีสะดวกและสามารถนำไปใช้ในไฟล์อื่นได้ง่าย ไม่ต้องมานั่งแก้ไข file path ทุกครั้งที่ copy query ไปใช้ที่อื่น 😎
ความสามารถและการทำงานของฟังก์ชัน
ฟังก์ชันนี้ทำงานโดยการสแกนหาออบเจ็กต์ที่มีชื่อทั้งหมดภายในเวิร์กบุ๊กและส่งกลับมาเป็นตารางที่มีรายละเอียดของแต่ละออบเจ็กต์
ซึ่งแตกต่างจากฟังก์ชัน Excel.Workbook ที่ใช้เปิดไฟล์จากภายนอกและสามารถเข้าถึงเวิร์กชีตได้ ในขณะที่ฟังก์ชันนี้จะทำงานภายในเวิร์กบุ๊กเดียวกันเท่านั้นและไม่แสดงเวิร์กชีตออกมา
การใช้งานฟังก์ชันนี้มีประโยชน์อย่างมากในการสร้างคิวรีที่ต้องการรวมข้อมูลจากหลายแหล่งภายในไฟล์เดียวกัน หรือเมื่อต้องการสร้างระบบที่อ้างอิงตารางหรือช่วงข้อมูลแบบยืดหยุ่น
ส่วนตัวผมชอบใช้ฟังก์ชันนี้เวลาต้องรวมข้อมูลจากหลาย Table ที่มี naming convention เดียวกัน เช่น Sales_Q1, Sales_Q2, Sales_Q3 ใช้ร่วมกับ Table.SelectRows กรองตามชื่อ แล้ว combine ข้อมูลเข้าด้วยกัน สะดวกมากครับ 💡
โครงสร้างข้อมูลที่ได้รับ
ตารางที่ฟังก์ชันส่งกลับมาจะประกอบด้วยคอลัมน์สำคัญสามคอลัมน์:
- Name – ชื่อของออบเจ็กต์
- Content – ข้อมูลจริงในรูปแบบตาราง
- Kind – ประเภท (Table หรือ Range)
การเข้าถึงข้อมูลเหล่านี้สามารถทำได้โดยการใช้ฟังก์ชันจัดการตารางต่างๆ โดยเฉพาะ Table.SelectRows ที่สามารถเลือกเฉพาะออบเจ็กต์ที่ต้องการได้อย่างแม่นยำ
การทำงานร่วมกับฟังก์ชันกรองแถวช่วยให้สามารถระบุตารางหรือช่วงข้อมูลที่ต้องการได้อย่างชัดเจน โดยใช้ each keyword สำหรับการวนซ้ำแต่ละแถวเพื่อตรวจสอบเงื่อนไขจากคอลัมน์ชื่อ ซึ่งเป็นรูปแบบการเขียนคิวรีที่นิยมใช้กันอย่างแพร่หลายในการพัฒนาคิวรีขั้นสูง
How it works
เข้าถึง Excel Table ภายใน Workbook
เมื่อต้องการดึงข้อมูลจาก Excel Table ที่สร้างไว้ใน workbook เดียวกัน โดยไม่ต้องระบุ absolute path
Dynamic Reference ไปยัง Named Range
เมื่อต้องการอ้างอิงข้อมูลจาก Named Range ที่อาจเปลี่ยนแปลงตำแหน่งหรือขนาดได้
Consolidate หลาย Table ใน Workbook เดียวกัน
เมื่อต้องการรวมข้อมูลจากหลาย Table หรือ Named Range ที่อยู่ใน workbook เดียวกันเข้าด้วยกัน
Examples
ตัวอย่างที่ 1: ดึงรายการ Table และ Named Range ทั้งหมดExcel.CurrentWorkbook()
Excel.CurrentWorkbook() return table ที่มี 3 columns หลัก: Name (ชื่อ object), Content (ข้อมูลเป็น table format) และ Kind (ประเภท เช่น Table หรือ Range)
แต่ละแถวคือหนึ่ง object ที่สามารถเข้าถึงได้ในไฟล์ ไม่รวม worksheet tabs นะครับ
💡 สังเกตว่า Kind จะบอกว่า object นั้นเป็น Table (สร้างด้วย Ctrl+T) หรือ Range (Named Range ทั่วไป) ทำให้เราสามารถ filter แยกประเภทได้
แสดงรายการของทุก Table, Named Range และ Dynamic Array ในไฟล์
ตัวอย่าง result:
Name | Content | Kind
SalesData | [Table] | Table
ProductList | [Table] | Range
MonthlyTarget | [Table] | Range
ตัวอย่างที่ 2: เลือกเฉพาะ Table ที่ต้องการด้วย Table.SelectRowslet AllObjects = Excel.CurrentWorkbook(), SalesTable = Table.SelectRows(AllObjects, each [Name] = "SalesData"), Content = SalesTable{0}[Content] in Content
ใช้ Table.SelectRows ร่วมกับ <code>each</code> keyword เพื่อ filter เฉพาะแถวที่ [Name] = "SalesData"
จากนั้นใช้ <code>{0}</code> เพื่อเข้าถึงแถวแรก (index 0) และดึง [Content] column ออกมาซึ่งเป็น table ข้อมูลจริง
📝 Pattern นี้ใช้บ่อยมากครับเมื่อต้องการเข้าถึงข้อมูลจาก Table เฉพาะตัวใน workbook ส่วนตัวผมใช้เกือบทุกครั้งที่ทำงานกับ Excel.CurrentWorkbook
ถ้า SalesData มีข้อมูล:
OrderID | Product | Amount
1 | Laptop | 15000
2 | Mouse | 500
3 | Keyboard | 1200
Result จะได้ table ข้อมูลทั้งหมดจาก SalesData
ตัวอย่างที่ 3: Filter เฉพาะ Excel Table (ไม่รวม Named Range)let AllObjects = Excel.CurrentWorkbook(), TablesOnly = Table.SelectRows(AllObjects, each [Kind] = "Table"), TableNames = TablesOnly[Name] in TableNames
ใช้ Table.SelectRows กับ condition <code>[Kind] = "Table"</code> เพื่อ filter เฉพาะ Excel Table (ไม่รวม Named Range ที่จะมี Kind = "Range")
จากนั้นดึงเฉพาะ Name column ออกมาเป็น list ของชื่อ Table ทั้งหมด
💡 วิธีนี้เหมาะเมื่อต้องการแยกประเภท object ที่ Excel.CurrentWorkbook return มา เพราะบางครั้งเราอาจมีทั้ง Table และ Named Range ปนกัน ต้องการเฉพาะ Table เท่านั้น
ตัวอย่าง:
{"SalesData", "ProductList", "CustomerInfo"}
ไม่รวม Named Range ที่มี Kind = "Range"
ตัวอย่างที่ 4: Combine ข้อมูลจากหลาย Table ด้วย Table.Combinelet AllObjects = Excel.CurrentWorkbook(), SalesTables = Table.SelectRows(AllObjects, each Text.StartsWith([Name], "Sales_")), TableContents = SalesTables[Conten…
ใช้ Text.StartsWith ร่วมกับ Table.SelectRows เพื่อ filter เฉพาะ Table ที่ชื่อขึ้นต้นด้วย "Sales_" (เช่น Sales_Q1, Sales_Q2)
จากนั้นดึงเฉพาะ [Content] column ออกมาเป็น list ของ tables แล้วใช้ Table.Combine รวมทุก table เข้าด้วยกัน
🔥 Pattern นี้เจ๋งมากครับ เหมาะสำหรับ consolidate ข้อมูลจากหลาย Table ที่มี naming convention เดียวกัน ไม่ต้องมานั่งเพิ่ม Table ทีละตัวในคิวรี แค่ตั้งชื่อให้ตรงกับ pattern ก็รวมให้อัตโนมัติเลย 😎
ถ้ามี Sales_Q1, Sales_Q2, Sales_Q3:
Result จะรวมข้อมูลจากทั้ง 3 Table เป็น table เดียว
OrderID | Product | Amount | Quarter
1 | Laptop | 15000 | Q1
2 | Mouse | 500 | Q1
3 | Monitor | 8000 | Q2
4 | Keyboard| 1200 | Q3
FAQs
Excel.CurrentWorkbook จะ return worksheet tabs ด้วยหรือไม่?+
ไม่ครับ Excel.CurrentWorkbook จะ return เฉพาะ named object เท่านั้น ได้แก่ Excel Table (ที่สร้างด้วย Ctrl+T), Named Range และ Dynamic Array
ไม่รวม worksheet tabs นะครับ ถ้าต้องการเข้าถึง worksheet ต้องใช้ Excel.Workbook แทน
💡 เคยสงสัยมั้ยว่าทำไมถึงไม่ให้เข้าถึง worksheet? เพราะ Excel.CurrentWorkbook ออกแบบมาให้ query มี portability สูง ถ้าอิงกับ worksheet name แล้วเอาไป copy ไปใช้ที่อื่น มันก็พังครับ 😅
ต่างจาก Excel.Workbook อย่างไร?+
Excel.Workbook ใช้เปิดไฟล์ Excel จาก file path และ return ทั้ง worksheets, Tables และ Named Ranges
ในขณะที่ Excel.CurrentWorkbook ใช้กับ workbook ปัจจุบันเท่านั้น (ไม่ต้องระบุ path) และ return เฉพาะ named object ไม่รวม worksheet tabs
ส่วนตัวผมมองว่า Excel.CurrentWorkbook เหมาะสำหรับ reference ข้อมูลภายใน workbook เดียวกัน ส่วน Excel.Workbook เหมาะเมื่อต้องดึงข้อมูลจากไฟล์อื่น
ถ้าไม่มี Table หรือ Named Range ใน workbook จะเกิดอะไรขึ้น?+
Excel.CurrentWorkbook จะ return table ว่างเปล่า (empty table) ที่มี column structure แต่ไม่มีแถวข้อมูล ไม่เกิด error
ดังนั้นควร check ว่า table ที่ return มา empty หรือไม่ก่อนใช้งาน เช่น Table.RowCount(Excel.CurrentWorkbook()) = 0
📝 Tip: ถ้าเจอ empty table บ่อยๆ อาจเป็นเพราะลืมสร้าง Table (Ctrl+T) หรือลืม define Named Range ไปก็ได้นะครับ
วิธีเลือกเฉพาะ Table ที่ต้องการจาก result ทำอย่างไร?+
ใช้ Table.SelectRows ร่วมกับ each [Name] = "ชื่อ Table" เพื่อ filter แถวที่ต้องการ
จากนั้นใช้ {0}[Content] เพื่อดึง table ข้อมูลออกมา
ตัวอย่าง:
Table.SelectRows(Excel.CurrentWorkbook(), each [Name] = "SalesData"){0}[Content]
จะได้ข้อมูลจาก SalesData Table ออกมาทันที 😎
สามารถใช้ Excel.CurrentWorkbook ใน Power BI Desktop ได้หรือไม่?+
ได้ครับ แต่ต้อง import ไฟล์ Excel เข้าไปใน Power BI Desktop ก่อน
เมื่อใช้ Excel.CurrentWorkbook ใน query editor ของ Power BI จะ return object จากไฟล์ Excel ที่ import ไว้ ไม่ใช่ workbook ของ Power BI เอง
💡 ทำไมถึงเป็นแบบนี้? เพราะ Power BI ไม่ใช่ Excel workbook ครับ มันแค่ import ข้อมูลจาก Excel มาเท่านั้น
Excel.CurrentWorkbook detect Dynamic Array (spill range) ได้หรือไม่?+
ได้ครับ ใน Excel 365 ที่มี Dynamic Array function ถ้า define Dynamic Array เป็น Named Range Excel.CurrentWorkbook จะสามารถ return object นั้นออกมาได้
โดยจะมี Kind = "Range" เหมือนกับ Named Range ทั่วไป ทำให้สามารถอ้างอิงข้อมูลจาก Dynamic Array ใน Power Query ได้
🔥 เจ๋งตรงที่ถ้า Dynamic Array ขยายหรือหดตัว (spill) ข้อมูลที่ดึงมาก็จะปรับตามอัตโนมัติ ไม่ต้องมานั่งแก้ช่วงข้อมูลเอง
ถ้าเปลี่ยนชื่อ Table หรือ Named Range จะกระทบ query หรือไม่?+
ถ้า query ใช้ hardcode ชื่อใน Table.SelectRows เช่น each [Name] = "OldName" แล้วเปลี่ยนชื่อ Table เป็น "NewName" query จะหา Table ไม่เจอและเกิด error
ต้อง update ชื่อใน query ให้ตรงกับชื่อใหม่ครับ
ส่วนตัวผมแนะนำให้มี naming convention ที่ชัดเจนและไม่เปลี่ยนชื่อบ่อย ถ้าต้องเปลี่ยนจริงๆ ก็ต้อง Ctrl+H แก้ทุก query ที่อ้างอิงไปครับ 😅
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ Excel.CurrentWorkbook จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
Excel.ShapeTable ใช้สำหรับการประมวลผลข้อมูล
Excel.Workbook อ่าน binary content ของไฟล์ Excel แล้วคืนค่าเป็น table ที่แต่ละ row แทนด้วย worksheet, named range หรือ Excel table โดยมี columns คือ Item (ชื่อ), Kind (ประเภท), Name (ชื่อเต็ม), Data (nested table ของข้อมูลจริง), และ Hidden (boolean) รองรับ useHeaders และ delayTypes เป็น parameters แต่ Microsoft แนะนำให้ใช้ Table.PromoteHeaders แทนเพื่อหลีกเลี่ยงปัญหา culture conversion นอกจากนี้ยังมี InferSheetDimensions option สำหรับแก้ปัญหา metadata ที่ผิดพลาด ใช้ร่วมกับ File.Contents หรือ Web.Contents สำหรับโหลดไฟล์
Table.SelectRows เป็นฟังก์ชันสำหรับกรองตาราง (filter table) ใน Power Query โดยใช้ฟังก์ชันเงื่อนไข (condition function) เพื่อตรวจสอบแต่ละแถว ถ้าเงื่อนไขคืนค่า true จะเก็บแถวนั้นไว้ ถ้าเป็น false จะตัดทิ้ง สามารถใช้ keyword 'each' ร่วมกับการอ้างอิงคอลัมน์ด้วย [ColumnName] เพื่อเขียนเงื่อนไขได้สะดวก รองรับการกรองตามตัวเลข ข้อความ วันที่ การตรวจสอบค่า null และเงื่อนไขที่ซับซ้อนด้วย and/or operators ฟังก์ชันนี้รองรับ Query Folding ซึ่งช่วยเพิ่มประสิทธิภาพเมื่อทำงานกับ data source ขนาดใหญ่
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่