Menjelajahi Fungsi Get & Transform Excel

Ringkasan bisnis plan

Di era data lake dan database skala petabyte ini, betapa seringnya saya masih menerima data dalam bentuk file CSV, teks, dan Excel. Sementara analitik modern berfokus pada kemajuan mutakhir dalam algoritme pembelajaran mesin, analisis data sehari-hari yang melelahkan masih merupakan proses manual untuk menemukan, menyusun, dan memperdebatkan tipe data yang berbeda.

Untuk analis keuangan, data sering kali datang sebagai spreadsheet Excel, tetapi sering kali, itu adalah dump data ke CSV atau kueri ke database SQL. Terkadang, data disusun dalam tata letak yang membingungkan atau tidak memiliki semua komponen yang diperlukan untuk analisis. Waktu yang dihabiskan untuk menggosok data ini adalah waktu yang berharga yang terbuang untuk analis, namun terkadang tugas ini diterima sebagai kejahatan yang perlu untuk ditoleransi.

Apa yang Get & Transform Lakukan?

Solusi untuk masalah umum ini sebenarnya cukup mudah diakses: Excel dan Power BI memiliki seluruh rangkaian alat transformasi data yang hanya diketahui oleh sedikit pengguna, bernama Get & Transform (sebelumnya dikenal sebagai Power Query). Menggunakan fungsionalitas ekstrak, transformasi, dan muat (ETL) yang disematkan memungkinkan analis keuangan untuk menautkan dengan mulus ke sumber data mereka dan mendapatkan wawasan lebih cepat.

Saat kami mengumpulkan data untuk dimuat ke Excel atau Power BI, kami biasanya harus melakukan beberapa transformasi pada data. Beberapa contoh manipulasi data meliputi:

• Menghapus kolom,
• Memfilter data,
• Mengelompokkan data,
• Memutar/membatalkan putaran data,
• Memisahkan string menjadi substring,
• Mengekstrak kata kunci dari string,
• Menambahkan baris dari tabel lain, dan
• Menggabungkan tabel dua dimensi.

Dalam diagram di bawah, kita melihat bahwa Get & Transform melakukan peran yang membosankan ini dalam pra-pemrosesan data sebelum dimuat.

Mengapa Anda Harus Menggunakan Get & Transform?

Mengapa mempelajari cara menggunakan Get & Transform bermanfaat? Nah, ketika saya melihat untuk apa saya secara pribadi menggunakan fungsi ini, itu telah menawarkan kepada saya seperangkat alat yang dapat ditempa untuk:

• Memuat seluruh folder file teks ke dalam satu tabel data
• Mengubah file akuntansi yang diekspor menjadi tata letak yang mudah dicerna
• Memuat jutaan baris penjualan ke Power Pivot secara langsung
• Mengelompokkan data harian ke dalam hasil bulanan yang dapat dikelola sebelum mengimpornya ke Excel
• Penyambungan data dari tabel lain dengan bergabung pada kolom yang cocok

Umumnya, ketika saya menerima data baru, saya akan menjelajahinya menggunakan Get & Transform sebelum memuatnya ke Power Pivot. Ini memungkinkan saya untuk melihat transformasi apa yang mungkin diperlukan dan dengan cepat melakukan beberapa pivot dan pengelompokan pada data untuk merumuskan kerangka kerja untuk analisis. Dalam banyak kasus, pada tahap ini, saya akan menemukan bahwa saya membutuhkan lebih banyak data, atau ada masalah data. Dengan menggunakan platform berbasis Excel, saya dapat dengan cepat beralih dengan sumber data saya untuk menemukan anomali data ini.

Pada akhirnya, keputusan untuk tetap menggunakan Excel atau memindahkan analisis data ke platform lain akan bergantung pada audiens dan pengulangan serta distribusi analisis. Jika klien saya hanya menggunakan Excel, maka saya hampir selalu menggunakan Get & Transform untuk memuat data, Power Pivot untuk melakukan analisis, dan Excel untuk menghasilkan PivotTable dan bagan. Bagi klien, ini akan terasa mulus karena semuanya ditempatkan di dalam Excel.

Namun, jika klien saya:

1. Ingin menggunakan alat visualisasi lain,
2. Memiliki banyak pengguna yang akan menyegarkan data, atau
3. Perlu menggunakan model pembelajaran mesin,

Kemudian saya akan menggunakan Get & Transform hanya untuk eksplorasi data awal, dan kemudian memindahkan angkat berat ke R.

