Praktik terbaik saat bekerja dengan Power Query

Praktik terbaik Power Query ini membantu Anda meningkatkan performa kueri, memanfaatkan pelipatan kueri, memilih jenis data yang benar, mengatur transformasi, dan menggunakan kembali logika dengan parameter dan fungsi kustom. Mereka berlaku baik untuk Power Query Desktop maupun Power Query Online.

Pilih konektor yang tepat

Power Query menawarkan banyak konektor data. Konektor ini berkisar dari sumber data seperti file TXT, CSV, dan Excel, hingga database seperti Microsoft SQL Server, dan produk perangkat lunak sebagai layanan (SaaS) populer seperti Microsoft Dynamics 365 dan Salesforce. Jika konektor yang dibuat khusus tidak tersedia di jendela Dapatkan Data , gunakan konektor generik seperti ODBC atau OLE DB.

Pilih konektor yang dibuat khusus untuk sumber data Anda saat tersedia. Misalnya, konektor SQL Server memberikan pengalaman Dapatkan Data yang lebih baik daripada konektor ODBC generik saat Anda tersambung ke database SQL Server. Konektor SQL Server juga mendukung fitur kinerja seperti query folding. Untuk mempelajari selengkapnya, buka Gambaran Umum evaluasi kueri dan pelipatan kueri di Power Query.

Setiap konektor data mengikuti pengalaman standar seperti yang dijelaskan dalam Mendapatkan data. Pengalaman standar ini memiliki tahap yang disebut Pratinjau Data. Pada tahap ini, Anda disediakan jendela yang mudah digunakan untuk memilih data yang ingin Anda dapatkan dari sumber data Anda, jika konektor mengizinkannya, dan pratinjau data sederhana dari data tersebut. Anda bahkan dapat memilih beberapa himpunan data dari sumber data Anda melalui jendela Navigator .

Nota

Untuk melihat daftar lengkap konektor yang tersedia di Power Query, buka Konektor di Power Query.

Memfilter data lebih awal untuk meningkatkan performa

Filter data sedini mungkin untuk mengurangi jumlah baris yang Power Query proses dalam transformasi selanjutnya. Untuk konektor yang mendukung pelipatan kueri, Power Query dapat mendorong filter kembali ke sumber data, seperti yang dijelaskan dalam Gambaran Umum evaluasi kueri dan pelipatan kueri di Power Query. Memfilter data yang tidak relevan juga membatasi data yang ditampilkan dalam pratinjau data.

Gunakan menu filter otomatis, yang memperlihatkan daftar berbeda dari nilai yang ditemukan di kolom Anda, untuk memilih nilai yang ingin Anda pertahankan atau filter. Gunakan bilah pencarian untuk membantu Anda menemukan nilai di kolom Anda.

Anda juga dapat memanfaatkan filter khusus jenis seperti Sebelumnya untuk kolom tanggal, tanggalwaktu, atau bahkan zona waktu tanggal.

Filter spesifik jenis ini dapat membantu Anda membuat filter dinamis yang selalu mengambil data dalam jumlah x detik, menit, jam, hari, minggu, bulan, kuartal, atau tahun sebelumnya.

Nota

Untuk mempelajari selengkapnya tentang memfilter data Anda berdasarkan nilai dari kolom, buka Filter menurut nilai.

Lakukan operasi yang mahal di bagian akhir untuk meningkatkan kinerja

Untuk meningkatkan kinerja pratinjau di editor Power Query, lakukan operasi yang memakan banyak sumber daya pada tahap akhir. Operasi tertentu mengharuskan membaca sumber data lengkap untuk mengembalikan hasil apa pun dan karenanya lambat untuk dipratinjau. Misalnya, jika Anda melakukan pengurutan, ada kemungkinan bahwa beberapa baris pertama yang diurutkan berada di akhir data sumber. Untuk mengembalikan hasil apa pun, operasi pengurutan harus terlebih dahulu membaca semua baris.

