Bagaimana cara menggunakan Power Query di Excel?
Excel Power Query digunakan untuk mencari sumber data, membuat koneksi dengan sumber data, lalu membentuk data sesuai dengan kebutuhan analisis kami. Setelah kami selesai dengan membentuk data sesuai kebutuhan kami, kami juga dapat membagikan temuan kami dan membuat berbagai laporan menggunakan lebih banyak kueri.
Langkah
Pada dasarnya, ada 4 langkah, dan urutan 4 langkah ini di Power Query adalah sebagai berikut:
- Hubungkan: Pertama-tama kami terhubung ke data, yang bisa berada di suatu tempat, di cloud, dalam layanan, atau secara lokal.
- Transformasi: Langkah kedua adalah mengubah bentuk data sesuai kebutuhan pengguna.
- Gabungkan: Pada langkah ini, kami melakukan beberapa langkah transformasi dan agregasi serta menggabungkan data dari kedua sumber untuk menghasilkan laporan gabungan.
- Kelola: Ini menggabungkan dan menambahkan kolom dalam kueri dengan kolom yang cocok di kueri lain di buku kerja.
Ada banyak fitur super canggih dari Excel Power Query.
Misalkan kita memiliki data pembelian selama 15 tahun terakhir dalam 180 file. Sekarang manajemen organisasi akan membutuhkan penggabungan angka-angka sebelum menganalisanya. Manajemen dapat menggunakan salah satu metode berikut:
- Mereka akan membuka semua file dan menyalin-menempelkannya ke satu file.
- Di sisi lain, mereka dapat menggunakan solusi bijak, yaitu dengan menerapkan rumus, karena rawan kesalahan.
Apapun metode yang mereka pilih, itu mengandung banyak pekerjaan manual, dan setelah beberapa bulan, akan ada data penjualan baru untuk durasi tambahan. Mereka harus melakukan latihan yang sama lagi.
Namun, Power Query dapat membantu mereka untuk tidak melakukan pekerjaan yang membosankan dan berulang ini. Mari kita pahami power query excel ini dengan sebuah contoh.
Contoh
Misalkan kita memiliki file teks dalam folder dengan data penjualan, dan kita ingin mendapatkan data tersebut di file excel kita.
Seperti yang bisa kita lihat pada gambar di bawah ini bahwa kita memiliki dua jenis file di folder, tetapi kami ingin mendapatkan data hanya file teks di file excel.
Untuk melakukan hal yang sama, langkah-langkahnya adalah:
Langkah 1: Pertama, kita perlu mendapatkan data di Power Query sehingga kita bisa membuat perubahan yang diperlukan dalam data untuk diimpor ke file excel.
Untuk melakukan hal yang sama, kita akan memilih opsi "Dari Folder" dari menu "Dari File" setelah mengklik perintah "Dapatkan Data" dari grup "Dapatkan & Transformasi" di tab "Data" .
Langkah 2: Pilih lokasi folder dengan browsing.
Klik 'OK'
Langkah 3: Sebuah kotak dialog akan terbuka berisi daftar semua file di folder yang dipilih dengan tajuk kolom sebagai 'Konten,' 'Nama,' 'Ekstensi,' 'Tanggal diakses,' 'Tanggal diubah,' 'Tanggal dibuat,' 'Atribut' dan 'Jalur Folder'.
Ada 3 pilihan yaitu Combine , Load, dan Transform Data .
- Gabungkan : Opsi ini digunakan untuk pergi ke layar tempat kita dapat memilih data mana yang akan digabungkan. Langkah edit dilewati untuk opsi ini dan tidak memberi kami kendali atas file mana yang akan digabungkan. Fungsi Combine mengambil setiap file di folder untuk digabungkan, yang dapat menyebabkan kesalahan.
- Muat: Opsi ini hanya akan memuat tabel seperti yang ditampilkan di atas pada gambar ke dalam lembar kerja Excel alih-alih data aktual dalam file.
- Transformasi Data: Berbeda dengan perintah 'Combine' , jika kita menggunakan perintah ini, maka kita dapat memilih file mana yang akan digabungkan, yaitu kita hanya dapat menggabungkan satu jenis file (ekstensi yang sama).
Seperti dalam kasus kami, kami ingin menggabungkan hanya file teks (.txt); kita akan memilih perintah "Transform Data" .
Kita bisa melihat "Langkah Terapan" di sisi kanan jendela. Untuk saat ini, hanya ada satu langkah yang dilakukan yaitu mengambil detail file dari folder.
Langkah 4: Ada kolom bernama 'Extension' di mana kita dapat melihat bahwa nilai-nilai di kolom tersebut ditulis dalam kedua kasus, yaitu huruf besar dan kecil.
Namun, kita perlu mengubah semua nilai menjadi huruf kecil karena filter membedakan keduanya. Untuk melakukan hal yang sama, kita perlu memilih kolom dan kemudian memilih "Huruf Kecil" dari menu perintah "Format" .
Langkah 5: Kami akan memfilter data menggunakan kolom 'Ekstensi' untuk file teks.
Langkah 6: Sekarang kita perlu menggabungkan data untuk kedua file teks menggunakan kolom pertama 'Konten'. Kami akan mengklik ikon yang ditempatkan di sisi kanan nama kolom.
Langkah 7: Kotak dialog bertuliskan 'Combine Files' akan terbuka di mana kita perlu memilih pembatas sebagai 'Tab' untuk file teks (file dengan ekstensi '.txt') dan dapat memilih basis untuk deteksi tipe data. Dan klik 'OK.'
Setelah mengklik 'OK,' kita akan mendapatkan data gabungan dari file teks di jendela 'Power Query' .
Kita dapat mengubah tipe data kolom sesuai kebutuhan. Untuk kolom 'Pendapatan' , kami akan mengubah jenis data menjadi 'Mata Uang'.
Kita dapat melihat langkah-langkah yang diterapkan pada data menggunakan power query di sisi kanan jendela.
Setelah membuat semua perubahan yang diperlukan pada data, kita dapat memuat data ke dalam lembar kerja excel menggunakan perintah 'Tutup & Muat Ke' di bawah grup 'Tutup' di tab 'Beranda' .
Kita perlu memilih apakah kita ingin memuat data sebagai Tabel atau Koneksi. Kemudian klik 'OK.'
Sekarang kita bisa melihat data sebagai tabel di lembar kerja.
Dan panel 'Kueri Buku Kerja' di sisi kanan, yang bisa kita gunakan untuk mengedit, menduplikasi, menggabungkan, menambahkan kueri, dan untuk banyak tujuan lainnya.
Excel Power Query sangat berguna karena kita dapat melihat bahwa 601.612 baris telah dimuat dalam beberapa menit.
Hal-hal untuk diingat
- Power Query tidak mengubah data sumber asli. Alih-alih mengubah data sumber asli, ia merekam setiap langkah yang diambil oleh pengguna saat menyambungkan atau mengubah data, dan setelah pengguna menyelesaikan pembentukan data, ia mengambil kumpulan data yang diperhalus dan membawanya ke dalam buku kerja.
- Power Query peka huruf besar kecil.
- Saat menggabungkan file dalam folder tertentu, kita perlu memastikan bahwa menggunakan kolom 'Ekstensi', dan kita harus mengecualikan file sementara (memiliki ekstensi '.tmp' dan nama file ini dimulai dengan tanda '~') sebagai Power Query juga bisa mengimpor file ini.