Cara Mengakses Get & Transform di Excel atau Power BI

Di versi Excel sebelumnya, Power Query adalah add-in yang dapat diinstal untuk membantu fungsi ETL. Namun, di Excel 2016 dan Power BI, alat ini terintegrasi lebih erat. Di Excel 2016, mereka dapat diakses melalui tab Data , lalu bagian Dapatkan & Transformasikan Data .

Di Power BI, fungsionalitas ada di tab Beranda , di bagian Data Eksternal .

Dalam artikel ini, contoh saya terjadi di Power BI, tetapi antarmukanya hampir identik dengan Excel. Saya akan menunjukkan perbedaan ketika mereka muncul sehingga tutorial harus masuk akal untuk kedua jenis pengguna.

1. Memuat File CSV

Untuk membantu tutorial ini, saya telah membuat beberapa contoh data penjualan untuk pengecer fiksi yang menjual perlengkapan dan pakaian luar ruangan. Dalam setiap contoh ini, data akan dihasilkan dengan cara yang berbeda untuk mendemonstrasikan metode pembuangan data yang realistis.

Sebagai contoh awal, kita akan melihat data yang disajikan sebagai dump data besar ke dalam file CSV. Faktor yang memperumit adalah bahwa data disajikan dengan beberapa kolom yang mewakili berbagai toko. Kami idealnya ingin mengimpor dan mengubah data menjadi tata letak yang lebih bermanfaat.

Di bawah ini adalah tangkapan layar dari apa yang tampak seperti CSV mentah:

Mengapa kita ingin mengubah ini? Untuk memanfaatkan kemampuan hubungan yang dimungkinkan dalam aplikasi ini. Kita akan melihat permainan ini lebih lanjut dalam diskusi.

Untuk saat ini, mari kita asumsikan bahwa kita perlu melihat data sebagai struktur yang "lebih sempit dan lebih tinggi", daripada struktur yang "lebih lebar dan lebih pendek". Langkah pertama adalah memuat CSV; kemudian, kita akan mulai "membatalkan" data.

Seperti yang Anda lihat, struktur akhir data lebih sempit dari data awal, dan lebih panjang. Poin lainnya adalah, saat kita mengklik tindakan yang berbeda, alat di sisi kanan menghasilkan daftar langkah yang diterapkan yang digunakan untuk membuat kueri. Penting untuk dipahami bahwa ini terjadi di latar belakang, karena akan ditinjau kembali nanti.

Dapatkan & Transform terlihat dan berperilaku serupa antara Power BI dan Excel untuk sebagian besar. Namun, di Excel, setelah mengklik Tutup dan Muat , ada satu perintah tambahan. Pada gambar di bawah, kita dapat beralih antara apakah kita ingin memuat data ke dalam:

1. Sebuah tabel di Excel,
2. PivotTable dibuat terhadap data,
3. PivotChart dibuat berdasarkan data, atau
4. “Hanya Buat Koneksi.”

Selain itu, kita juga diberikan opsi untuk menambahkan data ini ke Model Data atau tidak. Mencentang kotak ini memuat data ke dalam tabel Power Pivot. Jika kita akan menganalisis data di Power Pivot, saya menyarankan untuk memilih Hanya Buat Koneksi , lalu pastikan bahwa opsi Tambahkan data ini ke Model Data dipilih. Jika data berada dalam batas baris Excel, dan kami lebih suka melakukan analisis di Excel, maka pilih saja Tabel .

Dalam klip berikutnya, kita akan melihat bahwa alasan mengapa kami memformat data menjadi panjang dan kurus adalah agar kami dapat menganalisis penjualan tidak hanya berdasarkan toko tetapi juga berdasarkan wilayah dan negara bagian. Untuk menyelesaikan tugas ini, kami akan mengimpor tabel yang memetakan setiap toko ke wilayah dan negara bagian. Kita akan melihat di bawah bahwa kita dapat dengan cepat membuat laporan yang menunjukkan penjualan berdasarkan pengelompokan yang berbeda ini.

Anda dapat membayangkan bagaimana jenis kemampuan untuk transformasi data di Excel, atau Power BI ini, dapat diterapkan dengan kuat ke kasus apa pun di mana kami memiliki pengelompokan data yang dinamis, seperti:

• Menggulung data harian ke dalam minggu, bulan, dan kuartal;
• Pengelompokan tenaga penjualan ke dalam departemen dan wilayah; atau
• Memetakan SKU ke jenis produk.