Operasi lain (seperti filter) tidak perlu membaca semua data sebelum mengembalikan hasil apa pun. Sebaliknya, mereka beroperasi melalui data dengan apa yang disebut mode "streaming". Data mengalir, dan hasil dikembalikan sepanjang proses. Di editor Power Query, operasi tersebut hanya perlu membaca cukup data sumber untuk mengisi pratinjau.

Jika memungkinkan, lakukan operasi streaming tersebut terlebih dahulu, dan lakukan operasi yang lebih mahal yang terakhir. Melakukan operasi dalam urutan ini membantu meminimalkan jumlah waktu yang Anda habiskan untuk menunggu pratinjau dirender setiap kali Anda menambahkan langkah baru ke kueri Anda.

Menggunakan subset data saat mengembangkan kueri

Jika menambahkan langkah baru di editor Power Query lambat, gunakan Pertahankan Baris Pertama untuk membatasi data yang diproses saat Anda mengembangkan kueri. Setelah Anda menambahkan semua langkah yang diperlukan, hapus langkah Pertahankan Baris Pertama sehingga kueri yang selesai memproses himpunan data lengkap.

Menggunakan jenis data yang benar

Atur jenis data yang benar untuk setiap kolom sehingga Power Query dapat membuat transformasi dan filter khusus jenis tersedia. Misalnya, saat Anda memilih kolom tanggal, Anda bisa menggunakan opsi di bawah grup kolom Tanggal dan waktu di menu Tambahkan Kolom . Jika kolom tidak memiliki kumpulan tipe data, opsi ini berwarna abu-abu.

Situasi serupa terjadi untuk filter khusus jenis, karena khusus untuk jenis data tertentu. Jika kolom Anda tidak memiliki tipe data yang benar yang ditentukan, filter khusus jenis ini tidak tersedia.

Sangat penting bahwa Anda selalu bekerja dengan jenis data yang benar untuk kolom Anda. Saat Anda bekerja dengan sumber data terstruktur seperti database, informasi jenis data dibawa dari skema tabel yang ditemukan dalam database. Tetapi untuk sumber data yang tidak terstruktur seperti file TXT dan CSV, penting bagi Anda untuk mengatur jenis data yang benar untuk kolom yang berasal dari sumber data tersebut. Secara default, Power Query menawarkan deteksi jenis data otomatis untuk sumber data yang tidak terstruktur. Anda dapat membaca selengkapnya tentang fitur ini dan bagaimana fitur ini dapat membantu Anda dalam Jenis data.

Nota

Untuk mempelajari selengkapnya tentang pentingnya jenis data dan cara bekerja dengannya, buka Jenis data.

Memprofilkan dan menjelajahi data Anda

Sebelum Anda menyiapkan data dan menambahkan langkah-langkah transformasi, aktifkan alat pembuatan profil data Power Query untuk menemukan informasi tentang data Anda.

Power Query menyediakan tiga alat pembuatan profil data:

Perangkat	Apa yang ditampilkannya
Kualitas kolom	Proporsi nilai dalam kolom yang valid, berisi kesalahan, atau kosong.
Distribusi kolom	Frekuensi dan distribusi nilai di setiap kolom.
Profil kolom	Statistik terperinci tentang kolom yang dipilih.

Anda juga dapat berinteraksi dengan fitur-fitur ini, yang membantu Anda menyiapkan data Anda.

Nota

Untuk mempelajari selengkapnya tentang alat pembuatan profil data, buka Alat pembuatan profil data.

Dokumentasikan pekerjaan Anda

Dokumentasikan solusi Power Query dengan memberikan langkah, kueri, dan grup nama dan deskripsi yang bermakna. Detail ini membuat tujuan setiap transformasi lebih mudah dipahami dan dikelola.

Meskipun Power Query secara otomatis membuat nama langkah untuk Anda di panel langkah yang diterapkan, Anda juga bisa mengganti nama langkah Anda atau menambahkan deskripsi ke salah satunya.

