Excel Power Query 2026: ETL untuk Data Analyst dan Pertanyaan Interview

Excel Power Query mengubah cara data analyst menangani workflow ETL (Extract, Transform, Load) langsung di dalam Excel. Berbeda dengan copy-paste manual atau macro VBA yang kompleks, Power Query menyediakan antarmuka visual yang didukung bahasa M, memungkinkan transformasi data yang dapat diulang dan di-refresh hanya dengan satu klik.

Masalah yang Diselesaikan Power Query untuk Data Analyst

Data analyst menghabiskan waktu signifikan untuk persiapan data: menggabungkan file dari berbagai sumber, membersihkan format yang tidak konsisten, memfilter baris yang tidak relevan, dan mengubah bentuk tabel untuk analisis. Power Query mengatasi tugas-tugas ini melalui query editor yang merekam setiap langkah transformasi. Ketika data sumber berubah, seluruh pipeline dieksekusi ulang secara otomatis.

Workflow mengikuti tiga tahap: koneksi ke sumber data, penerapan transformasi, dan pemuatan hasil ke tabel Excel atau data model. Setiap langkah direkam di formula bar menggunakan sintaks bahasa M, yang dapat diedit langsung untuk skenario lanjutan.

Pendekatan ini berbeda dari formula Excel tradisional. Sementara formula menghitung ulang per sel, Power Query beroperasi pada keseluruhan tabel sebelum mencapai worksheet. Query yang mengkonsolidasi 50 file CSV, menghapus duplikat, dan melakukan unpivot kolom berjalan sekali dan menghasilkan tabel bersih, bukan membangun formula bersarang kompleks yang memperlambat workbook.

Menghubungkan ke Sumber Data dengan Get Data

Power Query mendukung koneksi ke file (CSV, Excel, JSON, XML), database (SQL Server, MySQL, PostgreSQL, Oracle), layanan cloud (SharePoint, Azure, Salesforce), dan halaman web. Tipe koneksi menentukan opsi autentikasi dan impor yang muncul.

// 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)

Opsi "From Folder" sangat berguna untuk mengkonsolidasi banyak file. Alih-alih mengimpor setiap file secara terpisah, Power Query memindai folder, mencantumkan semua file yang cocok, dan menggabungkannya menjadi satu query. Menambahkan file baru ke folder secara otomatis menyertakannya pada refresh berikutnya.

Saat menghubungkan ke database SQL, Power Query dapat mendorong logika transformasi ke server melalui query folding. Filter dan seleksi kolom diterjemahkan menjadi klausa SQL WHERE dan SELECT, mengurangi data yang ditransfer ke Excel. Formula bar menampilkan opsi "View Native Query" ketika folding aktif.

Transformasi Inti di Query Editor

Query Editor menyediakan perintah ribbon untuk operasi umum, tetapi memahami kode M yang mendasarinya membantu ketika diperlukan kustomisasi. Setiap transformasi menambahkan langkah ke panel "Applied Steps", menciptakan urutan yang dapat diaudit.

Memfilter dan Mengurutkan Baris

Filtering menghapus baris yang tidak memenuhi kriteria. Dropdown header kolom menyediakan filter cepat, sementara dialog "Filter Rows" mendukung kondisi kompleks dengan logika 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

Fungsi Table.SelectRows mengambil tabel dan kondisi. Kata kunci each membuat fungsi di mana _ mewakili baris saat ini, dan akses field menggunakan notasi bracket [Region]. Beberapa kondisi digabungkan dengan operator and atau or.

Menghapus dan Mengganti Nama Kolom

Sumber data sering menyertakan kolom yang tidak diperlukan untuk analisis. Menghapusnya lebih awal mengurangi penggunaan memori dan menyederhanakan langkah-langkah selanjutnya.

// 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

Daftar kolom menggunakan kurung kurawal {} untuk beberapa item. Penggantian nama menggunakan daftar pasangan, di mana setiap pasangan berisi nama lama dan nama baru. Konvensi penamaan yang konsisten di seluruh query mempermudah penggabungan dataset.

Memisah dan Menggabungkan Kolom

Kolom teks sering memerlukan parsing. Kolom "FullName" mungkin perlu dipisah menjadi nama depan dan belakang, atau kolom tanggal dan waktu terpisah mungkin perlu digabung.

// 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

Fungsi Splitter.SplitTextByDelimiter menangani logika parsing. Untuk pola yang lebih kompleks, Splitter.SplitTextByEachDelimiter atau Splitter.SplitTextByPositions menawarkan kontrol tambahan. Ketika jumlah kolom hasil bervariasi, Power Query membuat kolom secara dinamis.

Konversi Tipe dan Kualitas Data

Power Query menyimpulkan tipe kolom saat impor, tetapi penetapan tipe eksplisit menangkap error lebih awal. Kolom teks yang berisi ID numerik harus tetap sebagai teks jika leading zeros penting. Kolom tanggal yang diimpor sebagai teks menyebabkan masalah pengurutan.

// 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

Kata kunci type menentukan tipe target. Tipe yang tersedia termasuk text, number, date, datetime, datetimezone, time, duration, logical, dan binary. Error tipe muncul sebagai nilai "Error" di sel, membuat masalah kualitas data terlihat sebelum analisis.

