Mengparameterkan Laluan Data dalam Power Query

Jika anda telah mula menggunakan alat tambahan Power Query percuma dalam Microsoft Excel, maka tidak lama lagi anda akan menghadapi satu masalah yang sangat khusus, tetapi sangat kerap dan menjengkelkan yang dikaitkan dengan sentiasa memutuskan pautan ke data sumber. Intipati masalahnya ialah jika dalam pertanyaan anda anda merujuk kepada fail atau folder luaran, maka Power Query mengodkan laluan mutlak kepada mereka dalam teks pertanyaan. Semuanya berfungsi dengan baik pada komputer anda, tetapi jika anda memutuskan untuk menghantar fail dengan permintaan kepada rakan sekerja anda, maka mereka akan kecewa, kerana. mereka mempunyai laluan berbeza ke data sumber pada komputer mereka dan pertanyaan kami tidak akan berfungsi.

Apa yang perlu dilakukan dalam keadaan sedemikian? Mari kita lihat kes ini dengan lebih terperinci dengan contoh berikut.

Perumusan masalah

Katakan kita ada dalam folder E:Laporan jualan terletak fail 100 produk teratas.xls, yang merupakan muat naik daripada pangkalan data korporat kami atau sistem ERP (1C, SAP, dsb.) Fail ini mengandungi maklumat tentang item komoditi paling popular dan kelihatan seperti ini di dalam:

Mungkin sudah jelas bahawa hampir mustahil untuk bekerja dengannya dalam Excel dalam bentuk ini: baris kosong melalui satu dengan data, sel bercantum, lajur tambahan, pengepala berbilang peringkat, dll. akan mengganggu.

Oleh itu, di sebelah fail ini dalam folder yang sama, kami mencipta satu lagi fail baharu Pengendali.xlsx, di mana kami akan membuat pertanyaan Power Query yang akan memuatkan data hodoh daripada fail muat naik sumber 100 produk teratas.xls, dan susunkannya:

Membuat permintaan kepada fail luaran

Membuka fail Pengendali.xlsx, pilih pada tab Tarikh Perintah Dapatkan Data – Dari Fail – Dari Buku Kerja Excel (Data — Dapatkan Data — Dari fail — Daripada Excel), kemudian nyatakan lokasi fail sumber dan helaian yang kami perlukan. Data yang dipilih akan dimuatkan ke dalam editor Power Query:

Mari kita kembalikan mereka seperti biasa:

1. Padamkan baris kosong dengan Laman Utama — Padam baris — Padam baris kosong (Laman Utama — Alih Keluar Baris — Alih Keluar Baris Kosong).
2. Padamkan 4 baris teratas yang tidak perlu Laman Utama — Padam Baris — Padam Baris Teratas (Laman Utama — Alih Keluar Baris — Alih Keluar Baris Atas).
3. Naikkan baris pertama ke pengepala jadual dengan butang Gunakan baris pertama sebagai tajuk tab Laman Utama (Laman Utama — Gunakan baris pertama sebagai pengepala).
4. Pisahkan artikel lima digit daripada nama produk dalam lajur kedua menggunakan arahan lajur belah tab Transformasi (Ubah — Lajur Pisah).
5. Padamkan lajur yang tidak perlu dan namakan semula tajuk yang selebihnya untuk keterlihatan yang lebih baik.

Akibatnya, kita harus mendapatkan gambar berikut yang lebih menyenangkan:

Ia kekal untuk memuat naik jadual mulia ini kembali ke helaian dalam fail kami Pengendali.xlsx pasukan tutup dan muat turun (Laman Utama — Tutup&Muatkan) tab Laman Utama:

Mencari laluan ke fail dalam permintaan

Sekarang mari kita lihat bagaimana pertanyaan kami kelihatan "di bawah hud", dalam bahasa dalaman yang terbina dalam Power Query dengan nama ringkas "M". Untuk melakukan ini, kembali ke pertanyaan kami dengan mengklik dua kali padanya di anak tetingkap kanan Permintaan dan sambungan dan pada tab ULASAN memilih Editor Lanjutan (Lihat — Editor Lanjutan):

