Studi Kasus: Membangun Dashboard Excel dari Google Sheets (dengan Bantuan ChatGPT & Power Query)

Bagi banyak orang, termasuk saya, Google Sheets unggul dalam hal kolaborasi, sementara Microsoft Excel tetap menjadi raja untuk analisis data dan pembuatan dashboard yang kompleks. Pertanyaannya adalah, bisakah kita mendapatkan yang terbaik dari kedua dunia? Jawabannya adalah ya, dengan menggunakan Power Query di Excel.

Ini adalah catatan tentang bagaimana saya beralih ke alur kerja yang lebih efisien. Semua data mentah tetap berada di Google Sheets untuk kolaborasi tim, tetapi ditarik, digabungkan, dan diolah di Excel untuk analisis yang lebih mendalam.

Alur Kerja: Data di Cloud, Analisis di Desktop

Idenya sederhana:

1. Sumber Data: Semua data mentah dari berbagai periode atau kategori dimasukkan oleh tim ke dalam beberapa sheet di dalam satu file Google Sheets.
2. Proses Pengambilan: Di Excel (Microsoft 365), saya menggunakan fitur Data > Get Data > From Web dan Blank Query untuk menarik data dari setiap sheet tersebut.
3. Transformasi dengan Power Query: Data yang sudah ditarik kemudian digabungkan dan dibersihkan menggunakan kode M di Advanced Editor Power Query.
4. Analisis: Tabel hasil olahan ini kemudian menjadi dasar untuk membuat PivotTable, Slicer, dan dashboard interaktif di Excel.

Keuntungannya? Data di Excel tetap sinkron. Cukup dengan mengklik "Refresh", Excel akan kembali menarik data terbaru dari Google Sheets, tanpa perlu menyalin atau menempel secara manual.

---

Kolaborasi dengan AI: Memanfaatkan ChatGPT untuk Kode Power Query

Di sinilah keajaiban sesungguhnya terjadi. Bahasa M di Power Query bisa terasa asing dan rumit jika Anda tidak terbiasa. Awalnya saya berpikir ini akan menjadi proses yang memakan waktu, tetapi saya memutuskan untuk mencoba berkolaborasi dengan ChatGPT.

Saya membuka Advanced Editor Power Query dan, alih-alih menulis kode dari nol, saya hanya menjelaskan tujuan saya kepada ChatGPT dalam bahasa biasa:

"Saya punya 4 URL CSV dari Google Sheets. Saya ingin menggabungkannya menjadi satu tabel. Sheet pertama memiliki header di baris pertama, sedangkan sheet lainnya memiliki header di baris kedua. Saya juga perlu menghapus kolom 'No.' dan memastikan semua data teks dan angka diformat dengan benar."

ChatGPT segera memberikan kerangka dasar kode M yang hampir lengkap. Saya hanya perlu melakukan beberapa penyesuaian kecil untuk mencocokkan nama kolom dan URL yang spesifik. Proses yang mungkin memakan waktu berjam-jam untuk riset dan trial-error, selesai dalam hitungan menit.

Berikut adalah kode M hasil kolaborasi tersebut.

