Tutorial excel power‌ kueri (dapatkan dan transformasi)

Jika Anda salah satu dari orang-orang yang banyak bekerja dengan data, Anda bisa menjadi siapa saja ( akuntan , HR, analis data, dll.), PowerQuery bisa menjadi alat yang ampuh.

Izinkan saya langsung saja, Power Query adalah salah satu keterampilan Excel tingkat lanjut yang perlu Anda pelajari dan dalam tutorial ini, Anda akan menjelajahi PowerQuery secara mendetail dan mempelajari cara mengubah data dengannya.

Mari kita mulai.

Apa itu Power Query Excel

Power Query adalah add-in Excel yang bisa Anda gunakan untuk ETL. Ini berarti Anda dapat mengekstrak data dari berbagai sumber, mengubahnya , lalu memuatnya ke dalam spreadsheet. Bisa dibilang POWER QUERY adalah mesin pembersih data karena memiliki semua opsi untuk mengubah data. Ini real-time dan mencatat setiap langkah yang Anda ambil.

Mengapa Anda harus menggunakan Power Query (Manfaat)?

Jika Anda memikirkan pertanyaan ini, inilah jawaban saya untuk Anda:

• Sumber data yang berbeda : Anda dapat memuat data ke dalam editor kueri yang canggih dari sumber data yang berbeda, seperti CSV , TXT, JSON, dll.
• Transformasi data dengan mudah: Biasanya, Anda menggunakan rumus dan tabel pivot untuk transformasi data, namun dengan POWER QUERY, Anda dapat melakukan banyak hal hanya dengan beberapa klik.
• Ini real-time: tulis kueri satu kali dan Anda dapat menyegarkannya setiap kali ada perubahan pada data, dan ini akan mengubah data baru yang Anda perbarui.

Izinkan saya membagikan sebuah contoh:

Bayangkan Anda memiliki 100 file Excel yang berisi data dari 100 kota dan sekarang atasan Anda ingin Anda membuat laporan dengan semua data dari 100 file tersebut. OKE, jika Anda memutuskan untuk membuka setiap file secara manual dan menyalin dan menempelkan data dari file-file ini dan Anda memerlukan setidaknya satu jam untuk itu.

Namun dengan Power Query, Anda dapat melakukannya dalam hitungan menit. Apakah Anda merasa bersemangat? BAGUS.

Nanti di tutorial ini Anda akan mempelajari cara menggunakan Power Query dengan banyak contoh, namun pertama-tama Anda perlu memahami konsepnya.

Konsep permintaan listrik

Untuk mempelajari power query, Anda perlu memahami konsepnya yang bekerja dalam 3 langkah:

1. Dapatkan datanya

Power query memungkinkan Anda mendapatkan data dari berbagai sumber seperti web, CSV, file teks, beberapa buku kerja dari satu folder dan banyak sumber lain tempat kami dapat menyimpan data.

2. Transformasikan data

Setelah Anda memasukkan data ke dalam power query, Anda memiliki banyak opsi yang dapat Anda gunakan untuk mengubah dan membersihkannya. Ini menciptakan kueri untuk semua langkah yang Anda lakukan (dalam urutan langkah demi langkah).

3. Memuat data

Dari Editor Kueri Tingkat Lanjut, Anda bisa memuat data yang diubah ke dalam lembar kerja, atau Anda bisa langsung membuat PivotTable atau PivotChart atau membuat koneksi hanya data.

Di mana Power Query (Cara menginstal)?

Di bawah ini Anda dapat melihat cara menginstal Power Query Access di berbagai versi Microsoft Excel.

Unggul 2007

