Praktik terbaik saat bekerja dengan Power Query

Artikel ini berisi beberapa tips dan trik untuk memanfaatkan pengalaman manipulasi data Anda secara maksimal di Power Query.

Pilih konektor yang tepat

Power Query menawarkan sejumlah besar konektor data. Konektor ini berkisar dari sumber data seperti TXT, CSV, dan file Excel, hingga database seperti Microsoft SQL Server, dan layanan SaaS populer seperti Microsoft Dynamics 365 dan Salesforce. Jika Anda tidak melihat sumber data yang tercantum di jendela Dapatkan Data , Anda selalu bisa menggunakan konektor ODBC atau OLEDB untuk menyambungkan ke sumber data Anda.

Menggunakan konektor terbaik untuk tugas akan memberi Anda pengalaman dan performa terbaik. Misalnya, menggunakan konektor SQL Server alih-alih konektor ODBC saat menyambungkan ke database SQL Server tidak hanya memberi Anda pengalaman Dapatkan Data yang jauh lebih baik, tetapi konektor SQL Server juga menawarkan fitur yang dapat meningkatkan pengalaman dan performa Anda, seperti pelipatan kueri. Untuk membaca selengkapnya tentang pelipatan kueri, 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 , seperti yang ditunjukkan pada gambar berikut.

Filter lebih awal

Selalu disarankan untuk memfilter data Anda pada tahap awal kueri Anda atau sedini mungkin. Beberapa konektor akan memanfaatkan filter Anda melalui pelipatan kueri, seperti yang dijelaskan dalam Gambaran Umum evaluasi kueri dan pelipatan kueri di Power Query. Ini juga merupakan praktik terbaik untuk memfilter data apa pun yang tidak relevan untuk kasus Anda. Ini akan memungkinkan Anda lebih fokus pada tugas Anda dengan hanya menampilkan data yang relevan di bagian pratinjau data.

Anda bisa menggunakan menu filter otomatis yang menampilkan daftar nilai berbeda yang ditemukan di kolom Anda untuk memilih nilai yang ingin Anda pertahankan atau filter. Anda juga dapat menggunakan 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 khusus jenis ini dapat membantu Anda membuat filter dinamis yang akan selalu mengambil data yang ada di x jumlah detik, menit, jam, hari, minggu, bulan, kuartal, atau tahun sebelumnya seperti yang ditunjukkan dalam gambar berikut.

Lakukan operasi mahal yang terakhir

Operasi tertentu memerlukan pembacaan sumber data lengkap untuk mengembalikan hasil apa pun, dan dengan demikian akan lambat untuk dipratinjau dalam Editor Power Query. Misalnya, jika Anda melakukan pengurutan, ada kemungkinan bahwa beberapa baris pertama yang diurutkan berada di akhir data sumber. Jadi 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 "streaming" oleh, dan hasil dikembalikan di sepanjang jalan. Dalam 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. Ini akan membantu meminimalkan jumlah waktu yang Anda habiskan untuk menunggu pratinjau dirender setiap kali Anda menambahkan langkah baru ke kueri Anda.

Bekerja sementara terhadap subset data Anda

Jika menambahkan langkah baru ke kueri Anda di Editor Power Query lambat, pertimbangkan untuk terlebih dahulu melakukan operasi "Pertahankan Baris Pertama" dan batasi jumlah baris yang sedang Anda kerjakan. Kemudian, setelah Anda menambahkan semua langkah yang Anda butuhkan, hapus langkah "Pertahankan Baris Pertama".

Menggunakan jenis data yang benar

Beberapa fitur di Power Query bersifat kontekstual dengan tipe data kolom yang dipilih. Misalnya, saat memilih kolom tanggal, opsi yang tersedia di bawah grup kolom Tanggal dan waktu di menu Tambahkan Kolom akan tersedia. Tetapi jika kolom tidak memiliki kumpulan tipe data, maka opsi ini akan 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 akan tersedia.