let
    // 1. Pengaturan Awal: Ambil Header & Tipe Data dari Sheet Pertama
    Source1_Raw = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/YOUR_GOOGLE_SHEETS_ID/pub?gid=0&single=true&output=csv"),[Delimiter=",", Columns=42, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
    Source1_Promoted = Table.PromoteHeaders(Source1_Raw, [PromoteAllScalars=true]),
    OriginalHeaders = Table.ColumnNames(Source1_Promoted),

    // Definisikan tipe data untuk setiap kolom
    OriginalTypes = {
        {"No.", type text}, {"Nama Pekerjaan", type text}, {"PID", type text}, {"WBS", type text}, 
        {"Jenis Pekerjaan", type text}, {"Nama Mitra", type text}, {"ID PR", type text}, {"ID PO", type text}, 
        {"Nodin / Nota Pengadaan", type text}, {"No. Penetapan", type text}, {"No. Kesanggupan", type text}, 
        {"No. Surat Pesanan", type text}, {"Judul Surat Pesanan", type text}, {"Tanggal Surat Pesanan", type text}, 
        {"Jangka Waktu", type text}, {"TOC ", type text}, {"Tahun Pekerjaan", type text}, {"Status SP Mitra", type text}, 
        {"Akselersi", type text}, {"Nilai Material", type text}, {"Nilai Jasa", type text}, 
        {"Total Material + Jasa", type text}, {"PPN 10% / PPN 11%", type text}, {"Nilai Total (setelah PPN 11%)", type text}, 
        {"Grade Mitra", type text}, {"Nilai Bayar Ke Mitra", type text}, {"Adjustment (Potong Tagihan)", type text}, 
        {"ID GR", type text}, {"Short Text", type text}, {"No. BAST", type text}, {"Tanggal BAST", type text}, 
        {"No. Invoice", type text}, {"No. Faktur Pajak", type text}, {"Tanggal Faktur", type text}, 
        {"Status APM", type text}, {"VALIDASI PROC", type text}, {"ID APM", type text}, {"VALIDASI SMS", type text}, 
        {"PIC", type text}, {"KHS", type text}, {"Payment Entry Date", type text}, {"Due Date", type text}
    },
    FinalTypes = List.FirstN(List.RemoveFirstN(OriginalTypes, 1), List.Count(OriginalHeaders) - 1),

    // 2. Proses Sheet 1
    Source1_Skipped = Table.Skip(Source1_Promoted, 1),
    Source1_Filtered = Table.SelectRows(Source1_Skipped, each [Nama Pekerjaan] <> null and [Nama Pekerjaan] <> ""),
    Source1_Final = Table.RemoveColumns(Source1_Filtered, {"No."}),

    // 3. Proses Sheet 2 (dan seterusnya)
    Source2_Raw = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/YOUR_GOOGLE_SHEETS_ID/pub?gid=992071846&single=true&output=csv"),[Delimiter=",", Columns=42, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
    Source2_Skipped = Table.Skip(Source2_Raw, 2),
    Source2_Renamed = Table.RenameColumns(Source2_Skipped, List.Zip({Table.ColumnNames(Source2_Skipped), OriginalHeaders})),
    Source2_Filtered = Table.SelectRows(Source2_Renamed, each [Nama Pekerjaan] <> null and [Nama Pekerjaan] <> ""),
    Source2_Final = Table.RemoveColumns(Source2_Filtered, {"No."}),

    Source3_Raw = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/YOUR_GOOGLE_SHEETS_ID/pub?gid=1078684528&single=true&output=csv"),[Delimiter=",", Columns=42, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
    Source3_Skipped = Table.Skip(Source3_Raw, 2),
    Source3_Renamed = Table.RenameColumns(Source3_Skipped, List.Zip({Table.ColumnNames(Source3_Skipped), OriginalHeaders})),
    Source3_Filtered = Table.SelectRows(Source3_Renamed, each [Nama Pekerjaan] <> null and [Nama Pekerjaan] <> ""),
    Source3_Final = Table.RemoveColumns(Source3_Filtered, {"No."}),

    Source4_Raw = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/YOUR_GOOGLE_SHEETS_ID/pub?gid=2065889582&single=true&output=csv"),[Delimiter=",", Columns=42, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
    Source4_Skipped = Table.Skip(Source4_Raw, 2),
    Source4_Renamed = Table.RenameColumns(Source4_Skipped, List.Zip({Table.ColumnNames(Source4_Skipped), OriginalHeaders})),
    Source4_Filtered = Table.SelectRows(Source4_Renamed, each [Nama Pekerjaan] <> null and [Nama Pekerjaan] <> ""),
    Source4_Final = Table.RemoveColumns(Source4_Filtered, {"No."}),

    // 6. Gabungkan Semua Tabel
    CombinedTable = Table.Combine({Source1_Final, Source2_Final, Source3_Final, Source4_Final}),

    // 7. Terapkan Tipe Data
    FinalTypedTable = Table.TransformColumnTypes(CombinedTable, FinalTypes),

    // 8. Koreksi Angka & Tanggal
    ReplacedValue = Table.ReplaceValue(FinalTypedTable,".","",Replacer.ReplaceText,{"Nilai Material", "Nilai Jasa"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValue,{
        {"Nilai Material", Int64.Type}, 
        {"Nilai Jasa", Int64.Type}, 
        {"Total Material + Jasa", Int64.Type}, 
        {"PPN 10% / PPN 11%", Int64.Type}, 
        {"Nilai Total (setelah PPN 11%)", Int64.Type}, 
        {"Tanggal Surat Pesanan", type date}, 
        {"TOC ", type date}, 
        {"Tanggal BAST", type date}
    })
in
    ChangedType

Penjelasan Kode Power Query (M)

• Langkah 1 (Pengaturan Awal): Kita mengambil data dari sheet pertama dan menggunakannya sebagai "cetakan". Kita mempromosikan baris pertama menjadi header, lalu menyimpan nama-nama header dan mendefinisikan tipe data yang benar untuk setiap kolom.
• Langkah 2-5 (Proses per Sheet): Untuk setiap sheet (termasuk yang pertama), kita membuang baris yang tidak perlu (seperti header duplikat), memfilter baris kosong, dan memastikan nama kolomnya seragam.
• Langkah 6 (Gabungkan): Table.Combine adalah fungsi kunci di sini. Ia menggabungkan semua tabel hasil olahan menjadi satu tabel vertikal yang besar.
• Langkah 7 & 8 (Pembersihan Akhir): Kita menerapkan tipe data yang benar (misalnya, mengubah teks menjadi angka atau tanggal) dan membersihkan format angka (misalnya, menghapus pemisah ribuan . agar bisa dihitung).

Dengan alur kerja ini, saya bisa lebih mudah mengelola data dan menjaga sinkronisasi di berbagai file Excel yang saya kelola untuk masa depan.

Langkah Selanjutnya: PivotTable dan Slicer

Setelah data bersih ini dimuat ke dalam Excel, pintu menuju analisis lanjutan terbuka lebar. Saya bisa dengan mudah:

• Membuat PivotTable untuk meringkas total nilai proyek per mitra atau per tahun.
• Menambahkan Slicer untuk memfilter data secara interaktif berdasarkan PIC, status, atau jenis pekerjaan.
• Membangun grafik dinamis yang berubah sesuai dengan pilihan pada Slicer.

Kombinasi ini mengubah spreadsheet statis menjadi sebuah dashboard yang hidup dan interaktif, semuanya sambil menjaga sumber data utama tetap terpusat dan mudah diakses oleh tim di Google Sheets.