Nota

Untuk mempelajari selengkapnya tentang semua fitur dan komponen yang tersedia yang ditemukan di dalam panel langkah-langkah yang diterapkan, buka Menggunakan daftar Langkah yang diterapkan.

Membagi kueri besar menjadi modul

Pisahkan kueri Power Query besar menjadi kueri yang direferensikan yang lebih kecil untuk membuat fase transformasinya lebih mudah dipahami dan dikelola. Meskipun satu kueri dapat berisi semua transformasi dan perhitungan yang Anda butuhkan, kueri dengan banyak langkah lebih mudah dikelola saat satu kueri mereferensikan kueri berikutnya.

Misalnya, kueri berikut memiliki sembilan langkah dan menyertakan langkah Gabungkan dengan tabel Harga.

Anda dapat membagi kueri ini menjadi dua pada langkah Tabel Gabungkan dengan Harga. Dengan begitu lebih mudah untuk memahami langkah-langkah yang diterapkan ke kueri penjualan sebelum penggabungan. Untuk melakukan operasi ini, Anda mengklik kanan langkah Menggabungkan dengan tabel Harga dan pilih opsi Ekstrak Sebelumnya.

Anda kemudian diminta dengan dialog untuk memberi nama kueri baru Anda. Langkah ini secara efektif membagi kueri Anda menjadi dua kueri. Sebuah kueri mencakup semua langkah sebelum penggabungan. Kueri lain memiliki langkah awal yang merujuk pada kueri baru Anda dan langkah-langkah lain yang Anda miliki dalam kueri asli Anda dari tabel Gabungkan dengan Harga berlanjut ke bawah.

Anda juga bisa menggunakan referensi kueri sesuai keinginan Anda. Tetapi ada baiknya untuk menyimpan kueri Anda pada tingkat yang tidak tampak menakutkan pada pandangan pertama meskipun terdapat banyak langkah.

Nota

Untuk mempelajari selengkapnya tentang referensi kueri, buka Memahami panel kueri.

Atur kueri ke dalam kelompok

Gunakan grup di panel kueri untuk menjaga pekerjaan Anda tetap teratur.

Satu-satunya tujuan grup adalah untuk membantu Anda menjaga pekerjaan Anda tetap teratur dengan melayani sebagai folder untuk kueri Anda. Anda dapat membuat grup dalam grup jika perlu. Memindahkan kueri antar grup semudah menyeret dan meletakkan.

Cobalah untuk memberi grup Anda nama yang bermakna yang masuk akal bagi Anda dan kasus Anda.

Nota

Untuk mempelajari selengkapnya tentang semua fitur dan komponen yang tersedia yang ditemukan di dalam panel kueri, buka Memahami panel kueri.

Kueri yang siap menghadapi masa depan

Rancang kueri untuk menangani perubahan yang diperkirakan pada data sumber agar penyegaran di masa mendatang tetap berhasil. Power Query menyediakan transformasi yang membuat kueri tangguh saat baris, kolom, atau nilai dalam sumber data berubah.

Tentukan cakupan kueri Anda, termasuk apa yang harus dilakukan dan apa yang harus dipertimbangkan dalam hal struktur, tata letak, nama kolom, jenis data, dan komponen relevan lainnya.

Transformasi berikut ini dapat membantu kueri tetap tahan terhadap perubahan:

Skenario data sumber	Transformasi Power Query	Pelajari lebih lanjut
Jumlah baris data berubah, tetapi Anda harus menghapus jumlah baris footer tetap.	Menghapus baris bawah	Memfilter tabel menurut posisi baris
Jumlah kolom berubah, tetapi kueri hanya memerlukan kolom tertentu.	Pilih kolom	Memilih atau menghapus kolom
Jumlah kolom berubah, tetapi kueri hanya boleh melakukan unpivot pada subset tertentu saja.	Lakukan Unpivot pada kolom yang dipilih saja	kolom Unpivot
Konversi jenis data menghasilkan kesalahan untuk nilai yang tidak sesuai dengan jenis target.	Hapus baris yang berisi kesalahan.	Berurusan dengan kesalahan