Dalam tetingkap yang terbuka, baris kedua segera mendedahkan laluan berkod keras ke fail muat naik asal kami. Jika kita boleh menggantikan rentetan teks ini dengan parameter, pembolehubah atau pautan ke sel helaian Excel di mana laluan ini dipratulis, maka kita boleh menukarnya dengan mudah kemudian.

Tambah jadual pintar dengan laluan fail

Mari tutup Power Query buat masa ini dan kembali ke fail kami Pengendali.xlsx. Mari tambah helaian kosong baharu dan buat jadual "pintar" kecil di atasnya, dalam satu-satunya sel yang laluan penuh ke fail data sumber kami akan ditulis:

Untuk mencipta jadual pintar daripada julat biasa, anda boleh menggunakan pintasan papan kekunci Ctrl+T atau butang Format sebagai jadual tab Laman Utama (Laman Utama — Format sebagai Jadual). Tajuk lajur (sel A1) boleh menjadi apa-apa sahaja. Juga ambil perhatian bahawa untuk kejelasan saya telah memberikan nama jadual Parameter tab Pembina (Reka Bentuk).

Menyalin laluan dari Explorer atau bahkan memasukkannya secara manual, sudah tentu, tidak begitu sukar, tetapi yang terbaik adalah untuk meminimumkan faktor manusia dan menentukan laluan, jika boleh, secara automatik. Ini boleh dilaksanakan menggunakan fungsi lembaran kerja Excel standard SEL (SEL), yang boleh memberikan banyak maklumat berguna tentang sel yang dinyatakan sebagai hujah – termasuk laluan ke fail semasa:

Jika kami menganggap bahawa fail data sumber sentiasa terletak dalam folder yang sama dengan Pemproses kami, maka laluan yang kami perlukan boleh dibentuk dengan formula berikut:

=LEFT(CELL(“nama fail”);FIND(“[“;CELL(“nama fail”))-1)&”100 produk teratas.xls”

atau dalam versi bahasa Inggeris:

=LEFT(CELL(«nama fail»);FIND(«[«;CELL(«nama fail»))-1)&»Топ-100 товаров.xls»

… di mana fungsinya LEVSIMV (DIBIARKAN) mengambil sekeping teks daripada pautan penuh sehingga kurungan segi empat sama pembukaan (iaitu laluan ke folder semasa), dan kemudian nama dan sambungan fail data sumber kami dilekatkan padanya.

Parameterkan laluan dalam pertanyaan

Sentuhan terakhir dan paling penting kekal - untuk menulis laluan ke fail sumber dalam permintaan 100 produk teratas.xls, merujuk kepada sel A2 pada jadual "pintar" yang kami buat Parameter.

Untuk melakukan ini, mari kembali ke pertanyaan Power Query dan bukanya semula Editor Lanjutan tab ULASAN (Lihat — Editor Lanjutan). Daripada laluan rentetan teks dalam petikan “E:Laporan jualan 100 produk teratas.xlsx” Mari perkenalkan struktur berikut:

Excel.CurrentWorkbook(){[Name="Tetapan"]}[Kandungan]0 {}[Laluan ke sumber data]

Mari lihat apa yang terdiri daripada:

• Excel.CurrentWorkbook() ialah fungsi bahasa M untuk mengakses kandungan fail semasa
• {[Name="Tetapan"]}[Kandungan] – ini adalah parameter penghalusan kepada fungsi sebelumnya, menunjukkan bahawa kami ingin mendapatkan kandungan jadual "pintar" Parameter
• [Laluan ke sumber data] ialah nama lajur dalam jadual Parameteryang kami rujuk
  
• 0 {} ialah nombor baris dalam jadual Parameterdari mana kami ingin mengambil data. Cap tidak dikira dan penomboran bermula dari sifar, bukan dari satu.

Itu sahaja, sebenarnya.

Ia kekal untuk mengklik Selesai dan semak cara permintaan kami berfungsi. Kini, apabila menghantar keseluruhan folder dengan kedua-dua fail di dalam ke PC lain, permintaan akan kekal beroperasi dan menentukan laluan ke data secara automatik.

• Apakah Power Query dan mengapa ia diperlukan semasa bekerja dalam Microsoft Excel
• Cara mengimport coretan teks terapung ke dalam Power Query
• Mereka bentuk semula Tab Silang XNUMXD ke Meja Rata dengan Pertanyaan Kuasa