Sementara artikel ini membahas CSV dan file Excel lainnya, Get & Transform menangani berbagai tipe data. Setelah kueri dibuat, kueri dapat disegarkan seiring waktu saat data berubah.

2. Menangani String Teks

Untuk mendemonstrasikan kemampuan Get & Transform untuk memanipulasi string, saya membuat kumpulan data lain yang meniru file teks yang menunjukkan transaksi akuntansi dari buku besar perusahaan (GL).

Perhatikan bagaimana nomor rekening dan nama muncul dalam string yang sama? Di Power BI, kami dapat dengan mudah mengurai nomor dan nama akun ke dalam bidang terpisah.

Dalam video ini, Anda dapat melihat bahwa setelah saya membagi kolom, alat tersebut menebak bahwa sisi kiri baru bidang Akun harus berupa angka, dan itu membuat langkah "Tipe Berubah1". Karena pada akhirnya kita menginginkan bidang ini sebagai string, kita dapat melanjutkan dan menghapus langkah secara manual di bawah langkah-langkah yang diterapkan.

Selanjutnya, kami mengambil data yang sama dan membuat bagan akun dengan pemetaan ke kategori akun.

Mengapa kita harus melalui semua langkah itu untuk memetakan beberapa nomor rekening? Buku besar umum yang sebenarnya bisa menjadi ratusan atau bahkan ribuan akun. Kueri pemetaan cepat ini, seperti yang telah kami tunjukkan, akan diskalakan ke tingkat itu tanpa pekerjaan tambahan.

3. Bekerja dengan Sumber Data yang Berbeda

Dapatkan & Transform mendukung banyak sumber data yang berbeda. Meskipun bukan daftar yang lengkap, berikut adalah beberapa contohnya:

Secara pribadi, saya hanya mencoba sekitar setengah dari koneksi pada daftar di atas. Setiap konektor yang saya gunakan cukup kuat; Saya telah mendapatkan dari data mentah ke wawasan tanpa jumlah pekerjaan yang memberatkan. Sama pentingnya, ini berfungsi sebagai validator antara sumber data yang berbeda, memastikan bahwa output akhir memiliki tingkat kontrol kualitas yang dinormalisasi.

4. Personalisasi Kode dengan Bahasa M

Di latar belakang, Get & Transform menghasilkan kode setiap kali kita mengklik tombol di alat atau membuat pilihan. Di bawah ini adalah contoh bagaimana Anda akan mengakses kode untuk kueri pemetaan akun yang kami buat:

Kode menggunakan bahasa fungsional bernama M, yang dihasilkan secara otomatis untuk kasus penggunaan dasar. Namun, untuk pertengkaran data yang lebih rumit, kita dapat mengedit dan menulis kode kita sendiri. Untuk sebagian besar kasus, saya hanya akan membuat sedikit modifikasi pada kode ini. Dalam transformasi yang lebih rumit, saya dapat menulis sebagian besar kode dari awal ke tabel sementara tahap, atau untuk melakukan penggabungan yang lebih rumit.

Batas Get & Transform

Excel cenderung mencapai batasnya saat Anda mencoba mengekspor lebih dari satu juta baris. Dalam kasus di mana saya telah mengubah jutaan baris dengan Get & Transform, satu-satunya cara untuk mengirimkan baris yang tidak dikelompokkan adalah melalui peretasan atau solusi yang membosankan. Saya juga menemukan bahwa kueri Get & Transform bisa menjadi tidak stabil untuk diterapkan ke banyak pengguna, terutama jika Anda menggunakan beberapa sumber data dan bergabung. Dalam kasus tersebut, saya akan selalu menggunakan R untuk menyebarkan perselisihan data yang dapat digandakan. Terakhir, Excel tidak dibuat untuk pemodelan data yang lebih canggih. Anda dapat melakukan regresi linier dengan cukup cepat, tetapi di luar itu, Anda perlu menggunakan platform yang lebih ketat.

Setelah mengatakan semua itu, saya menemukan bahwa Excel adalah yang paling nyaman bagi sebagian besar klien saya. Excel masih merupakan alat yang paling penting dalam gudang analis keuangan. Dengan menggabungkan fungsionalitas Dapatkan & Transformasikan, Excel dan Power BI menjadi lebih kuat melalui berbagai sumber data yang dapat mereka terima.