Penanganan nilai null memerlukan logika eksplisit. Fungsi Table.ReplaceValue mengganti null dengan default, sementara Table.SelectRows dengan [Column] <> null memfilternya.

Pengelompokan dan Agregasi dengan Group By

Mengagregasi data berdasarkan kategori adalah kebutuhan yang sering. Transformasi "Group By" menggabungkan baris yang memiliki nilai kunci yang sama dan menerapkan fungsi agregat.

// 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

Kolom pengelompokan muncul pertama, diikuti oleh definisi agregasi. Setiap agregasi menentukan nama kolom baru, fungsi agregasi, dan tipe hasil opsional. Kata kunci each mewakili subtabel untuk setiap grup, memungkinkan fungsi tabel atau list apa pun.

Agregasi bersarang memungkinkan perhitungan seperti "persentase dari total grup" dengan mereferensikan nilai baris dan agregat grup dalam langkah berikutnya.

Pivoting dan Unpivoting untuk Mengubah Bentuk Data

Pivoting mengubah nilai baris menjadi kolom, membuat layout crosstab. Unpivoting melakukan sebaliknya, mengubah kolom menjadi baris untuk struktur yang dinormalisasi.

// 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

Fungsi Table.UnpivotOtherColumns mempertahankan kolom yang ditentukan dan melakukan unpivot sisanya. Ini lebih aman daripada mencantumkan semua kolom untuk di-unpivot, karena menambahkan kolom bulan baru secara otomatis menyertakannya. Dua parameter terakhir menamai kolom atribut ("Month") dan kolom nilai ("Sales").

Pivoting menggunakan Table.Pivot dengan fungsi agregasi untuk kasus di mana beberapa nilai ada untuk kombinasi baris-kolom yang sama:

// 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

Menggabungkan Query dengan Merge dan Append

Menggabungkan data dari berbagai sumber adalah di mana Power Query mengurangi upaya manual. Merge melakukan join antara dua tabel berdasarkan kolom yang cocok. Append menumpuk tabel secara vertikal.

// 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

Fungsi Table.NestedJoin membuat kolom tabel bersarang yang berisi baris yang cocok. Fungsi Table.ExpandTableColumn kemudian meratakan struktur bersarang menjadi kolom reguler. Jenis join termasuk Inner, LeftOuter, RightOuter, FullOuter, LeftAnti, dan RightAnti.

Append dengan Table.Combine memerlukan nama kolom yang cocok. Ketika skema berbeda, Table.SelectColumns pada setiap sumber sebelum menggabungkan memastikan konsistensi:

// 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

Kolom Kustom dan Logika Kondisional

Fitur "Add Column > Custom Column" memungkinkan field terhitung menggunakan ekspresi M. Logika kondisional menggunakan sintaks 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

Ekspresi if harus menyertakan branch then dan else. Kondisi bersarang dirangkai dengan else if. Parameter terakhir menentukan tipe kolom, meningkatkan performa dan mencegah masalah inferensi tipe.

Untuk transformasi kompleks, fungsi helper yang didefinisikan di blok let menjaga kode tetap terbaca:

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

Penanganan Error di Power Query

Error transformasi muncul sebagai nilai "Error" di sel alih-alih menggagalkan seluruh query. Konstruksi try-otherwise menangani error dengan baik:

// 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

Kata kunci try mencoba ekspresi dan mengembalikan record dengan field HasError dan Value. Klausa otherwise menyediakan fallback ketika HasError bernilai true. Untuk kontrol lebih, akses detail error dengan try Expression:

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

Pertanyaan Interview Power Query untuk Data Analyst

Pewawancara menilai baik keterampilan praktis maupun pemahaman tentang kapan Power Query cocok untuk workflow. Pertanyaan-pertanyaan ini sering muncul dalam interview data analyst.

Apa itu query folding dan mengapa penting?

Query folding menerjemahkan transformasi Power Query menjadi query native untuk sumber data. Saat menghubungkan ke SQL Server, langkah filter menjadi klausa WHERE yang dieksekusi di server, mengurangi transfer jaringan. Tidak semua transformasi dapat di-fold: fungsi M kustom, manipulasi tanggal tertentu, dan operasi setelah langkah non-folding memutus rantai. Periksa status folding dengan klik kanan pada langkah dan cari "View Native Query."

Bagaimana Power Query berbeda dari formula Excel untuk transformasi data?

Formula Excel beroperasi per sel dalam worksheet dan menghitung ulang pada setiap perubahan. Power Query beroperasi pada tabel sebelum mencapai worksheet, memproses data secara massal selama refresh. Untuk transformasi seperti unpivoting, deduplikasi, atau penggabungan file, Power Query mengekspresikan logika lebih langsung daripada INDEX-MATCH bersarang atau kolom helper.

Kapan memilih Power Query daripada Power BI untuk ETL?

