Thep Excel

Power Query · Accessing data functions

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 สำหรับโหลดไฟล์

fx
=Excel.Workbook(workbook, [useHeaders], [delayTypes]) as table

ความนิยม10/10
ความยาก3/10
ประโยชน์10/10

อัปเดต 18 December 2025

ในหน้านี้Syntax & ArgumentsAdditional NotesExamplesFAQsResources & Related

Syntax & Arguments

อาร์กิวเมนต์ ชนิด ค่าเริ่มต้น คำอธิบาย
workbook binary Binary content ของไฟล์ Excel ที่ต้องการอ่าน มักได้จาก File.Contents(“path.xlsx”) สำหรับไฟล์ในเครื่อง หรือ Web.Contents(url) สำหรับไฟล์จากอินเทอร์เน็ต รองรับทั้งไฟล์ .xlsx, .xlsm (Open XML) และ .xls, .xlsb (Legacy Excel)
[useHeaders]ไม่บังคับ any 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 false กำหนดว่าจะเลื่อนการคาดเดา data type ของ columns หรือไม่ ค่า true จะเก็บข้อมูลทุก column เป็น text ไว้ก่อน (เหมาะกับไฟล์ขนาดใหญ่เพื่อเพิ่มความเร็วการโหลด) ส่วนค่า false (ค่าเริ่มต้น) จะคาดเดา type ทันทีซึ่งแม่นยำแต่อาจช้ากับไฟล์ใหญ่ หลังเลื่อน type inference แล้วสามารถใช้ Table.TransformColumnTypes กำหนด type ที่ถูกต้องให้เฉพาะ columns ที่ต้องการได้

[ ] = อาร์กิวเมนต์ที่ไม่บังคับ

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 ได้อย่างมีประสิทธิภาพนะครับ

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 ของ workbooklet // สร้าง sample Excel data (จำลองไฟล์ Sales.xlsx) SampleData = Table.FromRows( { {"Sheet1", "Sheet", "Sales_Sheet1", #table({"OrderID", "Amount"}, {{1, 500}…

นี่คือโครงสร้างพื้นฐานที่ Excel.Workbook คืนค่าครับ ในการใช้งานจริงจะใช้ File.Contents("path.xlsx") แปลงไฟล์เป็น binary แล้วส่งให้ Excel.Workbook

สังเกตว่าพารามิเตอร์ที่ 2 เป็น null (ไม่ promote headers) และพารามิเตอร์ที่ 3 เป็น true (delay type inference เพื่อเพิ่มความเร็ว) ซึ่งเป็น best practice ที่ Microsoft แนะนำ

ผลลัพธ์ที่ได้เป็น table ที่แต่ละ row แทนด้วย worksheet หนึ่งแผ่น column Data บรรจุ nested table ของข้อมูลจริง ซึ่งต้องใช้ [Data] หรือ Table.ExpandTableColumn เพื่อดึงออกมาใช้งานต่อครับ

fx
=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…

หลังได้ table ของทุก worksheet จาก Excel.Workbook เราใช้ row accessor syntax {[Item="Sales", Kind="Sheet"]} เพื่อกรองเอา row ที่ต้องการ

สังเกตว่าเราระบุทั้ง 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 ทำให้ผลลัพธ์ไม่สม่ำเสมอครับ 😅

fx
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…

ตัวอย่างนี้แสดงเทคนิคขั้นสูงที่ผมแนะนำเมื่อต้องจัดการไฟล์ Excel ขนาดใหญ่หรือไฟล์ที่มีปัญหา metadata 😎

เราใช้ 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 และแม่นยำกว่าครับ

fx
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…

ตัวอย่างนี้เป็น pattern ที่ผมใช้บ่อยมากเวลาต้องรวมข้อมูลจากหลาย sheets ที่มีโครงสร้างเดียวกัน (Union All pattern) 💡

เริ่มต้นด้วยการใช้ 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 ไหนด้วย

fx
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

ฟังก์ชันที่ผู้เขียนโยงไว้กับ Excel.Workbook จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง

ฟังก์ชันนี้Excel.WorkbookAccessing data functions
Accessing data functions (6)
Csv.Document

Csv.Document แปลงข้อมูลรูปแบบ CSV (Comma-Separated Values) ให้เป็นตารางข้อมูล (table) ใน Power Query โดยรองรับการกำหนดตัวแบ่งข้อมูล (delimiter), รหัสภาษา (encoding) และโครงสร้างคอลัมน์ (column structure) แบบต่างๆ ได้ตามความต้องการ function นี้สามารถแยกวิเคราะห์ทั้งข้อมูลแบบ binary content ที่ได้จาก File.Contents และข้อความแบบ text string ที่ได้จาก Web.Contents พร้อมจัดการข้อความที่ครอบด้วย quote ตามมาตรฐานสากล CSV standard เหมาะสมสำหรับการนำเข้าไฟล์ CSV จากระบบไฟล์ในเครื่อง (local), ที่อยู่เว็บไซต์ (URL) หรือการตอบกลับจาก API (API response) ที่มีตัวแบ่งข้อมูลแบบกำหนดเอง (custom delimiter), เนื้อหาภาษาต่างประเทศ หรือโครงสร้างพิเศษที่ต้องการการควบคุมเฉพาะ คืนค่าออกมาเป็นตารางที่ทุกคอลัมน์มีประเภทข้อมูลเป็นข้อความ (text type) ควรใช้ร่วมกับ Table.PromoteHeaders เพื่อยกแถวแรกขึ้นเป็นหัวตาราง และ Table.TransformColumnTypes เพื่อแปลงประเภทข้อมูลให้เหมาะสมกับการใช้งาน

