Excel Power Query 2026: ETL สำหรับ Data Analyst และคำถามสัมภาษณ์

Excel Power Query เปลี่ยนแปลงวิธีที่ data analyst จัดการ workflow ETL (Extract, Transform, Load) โดยตรงภายใน Excel แตกต่างจากการ copy-paste แบบ manual หรือ macro VBA ที่ซับซ้อน Power Query มี visual interface ที่ขับเคลื่อนด้วยภาษา M ช่วยให้การแปลงข้อมูลสามารถทำซ้ำได้และ refresh ได้ในคลิกเดียว

ปัญหาที่ Power Query แก้ไขสำหรับ Data Analyst

Data analyst ใช้เวลาอย่างมากในการเตรียมข้อมูล: รวมไฟล์จากหลายแหล่ง ทำความสะอาด format ที่ไม่สอดคล้องกัน กรองแถวที่ไม่เกี่ยวข้อง และปรับรูปร่างตารางสำหรับการวิเคราะห์ Power Query จัดการงานเหล่านี้ผ่าน query editor ที่บันทึกทุกขั้นตอนการแปลง เมื่อข้อมูลต้นทางเปลี่ยนแปลง pipeline ทั้งหมดจะถูก execute ใหม่โดยอัตโนมัติ

Workflow ดำเนินตามสามขั้นตอน: เชื่อมต่อกับแหล่งข้อมูล ใช้การแปลง และโหลดผลลัพธ์ลงในตาราง Excel หรือ data model แต่ละขั้นตอนถูกบันทึกใน formula bar โดยใช้ syntax ภาษา M ซึ่งสามารถแก้ไขโดยตรงสำหรับสถานการณ์ขั้นสูง

วิธีการนี้แตกต่างจากสูตร Excel แบบดั้งเดิม ในขณะที่สูตรคำนวณใหม่ทีละ cell Power Query ทำงานกับตารางทั้งหมดก่อนที่จะถึง worksheet Query ที่รวม 50 ไฟล์ CSV ลบข้อมูลซ้ำ และ unpivot คอลัมน์ จะทำงานครั้งเดียวและสร้างตารางที่สะอาด แทนที่จะสร้างสูตรซ้อนกันที่ซับซ้อนซึ่งทำให้ workbook ช้าลง

การเชื่อมต่อกับแหล่งข้อมูลด้วย Get Data

Power Query รองรับการเชื่อมต่อกับไฟล์ (CSV, Excel, JSON, XML) ฐานข้อมูล (SQL Server, MySQL, PostgreSQL, Oracle) cloud services (SharePoint, Azure, Salesforce) และหน้าเว็บ ประเภทการเชื่อมต่อกำหนดตัวเลือก authentication และ import ที่ปรากฏ

// Common data sources in Power Query
Data > Get Data > From File > From CSV
Data > Get Data > From Database > From SQL Server Database
Data > Get Data > From Other Sources > From Web
Data > Get Data > From Folder (multiple files)

ตัวเลือก "From Folder" มีประโยชน์อย่างยิ่งสำหรับการรวมหลายไฟล์ แทนที่จะ import แต่ละไฟล์แยกกัน Power Query สแกน folder แสดงรายการไฟล์ที่ตรงกันทั้งหมด และรวมเข้าเป็น query เดียว การเพิ่มไฟล์ใหม่ลงใน folder จะรวมไฟล์นั้นโดยอัตโนมัติเมื่อ refresh ครั้งต่อไป

เมื่อเชื่อมต่อกับฐานข้อมูล SQL Power Query สามารถ push logic การแปลงไปยัง server ผ่าน query folding Filters และการเลือกคอลัมน์จะถูกแปลเป็น SQL WHERE และ SELECT clauses ลดข้อมูลที่ส่งไปยัง Excel Formula bar แสดงตัวเลือก "View Native Query" เมื่อ folding ทำงานอยู่

การแปลงหลักใน Query Editor

Query Editor มี ribbon commands สำหรับการดำเนินการทั่วไป แต่การเข้าใจ M code พื้นฐานจะช่วยเมื่อต้องการ customization แต่ละการแปลงจะเพิ่มขั้นตอนใน "Applied Steps" pane สร้างลำดับที่สามารถตรวจสอบได้

การกรองและเรียงลำดับแถว

การกรองจะลบแถวที่ไม่ตรงตามเกณฑ์ Column header dropdown มี quick filters ในขณะที่ dialog "Filter Rows" รองรับเงื่อนไขที่ซับซ้อนด้วย logic AND/OR

