Parameterisasi Jalur Data dina Power Query

Upami anjeun parantos ngamimitian nganggo alat tambihan Power Query gratis dina Microsoft Excel, maka pas pisan anjeun bakal mendakan hiji masalah anu khusus, tapi sering pisan sareng ngaganggu anu aya hubunganana sareng sambungan anu terus-terusan ka sumber data. Intina masalahna nyaéta upami dina pamundut anjeun anjeun ngarujuk kana file atanapi polder éksternal, maka Power Query hardcodes jalur mutlak pikeun aranjeunna dina téks pamundut. Sagalana jalan rupa dina komputer Anjeun, tapi lamun mutuskeun pikeun ngirim file kalawan pamundut ka kolega Anjeun, aranjeunna bakal kuciwa, sabab. aranjeunna gaduh jalur anu béda pikeun sumber data dina komputerna, sareng pamundut urang moal jalan.

Naon anu kudu dipigawé dina kaayaan kitu? Hayu urang tingali kasus ieu dina leuwih jéntré ku conto di handap ieu.

Rumusan masalah

Anggap we aya dina folder E:Laporan penjualan perenahna file Top 100 produk.xls, nu mangrupa unggah ti database perusahaan urang atawa sistem ERP (1C, SAP, jsb) file ieu ngandung émbaran ngeunaan item komoditi nu pang populerna jeung kasampak kawas kieu di jero:

Panginten écés langsung yén éta ampir teu mungkin pikeun damel sareng éta dina Excel dina bentuk ieu: baris kosong ngaliwatan hiji data, sél ngahiji, kolom tambahan, header multi-level, jsb bakal ngaganggu.

Ku alatan éta, gigireun file ieu dina folder sarua, urang nyieun file anyar sejen Panangan.xlsx, dimana urang bakal nyieun query Power Query anu bakal ngamuat data awon tina file unggah sumber Top 100 produk.xls, sarta nempatkeun aranjeunna dina urutan:

Nyuhunkeun pamundut ka file éksternal

Muka file Panangan.xlsx, pilih dina tab data parentah Kéngingkeun Data - Tina File - Tina Buku Kerja Excel (Data — Kéngingkeun Data — Tina file — Tina Excel), teras tangtukeun lokasi file sumber sareng lambaran anu urang peryogikeun. Data anu dipilih bakal dimuat kana pangropéa Power Query:

Hayu urang balikkeun deui ka normal:

1. Hapus garis kosong kalawan Imah — Hapus garis — Hapus garis kosong (Imah — Cabut Baris — Cabut Baris Kosong).
2. Hapus 4 garis luhur anu teu perlu Imah - Hapus Baris - Hapus Baris Top (Imah - Cabut Baris - Cabut Baris Top).
3. Angkat baris kahiji ka lulugu tabel kalawan tombol Paké baris kahiji salaku lulugu tab imah (Imah - Paké baris kahiji salaku lulugu).
4. Misahkeun artikel lima digit tina ngaran produk dina kolom kadua ngagunakeun paréntah kolom pamisah tab transformasi (Transformasi - Kolom Pisah).
5. Pupus kolom anu teu dipikabutuh sareng ganti nami judul anu sésana pikeun katingali langkung saé.

Hasilna, urang kudu meunang gambar di handap ieu, leuwih pikaresepeun:

Tetep unggah tabel ennoble ieu deui ka lambaran dina file urang Panangan.xlsx timnya nutup tur ngundeur (Imah - Tutup & Muatan) tab imah:

Milarian jalur ka file dina pamundut

Ayeuna hayu urang tingali kumaha pamundut urang kasampak "dina tiung", dina basa internal diwangun kana Power Query kalawan ngaran singket "M". Jang ngalampahkeun ieu, balik deui ka pamundut urang ku ngaklik dua kali dina jandela katuhu Requests sarta sambungan jeung dina tab tingalian milih Éditor canggih (Témbongkeun - Éditor Canggih):

Dina jandela anu muka, baris kadua langsung nembongkeun jalur hard-coded kana file unggah asli urang. Upami urang tiasa ngagentos senar téks ieu ku parameter, variabel, atanapi tautan kana sél lambar Excel dimana jalur ieu tos ditulis, teras urang tiasa ngarobih engké.

