Konten
Jika Anda sudah mulai menggunakan alat add-in Power Query gratis di Microsoft Excel, maka Anda akan segera menghadapi satu masalah yang sangat khusus, tetapi sangat sering dan mengganggu yang terkait dengan terus-menerus memutus tautan ke data sumber. Inti masalahnya adalah jika dalam kueri Anda merujuk ke file atau folder eksternal, maka Power Query membuat hardcode jalur absolut ke file atau folder tersebut dalam teks kueri. Semuanya berfungsi dengan baik di komputer Anda, tetapi jika Anda memutuskan untuk mengirim file dengan permintaan ke kolega Anda, mereka akan kecewa, karena. mereka memiliki jalur berbeda ke sumber data di komputer mereka, dan kueri kami tidak akan berfungsi.
Apa yang harus dilakukan dalam situasi seperti itu? Mari kita lihat kasus ini lebih detail dengan contoh berikut.
Rumusan masalah
Misalkan kita ada di folder E:Laporan penjualan terletak filenya 100 produk teratas.xls, yang merupakan unggahan dari database perusahaan atau sistem ERP kami (1C, SAP, dll.) File ini berisi informasi tentang item komoditas paling populer dan terlihat seperti ini di dalamnya:
Mungkin langsung jelas bahwa hampir tidak mungkin untuk bekerja dengannya di Excel dalam bentuk ini: baris kosong melalui satu dengan data, sel yang digabungkan, kolom tambahan, header multi-level, dll. akan mengganggu.
Oleh karena itu, di sebelah file ini di folder yang sama, kami membuat file baru lainnya Penangan.xlsx, di mana kita akan membuat kueri Power Query yang akan memuat data jelek dari file unggahan sumber 100 produk teratas.xls, dan urutkan:
Membuat permintaan ke file eksternal
Membuka file Penangan.xlsx, pilih pada tab Data perintah Dapatkan Data – Dari File – Dari Buku Kerja Excel (Data — Dapatkan Data — Dari file — Dari Excel), lalu tentukan lokasi file sumber dan lembar yang kita butuhkan. Data yang dipilih akan dimuat ke editor Power Query:
Mari kita membawa mereka kembali normal:
- Hapus baris kosong dengan Beranda — Hapus baris — Hapus baris kosong (Beranda — Hapus Baris — Hapus Baris Kosong).
- Hapus 4 baris teratas yang tidak perlu melalui Beranda — Hapus Baris — Hapus Baris Teratas (Beranda — Hapus Baris — Hapus Baris Atas).
- Naikkan baris pertama ke header tabel dengan tombol Gunakan baris pertama sebagai header tab Beranda (Beranda — Gunakan baris pertama sebagai tajuk).
- Pisahkan artikel lima digit dari nama produk di kolom kedua menggunakan perintah kolom terpisah tab Transformasi (Transformasi — Pisahkan Kolom).
- Hapus kolom yang tidak perlu dan ganti nama judul yang tersisa untuk visibilitas yang lebih baik.
Akibatnya, kita harus mendapatkan gambaran yang jauh lebih menyenangkan berikut ini:
Tetap mengunggah tabel yang dimuliakan ini kembali ke lembar di file kami Penangan.xlsx tim tutup dan unduh (Beranda — Tutup&Muat) tab Beranda:
Menemukan jalur ke file dalam permintaan
Sekarang mari kita lihat bagaimana kueri kita terlihat "di balik layar", dalam bahasa internal yang dibangun ke dalam Power Query dengan nama ringkas "M". Untuk melakukan ini, kembali ke kueri kami dengan mengklik dua kali di panel kanan Permintaan dan koneksi dan di tab ULASAN pilih Editor Tingkat Lanjut (Lihat — Editor Lanjutan):
Di jendela yang terbuka, baris kedua segera mengungkapkan jalur hard-code ke file unggahan asli kami. Jika kita dapat mengganti string teks ini dengan parameter, variabel, atau tautan ke sel lembar Excel di mana jalur ini telah ditulis sebelumnya, maka kita dapat dengan mudah mengubahnya nanti.
Tambahkan tabel pintar dengan jalur file
Mari kita tutup Power Query untuk saat ini dan kembali ke file kita Penangan.xlsx. Mari tambahkan lembar kosong baru dan buat tabel "pintar" kecil di atasnya, di satu-satunya sel tempat path lengkap ke file data sumber kita akan ditulis:
Untuk membuat tabel pintar dari rentang reguler, Anda dapat menggunakan pintasan keyboard Ctrl+T atau tombol Format sebagai tabel tab Beranda (Beranda — Format sebagai Tabel). Judul kolom (sel A1) bisa berupa apa saja. Perhatikan juga bahwa untuk kejelasan saya telah memberi nama tabel parameter tab Pembina (Rancangan).
Menyalin jalur dari Explorer atau bahkan memasukkannya secara manual, tentu saja, tidak terlalu sulit, tetapi yang terbaik adalah meminimalkan faktor manusia dan menentukan jalur, jika memungkinkan, secara otomatis. Ini dapat diimplementasikan menggunakan fungsi lembar kerja Excel standar SEL (SEL), yang dapat memberikan banyak informasi berguna tentang sel yang ditentukan sebagai argumen – termasuk jalur ke file saat ini:
Jika kita berasumsi bahwa file data sumber selalu berada di folder yang sama dengan Processor kita, maka path yang kita butuhkan dapat dibentuk dengan rumus berikut:
=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”))-1)&”100 produk teratas.xls”
atau dalam versi bahasa Inggris:
=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 оваров.xls»
… dimana fungsinya LEVSIMV (KIRI) mengambil sepotong teks dari tautan lengkap hingga kurung siku pembuka (yaitu jalur ke folder saat ini), dan kemudian nama dan ekstensi file data sumber kami direkatkan ke sana.
Parameterisasikan jalur dalam kueri
Sentuhan terakhir dan terpenting tetap – untuk menulis jalur ke file sumber dalam permintaan 100 produk teratas.xls, mengacu pada sel A2 dari tabel "pintar" yang kami buat parameter.
Untuk melakukannya, mari kembali ke kueri Power Query dan membukanya lagi Editor Tingkat Lanjut tab ULASAN (Lihat — Editor Lanjutan). Alih-alih jalur string teks dalam tanda kutip “E:Laporan penjualan 100 produk teratas.xlsx” Mari kita perkenalkan struktur berikut:
Excel.Buku Kerja Saat Ini(){[Nama="Setelan"]}[Konten]0 {}[Jalur ke sumber data]
Mari kita lihat apa saja yang terdiri dari:
- Excel.Buku Kerja Saat Ini() adalah fungsi bahasa M untuk mengakses konten file saat ini
- {[Nama="Setelan"]}[Konten] – ini adalah parameter penyempurnaan ke fungsi sebelumnya, yang menunjukkan bahwa kita ingin mendapatkan isi tabel “pintar” parameter
- [Jalur ke sumber data] adalah nama kolom dalam tabel parameteryang kami rujuk
- 0 {} adalah nomor baris dalam tabel parameterdari mana kita ingin mengambil data. Tutup tidak dihitung dan penomoran dimulai dari nol, bukan dari satu.
Itu saja, sebenarnya.
Tetap mengklik Finish dan periksa cara kerja permintaan kami. Sekarang, saat mengirim seluruh folder dengan kedua file di dalamnya ke PC lain, permintaan akan tetap beroperasi dan menentukan jalur ke data secara otomatis.
- Apa itu Power Query dan mengapa dibutuhkan saat bekerja di Microsoft Excel
- Cara mengimpor cuplikan teks mengambang ke Power Query
- Mendesain ulang XNUMXD Crosstab menjadi Flat Table dengan Power Query