Power Query ada di dalam versi Windows Office 365, Excel 2016, Excel 2019 dan tersedia sebagai unduhan gratis di versi Windows Excel 2010 dan Excel 2013. Alat ini dirancang untuk mengekstrak, mengubah, dan memuat data ke dalam Excel dari berbagai sumber. Bagian terbaiknya: Power Query mengingat langkah-langkah Anda dan akan memutarnya kembali saat Anda ingin merefresh data. Ini berarti Anda dapat membersihkan data pada Hari 1 dalam 80% waktu normal, dan Anda dapat membersihkan data pada Hari 2 hingga 400 hanya dengan mengeklik Segarkan.
Saya mengatakan ini tentang banyak fitur Excel baru, tetapi ini benar-benar fitur terbaik untuk mencapai Excel dalam 20 tahun.
Saya menceritakan sebuah cerita dalam seminar langsung saya tentang bagaimana Power Query ditemukan sebagai penopang untuk pelanggan SQL Server Analysis Services yang dipaksa untuk menggunakan Excel untuk mengakses Power Pivot. Tapi Power Query terus menjadi lebih baik, dan setiap orang yang menggunakan Excel harus meluangkan waktu untuk mempelajari Power Query.
Dapatkan Power Query
Anda mungkin sudah memiliki Power Query. Ini ada di grup Dapatkan & Transformasi pada tab Data.
Namun jika Anda menggunakan Excel 2010 atau Excel 2013, masuk ke Internet dan cari Unduh Power Query. Perintah Power Query Anda akan muncul di tab Power Query khusus di Pita.
Bersihkan Data untuk Pertama Kalinya di Power Query
Untuk memberi Anda contoh beberapa kehebatan Power Query, katakanlah Anda mendapatkan file yang diperlihatkan di bawah ini setiap hari. Kolom A tidak diisi. Seperempat melewati halaman bukannya ke bawah.
Untuk memulai, simpan buku kerja itu ke hard drive Anda. Letakkan di tempat yang dapat diprediksi dengan nama yang akan Anda gunakan untuk file itu setiap hari.
Di Excel, pilih Dapatkan Data, Dari File, Dari Buku Kerja.
Telusuri ke buku kerja. Di panel Preview, klik pada Sheet1. Daripada mengklik Muat, klik Edit. Anda sekarang melihat buku kerja dalam kisi yang sedikit berbeda-kisi Power Query.
Sekarang Anda perlu memperbaiki semua sel kosong di kolom A. Jika Anda melakukan ini di antarmuka pengguna Excel, urutan perintah yang berat adalah Beranda, Temukan & Pilih, Buka Khusus, Kosong, Sama dengan, Panah Atas, Ctrl + Enter .
Di Power Query, pilih Transformasi, Isi, Bawah.
Semua nilai nol diganti dengan nilai dari atas. Dengan Power Query, dibutuhkan tiga klik, bukan tujuh.
Masalah berikutnya: Perempatnya akan menyeberang, bukan ke bawah. Di Excel, Anda dapat memperbaikinya dengan tabel pivot Multiple Consolidation Range. Ini membutuhkan 12 langkah dan 23+ klik.
Di Power Query, pilih dua kolom yang bukan perempat. Buka dropdown Unpivot Columns pada tab Transform dan pilih Unpivot Other Columns, seperti yang ditunjukkan di bawah ini.
Klik kanan pada kolom Atribut yang baru dibuat dan ubah namanya menjadi Quarter, bukan Attribute. Lebih dari dua puluh klik di Excel menjadi lima klik di Power Query.
Sekarang, agar adil, tidak setiap langkah pembersihan lebih singkat di Power Query daripada di Excel. Menghapus kolom masih berarti mengklik kanan kolom dan memilih Hapus Kolom. Tapi sejujurnya, cerita di sini bukan tentang penghematan waktu di Hari 1.
Tapi Tunggu: Power Query Mengingat Semua Langkah Anda
Lihat di sisi kanan jendela Power Query. Ada daftar yang disebut Langkah Terapan. Ini adalah jejak audit instan dari semua langkah Anda. Klik ikon roda gigi mana saja untuk mengubah pilihan Anda pada langkah itu dan biarkan perubahan mengalir melalui langkah-langkah selanjutnya. Klik pada langkah mana pun untuk melihat bagaimana data terlihat sebelum langkah itu.
Ketika Anda selesai membersihkan data, klik Close & Load seperti yang ditunjukkan di bawah ini.
Tip
Jika data Anda lebih dari 1.048.576 baris, Anda dapat menggunakan menu turun-bawah Tutup & Muat untuk memuat data secara langsung ke Model Data Power Pivot, yang dapat menampung 995 juta baris jika Anda memiliki cukup memori yang terinstal di mesin.
Dalam beberapa detik, data Anda yang telah diubah muncul di Excel. Mengagumkan.
Imbalannya: Bersihkan Data Besok Dengan Satu Klik
Tapi sekali lagi, cerita Power Query bukan tentang penghematan waktu di Hari 1. Saat Anda memilih data yang dikembalikan oleh Power Query, panel Queries & Connections muncul di sisi kanan Excel, dan di atasnya ada tombol Refresh. (Kami memerlukan tombol Edit di sini, tetapi karena tidak ada, Anda harus mengklik kanan kueri asli untuk melihat atau membuat perubahan pada kueri asli).
Sangat menyenangkan membersihkan data di Hari ke-1. Saya suka melakukan sesuatu yang baru. Tetapi ketika manajer saya melihat laporan yang dihasilkan dan berkata, "Bagus. Bisakah kamu melakukan ini setiap hari? ” Saya dengan cepat membenci kebosanan dalam membersihkan kumpulan data yang sama setiap hari.
Jadi, untuk mendemonstrasikan Hari 400 pembersihan data, saya telah sepenuhnya mengubah file aslinya. Produk baru, pelanggan baru, jumlah lebih kecil, lebih banyak baris, seperti yang ditunjukkan di bawah ini. Saya menyimpan versi baru file ini di jalur yang sama dan dengan nama file yang sama dengan file aslinya.
Jika saya membuka buku kerja kueri dan mengklik Refresh, dalam beberapa detik, Power Query melaporkan 92 baris, bukan 68 baris.
Membersihkan data pada Hari 2, Hari 3, Hari, 4,… Hari 400,… Hari Keabadian kini membutuhkan dua klik.
Contoh yang satu ini hanya menggores permukaan Power Query. Jika Anda menghabiskan dua jam dengan buku, M untuk (Data) Monkey oleh Ken Puls dan Miguel Escobar, Anda akan belajar tentang fitur lain, seperti ini:
- Menggabungkan semua file Excel atau CSV dari folder ke dalam satu kisi Excel
- Mengonversi sel dengan Apple; Banana; Cherry; Dill; Eggplant menjadi lima baris di Excel
- Melakukan VLOOKUP ke buku kerja pencarian saat Anda membawa data ke Power Query
- Membuat satu kueri menjadi fungsi yang bisa diterapkan ke setiap baris di Excel
Untuk deskripsi lengkap tentang Power Query, lihat M Is untuk (Data) Monkey oleh Ken Puls dan Miguel Escobar. Pada akhir 2019, edisi kedua yang diberi judul ulang, Kuasai Data Anda, akan tersedia.
Terima kasih kepada Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser, dan Colin Michael untuk menominasikan Power Query.