// FilteredRows step in M language
let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Region] = "EMEA" and [Amount] > 1000)
in
    FilteredRows

ฟังก์ชัน Table.SelectRows รับตารางและเงื่อนไข Keyword each สร้างฟังก์ชันที่ _ แทนแถวปัจจุบัน และการเข้าถึง field ใช้ bracket notation [Region] หลายเงื่อนไขรวมกันด้วย operators and หรือ or

การลบและเปลี่ยนชื่อคอลัมน์

แหล่งข้อมูลมักรวมคอลัมน์ที่ไม่จำเป็นสำหรับการวิเคราะห์ การลบออกตั้งแต่เนิ่นๆ ช่วยลดการใช้หน่วยความจำและทำให้ขั้นตอนต่อไปง่ายขึ้น

// Remove columns, then rename remaining ones
let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    RemovedColumns = Table.RemoveColumns(Source, {"TempID", "InternalNotes", "Debug"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns, {{"Cust_Name", "CustomerName"}, {"Amt", "Amount"}})
in
    RenamedColumns

รายการคอลัมน์ใช้ curly braces {} สำหรับหลายรายการ การเปลี่ยนชื่อใช้รายการคู่ โดยแต่ละคู่มีชื่อเก่าและชื่อใหม่ รูปแบบการตั้งชื่อที่สอดคล้องกันใน queries ทำให้การรวม datasets ง่ายขึ้น

การแยกและรวมคอลัมน์

คอลัมน์ข้อความมักต้องการ parsing คอลัมน์ "FullName" อาจต้องแยกเป็นชื่อและนามสกุล หรือคอลัมน์วันที่และเวลาแยกกันอาจต้องรวมกัน

// Split FullName by delimiter into two columns
let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    SplitColumn = Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"FirstName", "LastName"})
in
    SplitColumn

ฟังก์ชัน Splitter.SplitTextByDelimiter จัดการ parsing logic สำหรับ patterns ที่ซับซ้อนกว่า Splitter.SplitTextByEachDelimiter หรือ Splitter.SplitTextByPositions มีการควบคุมเพิ่มเติม เมื่อจำนวนคอลัมน์ผลลัพธ์แตกต่างกัน Power Query สร้างคอลัมน์แบบ dynamic

การแปลงประเภทและคุณภาพข้อมูล

Power Query อนุมานประเภทคอลัมน์เมื่อ import แต่การกำหนดประเภทอย่างชัดเจนจะจับ errors ได้เร็ว คอลัมน์ข้อความที่มี numeric IDs ควรยังคงเป็นข้อความหากเลข 0 นำหน้าสำคัญ คอลัมน์วันที่ที่ import เป็นข้อความทำให้เกิดปัญหาการเรียงลำดับ

// Explicit type assignments
let
    Source = Csv.Document(File.Contents("C:\\Data\\transactions.csv")),
    TypedColumns = Table.TransformColumnTypes(Source, {
        {"TransactionID", type text},
        {"Date", type date},
        {"Amount", type number},
        {"IsProcessed", type logical}
    })
in
    TypedColumns

Keyword type ระบุประเภทเป้าหมาย ประเภทที่มีได้แก่ text, number, date, datetime, datetimezone, time, duration, logical และ binary Type errors ปรากฏเป็นค่า "Error" ใน cells ทำให้เห็นปัญหาคุณภาพข้อมูลก่อนการวิเคราะห์

การจัดการค่า null ต้องการ logic ที่ชัดเจน ฟังก์ชัน Table.ReplaceValue แทนที่ null ด้วยค่าเริ่มต้น ในขณะที่ Table.SelectRows กับ [Column] <> null กรองออก

การจัดกลุ่มและการรวมด้วย Group By

การรวมข้อมูลตามหมวดหมู่เป็นความต้องการที่พบบ่อย การแปลง "Group By" รวมแถวที่มีค่า key เดียวกันและใช้ aggregate functions

// Group sales by region and year, calculate sum and count
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source, {"Region", "Year"}, {
        {"TotalSales", each List.Sum([Amount]), type number},
        {"OrderCount", each Table.RowCount(_), Int64.Type},
        {"AvgOrderValue", each List.Average([Amount]), type number}
    })
in
    Grouped

คอลัมน์การจัดกลุ่มปรากฏก่อน ตามด้วยคำจำกัดความการรวม แต่ละการรวมระบุชื่อคอลัมน์ใหม่ aggregation function และประเภทผลลัพธ์ที่เลือกได้ Keyword each แทน subtable สำหรับแต่ละกลุ่ม อนุญาตให้ใช้ฟังก์ชัน table หรือ list ใดๆ