Sangat penting bahwa Anda selalu bekerja dengan jenis data yang benar untuk kolom Anda. Saat bekerja dengan sumber data terstruktur seperti database, informasi jenis data akan 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.

Jelajahi sampel Anda

Sebelum Anda mulai menyiapkan data dan menambahkan langkah-langkah transformasi baru, kami sarankan Anda mengaktifkan alat pembuatan profil data Power Query untuk menemukan informasi tentang data Anda dengan mudah.

Alat pembuatan profil data ini membantu Anda lebih memahami data Anda. Alat ini memberi Anda visualisasi kecil yang menunjukkan informasi per kolom, seperti:

• Kualitas kolom—Menyediakan bagan batang kecil dan tiga indikator dengan representasi berapa banyak nilai dalam kolom yang termasuk dalam kategori nilai yang valid, kesalahan, atau kosong.
• Distribusi kolom—Menyediakan sekumpulan visual di bawah nama kolom yang menampilkan frekuensi dan distribusi nilai di setiap kolom.
• Profil kolom—Menyediakan tampilan kolom Anda yang lebih menyeluruh dan statistik yang terkait dengannya.

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

Dokumentasikan pekerjaan Anda

Sebaiknya Dokumentasikan kueri Anda dengan mengganti nama atau menambahkan deskripsi ke langkah, kueri, atau grup sesuai keinginan Anda.

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.

Mengambil pendekatan modular

Anda dapat membuat satu kueri yang berisi semua transformasi dan perhitungan yang mungkin Anda butuhkan. Tetapi jika kueri berisi sejumlah besar langkah, maka mungkin ide yang baik untuk membagi kueri menjadi beberapa kueri, di mana satu kueri mereferensikan kueri berikutnya. Tujuan dari pendekatan ini adalah untuk menyederhanakan dan memisahkan fase transformasi menjadi bagian yang lebih kecil sehingga lebih mudah dipahami.

Misalnya, Anda memiliki kueri dengan sembilan langkah yang diperlihatkan dalam gambar berikut.

Anda dapat membagi kueri ini menjadi dua di 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 Tabel gabungkan dengan Harga dan pilih opsi Ekstrak Sebelumnya .

Anda kemudian akan diminta dengan dialog untuk memberi nama kueri baru Anda. Ini akan secara efektif membagi kueri Anda menjadi dua kueri. Satu kueri akan memiliki semua kueri sebelum penggabungan. Kueri lain akan memiliki langkah awal yang akan mereferensikan kueri baru Anda dan langkah-langkah lainnya yang Anda miliki dalam kueri asli Anda dari tabel Gabungkan dengan Harga melangkah ke bawah.

Anda juga dapat memanfaatkan penggunaan referensi kueri sesuai keinginan Anda. Tetapi ada baiknya untuk menyimpan kueri Anda pada tingkat yang tampaknya tidak menakutkan pada pandangan pertama dengan begitu banyak langkah.

Membuat grup

Cara yang bagus untuk menjaga pekerjaan Anda tetap teratur adalah dengan memanfaatkan penggunaan grup di panel kueri.

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 di seluruh grup semampu seret dan letakkan.

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

Kueri pemeriksa masa depan

Pastikan Anda membuat kueri yang tidak akan mengalami masalah selama refresh mendatang adalah prioritas utama. Ada beberapa fitur di Power Query untuk membuat kueri Anda tahan terhadap perubahan dan dapat di-refresh bahkan ketika beberapa komponen sumber data Anda berubah.

Ini adalah praktik terbaik untuk menentukan cakupan kueri Anda tentang apa yang harus dilakukannya dan apa yang harus diperhatikan dalam hal struktur, tata letak, nama kolom, jenis data, dan komponen lain yang Anda anggap relevan dengan cakupan.

Beberapa contoh transformasi yang dapat membantu Anda membuat kueri tahan terhadap perubahan adalah:

• Jika kueri Anda memiliki jumlah baris dinamis dengan data, tetapi jumlah baris tetap yang berfungsi sebagai footer yang harus dihapus, Anda bisa menggunakan fitur Hapus baris bawah.

  Catatan

  Untuk mempelajari selengkapnya tentang memfilter data Anda menurut posisi baris, buka Memfilter tabel menurut posisi baris.

• Jika kueri Anda memiliki jumlah kolom dinamis, tetapi Anda hanya perlu memilih kolom tertentu dari himpunan data, Anda bisa menggunakan fitur Pilih kolom .

  Catatan

  Untuk mempelajari selengkapnya tentang memilih atau menghapus kolom, buka Memilih atau menghapus kolom.

• Jika kueri Anda memiliki jumlah kolom dinamis dan Anda hanya perlu membatalkan pivot subset kolom, Anda bisa menggunakan fitur unpivot hanya kolom yang dipilih.

  Catatan

  Untuk mempelajari selengkapnya tentang opsi untuk membuka pivot kolom Anda, buka Kolom unpivot.

• Jika kueri Anda memiliki langkah yang mengubah tipe data kolom, tetapi beberapa sel menghasilkan kesalahan karena nilai tidak sesuai dengan tipe data yang diinginkan, Anda dapat menghapus baris yang menghasilkan nilai kesalahan.

  Catatan

  Untuk informasi selengkapnya tentang bekerja dan menangani kesalahan, buka Menangani kesalahan.

Menggunakan parameter

Membuat kueri yang dinamis dan fleksibel adalah praktik terbaik. Parameter di Power Query membantu Anda membuat kueri Anda lebih dinamis dan fleksibel. Parameter berfungsi sebagai cara untuk menyimpan dan mengelola nilai dengan mudah yang dapat digunakan kembali dengan berbagai cara. Tetapi lebih umum digunakan dalam dua skenario:

• Argumen langkah—Anda dapat menggunakan parameter sebagai argumen dari beberapa transformasi yang didorong dari antarmuka pengguna.

  

• Argumen Fungsi Kustom—Anda dapat membuat fungsi baru dari kueri, dan mereferensikan parameter sebagai argumen fungsi kustom Anda.

  

Manfaat utama membuat dan menggunakan parameter adalah:

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

  

• Penggunaan kembali parameter dalam beberapa langkah atau kueri.

• Membuat pembuatan fungsi kustom mudah 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 akan diperbarui.

Membuat fungsi yang dapat digunakan kembali

Jika Anda menemukan diri Anda dalam situasi di mana Anda perlu menerapkan kumpulan transformasi yang sama ke kueri atau nilai yang berbeda, membuat fungsi kustom Power Query yang dapat digunakan kembali sebanyak yang Anda butuhkan bisa bermanfaat. Fungsi kustom Power Query adalah pemetaan dari sekumpulan nilai input ke satu nilai output, dan dibuat dari fungsi dan operator M asli.

Misalnya, Anda memiliki beberapa kueri atau nilai yang memerlukan serangkaian transformasi yang sama. Anda dapat membuat fungsi kustom yang nantinya dapat dipanggil terhadap kueri atau nilai pilihan Anda. Fungsi kustom ini akan menghemat waktu Anda dan membantu Anda dalam mengelola serangkaian transformasi Anda 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 akan mendekode nilai tersebut.

Anda mulai dengan memiliki parameter yang memiliki 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:

• Origin = PTY
• Tujuan = LAX
• Maskapai = CM
• FlightID = 1090

Anda kemudian dapat mengubah kueri tersebut menjadi fungsi dengan melakukan klik kanan pada kueri dan memilih Buat Fungsi. Terakhir, Anda dapat memanggil fungsi kustom Anda ke dalam salah satu kueri atau nilai Anda, seperti yang ditunjukkan pada gambar berikut.

Setelah beberapa transformasi lagi, Anda dapat melihat bahwa Anda telah mencapai output yang Anda inginkan dan memanfaatkan logika untuk transformasi seperti itu dari fungsi kustom.