Thep Excel
Excel.Workbook – อ่านและแยกเนื้อหาไฟล์ Excel ทั้งหมด
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 สำหรับโหลดไฟล์
=Excel.Workbook(workbook as binary, optional useHeaders as any, optional delayTypes as nullable logical) as table
Function Metrics
Popularity
10/10
Difficulty
3/10
Usefulness
10/10
Syntax & Arguments
=Excel.Workbook(workbook as binary, optional useHeaders as any, optional delayTypes as nullable logical) as table
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| workbook | binary | Yes | Binary content ของไฟล์ Excel ที่ต้องการอ่าน มักได้จาก File.Contents(“path.xlsx”) สำหรับไฟล์ในเครื่อง หรือ Web.Contents(url) สำหรับไฟล์จากอินเทอร์เน็ต รองรับทั้งไฟล์ .xlsx, .xlsm (Open XML) และ .xls, .xlsb (Legacy Excel) | |
| useHeaders | any | Optional | null | สามารถเป็นได้ 3 แบบ: (1) null – ไม่ promote headers (ค่าเริ่มต้น), (2) logical (true/false) – กำหนดว่าแถวแรกเป็น header, (3) record options – ระบุ [UseHeaders=true/false, DelayTypes=true/false, InferSheetDimensions=true/false] เพื่อควบคุมการอ่านแบบละเอียด ⚠️ สำคัญมาก: Microsoft แนะนำไม่ให้ใช้ logical values โดยตรงเนื่องจากมีปัญหา culture conversion (แปลงตัวเลขและวันที่เป็น text ตาม OS culture) แนะนำให้ใช้ null แล้วตามด้วย Table.PromoteHeaders แทน |
| delayTypes | nullable logical | Optional | false | กำหนดว่าจะเลื่อนการคาดเดา data type ของ columns หรือไม่ ค่า true จะเก็บข้อมูลทุก column เป็น text ไว้ก่อน (เหมาะกับไฟล์ขนาดใหญ่เพื่อเพิ่มความเร็วการโหลด) ส่วนค่า false (ค่าเริ่มต้น) จะคาดเดา type ทันทีซึ่งแม่นยำแต่อาจช้ากับไฟล์ใหญ่ หลังเลื่อน type inference แล้วสามารถใช้ Table.TransformColumnTypes กำหนด type ที่ถูกต้องให้เฉพาะ columns ที่ต้องการได้ |
How it works
นำเข้าข้อมูลจากไฟล์ Excel ในเครื่องหรือ network drive
โหลดข้อมูลจากไฟล์ Excel ที่เก็บในเครื่องหรือบน network path โดยใช้ File.Contents แปลงไฟล์เป็น binary content แล้วส่งให้ Excel.Workbook อ่านเนื้อหาทุก worksheet, table และ named range เหมาะสำหรับการนำเข้าข้อมูลจากไฟล์รายงานที่ส่งมาทุกวันหรือไฟล์งานประจำ
เลือกดึงข้อมูลจาก worksheet เฉพาะใน workbook ที่มีหลาย sheets
กรองเลือกเฉพาะ worksheet หรือ table ที่ต้องการจาก workbook ที่มีหลาย items โดยใช้ {[Item="SheetName"]}[Data] หรือ Table.SelectRows กรองตาม Kind และ Item ช่วยลดข้อมูลที่ไม่จำเป็นและเพิ่มความเร็วในการประมวลผล
ดาวน์โหลดและอ่านไฟล์ Excel จาก URL โดยตรง
ใช้ Web.Contents ดาวน์โหลดไฟล์ Excel จาก URL หรือ SharePoint โดยตรง แล้วส่ง binary content ให้ Excel.Workbook อ่านเนื้อหา เหมาะสำหรับข้อมูลที่ update บนเว็บไซต์หรือ cloud storage สม่ำเสมอ เพราะทุกครั้งที่ refresh จะได้ไฟล์เวอร์ชันล่าสุดอัตโนมัติ
แก้ปัญหาไฟล์ Excel ที่มี metadata dimensions ผิดพลาดหรือโหลดช้า
บางไฟล์ Excel หลังจากลบข้อมูลจำนวนมากออกแล้วยัง metadata ระบุว่ามีหลายพัน rows (UsedRange ผิดพลาด) ทำให้โหลดข้อมูลช้า ใช้ InferSheetDimensions=true บังคับให้อ่านขนาด worksheet จากเนื้อหาจริงแทนการใช้ metadata ช่วยแก้ปัญหาและเพิ่มความเร็วอย่างมาก (รองรับเฉพาะไฟล์ Open XML เช่น .xlsx, .xlsm เท่านั้น)
รวมข้อมูลจากหลาย worksheets ที่มีโครงสร้างเดียวกัน (Union All pattern)
โหลดข้อมูลจากหลาย worksheets ที่มี schema เดียวกัน (เช่น รายงานแยกตามภูมิภาค เดือน หรือแผนก) แล้วรวมเป็น table เดียวโดยใช้ Table.SelectRows กรอง sheets ที่ต้องการ ตามด้วย Table.ExpandTableColumn เพื่อขยาย nested tables ออกมา เหมาะสำหรับการวิเคราะห์ข้อมูลรวมจากหลายแหล่ง
Examples
ตัวอย่างที่ 1: Foundation – โหลดไฟล์ Excel และดู structure ของ workbook
let // สร้าง sample Excel data (จำลองไฟล์ Sales.xlsx) SampleData = Table.FromRows( { {"Sheet1", "Sheet", "Sales_Sheet1", #table({"OrderID", "Amount"}, {{1, 500}…
.
สังเกตว่าพารามิเตอร์ที่ 2 เป็น null (ไม่ promote headers) และพารามิเตอร์ที่ 3 เป็น true (delay type inference เพื่อเพิ่มความเร็ว) ซึ่งเป็น best practice ที่ Microsoft แนะนำ
.
ผลลัพธ์ที่ได้เป็น table ที่แต่ละ row แทนด้วย worksheet หนึ่งแผ่น column Data บรรจุ nested table ของข้อมูลจริง ซึ่งต้องใช้ [Data] หรือ Table.ExpandTableColumn เพื่อดึงออกมาใช้งานต่อครับ
=let
// สร้าง sample Excel data (จำลองไฟล์ Sales.xlsx)
SampleData = Table.FromRows(
{
{"Sheet1", "Sheet", "Sales_Sheet1", #table({"OrderID", "Amount"}, {{1, 500}, {2, 1200}}), false},
{"Sheet2", "Sheet", "Sales_Sheet2", #table({"Region", "Total"}, {{"North", 5000}, {"South", 3000}}), false},
{"Summary", "Sheet", "Sales_Summary", #table({"Month", "Revenue"}, {{"Jan", 8000}}), true}
},
{"Item", "Kind", "Name", "Data", "Hidden"}
),
// ในการใช้งานจริง จะเป็น:
// FileContent = File.Contents("C:\\Data\\Sales.xlsx"),
// Workbook = Excel.Workbook(FileContent, null, true)
Result = SampleData
in
Result
// Result: Table มี 3 rows (3 sheets) พร้อม columns: Item, Kind, Name, Data (nested table), Hidden
Table มี 3 rows แทนด้วย 3 worksheets โดยมี columns: Item ("Sheet1", "Sheet2", "Summary"), Kind ("Sheet"), Name (ชื่อเต็ม), Data (nested table ของข้อมูลจริงในแต่ละ sheet), Hidden (true/false บอกว่า sheet ถูกซ่อนหรือไม่)
ตัวอย่างที่ 2: Practical – เลือก worksheet เฉพาะและ promote headers อย่างปลอดภัย
let // จำลอง workbook structure WorkbookData = Table.FromRows( { {"Sales", "Sheet", "Monthly_Sales", Table.FromRows( {{"OrderID", "ProductName", "Amount"}, {101…
.
สังเกตว่าเราระบุทั้ง Item="Sales" และ Kind="Sheet" เพื่อให้แน่ใจว่าเป็น worksheet ชื่อ Sales (ไม่ใช่ named range หรือ Excel table ชื่อเดียวกัน) แล้วดึง [Data] column ออกมาได้ nested table
.
จากนั้นใช้ Table.PromoteHeaders พร้อม option [PromoteAllScalars=true] เพื่อแปลงแถวแรกให้เป็น column names อย่างปลอดภัย
.
วิธีนี้เป็น best practice ที่ Microsoft แนะนำเพราะหลีกเลี่ยงปัญหา culture conversion ที่เกิดจากการใช้ useHeaders=true โดยตรง ซึ่งจะแปลงตัวเลขและวันที่เป็น text ตาม OS culture settings ทำให้ผลลัพธ์ไม่สม่ำเสมอครับ 😅
let
// จำลอง workbook structure
WorkbookData = Table.FromRows(
{
{"Sales", "Sheet", "Monthly_Sales",
Table.FromRows(
{{"OrderID", "ProductName", "Amount"},
{101, "Laptop", 25000},
{102, "Mouse", 500},
{103, "Keyboard", 1200}},
{"Column1", "Column2", "Column3"}
), false},
{"Summary", "Sheet", "Monthly_Summary", #table({"A"}, {{"Total"}}), false}
},
{"Item", "Kind", "Name", "Data", "Hidden"}
),
// ในการใช้งานจริง:
// Source = File.Contents("C:\\Reports\\Monthly.xlsx"),
// WorkbookData = Excel.Workbook(Source, null, true),
// กรองเอา Sales sheet (ใช้ {[Item="Sales"]} แทน Table.SelectRows)
SalesSheet = WorkbookData{[Item="Sales", Kind="Sheet"]}[Data],
// Promote headers อย่างปลอดภัย (best practice)
PromotedHeaders = Table.PromoteHeaders(SalesSheet, [PromoteAllScalars = true])
in
PromotedHeaders
// Result: Table ข้อมูล Sales sheet โดยแถวแรก (OrderID, ProductName, Amount) ถูก promote เป็น column names
Table ของข้อมูลใน Sales sheet โดยมี columns: OrderID (101, 102, 103), ProductName ("Laptop", "Mouse", "Keyboard"), Amount (25000, 500, 1200) โดยแถวแรกถูก promote เป็น column headers อย่างปลอดภัย
ตัวอย่างที่ 3: Advanced – ใช้ record options เพื่อแก้ปัญหา metadata ผิดพลาด
let // จำลอง workbook จาก URL SampleWorkbook = Table.FromRows( { {"RawData", "Sheet", "Data_RawData", Table.FromRows( {{"Column1", "Column2", "Column3"}, {"1234…
.
เราใช้ Web.Contents ดาวน์โหลดไฟล์จาก URL แล้วส่งให้ Excel.Workbook พร้อม record options แทนการใช้ logical values โดยระบุ UseHeaders=false, DelayTypes=true, และ InferSheetDimensions=true
.
Option สุดท้ายนี้สำคัญมากเพราะแก้ปัญหาไฟล์ Excel ที่มี metadata dimensions ผิดพลาด เคยเจอมั้ยครับ… ไฟล์ที่เคยมีข้อมูล 10,000 rows แล้วลบออกไปแต่ metadata ยังบอกว่ามี 10,000 rows? 😭 ทำให้ Power Query โหลดช้ามากกกก
.
InferSheetDimensions=true จะบังคับให้อ่านขนาดจริงจากเนื้อหา แทนการเชื่อ metadata ซึ่งช่วยแก้ปัญหานี้ได้เลย
.
หลัง promote headers ด้วย Table.PromoteHeaders เราใช้ Table.TransformColumnTypes กำหนด data type ที่ถูกต้องให้แต่ละ column (เพราะใช้ DelayTypes=true ทำให้ข้อมูลเริ่มต้นเป็น text ทั้งหมด) วิธีนี้ให้เราควบคุม type conversion ได้แบบ manual และแม่นยำกว่าครับ
let
// จำลอง workbook จาก URL
SampleWorkbook = Table.FromRows(
{
{"RawData", "Sheet", "Data_RawData",
Table.FromRows(
{{"Column1", "Column2", "Column3"},
{"12345", "45000.50", "2024-01-15"},
{"67890", "32000.75", "2024-01-16"}},
{"A", "B", "C"}
), false}
},
{"Item", "Kind", "Name", "Data", "Hidden"}
),
// ในการใช้งานจริง (ดาวน์โหลดจาก URL):
// FileUrl = "https://example.com/data/LargeFile.xlsx",
// DownloadedFile = Web.Contents(FileUrl),
// SampleWorkbook = Excel.Workbook(
// DownloadedFile,
// [
// UseHeaders = false,
// DelayTypes = true,
// InferSheetDimensions = true
// ]
// ),
DataSheet = SampleWorkbook{[Item="RawData"]}[Data],
// Promote headers
WithHeaders = Table.PromoteHeaders(DataSheet, [PromoteAllScalars = true]),
// กำหนด data type ที่ถูกต้อง (manual typing)
TypedColumns = Table.TransformColumnTypes(
WithHeaders,
{
{"Column1", Int64.Type},
{"Column2", type number},
{"Column3", type date}
}
)
in
TypedColumns
// Result: Table จาก RawData sheet โดย Column1 เป็น integer, Column2 เป็น number, Column3 เป็น date
Table จาก RawData sheet โดย Column1 (12345, 67890) เป็น Int64, Column2 (45000.5, 32000.75) เป็น number, Column3 เป็น date โดยอ่านขนาด worksheet จากเนื้อหาจริง (ไม่ใช่ metadata) และกำหนด data type แบบ manual
ตัวอย่างที่ 4: Real-World – รวมข้อมูลจากหลาย worksheets ที่มีโครงสร้างเดียวกัน
let // จำลอง workbook ที่มีหลาย regional sheets WorkbookData = Table.FromRows( { {"Region_North", "Sheet", "Q1_Region_North", Table.FromRows( {{"OrderID", "Prod…
.
เริ่มต้นด้วยการใช้ Table.SelectRows กรองเฉพาะ sheets ที่ Kind="Sheet" และชื่อขึ้นต้นด้วย "Region" (ใช้ each context กับ Text.StartsWith ซึ่งเป็น M language pattern สำคัญ)
.
จากนั้นใช้ Table.AddColumn สร้าง column ใหม่ชื่อ RegionData ซึ่งเก็บ table หลัง promote headers สำหรับแต่ละ row (แต่ละ sheet)
.
สุดท้ายใช้ Table.ExpandTableColumn ขยาย nested table ออกมาเป็น flat rows โดยระบุ columns ที่ต้องการดึงออกมา (OrderID, Product, Amount)
.
เทคนิคนี้เหมาะมากสำหรับรายงานที่มีหลาย sheets โครงสร้างเดียวกัน เช่น รายงานแยกตามภูมิภาค เดือน หรือแผนก แล้วต้องการรวมเป็น table เดียวเพื่อวิเคราะห์ครับ 😎 column Item จะบอกว่าแต่ละ row มาจาก sheet ไหนด้วย
let
// จำลอง workbook ที่มีหลาย regional sheets
WorkbookData = Table.FromRows(
{
{"Region_North", "Sheet", "Q1_Region_North",
Table.FromRows(
{{"OrderID", "Product", "Amount"},
{1001, "Laptop", 25000},
{1002, "Mouse", 500}},
{"Col1", "Col2", "Col3"}
), false},
{"Region_South", "Sheet", "Q1_Region_South",
Table.FromRows(
{{"OrderID", "Product", "Amount"},
{2001, "Keyboard", 1200},
{2002, "Monitor", 8000}},
{"Col1", "Col2", "Col3"}
), false},
{"Summary", "Sheet", "Q1_Summary", #table({"A"}, {{"Total"}}), false}
},
{"Item", "Kind", "Name", "Data", "Hidden"}
),
// ในการใช้งานจริง:
// Source = File.Contents("C:\\Sales\\Q1_Regional.xlsx"),
// WorkbookData = Excel.Workbook(Source, null, true),
// กรองเฉพาะ sheets ที่ชื่อขึ้นต้นด้วย "Region"
FilteredSheets = Table.SelectRows(
WorkbookData,
each [Kind] = "Sheet" and Text.StartsWith([Item], "Region")
),
// Promote headers สำหรับแต่ละ sheet
ExpandedData = Table.AddColumn(
FilteredSheets,
"RegionData",
each Table.PromoteHeaders([Data], [PromoteAllScalars = true])
),
// เก็บเฉพาะ columns ที่ต้องการ
RemovedOtherColumns = Table.SelectColumns(ExpandedData, {"Item", "RegionData"}),
// ขยาย nested table ออกเป็น rows
ExpandedTables = Table.ExpandTableColumn(
RemovedOtherColumns,
"RegionData",
{"OrderID", "Product", "Amount"},
{"OrderID", "Product", "Amount"}
)
in
ExpandedTables
// Result: Table รวมข้อมูลจาก Region_North และ Region_South (4 rows) พร้อม column Item บอกว่าข้อมูลมาจาก sheet ไหน
Table รวมข้อมูลจาก 2 regional sheets (4 rows) โดยมี columns: Item ("Region_North", "Region_South"), OrderID (1001, 1002, 2001, 2002), Product, Amount ซึ่งรวมข้อมูลจากทุก sheet ที่ชื่อขึ้นต้นด้วย "Region" แต่ไม่รวม Summary sheet
FAQs
Excel.Workbook คืนค่า table ที่มี columns อะไรบ้าง?
Excel.Workbook คืนค่าเป็น table ที่แต่ละ row แทนด้วย worksheet, Excel table หรือ named range หนึ่งรายการ โดยมี columns สำคัญคือ: (1) Item – ชื่อของ sheet, table หรือ named range (เช่น “Sheet1”, “SalesTable”), (2) Kind – ประเภทของรายการ (“Sheet”, “Table”, “DefinedName”), (3) Name – ชื่อแบบเต็มตามรูปแบบ Excel (เช่น “Sales_Sheet1″), (4) Data – nested table ที่บรรจุข้อมูลจริงของรายการนั้นๆ, (5) Hidden – boolean (true/false) บอกว่ารายการนั้นถูกซ่อนหรือไม่ ในการใช้งาน มักจะใช้ {[Item=”ชื่อ sheet”]}[Data] เพื่อกรองและดึง Data column ของ sheet ที่ต้องการออกมา
ทำไมไม่ควรใช้พารามิเตอร์ useHeaders โดยตรง?
เรื่องนี้ Microsoft บอกชัดเจนมากในเอกสารทางการครับว่าไม่ควรใช้ useHeaders แบบ logical (true/false) โดยตรง 😅
.
เพราะมีปัญหาการแปลงค่าตามภูมิภาค (culture-dependent conversion) กล่าวคือ เมื่อใช้ useHeaders=true, Power Query จะแปลงค่าตัวเลขและวันที่ในแถวแรก (ที่จะเป็น column names) เป็น text ตามการตั้งค่า OS culture (เช่น en-US, th-TH, ja-JP)
.
ผลลัพธ์ก็คือ… ไฟล์เดียวกัน query เดียวกัน แต่ได้ผลไม่เหมือนกันระหว่างเครื่องที่ใช้ culture ต่างกัน ปวดหัวครับ 😭
.
แนวทางที่ปลอดภัยและเป็น best practice คือใช้ Excel.Workbook(binary, null, true) เพื่อโหลดข้อมูลด้วย delay types จากนั้นใช้ Table.PromoteHeaders([PromoteAllScalars=true]) เพื่อ promote headers อย่างปลอดภัยในขั้นตอนถัดไป วิธีนี้ให้ผลลัพธ์สม่ำเสมอและไม่ขึ้นกับ OS culture settings ครับ
delayTypes parameter ใช้เมื่อไรและทำงานอย่างไร?
ตั้ง delayTypes=true เมื่อต้องการเพิ่มความเร็วในการโหลดไฟล์ Excel ขนาดใหญ่ครับ
.
เพราะ Power Query จะข้ามขั้นตอน type inference (การคาดเดา data type) ซึ่งต้องใช้เวลาสแกนข้อมูลจำนวนมากเพื่อวิเคราะห์ว่าแต่ละ column ควรเป็น type อะไร (text, number, date, etc.)
.
แทนที่จะเก็บทุก column เป็น text ไว้ก่อน จากนั้นคุณสามารถใช้ Table.TransformColumnTypes กำหนด data type ที่ถูกต้องให้เฉพาะ columns ที่ต้องการภายหลังได้ อย่างเป็นระบบและรวดเร็วกว่า 😎
.
ส่วนค่า false (ค่าเริ่มต้น) จะคาดเดา type ทันที ซึ่งสะดวกและแม่นยำสำหรับไฟล์ขนาดเล็ก แต่อาจช้ามากกับไฟล์ใหญ่
.
เทคนิคนี้เรียกว่า deferred type detection และเป็น performance optimization pattern สำคัญใน Power Query ครับ
InferSheetDimensions คืออะไรและช่วยแก้ปัญหาอะไร?
InferSheetDimensions เป็น advanced option ที่ช่วยชีวิตมากเวลาเจอไฟล์ Excel ที่มี metadata ผิดพลาดครับ 💡
.
มันบังคับให้ Power Query อ่านขนาดจริงของ worksheet จากเนื้อหาข้อมูล (actual cell data) แทนการใช้ metadata ที่บันทึกในไฟล์
.
ใช้เมื่อไร? เมื่อไฟล์ Excel มี metadata dimensions ผิดพลาด ซึ่งเกิดได้บ่อยเมื่อมีการลบข้อมูลจำนวนมากออก
.
เช่น เคยมีข้อมูล 10,000 rows แล้วถูกลบออกเหลือเพียง 100 rows แต่ metadata (UsedRange) ยังบอกว่ามี 10,000 rows 😭 ทำให้ Power Query พยายามอ่านข้อมูลทั้ง 10,000 rows จึงโหลดช้ามากกกก
.
การตั้ง [InferSheetDimensions=true] จะแก้ปัญหานี้โดยสแกนหาข้อมูลจริงแทน
.
แต่ข้อจำกัดคือ option นี้รองรับเฉพาะไฟล์ Open XML Excel (.xlsx, .xlsm) เท่านั้น ไม่รองรับไฟล์ legacy format (.xls, .xlsb) และอาจช้ากว่าการใช้ metadata ในไฟล์ที่ metadata ถูกต้องนะครับ
สามารถโหลดไฟล์ Excel จาก URL หรือ SharePoint ได้หรือไม่?
ได้เลยครับ โดยใช้ Web.Contents แทน File.Contents เพื่อดาวน์โหลดไฟล์จาก URL หรือ SharePoint โดยตรง
.
ตัวอย่าง: Excel.Workbook(Web.Contents(“https://example.com/data.xlsx”), null, true)
.
วิธีนี้เหมาะมากสำหรับข้อมูลที่ถูก update บนเว็บไซต์หรือ cloud storage สม่ำเสมอ เพราะทุกครั้งที่ refresh query จะดึงไฟล์เวอร์ชันล่าสุดมาใช้อัตโนมัติ 😎
.
สำหรับ SharePoint หรือ OneDrive อาจต้องใช้ Web.Contents พร้อม authentication options เพิ่มเติม หรือใช้ SharePoint connector โดยตรงใน Power Query แทน (แนะนำวิธีหลังเพราะง่ายกว่า)
.
ข้อจำกัด: ใน Power Query Online (Power BI Service) ไม่สามารถเข้าถึงไฟล์ที่มี sensitivity labels (encrypted files) หรือ password-protected files ได้ จึงต้องใช้ gateway พร้อมติดตั้ง Access Database Engine (ACE) สำหรับไฟล์ legacy format ครับ
ต่างระหว่าง Excel.Workbook กับ Excel.CurrentWorkbook อย่างไร?
ต่างกันครับ ลองดูเปรียบเทียบ:
.
**Excel.Workbook** → อ่านจากไฟล์ Excel ภายนอก โดยรับค่า binary content จาก File.Contents หรือ Web.Contents และคืนค่าเป็น table ของ worksheets, named ranges และ Excel tables ทั้งหมดในไฟล์ ใช้สำหรับนำเข้าข้อมูลจากไฟล์อื่น
.
**Excel.CurrentWorkbook** → อ่านจาก workbook ปัจจุบันที่ Power Query กำลังทำงานอยู่ (available เฉพาะใน Excel เท่านั้น ไม่มีใน Power BI Desktop หรือ Power BI Service) และคืนค่าเฉพาะ Excel Tables และ named ranges เท่านั้น ไม่รวม worksheets ทั่วไป
.
ใช้ Excel.CurrentWorkbook เมื่อต้องการอ้างอิงข้อมูลจาก Excel Table ในไฟล์เดียวกันกับที่ query อยู่ เช่น ดึงข้อมูลจาก Table ชื่อ “SalesData” มาประมวลผลใน Power Query ภายในไฟล์ Excel เดียวกันครับ
จะกรองเฉพาะ worksheets (ไม่เอา named ranges และ Excel tables) ได้อย่างไร?
มี 2 วิธีครับ ขึ้นอยู่กับว่ารู้ชื่อ sheet ที่แน่นอนหรือเปล่า:
.
**วิธีที่ 1: ใช้ row accessor (รู้ชื่อ sheet)** → {[Item=”Sales”, Kind=”Sheet”]}[Data] เร็วกว่าเพราะเป็น index lookup แต่ต้องรู้ชื่อ sheet ที่แน่นอน
.
**วิธีที่ 2: ใช้ Table.SelectRows (ยืดหยุ่นกว่า)** → Table.SelectRows(Workbook, each [Kind] = “Sheet”) จะได้เฉพาะ worksheets ไม่รวม tables และ named ranges
.
สามารถใช้เงื่อนไขซับซ้อนได้ เช่น each [Kind] = “Sheet” and Text.StartsWith([Item], “Region”) เพื่อกรองเฉพาะ sheets ที่ชื่อขึ้นต้นด้วย “Region”
.
ส่วนตัวผมชอบใช้วิธีที่ 2 เพราะยืดหยุ่นกว่าและเห็น pattern ชัดเจนกว่าครับ 😎
ไฟล์ Legacy Excel (.xls, .xlsb) ต้องมีข้อกำหนดพิเศษหรือไม่?
ใช่ครับ มีข้อกำหนดพิเศษ:
.
สำหรับไฟล์ Legacy Excel formats (.xls, .xlsb) ต้องติดตั้ง Access Database Engine (ACE) OLEDB provider ในเครื่องที่ใช้ Power Query ซึ่งดาวน์โหลดได้จาก Microsoft Download Center (มีทั้ง 32-bit และ 64-bit) โดยต้องเลือกให้ตรงกับเวอร์ชันของ Office หรือ Power BI Desktop
.
ข้อจำกัด: ACE provider ไม่สามารถใช้ได้ใน cloud environments (Power BI Service, Power Query Online) จึงต้องใช้ on-premises gateway ที่ติดตั้ง ACE ไว้แล้วเพื่อเชื่อมต่อ
.
นอกจากนี้ไฟล์ legacy ยังไม่รองรับ InferSheetDimensions option และอาจมีปัญหา numeric precision ซึ่งแตกต่างจากไฟล์ .xlsx ที่ Power Query รองรับเต็มรูปแบบโดยไม่ต้องติดตั้งอะไรเพิ่มเติม
.
ส่วนตัวผมแนะนำให้แปลงไฟล์เก่าเป็น .xlsx format เลยครับ ทั้งความเข้ากันได้และประสิทธิภาพดีกว่าเยอะ 😎
Resources & Related
Resources
-
Microsoft Learn – Excel.Workbook Official Documentation
documentation -
Microsoft Learn – Table.PromoteHeaders (Best Practice for Headers)
documentation -
Microsoft Learn – File.Contents Function
documentation -
Microsoft Learn – Web.Contents Function
documentation -
Microsoft Learn – Power Query Excel Connector Guide
guide -
Chris Webb's BI Blog – Working With Excel Named Ranges In Power Query
guide
Related posts
Related functions
Additional Notes
Excel.Workbook เป็นฟังก์ชันหลักใน Power Query ที่ใช้สำหรับอ่านและแยกวิเคราะห์เนื้อหาจากไฟล์ Excel ครับ
.
function นี้จะรับค่า binary content (ซึ่งได้จาก File.Contents สำหรับไฟล์ในเครื่อง หรือ Web.Contents สำหรับไฟล์จากอินเทอร์เน็ต) แล้วคืนค่าเป็น table ที่แต่ละ row แทนด้วย worksheet, named range, หรือ Excel table หนึ่งรายการ พร้อมทั้ง metadata ที่สำคัญและ Data column ซึ่งบรรจุข้อมูลจริงในรูปแบบ nested table
.
ฟังก์ชันนี้มีความยืดหยุ่นสูงด้วยพารามิเตอร์ optional สองตัว คือ useHeaders (สามารถเป็น logical หรือ record options) และ delayTypes (logical) ซึ่งช่วยควบคุมพฤติกรรมการอ่านข้อมูล
.
อย่างไรก็ตาม สิ่งสำคัญที่ต้องเข้าใจคือ Microsoft แนะนำอย่างชัดเจนว่าไม่ควรใช้พารามิเตอร์ useHeaders โดยตรง เนื่องจากมีปัญหาการแปลงค่าตามภูมิภาค (culture-dependent conversion) ที่ทำให้ตัวเลขและวันที่ในแถวแรกถูกแปลงเป็น text ตามการตั้งค่า OS culture ส่งผลให้ได้ผลลัพธ์ไม่สม่ำเสมอระหว่างเครื่องต่างๆ 😅
.
แนวทางปฏิบัติที่ดีที่สุด (best practice) คือการใช้ Excel.Workbook พร้อมตั้ง useHeaders เป็น null และ delayTypes เป็น true เพื่อโหลดข้อมูลอย่างรวดเร็ว จากนั้นจึงใช้ Table.PromoteHeaders พร้อม option [PromoteAllScalars=true] เพื่อแปลงแถวแรกเป็น column headers อย่างปลอดภัย 😎
.
นอกจากนี้ยังมี record options ขั้นสูงอย่าง InferSheetDimensions ที่ช่วยแก้ปัญหาไฟล์ Excel ที่มี metadata dimensions ผิดพลาด (เช่น ลบข้อมูลออกแล้วแต่ metadata ยังบอกว่ามีหลายพัน rows) โดยบังคับให้อ่านขนาด worksheet จากเนื้อหาจริงแทนการใช้ metadata ซึ่งเพิ่มประสิทธิภาพและความถูกต้องได้อย่างมากครับ 💡
.
ฟังก์ชันนี้จึงเป็นรากฐานสำคัญของการนำเข้าข้อมูล Excel ใน Power Query และใช้ร่วมกับ File.Contents, Web.Contents, Table.PromoteHeaders และ Table.SelectRows ได้อย่างมีประสิทธิภาพนะครับ