Nested aggregations ช่วยให้คำนวณเช่น "percentage of group total" โดยอ้างอิงทั้งค่าแถวและ group aggregate ในขั้นตอนถัดไป

Pivoting และ Unpivoting สำหรับการปรับรูปร่างข้อมูล

Pivoting แปลงค่าแถวเป็นคอลัมน์ สร้าง crosstab layout Unpivoting ทำตรงกันข้าม แปลงคอลัมน์เป็นแถวสำหรับโครงสร้างที่ normalized

// Unpivot month columns into rows
let
    Source = Excel.CurrentWorkbook(){[Name="MonthlySales"]}[Content],
    // Original: Product, Jan, Feb, Mar, Apr columns
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")
    // Result: Product, Month, Sales columns
in
    Unpivoted

ฟังก์ชัน Table.UnpivotOtherColumns เก็บคอลัมน์ที่ระบุไว้และ unpivot ส่วนที่เหลือ นี่ปลอดภัยกว่าการระบุคอลัมน์ทั้งหมดที่จะ unpivot เพราะการเพิ่มคอลัมน์เดือนใหม่จะรวมเข้าโดยอัตโนมัติ สอง parameters สุดท้ายตั้งชื่อคอลัมน์ attribute ("Month") และคอลัมน์ value ("Sales")

Pivoting ใช้ Table.Pivot พร้อม aggregation function สำหรับกรณีที่มีหลายค่าสำหรับ row-column combination เดียวกัน:

// Pivot sales by region
let
    Source = Excel.CurrentWorkbook(){[Name="DetailedSales"]}[Content],
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", "Amount", List.Sum)
in
    Pivoted

การรวม Queries ด้วย Merge และ Append

การรวมข้อมูลจากหลายแหล่งเป็นจุดที่ Power Query ลดความพยายามแบบ manual Merge ทำ join ระหว่างสองตารางตามคอลัมน์ที่ตรงกัน Append วางตารางซ้อนกันแนวตั้ง

// Left join: Orders with Customer details
let
    Orders = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Customers = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    Merged = Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"ID"}, "CustomerDetails", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "CustomerDetails", {"Name", "Email"})
in
    Expanded

ฟังก์ชัน Table.NestedJoin สร้างคอลัมน์ตาราง nested ที่มีแถวที่ตรงกัน ฟังก์ชัน Table.ExpandTableColumn จากนั้น flatten โครงสร้าง nested เป็นคอลัมน์ปกติ Join kinds ประกอบด้วย Inner, LeftOuter, RightOuter, FullOuter, LeftAnti และ RightAnti

Append ด้วย Table.Combine ต้องการชื่อคอลัมน์ที่ตรงกัน เมื่อ schemas แตกต่างกัน Table.SelectColumns บนแต่ละ source ก่อนรวมจะรับประกันความสอดคล้อง:

// Append two sales tables with consistent columns
let
    Sales2024 = Table.SelectColumns(Source2024, {"Date", "Product", "Amount"}),
    Sales2025 = Table.SelectColumns(Source2025, {"Date", "Product", "Amount"}),
    Combined = Table.Combine({Sales2024, Sales2025})
in
    Combined

Custom Columns และ Conditional Logic

ฟีเจอร์ "Add Column > Custom Column" อนุญาตให้สร้าง calculated fields โดยใช้ M expressions Conditional logic ใช้ syntax if-then-else

// Add a calculated column with conditional logic
let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    AddedColumn = Table.AddColumn(Source, "OrderCategory", each 
        if [Amount] >= 10000 then "Enterprise"
        else if [Amount] >= 1000 then "Business"
        else "Consumer",
        type text
    )
in
    AddedColumn

if expression ต้องมีทั้ง then และ else branches Nested conditions เชื่อมด้วย else if Parameter สุดท้ายระบุ column type ปรับปรุง performance และป้องกันปัญหา type inference

สำหรับการแปลงที่ซับซ้อน helper functions ที่กำหนดใน let block ทำให้ code อ่านง่าย:

let
    // Helper function for fiscal quarter
    GetFiscalQuarter = (date as date) as text =>
        let
            month = Date.Month(date),
            fiscalQ = if month >= 4 and month <= 6 then "Q1"
                     else if month >= 7 and month <= 9 then "Q2"
                     else if month >= 10 and month <= 12 then "Q3"
                     else "Q4"
        in
            fiscalQ,
    
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    AddedQuarter = Table.AddColumn(Source, "FiscalQuarter", each GetFiscalQuarter([Date]), type text)
in
    AddedQuarter