Excel.CurrentWorkbook

Excel.CurrentWorkbook เป็น function ที่ใช้เข้าถึง Table, Named Range และ Dynamic Array ทั้งหมดที่อยู่ใน Excel workbook ปัจจุบัน โดย return เป็น table ที่มี metadata และเนื้อหาของแต่ละ object ทำให้สามารถอ้างอิงข้อมูลภายใน workbook ได้อย่างยืดหยุ่น เหมาะสำหรับการสร้าง query ที่ portable และไม่ต้องพึ่งพา file path

File.Contents

File.Contents ใช้อ่านข้อมูล binary ของไฟล์จากพาธที่ระบุ เพื่อนำเข้าข้อมูลจากระบบไฟล์ลงในโปรแกรม

Folder.Contents

Folder.Contents เป็น function สำหรับดึงรายการไฟล์และโฟลเดอร์ทั้งหมดที่อยู่ใน folder path ที่กำหนดไว้ โดยจะคืนค่าเป็น table ที่มีข้อมูล metadata ครบถ้วน เช่น ชื่อไฟล์ วันที่สร้างและแก้ไข ขนาดไฟล์ และ binary content link สำหรับเข้าถึงเนื้อหาภายในไฟล์ เหมาะสำหรับงานที่ต้องการ import ข้อมูลจากหลายไฟล์พร้อมกันในโฟลเดอร์เดียว หรือการวิเคราะห์โครงสร้างไฟล์และคุณสมบัติต่างๆ ก่อนตัดสินใจนำเข้าข้อมูล

Folder.Files

Folder.Files ใช้สำหรับอ่านรายชื่อไฟล์ทั้งหมดในโฟลเดอร์ที่กำหนด รวมถึงโฟลเดอร์ย่อยด้วย คืนค่าเป็น table ที่มีข้อมูลไฟล์เช่น ชื่อ วันที่สร้าง ขนาด และลิงก์เข้าถึงไฟล์

Web.Contents

Web.Contents เป็นฟังก์ชันสำหรับดาวน์โหลดเนื้อหาจากเว็บไซต์หรือแหล่งข้อมูลบนอินเทอร์เน็ตผ่าน URL และคืนค่าเป็นข้อมูลแบบ binary data ที่สามารถนำไปประมวลผลต่อได้
.
รองรับการเชื่อมต่อและเรียกใช้งาน REST API อย่างครบถ้วนพร้อมระบบการยืนยันตัวตนผ่านทางตัวเลือกต่างๆ เช่น การกำหนดค่า Headers สำหรับส่งข้อมูลเพิ่มเติมในคำขอ การใช้ Query parameters สำหรับการกรองข้อมูลและส่งพารามิเตอร์ และการระบุ ApiKeyName เพื่อจัดเก็บคีย์การเข้าถึงอย่างปลอดภัย
.
เหมาะอย่างยิ่งสำหรับการเชื่อมต่อกับบริการเว็บและจุดเชื่อมต่อ API แบบไดนามิก โดยใช้ความสามารถของ RelativePath และตัวเลือก Query เพื่อสร้าง URL แบบยืดหยุ่นและทำให้กระบวนการรีเฟรชข้อมูลทำงานได้อย่างราบรื่นและมีประสิทธิภาพในระบบ Power BI Service

เอกสารอ้างอิง

Microsoft Learn – Excel.Workbook Official Documentationdocumentation
Microsoft Learn – Table.PromoteHeaders (Best Practice for Headers)documentation
Microsoft Learn – File.Contents Functiondocumentation
Microsoft Learn – Web.Contents Functiondocumentation
Microsoft Learn – Power Query Excel Connector Guideguide
Chris Webb's BI Blog – Working With Excel Named Ranges In Power Queryguide
บทความที่เกี่ยวข้อง

เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ
การรวมข้อมูลหลายๆ Sheet จาก Excel หลายๆ ไฟล์ใน Folder แบบที่ต้อง Transform ข้อมูลก่อนรวม
วิธีรวมไฟล์ใน Folder แบบไม่มีปัญหาเรื่องชื่อคอลัมน์ใน Power Query

Comments

อีเมลของคุณจะไม่ถูกเผยแพร่