Sangat umum bahwa Anda perlu menggabungkan data dari beberapa lembar kerja di buku kerja Excel yang sama saat Anda menggunakan Power BI atau Power Query/Get&Transform di Excel. Memang banyak orang telah membuat blog tentang cara mengatasi masalah ini, tetapi tidak ada solusi yang saya temukan di internet yang berfungsi dalam skenario yang lebih kompleks ketika data pada setiap lembar membutuhkan semacam transformasi sebelum dapat digabungkan. Saya diminta untuk menjelaskan bagaimana melakukan ini baru-baru ini saat mengajar kelas Power BI, jadi dalam posting blog ini saya akan membahas contoh yang berhasil dan menunjukkan beberapa masalah yang mungkin tersandung bahkan pengguna Power BI yang berpengalaman
Pertama-tama, sumber data. Katakanlah Anda memiliki buku kerja Excel dengan empat lembar kerja. Q1, Q2, Q3 dan Q4. Di setiap lembar kerja ada beberapa data penjualan selama tiga bulan di setiap kuartal;
… lembar kerja Q2 terlihat seperti ini
…dan seterusnya. Output yang diperlukan untuk Power BI harus berupa tabel yang terlihat seperti ini
Sekarang sebagian besar posting blog yang menjelaskan masalah ini, seperti posting Ken Puls di sini, menganggap setiap lembar kerja memiliki tabel dengan nama kolom yang sama di atasnya. Jika setiap lembar memiliki kolom yang sama, ini berarti Anda bisa menyambungkan ke buku kerja Excel dan mendapatkan tabel yang berisi konten [Miguel Escobar memiliki postingan bagus yang menjelaskan cara melakukannya di sini] lalu klik tombol Perluas/Gabungkan
Namun dalam kasus khusus ini tidak menyelesaikan masalah, karena kami mendapatkan ini
Aha, Anda mungkin berkata, kita harus mengubah data sebelum kita dapat menggabungkannya sehingga kita perlu membuat fungsi dan memanggilnya untuk setiap lembar kerja – teknik yang sudah saya buat di blog di sini. Dan ya, pada dasarnya itulah yang perlu terjadi, tetapi masalahnya ada pada detailnya
Inilah solusinya, langkah demi langkah
Langkah 1. Dapatkan meja dengan semua lembar kerja terdaftar
Di Power BI sambungkan ke file Excel Anda seperti biasa, lalu di panel Navigator klik kanan pada nama buku kerja Excel dan pilih Edit daripada memilih salah satu lembar kerja individual
Hasilnya akan menjadi tabel yang terlihat seperti ini
Jika perlu, filter baris mana pun yang tidak berisi "Lembar" di kolom Jenis dan filter juga lembar kerja yang datanya tidak ingin Anda gabungkan
Langkah 2. Buat kueri template Anda
Gandakan kueri di atas dan panggil Templat kueri baru
Sekarang, dalam kueri Templat, pilih salah satu lembar kerja yang akan digunakan untuk menyusun kueri yang logikanya akan diterapkan ke semua lembar kerja lainnya, dan filter tabel di atas sehingga hanya berisi baris untuk lembar kerja tersebut. Dalam hal ini saya menggunakan lembar kerja yang disebut Q1
Lalu – dan ini penting – hapus semua kolom lain di tabel kecuali kolom Data
Melakukan hal ini mengubah kode M yang dibuat untuk hal selanjutnya yang akan Anda lakukan;
Setelah itu klik tautan Tabel di dalam sel, dan Anda akan melihat konten lembar kerja
Mungkin akan ada langkah Tipe Berubah dalam kueri yang menetapkan tipe data untuk setiap kolom, dan Anda harus menghapusnya
Anda sekarang dapat melakukan transformasi lain yang Anda perlukan pada kueri ini, tetapi Anda harus menghindari transformasi apa pun yang menghasilkan kode M yang merujuk ke kolom apa pun pada lembar kerja asli yang tidak ada di lembar kerja lain. Ingat, transformasi ini perlu diterapkan ke lembar kerja lain dan akan gagal jika merujuk ke kolom yang tidak ada - inilah mengapa Anda harus menghapus langkah Tipe Berubah lebih awal, karena ini menetapkan tipe pada bulan Januari, . Buka Editor Tingkat Lanjut dan periksa kode M untuk seluruh kueri hanya untuk memastikan
Dalam hal ini, yang perlu saya lakukan hanyalah menghapus kolom bulan dengan memilih kolom Produk dan menggunakan tombol Unpivot Other Columns pada tab Transform, lalu mengganti nama kolom dengan tepat
Langkah 3. Buat fungsi
Selanjutnya Anda perlu membuat parameter baru dengan mengklik tombol Manage Parameters/New Parameters, panggil parameter Worksheet, setel tipe data ke teks dan minta kembali nama lembar kerja yang Anda pilih pada langkah sebelumnya
Sekarang, kembali ke kueri Templat, temukan langkah yang disebut Baris yang Difilter di awal tempat Anda memfilter ke satu lembar kerja, dan klik ikon roda gigi di sebelah langkah untuk mengeditnya
Kemudian, edit langkah sehingga menggunakan nilai yang dikembalikan oleh parameter untuk memfilter alih-alih nilai hard-code yang Anda masukkan sebelumnya. Untuk melakukan ini, klik tombol yang ditunjukkan di bawah ini, pilih Parameter lalu pilih parameter Worksheet di kotak dropdown berikutnya
Terakhir, buka panel Kueri di sisi kiri layar dan klik kanan kueri Templat dan pilih Buat Fungsi
Anda akan diminta untuk memberi nama fungsi baru;
Langkah 4. Aktifkan fungsi dan gabungkan data
Terakhir, kembali ke salinan duplikat kueri asli yang dibuat di awal langkah 2. Lalu buka tab Tambahkan Kolom di pita dan klik tombol Aktifkan Fungsi Kustom dan aktifkan fungsi GetData, meneruskan konten kolom Nama ke parameter satu-satunya fungsi
Terakhir, klik tombol Perluas/Agregat pada kolom baru dan perluas tabel bersarang
Setelah menghapus kolom yang tidak diperlukan, Anda akan melihat data dari semua lembar kerja digabungkan menjadi satu tabel sesuai keinginan
Jangan lupa untuk mengatur tipe data pada setiap kolom
Anda dapat mengunduh buku kerja Excel yang digunakan dalam postingan ini di sini dan contoh file Power BI Desktop di sini