การจัดการ Error ใน Power Query

Transformation errors ปรากฏเป็นค่า "Error" ใน cells แทนที่จะทำให้ query ทั้งหมดล้มเหลว Construct try-otherwise จัดการ errors อย่างเรียบร้อย:

// Handle potential division errors
let
    Source = Excel.CurrentWorkbook(){[Name="Metrics"]}[Content],
    AddedRatio = Table.AddColumn(Source, "Ratio", each 
        try [Value1] / [Value2] otherwise null,
        type number
    )
in
    AddedRatio

Keyword try พยายามทำ expression และคืนค่า record ที่มี fields HasError และ Value Clause otherwise ให้ค่า fallback เมื่อ HasError เป็น true สำหรับการควบคุมมากขึ้น เข้าถึงรายละเอียด error ด้วย try Expression:

// Capture error details
let
    result = try SomeRiskyFunction(),
    output = if result[HasError] then "Error: " & result[Error][Message] else result[Value]
in
    output

คำถามสัมภาษณ์ Power Query สำหรับ Data Analyst

ผู้สัมภาษณ์ประเมินทั้งทักษะปฏิบัติและความเข้าใจว่า Power Query เหมาะกับ workflow เมื่อใด คำถามเหล่านี้ปรากฏบ่อยในการสัมภาษณ์ data analyst

Query folding คืออะไรและทำไมจึงสำคัญ?

Query folding แปลการแปลง Power Query เป็น native queries สำหรับแหล่งข้อมูล เมื่อเชื่อมต่อกับ SQL Server filter step กลายเป็น WHERE clause ที่ execute บน server ลดการส่งผ่าน network ไม่ใช่ทุกการแปลงจะ fold ได้: custom M functions, date manipulations บางอย่าง และ operations หลังจาก non-folding step จะทำลาย chain ตรวจสอบสถานะ folding โดยคลิกขวาที่ step และหา "View Native Query"

Power Query แตกต่างจากสูตร Excel สำหรับการแปลงข้อมูลอย่างไร?

สูตร Excel ทำงานทีละ cell ภายใน worksheet และคำนวณใหม่ทุกการเปลี่ยนแปลง Power Query ทำงานกับตารางก่อนที่จะถึง worksheet ประมวลผลข้อมูลเป็น bulk ระหว่าง refresh สำหรับการแปลงเช่น unpivoting, deduplication หรือการรวมไฟล์ Power Query แสดง logic โดยตรงกว่า INDEX-MATCH ซ้อนกันหรือ helper columns

เมื่อใดควรเลือก Power Query แทน Power BI สำหรับ ETL?

Power Query ใน Excel เหมาะกับสถานการณ์ที่ analysts ต้องการข้อมูลที่แปลงแล้วในรูปแบบ spreadsheet สำหรับการวิเคราะห์แบบ ad-hoc, pivot tables หรือแชร์กับผู้ใช้ที่ไม่มี Power BI access Power BI มี visualization ที่หลากหลายกว่า data capacity ใหญ่กว่า และ enterprise sharing features M code เดียวกันทำงานในทั้งสองเครื่องมือ ดังนั้น queries ที่พัฒนาใน Excel สามารถ migrate ไป Power BI Desktop ได้โดยไม่ต้องเขียนใหม่

จะจัดการ data type mismatches เมื่อ append tables อย่างไร?

กำหนด explicit types บนแต่ละ source table ก่อนรวมด้วย Table.Combine ถ้าคอลัมน์เป็น text ใน source หนึ่งและ number ใน source อื่น append จะล้มเหลวหรือสร้าง errors ใช้ Table.TransformColumnTypes บนทั้งสอง sources เพื่อบังคับ types ที่สอดคล้องกัน Pattern try-otherwise จัดการ edge cases ที่ conversion ล้มเหลวสำหรับค่าเฉพาะ

อธิบายความแตกต่างระหว่าง Merge และ Append ใน Power Query

Merge ทำ horizontal join ตาม key columns ที่ตรงกัน คล้ายกับ SQL JOIN Append ทำ vertical union ของแถวจากหลายตาราง คล้ายกับ SQL UNION ALL Merge ต้องการอย่างน้อยหนึ่ง common column สำหรับการจับคู่ Append ต้องการคอลัมน์ที่มีชื่อเดียวกันเพื่อ align อย่างถูกต้อง

