Power Query menawarkan beberapa cara untuk membuat dan memuat Power query ke dalam buku kerja Anda. Anda juga dapat mengatur pengaturan pemuatan kueri default di jendela Opsi Kueri .
Tips Untuk mengetahui apakah data dalam lembar kerja dibentuk oleh Power Query, pilih sel data, dan jika tab pita konteks Kueri muncul, maka data dimuat dari Power Query.
Tentang integrasi Power Query ke Excel
Ketahui lingkungan tempat Anda berada Power Query terintegrasi dengan baik ke dalam antarmuka pengguna Excel, terutama saat Anda mengimpor data, bekerja dengan koneksi, dan mengedit Tabel Pivot, tabel Excel, dan rentang bernama. Untuk menghindari kebingungan, penting untuk mengetahui lingkungan mana yang Anda gunakan saat ini, Excel atau Power Query, kapan saja.
| Lembar kerja Excel , pita, dan kisi yang sudah dikenal | Pratinjau data dan pita Editor Power Query |
|---|---|
|
|
Misalnya, memanipulasi data di lembar kerja Excel pada dasarnya berbeda dengan Power Query. Selain itu, data tersambung yang Anda lihat di lembar kerja Excel, mungkin atau mungkin tidak membuat Power Query bekerja di belakang layar untuk membentuk data. Hal ini hanya terjadi jika Anda memuat data ke lembar kerja atau Model Data dari Power Query.
Mengganti nama tab lembar kerja Sebaiknya ganti nama tab lembar kerja dengan cara yang bermakna, terutama jika Anda memiliki banyak tab. Sangat penting untuk mengklarifikasi perbedaan antara lembar kerja data dan lembar kerja yang dimuat dari Editor Power Query. Meskipun Anda hanya memiliki dua lembar kerja, satu dengan tabel Excel yang disebut Sheet1, dan yang lainnya adalah kueri yang dibuat dengan mengimpor tabel Excel tersebut, yang disebut Table1, sangat mudah untuk bingung. Praktik yang selalu baik untuk mengubah nama default tab lembar kerja menjadi nama yang lebih masuk akal bagi Anda. Misalnya, ganti nama Sheet1 menjadi DataTable dan Table1 menjadi QueryTable. Sekarang tab mana yang memiliki data dan tab mana yang memiliki kueri.
Membuat kueri
Anda dapat membuat kueri dari data yang diimpor atau membuat kueri kosong.
Membuat kueri dari data yang diimpor
Ini adalah cara paling umum untuk membuat kueri.
- Impor beberapa data. Untuk informasi selengkapnya, lihat Mengimpor data dari sumber data eksternal.
- Pilih sel dalam data, lalu pilih Edit Kueri>.
Membuat kueri kosong
Anda mungkin ingin memulai dari awal. Ada dua cara untuk melakukannya.
- Pilih Data>Dapatkan Data>dari sumber> lainkueri kosong.
- Pilih Data>Dapatkan Data>Luncurkan Editor Power Query.
Pada titik ini, Anda dapat menambahkan langkah dan rumus secara manual jika mengetahui bahasa rumus Power Query M dengan baik.
Atau, Anda dapat memilih Beranda , lalu memilih perintah dalam grup Kueri Baru . Lakukan salah satu hal berikut ini.
- Pilih Sumber Baru untuk menambahkan sumber data. Perintah ini sama seperti perintah Data>Get Data di pita Excel.
- Pilih Sumber Terbaru untuk dipilih dari sumber data yang telah Anda gunakan. Perintah ini sama seperti perintah Data>Sumber Terbaru di pita Excel.
- Pilih Masukkan Data untuk memasukkan data secara manual. Anda dapat memilih perintah ini untuk mencoba Editor Power Query secara independen dari sumber data eksternal.
Muat kueri
Dengan asumsi kueri Anda valid dan tidak memiliki kesalahan, Anda dapat memuatnya kembali ke lembar kerja atau Model Data.
Muat kueri dari Editor Power Query
Di Editor Power Query, lakukan salah satu hal berikut:
Untuk memuat ke lembar kerja, pilih Beranda>Tutup & MuatTutup & Muat>.
Untuk memuat ke Model Data, pilih Beranda>Tutup & Muat>Tutup & Muat Ke.
Dalam kotak dialogImpor Data , pilih Tambahkan data ini ke Model Data.
TipsTerkadang perintah Load To diredupkan atau dinonaktifkan. Hal ini dapat terjadi saat pertama kali Anda membuat kueri di buku kerja. Jika hal ini terjadi, pilih Tutup & Muat, di lembar kerja baru, pilihtab Kueri Data> &Kueri Koneksi>, klik kanan kueri, lalu pilih Muat Ke. Atau, pada pita Editor Power Query, pilih Muat Kueri>Ke.
Memuat kueri dari panel Kueri dan Koneksi
Di Excel, Anda mungkin ingin memuat kueri ke lembar kerja atau Model Data lain.
- Di Excel, pilihKueriData> & Koneksi, lalu pilih tab Kueri.
- Dalam daftar kueri, temukan kueri, klik kanan kueri, lalu pilih Muat Ke. Kotak dialog Impor Data akan muncul.
- Tentukan bagaimana Anda ingin mengimpor data, lalu pilih OK. Untuk informasi selengkapnya tentang penggunaan kotak dialog ini, pilih tanda tanya (?).
Mengedit kueri dari lembar kerja
Ada beberapa cara untuk mengedit kueri yang dimuat ke lembar kerja.
Mengedit kueri dari data di lembar kerja Excel
- Untuk mengedit kueri, temukan kueri yang sebelumnya dimuat dari Editor Power Query, pilih sel dalam data, lalu pilih Edit Kueri>.
Mengedit kueri dari panel Kueri & Koneksi
Anda mungkin merasa panel Kueri & Koneksi lebih nyaman digunakan ketika Anda memiliki banyak kueri dalam satu buku kerja dan ingin menemukannya dengan cepat.
- Di Excel, pilihKueriData> & Koneksi, lalu pilih tab Kueri.
- Dalam daftar kueri, temukan kueri, klik kanan kueri, lalu pilih Edit.
Mengedit kueri dari kotak dialog Properti Kueri
- Di Excel, pilih>tab Data Data & Kueri Koneksi>, klik kanan kueri dan pilih Properti, pilih tab Definisi dalam kotak dialog Properti, lalu pilih Edit Kueri.
Tips Jika Anda berada dalam lembar kerja dengan kueri, pilihProperti Data>, pilih tab Definisi dalam kotak dialog Properti, lalu pilih Edit Kueri.
Mengedit kueri tabel dalam Model Data
Model Data biasanya berisi beberapa tabel yang disusun dalam relasi. Anda memuat kueri ke Model Data menggunakan perintah Muat Untuk menampilkan kotak dialog Impor Data , lalu memilih kotak centang Tambahkan data ini ke Mode Datal. Untuk informasi selengkapnya tentang Model Data, lihat Mencari tahu sumber data mana yang digunakan dalam model data buku kerja, Membuat Model Data di Excel, dan Menggunakan beberapa tabel untuk membuat PivotTable.
Untuk membuka Model Data, pilih Power Pivot>Manage.
Di bagian bawah jendela Power Pivot, pilih tab lembar kerja tabel yang Anda inginkan.
Pastikan tabel sudah benar ditampilkan. Model Data dapat memiliki banyak tabel.
Perhatikan nama tabel.
Untuk menutup jendela Power Pivot, pilihTutupFile>. Mungkin perlu beberapa detik untuk memulihkan memori.
Pilih tab Koneksi Data> &KueriProperti>, klik kanan kueri, lalu pilih Edit.
Setelah selesai membuat perubahan di Editor Power Query, pilih Tutup File>& Muat.
Hasil
Kueri di lembar kerja dan tabel dalam Model Data diperbarui.
Memuat kueri ke Model Data memakan waktu yang sangat lama
Jika Anda melihat bahwa memuat kueri ke Model Data membutuhkan waktu lebih lama daripada memuat ke lembar kerja, periksa langkah-langkah Power Query untuk melihat apakah Anda memfilter kolom teks atau kolom terstruktur Daftar menggunakan operator Berisi. Tindakan ini menyebabkan Excel menghitung lagi seluruh kumpulan data untuk setiap baris. Selain itu, Excel tidak dapat secara efektif menggunakan eksekusi multithreaded. Sebagai solusinya, coba gunakan operator lain seperti Sama dengan atauDimulai Dengan.
Microsoft menyadari masalah ini dan sedang diselidiki.
Mengatur opsi pemuatan kueri
Anda dapat memuat Power Query:
Ke lembar kerja. Di Editor Power Query, pilih Beranda>Tutup & MuatTutup & Muat>.
ke model data. Di Editor Power Query, pilih Beranda>Tutup & Muat>Tutup & Muat.
Secara default, Power Query memuat kueri ke lembar kerja baru saat memuat satu kueri, dan memuat beberapa kueri secara bersamaan ke Model Data. Anda dapat mengubah perilaku default untuk semua buku kerja atau hanya buku kerja saat ini. Saat mengatur opsi ini, Power Query tidak mengubah hasil kueri di lembar kerja atau data Model Data dan anotasi.
Anda juga dapat menimpa pengaturan default untuk kueri secara dinamis menggunakan kotak dialog Impor yang ditampilkan setelah Anda memilih Tutup & MemuatKe.
Pengaturan global yang berlaku untuk semua buku kerja Anda
Di Editor Power Query, pilih>Opsi File dan pengaturan>Opsi Kueri.
Dalam kotak dialog Opsi Kueri , di sisi kiri, di bawah bagian GLOBAL , pilih Beban Data.
Di bawah bagian Pengaturan Pemuatan Kueri Default , lakukan hal berikut:
- Pilih Gunakan pengaturan beban standar.
- Pilih Tentukan pengaturan pemuatan default kustom, lalu pilih atau hapus Muat ke lembar kerja atau Muat ke Model Data.
Tips Di bagian bawah kotak dialog, Anda dapat memilih Pulihkan Default untuk kembali ke pengaturan default dengan mudah.
Pengaturan buku kerja yang hanya berlaku untuk buku kerja saat ini
Dalam kotak dialog Opsi Kueri , di sisi kiri, di bawah bagian BUKU KERJA SAAT INI , pilih Beban Data.
Lakukan salah satu atau beberapa hal berikut:
Di bawah Deteksi Jenis, pilih atau hapus Deteksi tipe kolom dan header untuk sumber tidak terstruktur.
Perilaku defaultnya adalah mendeteksinya. Hapus opsi ini jika Anda lebih suka membentuk data sendiri.
Di bawah Hubungan, pilih atau kosongkan Buat hubungan antar tabel saat menambahkan ke Model Data untuk pertama kalinya.
Sebelum memuat ke Model Data, perilaku defaultnya adalah menemukan hubungan yang ada antar tabel, seperti kunci asing dalam database relasional, dan mengimpornya bersama data. Hapus opsi ini jika Anda lebih suka melakukannya sendiri.Di bawah Hubungan, pilih atau hapus Perbarui hubungan saat merefresh kueri yang dimuat ke Model Data.
Perilaku defaultnya adalah tidak memperbarui hubungan. Saat merefresh kueri yang sudah dimuat ke Model Data, Power Query menemukan hubungan yang ada antar tabel, seperti kunci asing, dalam database relasional, dan memperbaruinya. Tindakan ini mungkin menghapus hubungan yang dibuat secara manual setelah data diimpor atau memperkenalkan hubungan baru. Namun, jika Anda ingin melakukan ini, pilih opsi tersebut.
Di bawah Data Latar Belakang, pilih atau hapus Izinkan pratinjau data untuk diunduh di latar belakang.
Perilaku defaultnya adalah mengunduh pratinjau data di latar belakang. Hapus opsi ini jika Anda ingin langsung melihat semua data.