Jika Anda menggunakan Excel 2007, mohon maaf karena PQ tidak tersedia untuk versi ini, jadi Anda perlu mengupgrade ke latest version (Excel untuk Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

Excel 2010 dan Excel 2013

Untuk tahun 2010 dan 2013 Anda perlu menginstal add-in secara terpisah yang dapat Anda unduh dari tautan ini dan setelah diinstal Anda akan mendapatkan tab baru di pita Excel seperti di bawah ini:

• Pertama-tama, unduh add-in dari sini (situs web resmi Microsoft).
• Setelah file diunduh, buka dan ikuti instruksinya.
• Setelah itu, Anda akan secara otomatis mendapatkan tab “Power Query” di pita Excel Anda.

Jika entah kenapa tab “POWER QUERY” ini tidak muncul, tidak perlu khawatir. Anda dapat menambahkannya menggunakan opsi COM Add-in.

• Buka tab File ➜ Opsi ➜ Add-in.

• Pada opsi “Add-In”, pilih “COM Add-in” dan klik GO.
• Setelah itu, centang kotak “Microsoft Power Query untuk Excel”.

• Pada akhirnya, klik OK.

Unggul 2016, 2019, Office 365

Jika Anda menggunakan Excel 2016, Excel 2019, atau berlangganan OFFICE 365, langganan tersebut sudah ada di tab Data , sebagai grup bernama “GET & TRANSFORM” (Saya suka nama itu, bukan?).

Unggul Mac

Jika Anda menggunakan Excel di Mac, saya khawatir tidak ada add-in kueri daya untuk ini dan Anda hanya bisa menyegarkan kueri yang sudah ada , namun Anda tidak bisa membuat yang baru atau bahkan mengedit kueri ( LINK ).

Editor Power Query

Power Query memiliki editornya sendiri tempat Anda bisa mendapatkan data, melakukan semua langkah untuk membuat kueri, lalu memuatnya ke dalam spreadsheet. Untuk membuka Editor Power Query, Anda perlu masuk ke tab Data dan di Dapatkan & Transformasi ➜ Dapatkan Data ➜ Luncurkan Editor Power Query.

Di bawah ini adalah pratinjau pertama editor yang akan Anda dapatkan saat membukanya.

Sekarang, mari kita jelajahi setiap bagian secara mendetail:

1. Pita

Mari kita lihat semua tab yang tersedia:

• File: Dari tab File Anda dapat memuat data, menghapus editor, dan membuka parameter kueri.
• Beranda : Di tab BERANDA, Anda memiliki opsi untuk mengelola data yang dimuat, seperti, menghapus, dan memindahkan kolom dan baris.
• Transform : Tab ini berisi semua opsi yang Anda perlukan untuk mengubah dan membersihkan data, seperti menggabungkan kolom, mengubah urutan, dll.
• Tambahkan Kolom : Di sini Anda memiliki opsi untuk menambahkan kolom baru ke data yang Anda miliki di editor tingkat lanjut.
• View : Dari tab ini Anda dapat membuat perubahan pada tampilan Advanced Query Editor dan data yang dimuat.

2. Langkah-langkah diterapkan

Di sisi kanan editor, Anda memiliki panel pengaturan kueri yang menyertakan nama kueri dan semua langkah yang diterapkan secara berurutan.

Saat Anda mengklik kanan pada sebuah langkah, Anda memiliki daftar opsi yang dapat Anda lakukan, seperti, mengganti nama, menghapus, mengedit, memindahkan ke atas atau ke bawah, dll. dan ketika Anda mengklik suatu langkah, editor akan membawa Anda ke transformasi yang dilakukan pada langkah tersebut.

Lihat di bawah di mana Anda telah menerapkan kelima langkah secara total dan ketika saya mengklik langkah ke-4, saya dibawa ke transformasi pada langkah empat di mana nama kolom tidak berubah.

3. Pertanyaan

Panel kueri di sisi kiri mencantumkan semua kueri yang saat ini Anda miliki di buku kerja. Ini pada dasarnya adalah tempat di mana Anda dapat mengelola semua pertanyaan.

Saat Anda mengklik kanan pada nama kueri, Anda dapat melihat semua opsi yang dapat Anda gunakan (menyalin, menghapus, menggandakan, dll.)

Anda juga dapat membuat kueri baru hanya dengan mengklik kanan pada ruang kosong di panel kueri, lalu memilih opsi sumber data.

4. Bilah rumus

Seperti yang saya katakan, setiap kali Anda menerapkan suatu langkah di editor, itu menghasilkan kode M untuk langkah itu, dan Anda dapat melihat kode ini di bilah rumus. Anda cukup mengklik bilah rumus untuk mengedit kode.

Setelah Anda mempelajari cara menggunakan kode M, Anda juga dapat membuat langkah dengan menulis kode dan cukup mengklik tombol “FX” untuk memasukkan langkah khusus.

5. Ikhtisar Data

Area pratinjau data tampak seperti lembar bentang Excel, namun sedikit berbeda dari lembar bentang biasa di mana Anda dapat mengedit sel atau data secara langsung. Saat Anda memuat data ke dalam editor (kami akan melakukannya sebentar lagi), ini akan menampilkan semua kolom dengan header dengan nama kolom dan kemudian baris dengan data.

Di bagian atas setiap kolom, Anda dapat melihat tipe data dari data kolom tersebut. Saat Anda memuat data ke dalam editor, Kueri Tingkat Lanjut secara otomatis menerapkan tipe data yang benar (hampir setiap saat) ke setiap kolom.

Anda dapat mengklik tombol di kiri atas header kolom untuk mengubah tipe data yang diterapkan ke kolom. Ini berisi daftar semua tipe data yang dapat Anda ambil.

Dan di sisi kiri header kolom, Anda memiliki tombol filter yang bisa Anda gunakan untuk memfilter nilai kolom. Catatan: Saat Anda memfilter nilai kolom, kueri lanjutan menganggapnya sebagai satu langkah dan mencantumkannya di Langkah yang Diterapkan.

Jika Anda mengklik kanan pada tajuk kolom, Anda dapat melihat bahwa ada menu yang menyertakan daftar opsi yang dapat Anda gunakan untuk mengubah data dan menggunakan salah satu opsi dan PQ yang disimpannya sebagai langkah dalam proses yang diterapkan. bukan.

Sumber data untuk Power Query

Bagian terbaik dari Power Query adalah Anda memiliki kemampuan untuk mendapatkan data dari berbagai sumber dan mengubah data tersebut lalu memuatnya ke dalam spreadsheet. Saat Anda mengklik Dapatkan Data di GET & TRANSFORM, Anda dapat melihat daftar lengkap sumber data yang dapat Anda muat ke dalam editor.

Sekarang mari kita lihat beberapa sumber datanya:

• Dari Tabel/Rentang : Dengan opsi ini, Anda bisa memuat data ke Editor Kueri Lanjutan langsung dari lembar kerja aktif.
• Dari buku kerja : Dari buku kerja lain yang Anda miliki di komputer Anda. Anda hanya perlu mencari file ini menggunakan kotak dialog terbuka dan secara otomatis akan mendapatkan data dari file ini.
• Dari Teks/CSV : Dapatkan data dari file teks atau file yang dipisahkan koma, lalu Anda dapat memuatnya ke dalam spreadsheet.
• Dari Folder : Dibutuhkan semua file dari folder dan mendukung data dalam editor kueri yang kuat. (Lihat ini: Gabungkan file Excel dari satu folder).
• Dari Web : Dengan opsi ini Anda mendapatkan data dari alamat web, bayangkan Anda memiliki file yang disimpan di web atau Anda memiliki halaman web dari mana Anda perlu mendapatkan datanya.

Cara memuat data ke Editor Power Query

Sekarang mari pelajari cara memuat data ke dalam editor kueri yang canggih. Di sini Anda memiliki daftar nama siswa dan nilainya ( LINK ).

Anda akan memuat data langsung dari spreadsheet, jadi Anda perlu membuka file terlebih dahulu lalu ikuti langkah-langkah di bawah ini:

• Pertama, terapkan tabel Excel ke data (walaupun Anda tidak melakukannya, Excel akan melakukannya untuk Anda sebelum memuat data ke editor PQ).
• Sekarang, pilih sel dalam tabel dan klik “Dari Tabel/Rentang” (tab Dapatkan & Transformasi Data).

• Setelah Anda mengklik tombol tersebut, Excel mengonfirmasi rentang data untuk menerapkan tabel Excel ke dalamnya.

• Pada titik ini Anda memiliki data di Editor Power Query, dan tampilannya seperti di bawah ini.

• Di sini Anda dapat melihat:
  • Di bilah rumus, PQ telah menghasilkan kode M untuk tabel yang baru saja Anda muat ke editor.
  • Di sisi kiri editor Anda memiliki panel kueri tempat Anda memiliki daftar kueri.
  • Di sisi kanan, dalam pengaturan kueri, Anda memiliki bagian bernama “Langkah Terapan” yang berisi semua langkah yang tercantum. Catatan: Anda pasti berpikir bahwa Anda belum melakukan “Modified Type” apa pun, tetapi ada langkah yang disebut “Modified Type” di sana. Izinkan saya memberi tahu Anda KECERDASAN POWER QUERY ketika Anda memuat data ke dalam editor, secara otomatis memeriksa dan menerapkan tipe data yang benar untuk semua kolom.

Contoh Power Query (Tips dan Trik)

Anda bisa mempelajari cara melakukan beberapa tugas dasar yang biasa Anda lakukan dengan rumus fungsional di Excel, namun dengan PowerQuery Anda bisa melakukannya hanya dengan beberapa klik:

1. Ganti nilai

Anda memiliki daftar nilai dan Anda ingin mengganti satu atau lebih nilai dengan yang lain. Nah, dengan bantuan Power Query , Anda bisa membuat kueri dan mengganti nilai-nilai ini, dalam waktu singkat.

Dalam daftar di bawah, Anda perlu mengganti nama saya “Puneet” dengan “Punit”.

• Pertama, edit daftar di Editor Kueri Tingkat Lanjut.
• Setelah itu, di Editor Power Query, buka “Tab Transformasi” dan klik “Ganti Nilai”.

• Sekarang di “Value to find” masukkan “Puneet” dan di “Replace with” masukkan “Punit” dan setelah itu klik OK.

• Setelah Anda mengklik OK, semua nilai diganti dengan nilai baru dan sekarang klik “Tutup dan Muat” untuk memuat data ke dalam spreadsheet.

2. Urutkan data

Sama seperti pengurutan biasa, Anda bisa mengurutkan data menggunakan PowerQuery dan saya menggunakan daftar nama yang sama dengan yang Anda gunakan pada contoh di atas.

• Pertama, muat data ke dalam editor kueri yang canggih.
• Di tab Beranda, Anda memiliki dua tombol pengurutan (Ascending dan Descending).
• Klik salah satu tombol ini untuk mengurutkan.

3. Hapus kolom

Katakanlah Anda memiliki beberapa data di suatu tempat dan Anda perlu menghapus beberapa kolom darinya. Intinya kolom-kolom tersebut harus dihapus setiap kali menambah data baru, bukan? Namun, power query dapat mengatasi hal itu.

• Pilih kolom atau beberapa kolom yang ingin Anda hapus.

• Sekarang klik kanan dan pilih “Hapus”.

Tip Cepat: Ada juga opsi “Hapus kolom lain” di mana Anda dapat menghapus semua kolom yang tidak dipilih.

4. Pisahkan kolom

Sama seperti opsi teks ke kolom, Anda memiliki “Kolom Pisah” di power query. Izinkan saya memberi tahu Anda cara kerjanya.

• Pilih kolom dan buka tab Beranda ➜ Transformasi ➜ Pisahkan kolom ➜ Berdasarkan pembatas.

• Pilih kustom dari daftar drop-down dan masukkan “–” di dalamnya.
• Sekarang di sini Anda memiliki tiga opsi berbeda untuk memisahkan kolom.
  • Pembatas paling kiri
  • Pembatas paling kanan
  • Setiap kemunculan pembatas

Jika Anda hanya memiliki satu pembatas dalam satu sel, ketiganya akan berfungsi sama, namun jika Anda memiliki lebih dari satu pembatas, Anda harus memilih yang sesuai.

5. Ganti nama kolom

Anda cukup mengganti nama kolom dengan mengklik kanan lalu mengklik “Ganti Nama”.

Tip singkat : Katakanlah Anda memiliki pertanyaan untuk mengganti nama kolom dan orang lain tidak sengaja mengganti namanya. Anda dapat memulihkan nama ini dengan satu klik.

6. Kolom duplikat

Di Power Query, ada opsi sederhana untuk membuat kolom duplikat. Cukup klik kanan pada kolom yang ingin Anda buat kolom duplikatnya, lalu klik “Kolom Duplikat”.

7. Ubah urutan kolom atau baris

Dalam power query, transposisi adalah permainan anak-anak. Ya, cukup satu klik.

• Setelah Anda memuat data ke dalam Advanced Query Editor, yang perlu Anda lakukan hanyalah memilih kolom atau baris.
• Buka tab Transformasi ➜ Tabel ➜ Transposisi.

8. Ganti/hapus kesalahan

Biasanya, untuk mengganti atau menghapus kesalahan di Excel, Anda dapat menggunakan opsi Temukan dan Ganti atau kode VBA. Namun di PowerQuery jauh lebih mudah. Lihat kolom di bawah di mana Anda memiliki kesalahan dan Anda dapat menghapus dan menggantinya.

Saat Anda mengklik kanan pada kolom, Anda akan memiliki kedua opsi.

• Ganti kesalahan
• Hapus kesalahan

9. Ubah tipe data

Anda memiliki data di kolom tetapi formatnya tidak benar. Jadi setiap kali Anda perlu mengubah formatnya.

• Pertama, edit data di editor kueri canggih.
• Setelah itu, pilih kolom dan buka tab Transform.
• Sekarang, dari Tipe Data, pilih “Tanggal” sebagai tipenya.

10. Tambahkan kolom dari contoh

Di kueri tingkat lanjut, terdapat opsi untuk menambahkan kolom contoh yang sebenarnya bukan contoh yang terkait dengan kolom saat ini.

Izinkan saya memberi Anda sebuah contoh:

Bayangkan Anda memerlukan nama hari dari kolom tanggal. Daripada menggunakan rumus atau opsi lain yang bisa Anda gunakan, Anda bisa menggunakan “Tambahkan Kolom dari Contoh”.

Berikut cara melakukannya:

• Klik kanan pada kolom dan klik “Tambahkan Kolom dari Contoh”.

• Di sini Anda akan mendapatkan kolom kosong. Klik sel pertama di kolom untuk mendapatkan daftar nilai yang dapat Anda sisipkan.

• Pilih “Nama hari dalam seminggu dari tanggal” dan klik OK.

Ledakan! kolom baru Anda ada di sini.

11. Ganti kotak

Anda memiliki opsi berikut untuk mengubah huruf besar/kecil di PowerQuery.

• Kecil
• Kotak atas
• Gunakan huruf kapital untuk setiap kata

Anda dapat melakukan ini dengan mengklik kanan kolom dan memilih salah satu dari tiga opsi di atas. Atau, buka tab Transformasi ➜ Kolom Teks ➜ Format.

12. Rapikan dan bersihkan

Untuk menghapus data atau menghapus spasi yang tidak diinginkan , Anda dapat menggunakan opsi TRIM dan CLEAN di PowerQuery. Langkah-langkahnya sederhana:

• Klik kanan pada kolom atau pilih semua kolom jika Anda memiliki beberapa kolom.
• Buka tab Transformasi ➜ Kolom Teks ➜ Format.
  1. TRIM: untuk menghilangkan spasi di akhir dan awal sel.
  2. CLEAN: untuk menghapus karakter non-cetak dari sel.

13. Tambahkan awalan/akhiran

Jadi Anda memiliki daftar nilai dan dari daftar ini Anda ingin menambahkan awalan/akhiran di setiap sel. Di Excel Anda bisa menggunakan metode penggabungan, tapi di PowerQuery ada opsi yang mudah digunakan untuk keduanya.

• Pertama, pilih kolom di mana Anda perlu menambahkan awalan/akhiran.
• Lalu buka tab Transformasi ➜ Kolom Teks ➜ Format ➜ Tambah Awalan/Tambahkan Akhiran.

• Setelah Anda mengklik salah satu opsi, Anda akan mendapatkan kotak dialog untuk memasukkan teks.

• Dan setelah memasukkan teks, klik OK.

14. Ekstrak nilai

Jika Anda ahli rumus, saya yakin Anda setuju dengan saya bahwa mengekstraksi teks atau angka dari sel memerlukan penggabungan fungsi yang berbeda. Namun PowerQuery telah memecahkan banyak masalah ini. Anda memiliki tujuh cara untuk mengekstrak nilai dari sel.

15. Hanya tanggal atau waktu

Seringkali Anda memiliki tanggal dan waktu, keduanya dalam satu sel, namun Anda memerlukan salah satunya.

• Pilih kolom tempat Anda memiliki gabungan tanggal dan waktu.
• Jika kamu mau:
  • Tanggal : Klik kanan ➜ Transform ➜ Hanya tanggal.
  • Waktu : Klik kanan ➜ Transform ➜ Waktu saja.

16. Gabungkan tanggal dan waktu

Sekarang Anda tahu cara memisahkan tanggal dan waktu. Namun selanjutnya, Anda perlu tahu cara menggabungkannya.

• Pertama, pilih kolom tanggal dan klik opsi “Hanya tanggal”.
• Setelah itu, pilih kedua kolom (Tanggal dan Waktu) dan buka tab Transformasi dan dari grup “Kolom Tanggal dan Waktu”, buka Tanggal dan klik “Gabungkan Tanggal dan Waktu”.

17. Angka yang dibulatkan

Berikut adalah opsi berikut yang Anda miliki untuk pembulatan angka.

• Pembulatan ke bawah: Untuk membulatkan angka ke bawah.
• Putaran: Untuk membulatkan suatu angka.
• Pembulatan: Anda dapat memilih seberapa jauh Anda dapat membulatkan angka desimal saya.

Berikut langkah-langkahnya:

• Pilih kolom dan klik kanan ➜ Transform ➜ Round .
  1. Pembulatan ke bawah: Untuk membulatkan angka ke bawah.
  2. Putaran: Untuk membulatkan suatu angka.
  3. Pembulatan: Anda dapat memilih seberapa jauh Anda dapat membulatkan angka desimal saya.

Catatan: Saat Anda memilih opsi “#3 Round”, Anda harus memasukkan jumlah desimal yang akan dibulatkan.

18. Perhitungan

Ada opsi yang dapat Anda gunakan untuk melakukan penghitungan (banyak sekali). Anda dapat menemukan semua opsi ini di tab Transform (di grup Kolom Numerik).

• Dasar
• Statistik
• Ilmuwan
• Trigonometri
• Bulat
• Informasi

Untuk melakukan perhitungan ini, Anda harus memilih kolom dan kemudian opsi.

19. Kelompokkan berdasarkan

Katakanlah Anda memiliki kumpulan data yang besar dan ingin membuat tabel ringkasan. Inilah yang perlu Anda lakukan:

• Di tab Transform, klik tombol ‘Kelompokkan menurut’ dan Anda akan mendapatkan kotak dialog.

• Sekarang dari kotak dialog ini pilih kolom yang ingin Anda kelompokkan dan setelah itu tambahkan nama, pilih operasi dan kolom di mana Anda memiliki nilai.

• Pada akhirnya, klik OK.

Catatan: Ada juga opsi lanjutan di opsi “Kelompokkan Berdasarkan” yang dapat Anda gunakan untuk membuat tabel grup multi-level.

20. Hapus Nilai Negatif

Dalam salah satu postingan blog saya, saya mencantumkan tujuh metode untuk menghilangkan tanda-tanda negatif dan power query adalah salah satunya. Klik kanan pada kolom dan masuk ke opsi Transform, lalu klik “Nilai Absolut”.

Ini secara instan menghilangkan semua tanda negatif dari nilai-nilai tersebut.

Cara memuat data ke dalam spreadsheet

Setelah Anda mengubah data, Anda dapat memuatnya ke dalam spreadsheet dan menggunakannya untuk analisis lebih lanjut. Pada tab beranda ada tombol bernama “Tutup dan Muat” ketika Anda mengkliknya, Anda akan mendapatkan daftar drop-down yang menawarkan opsi tambahan:

• Tutup dan muat
• Tutup dan muat

• Setelah Anda mengklik tombol tersebut, maka akan muncul opsi berikut:

• Pilih bagaimana Anda ingin menampilkan data ini di spreadsheet Anda.
  • Lukisan
  • Laporan Tabel Pivot:
  • Poros tabel
  • Buat hanya satu koneksi
  • Di mana Anda ingin meletakkan datanya?
    • Lembar Kerja yang Ada
    • Lembar kerja baru.
  • Tambahkan data ini ke model data.
• Cukup pilih opsi tabel dan lembar kerja baru dan biarkan model data tidak dicentang dan klik OK.

• Saat Anda mengklik OK, itu menambahkan lembar kerja baru dengan datanya.

Lebih banyak contoh untuk dipelajari

Menyegarkan kueri secara otomatis

Dari semua contoh yang saya kutip di sini, ini adalah yang paling penting. Saat Anda membuat kueri, Anda bisa menyegarkannya secara otomatis (Anda bisa mengatur timer).

Dan inilah langkah-langkahnya:

• Di tab Data, klik “Kueri & Koneksi” dan Anda akan mendapatkan panel Kueri & Koneksi di sisi kanan jendela.

• Sekarang klik kanan pada kueri dan centang “Segarkan setiap” dan masukkan menitnya.

Cara menggunakan rumus dan fungsi di Power Query

Sama seperti Anda bisa menggunakan fungsi dan rumus dalam spreadsheet Excel, Kueri Tingkat Lanjut memiliki daftar fungsi sendiri yang bisa Anda gunakan. Dasar-dasar fungsi dan rumus di PowerQuery sama dengan fungsi spreadsheet Excel.

Di PQ Anda perlu menambahkan kolom khusus baru untuk menambahkan fungsi atau rumus.

Kita ambil contoh saja: Pada data dibawah ini (sudah ada di editor PQ) anda mempunyai nama depan dan belakang ( LINK DOWNLOAD ) .

Bayangkan Anda perlu menggabungkan kedua nama tersebut dan membuat kolom untuk nama lengkap. Dalam hal ini, Anda bisa memasukkan rumus sederhana untuk menggabungkan nama kedua kolom.

• Pertama, buka tab Tambah Kolom dan klik “Kolom Khusus”.

• Sekarang di kotak dialog kolom khusus, masukkan nama kolom baru “Nama Lengkap” atau apa pun yang Anda inginkan untuk memberi nama kolom baru tersebut.

• • Rumus kolom khusus adalah tempat Anda harus memasukkan rumus. Jadi masukkan rumus di bawah ini :

 [First Name]&" "&[Last Name] 

• Saat Anda memasukkan rumus pada “Rumus Kolom Kustom”, PQ akan memeriksa rumus yang Anda masukkan dan menampilkan pesan “Tidak ada kesalahan sintaksis yang terdeteksi” dan jika ada kesalahan maka akan menampilkan pesan kesalahan berdasarkan jenis kesalahannya.
• Setelah Anda memasukkan rumusnya dan tidak ada kesalahan, tekan saja OK.
• Anda sekarang memiliki kolom baru di akhir data yang berisi nilai dari dua kolom (nama depan dan nama belakang).

Cara menggunakan fungsi di Power Query

Demikian pula, Anda juga bisa menggunakan fungsi sambil menambahkan kolom kustom dan Power Query memiliki banyak daftar fungsi yang bisa Anda gunakan.

Mari kita pahami cara menggunakan suatu fungsi dengan contoh yang mudah dan sederhana. Saya melanjutkan contoh di atas dimana kami menambahkan kolom baru yang menggabungkan nama depan dan nama belakang.

Namun sekarang Anda perlu mengubah teks nama lengkap yang Anda miliki di kolom ini menjadi huruf besar. Fungsi yang dapat Anda gunakan adalah “ Text.Upper ” . Seperti namanya, ini mengubah teks menjadi teks huruf besar.

• Pertama, buka tab Tambahkan Kolom dan klik pada kolom khusus.

• • Sekarang, di kotak dialog kolom kustom, masukkan nama kolom dan rumus di bawah ini di kotak rumus kolom kustom:

 Text.Upper([Full Name]) 

• Dan ketika Anda mengklik OK, itu membuat kolom baru dengan semua nama dalam huruf besar.
• Selanjutnya adalah menghapus kolom lama dan mengganti nama kolom baru. Jadi klik kanan pada kolom pertama dan pilih hapus.

• Terakhir, ganti nama kolom baru menjadi “Nama Lengkap”.

Ada total 700 fungsi yang dapat Anda gunakan di power query sambil menambahkan kolom baru dan berikut adalah daftar lengkap yang disediakan oleh Microsoft untuk fungsi-fungsi tersebut, lihatlah.

Cara mengubah kueri di PQ

Jika Anda ingin membuat perubahan pada kueri yang sudah ada di buku kerja Anda, Anda cukup mengeditnya lalu membuat perubahan tersebut. Di tab Data, ada tombol bernama Kueri & Koneksi.

Saat Anda mengklik tombol ini, ini akan membuka panel di sisi kanan yang mencantumkan semua kueri yang Anda miliki di buku kerja saat ini.

Anda dapat mengklik kanan pada nama kueri dan memilih edit dan Anda akan mendapatkannya di editor kueri yang canggih untuk mengeditnya.

Saat Anda mengedit kueri, Anda bisa melihat bahwa semua langkah yang Anda lakukan sebelumnya tercantum dalam “Langkah yang Diterapkan” yang juga bisa Anda edit atau Anda bisa melakukan langkah baru.

Dan setelah Anda selesai mengedit, Anda cukup mengklik tombol “Tutup dan Muat”.

Koneksi ekspor dan impor

Jika Anda memiliki koneksi yang Anda gunakan untuk kueri dan sekarang Anda ingin berbagi koneksi tersebut dengan orang lain, Anda dapat mengekspor koneksi tersebut sebagai file odc.

Pada tabel kueri terdapat tombol yang disebut “Ekspor Koneksi” dan ketika Anda mengkliknya, Anda dapat menyimpan koneksi kueri tersebut ke dalam sistem Anda.

Dan jika Anda ingin mengimpor koneksi yang dibagikan oleh orang lain, Anda cukup membuka tab Data dan di Dapatkan & Transformasi klik Koneksi yang Ada .

Dan kemudian klik tombol “Jelajahi lebih lanjut” dari mana Anda dapat menemukan file koneksi yang dibagikan dengan Anda dan mengimpornya ke buku kerja Anda.

Bahasa PowerQuery (kode M)

Seperti yang saya sebutkan sebelumnya, untuk setiap langkah yang Anda ambil di PowerQuery, ini menghasilkan kode (di backend) yang disebut M Code . Di tab Beranda, ada tombol bernama ” Editor Tingkat Lanjut” yang dapat Anda gunakan untuk melihat kode.

Dan ketika Anda mengklik editor lanjutan, ia akan menampilkan editor kode dan kode ini terlihat seperti ini:

M adalah bahasa peka huruf besar-kecil dan seperti bahasa lainnya, M menggunakan variabel dan ekspresi. Struktur dasar kode terlihat seperti di bawah ini dimana kode dimulai dengan ekspresi LET.

Dalam kode ini kita memiliki dua variabel dan nilai yang ditetapkan padanya. Pada akhirnya, untuk mendapatkan nilainya, ekspresi IN digunakan. Sekarang ketika Anda mengklik OK, itu akan mengembalikan nilai yang ditetapkan ke variabel “Nama Variabel” di hasilnya.

Lihat sumber daya ini untuk mempelajari selengkapnya tentang Power Query Language.

Pada akhirnya

Anda tidak bisa menghindari POWER QUERY. Jika Anda berpikir seperti itu, banyak hal yang kita lakukan dengan fungsi Excel atau kode VBA dapat diotomatisasi dengan menggunakannya, dan saya yakin tutorial ini akan menginspirasi Anda untuk lebih sering menggunakannya.