สำหรับการเตรียมตัวเชิงลึกเกี่ยวกับแนวคิด SQL ที่เสริมทักษะ Power Query module SQL window functions ครอบคลุม ranking และ aggregation patterns ในขณะที่ module SQL subqueries และ CTEs กล่าวถึง query structuring techniques

ตัวเลือก Loading และกลยุทธ์ Refresh

หลังการแปลง ปุ่ม "Close & Load" มีตัวเลือก loading: load ไปยัง worksheet table, load เฉพาะไปยัง data model (Power Pivot) หรือสร้าง connection-only query Connection-only queries ทำหน้าที่เป็น staging steps สำหรับ queries อื่นโดยไม่ใช้พื้นที่ worksheet

พฤติกรรม refresh ขึ้นอยู่กับ load destination Worksheet tables refresh ด้วยปุ่ม "Refresh All" หรือสามารถตั้งให้ refresh เมื่อเปิดไฟล์ Data model tables เข้าร่วมใน data model refresh cycle ของ workbook สำหรับ queries ที่เชื่อมต่อกับ external databases credential prompts อาจปรากฏเมื่อ refresh เว้นแต่ saved credentials มีอยู่

Background refresh อนุญาตให้ workbook ใช้งานได้ระหว่าง data loading แต่สร้างความซับซ้อนเมื่อ downstream formulas ขึ้นอยู่กับ refreshed data ตัวเลือก "Enable background refresh" ใน query properties ควบคุมพฤติกรรมนี้ สำหรับ critical reports การปิด background refresh รับประกัน sequential execution

ข้อพิจารณาด้าน Performance สำหรับ Large Datasets

Power Query สามารถจัดการหลายล้านแถว แต่ workbook responsiveness ขึ้นอยู่กับวิธี load ข้อมูล Loading ไปยัง data model แทน worksheet tables หลีกเลี่ยง row limit ของ Excel และปรับปรุง pivot table performance การลบคอลัมน์ที่ไม่จำเป็นตั้งแต่เนิ่นๆ ลด memory footprint

สำหรับ queries ที่ใช้เวลาหลายนาที "Data preview" ใน Query Editor แสดงเฉพาะ sample Transformations ใช้กับ dataset เต็มระหว่าง refresh Errors ที่เห็นใน preview บ่งบอกปัญหา แต่บาง errors ปรากฏเฉพาะกับ full data การรัน test refresh บน subset validates query ก่อนประมวลผลทุกอย่าง

เมื่อรวมหลายไฟล์ ฟีเจอร์ binary combination ของ Power Query ประมวลผลไฟล์แบบ parallel การกำหนดฟังก์ชันที่แปลงหนึ่งไฟล์ แล้วเรียกใช้สำหรับแต่ละแถวในรายการไฟล์ ให้การควบคุมมากกว่า default combine approach

เอกสาร Microsoft เกี่ยวกับ Power Query best practices อธิบายรายละเอียด optimization techniques รวมถึง query dependency structures และการใช้ buffer

Power Query สำหรับ Repeatable ETL Pipelines

ขั้นตอนที่บันทึกใน Power Query สร้างเอกสารของ transformation logic เมื่อ requirements เปลี่ยน การแก้ไขขั้นตอนจะอัพเดท downstream steps ทั้งหมดโดยอัตโนมัติ นี่แตกต่างจาก ad-hoc Excel manipulations ที่ต้องสร้างใหม่จากต้นเมื่อ source data เปลี่ยน

ทีมสามารถแชร์ queries โดยการ export connections หรือโดยการเก็บ query M code ใน version control Advanced Editor (View > Advanced Editor) แสดง M script ที่สมบูรณ์ ซึ่งสามารถ copy และ paste ไปยัง workbook อื่น สำหรับ enterprise scenarios Power BI dataflows และ Fabric มี centralized query management

• Power Query จัดการ ETL ภายใน Excel โดยใช้ recorded, repeatable transformation steps
• ภาษา M พื้นฐานของ visual interface อนุญาตให้ customizations เกินกว่า ribbon commands
• Query folding push filters และ projections ไปยัง source databases สำหรับ performance ที่ดีขึ้น
• Merge ทำ joins ระหว่างตาราง Append วางตารางซ้อนกันแนวตั้ง
• Type assignments และ error handling ป้องกันปัญหา data quality ที่มองไม่เห็น
• Connection-only queries สร้าง reusable staging steps โดยไม่มี worksheet output
• คำถามสัมภาษณ์เน้นที่ query folding, การเปรียบเทียบสูตร และความแตกต่าง merge versus append