Gunakan parameter

Gunakan parameter Power Query untuk menyimpan dan mengelola nilai yang dapat Anda gunakan kembali dalam transformasi, fungsi sumber data, dan fungsi kustom. Parameter membuat kueri lebih mudah diperbarui karena Anda dapat mengubah nilai di satu lokasi alih-alih mengedit setiap kueri yang menggunakannya. Dua skenario umum adalah:

• Argumen langkah: Gunakan parameter sebagai argumen beberapa transformasi yang didorong dari antarmuka pengguna.

  

• Argumen Fungsi Kustom: Buat fungsi baru dari kueri, dan parameter referensi sebagai argumen fungsi kustom Anda.

  

Manfaat utama membuat dan menggunakan parameter adalah:

• Tampilan terpusat dari semua parameter Anda melalui jendela Kelola Parameter .

  

• Pengulangan parameter dalam beberapa tahap atau kueri.

• Membuat pembuatan fungsi kustom menjadi sederhana dan mudah.

Anda bahkan dapat menggunakan parameter dalam beberapa argumen konektor data. Misalnya, Anda dapat membuat parameter untuk nama server Anda saat menyambungkan ke database SQL Server Anda. Kemudian Anda dapat menggunakan parameter tersebut di dalam dialog database SQL Server.

Jika Anda mengubah lokasi server, yang perlu Anda lakukan adalah memperbarui parameter untuk nama server Anda, dan kueri Anda diperbarui.

Nota

Untuk mempelajari selengkapnya tentang membuat dan menggunakan parameter, buka Menggunakan parameter.

Membuat fungsi yang dapat digunakan kembali

Buat fungsi kustom Power Query saat Anda perlu menerapkan kumpulan transformasi yang sama ke kueri atau nilai yang berbeda. Fungsi kustom Power Query memetakan sekumpulan nilai input ke satu nilai output dan dibuat dari fungsi dan operator bahasa rumus Power Query M asli.

Misalnya, Anda memiliki beberapa kueri atau nilai yang memerlukan serangkaian transformasi yang sama. Anda dapat membuat fungsi kustom yang nantinya Anda panggil terhadap kueri atau nilai pilihan Anda. Fungsi kustom ini menghemat waktu Anda dan membantu Anda mengelola serangkaian transformasi di lokasi pusat, yang dapat Anda ubah kapan saja.

Fungsi kustom Power Query dapat dibuat dari kueri dan parameter yang sudah ada. Misalnya, bayangkan kueri yang memiliki beberapa kode sebagai string teks dan Anda ingin membuat fungsi yang mendekode nilai tersebut.

Anda mulai dengan memiliki parameter dengan nilai yang berfungsi sebagai contoh.

Dari parameter tersebut, Anda membuat kueri baru tempat Anda menerapkan transformasi yang Anda butuhkan. Untuk kasus ini, Anda ingin membagi kode PTY-CM1090-LAX menjadi beberapa komponen:

• Asal = PTY
• Tujuan = LAX
• Maskapai Penerbangan = CM
• FlightID = 1090

Anda kemudian dapat mengubah kueri tersebut menjadi fungsi dengan mengklik kanan kueri dan memilih Buat Fungsi. Terakhir, Anda dapat memanggil fungsi kustom Anda ke dalam salah satu kueri atau nilai Anda.

Setelah beberapa transformasi lagi, Anda dapat melihat bahwa Anda mencapai output yang Anda inginkan dan menerapkan logika untuk transformasi tersebut dari fungsi kustom.

Nota

Untuk mempelajari selengkapnya tentang cara membuat dan menggunakan fungsi kustom di Power Query, lihat Fungsi Kustom.