Power Query di Excel cocok untuk skenario di mana analyst memerlukan data yang ditransformasi dalam bentuk spreadsheet untuk analisis ad-hoc, pivot table, atau berbagi dengan pengguna yang tidak memiliki akses Power BI. Power BI menyediakan visualisasi lebih kaya, kapasitas data lebih besar, dan fitur berbagi enterprise. Kode M yang sama berfungsi di kedua tool, sehingga query yang dikembangkan di Excel dapat dimigrasi ke Power BI Desktop tanpa menulis ulang.

Bagaimana menangani ketidakcocokan tipe data saat append tabel?

Tetapkan tipe eksplisit pada setiap tabel sumber sebelum menggabungkan dengan Table.Combine. Jika kolom adalah teks di satu sumber dan angka di sumber lain, append gagal atau menghasilkan error. Gunakan Table.TransformColumnTypes pada kedua sumber untuk memastikan tipe yang konsisten. Pola try-otherwise menangani kasus tepi di mana konversi gagal untuk nilai tertentu.

Jelaskan perbedaan antara Merge dan Append di Power Query.

Merge melakukan join horizontal berdasarkan kolom kunci yang cocok, mirip SQL JOIN. Append melakukan union vertikal baris dari beberapa tabel, mirip SQL UNION ALL. Merge memerlukan setidaknya satu kolom umum untuk pencocokan. Append memerlukan kolom dengan nama yang sama agar selaras dengan benar.

Untuk persiapan lebih mendalam tentang konsep SQL yang melengkapi keterampilan Power Query, modul SQL window functions membahas pola ranking dan agregasi, sementara modul SQL subqueries dan CTEs membahas teknik strukturisasi query.

Opsi Loading dan Strategi Refresh

Setelah transformasi, tombol "Close & Load" menawarkan pilihan loading: load ke tabel worksheet, load hanya ke data model (Power Pivot), atau membuat query connection-only. Query connection-only berfungsi sebagai langkah staging untuk query lain tanpa menggunakan ruang worksheet.

Perilaku refresh tergantung pada tujuan load. Tabel worksheet di-refresh dengan tombol "Refresh All" atau dapat diatur untuk refresh saat file dibuka. Tabel data model berpartisipasi dalam siklus refresh data model workbook. Untuk query yang terhubung ke database eksternal, prompt kredensial mungkin muncul saat refresh kecuali kredensial tersimpan ada.

Background refresh memungkinkan workbook tetap dapat digunakan selama loading data, tetapi menciptakan kompleksitas ketika formula downstream bergantung pada data yang di-refresh. Opsi "Enable background refresh" di properti query mengontrol perilaku ini. Untuk laporan kritis, menonaktifkan background refresh memastikan eksekusi sekuensial.

Pertimbangan Performa untuk Dataset Besar

Power Query dapat menangani jutaan baris, tetapi responsivitas workbook tergantung pada bagaimana data di-load. Loading ke data model alih-alih tabel worksheet menghindari batas baris Excel dan meningkatkan performa pivot table. Menghapus kolom yang tidak diperlukan lebih awal mengurangi footprint memori.

Untuk query yang memakan waktu beberapa menit, "Data preview" di Query Editor hanya menampilkan sampel. Transformasi diterapkan ke dataset penuh selama refresh. Error yang terlihat di preview menunjukkan masalah, tetapi beberapa error hanya muncul dengan data penuh. Menjalankan test refresh pada subset memvalidasi query sebelum memproses semuanya.

Saat mengkonsolidasi banyak file, fitur binary combination Power Query memproses file secara paralel. Mendefinisikan fungsi yang mentransformasi satu file, kemudian memanggilnya untuk setiap baris dalam daftar file, memberikan kontrol lebih daripada pendekatan combine default.

Dokumentasi Microsoft tentang Power Query best practices menjelaskan teknik optimisasi termasuk struktur dependensi query dan penggunaan buffer.

Power Query untuk Pipeline ETL yang Dapat Diulang

Langkah-langkah yang direkam di Power Query membuat dokumentasi logika transformasi. Ketika persyaratan berubah, memodifikasi langkah memperbarui semua langkah downstream secara otomatis. Ini berbeda dengan manipulasi Excel ad-hoc yang memerlukan pembuatan ulang dari awal ketika data sumber berubah.

Tim dapat berbagi query dengan mengekspor koneksi atau dengan menyimpan kode M query di version control. Advanced Editor (View > Advanced Editor) menampilkan script M lengkap, yang dapat disalin dan ditempel ke workbook lain. Untuk skenario enterprise, Power BI dataflows dan Fabric menyediakan manajemen query terpusat.

• Power Query menangani ETL dalam Excel menggunakan langkah transformasi yang direkam dan dapat diulang
• Bahasa M yang mendasari antarmuka visual memungkinkan kustomisasi di luar perintah ribbon
• Query folding mendorong filter dan proyeksi ke database sumber untuk performa lebih baik
• Merge melakukan join antar tabel, Append menumpuk tabel secara vertikal
• Penetapan tipe dan penanganan error mencegah masalah kualitas data yang tidak terlihat
• Query connection-only membuat langkah staging yang dapat digunakan kembali tanpa output worksheet
• Pertanyaan interview fokus pada query folding, perbandingan formula, dan perbedaan merge versus append