Bagaimana Anda menarik data dari beberapa lembar kerja di excel menggunakan power query?

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

Bisakah Power Query membagi data menjadi beberapa lembar?

Bisakah Power Query menggabungkan beberapa file Excel?

Bagaimana cara menarik data dari beberapa buku kerja di Excel?

Bagaimana cara menggabungkan tabel atau lembar kerja Excel dengan Power Query?