Tambahkeun tabel pinter jeung jalur file

Hayu urang tutup Power Query ayeuna sareng uih deui ka file urang Panangan.xlsx. Hayu urang tambahkeun lambaran kosong anyar sareng ngadamel méja "pinter" leutik di dinya, dina hiji-hijina sél anu jalur lengkep kana file data sumber urang bakal ditulis:

Pikeun nyieun tabel pinter tina rentang biasa, Anjeun bisa make potong kompas keyboard Ctrl+T atawa tombol Format salaku méja tab imah (Imah - Format salaku Méja). Judul kolom (sél A1) tiasa nanaon. Ogé dicatet yén pikeun kajelasan Kuring geus dibikeun tabel ngaran parameter tab Constructor (Desain).

Nyalin jalur ti Explorer atawa malah ngasupkeun eta sacara manual, tangtosna, teu utamana hésé, tapi pangalusna pikeun ngaleutikan faktor manusa jeung nangtukeun jalur, upami mungkin, otomatis. Ieu tiasa dilaksanakeun nganggo fungsi lembar kerja Excel standar KASUK (SEL), anu tiasa masihan seueur inpormasi anu mangpaat ngeunaan sél anu ditetepkeun salaku argumen - kalebet jalur ka file ayeuna:

Upami urang nganggap yén file data sumber salawasna aya dina polder anu sami sareng Prosesor urang, maka jalur anu urang peryogikeun tiasa dibentuk ku rumus ieu:

=LEFT(CELL("ngaran koropak");MANGGIH("[";CELL("ngaran koropak"))-1)&"Top 100 products.xls"

atanapi dina versi Inggris:

=LEFT(CELL(«ngaran koropak»);MARI («[«;CELL(«ngaran koropak»))-1)&»Топ-100 товаров.xls»

… dimana fungsina LEVSIMV (Kénca) nyokot sapotong téks ti link pinuh nepi ka bracket pasagi lawang (ie jalur ka folder ayeuna), lajeng nami na extension file data sumber urang glued ka dinya.

Parameterize jalur dina pamundut

Sentuhan terakhir sareng anu paling penting tetep - pikeun nyerat jalur ka file sumber dina pamundut Top 100 produk.xls, ngarujuk kana sél A2 tina méja "pinter" anu didamel kami parameter.

Jang ngalampahkeun ieu, hayu urang balik deui ka query Power Query tur buka deui Éditor canggih tab tingalian (Témbongkeun - Éditor Canggih). Gantina hiji téks string-jalur dina tanda petik "E: Laporan penjualanTop 100 produk.xlsx" Hayu urang ngawanohkeun struktur handap:

Excel.CurrentWorkbook(){[Name="Setélan"]}[Eusi]0 {}[Jalur ka sumber data]

Hayu urang tingali naon eusina:

• Excel.CurrentWorkbook() mangrupa fungsi basa M pikeun ngakses eusi file ayeuna
• {[Name="Setélan"]}[Eusi] - ieu mangrupikeun parameter perbaikan kana fungsi sateuacana, nunjukkeun yén urang hoyong kéngingkeun eusi tabel "pinter" parameter
• [Jalur ka sumber data] nyaéta ngaran kolom dina tabél parameternu urang rujuk
  
• 0 {} nyaeta jumlah baris dina tabél parameterti mana urang hoyong nyandak data. Cap teu diitung sarta panomeran dimimitian ti enol, teu ti hiji.

Éta sadayana, kanyataanna.

Tetep klik dina ngabereskeun jeung pariksa kumaha pamundut urang jalan. Ayeuna, nalika ngirim sadayana folder sareng duanana file di jero ka PC anu sanés, pamundut bakal tetep operasional sareng nangtukeun jalur ka data sacara otomatis.

• Naon Power Query sareng naha éta diperyogikeun nalika damel di Microsoft Excel
• Kumaha ngimpor snippet téks ngambang kana Power Query
• Ngadesain ulang XXNUMD Crosstab ka Méja Datar sareng Power Query