Parameterisasi Data Path ing Power Query

Yen sampeyan wis miwiti nggunakake alat tambahan Power Query gratis ing Microsoft Excel, mula sampeyan bakal nemoni masalah khusus, nanging asring banget lan ngganggu sing ana gandhengane karo sambungan sing terus-terusan menyang sumber data. Inti masalah yaiku yen ing pitakon sampeyan ngrujuk menyang file utawa folder eksternal, banjur Power Query hardcode path absolut menyang teks pitakon kasebut. Kabeh bisa digunakake ing komputer, nanging yen sampeyan arep ngirim file karo panjalukan kanggo kolega, banjur padha bakal kuciwa, amarga. padha duwe path beda menyang sumber data ing komputer, lan pitakonan kita ora bakal bisa.

Apa sing kudu ditindakake ing kahanan kaya ngono? Ayo ndeleng kasus iki kanthi luwih rinci kanthi conto ing ngisor iki.

Formulasi masalah

Upaminipun kita duwe ing folder E:Laporan penjualan dumunung file Top 100 produk.xls, yaiku unggahan saka basis data perusahaan utawa sistem ERP (1C, SAP, lsp.) Berkas iki ngemot informasi babagan barang-barang komoditas sing paling populer lan katon kaya ing ngisor iki:

Iku mbokmenawa cetha langsung saka bat sing meh mokal kanggo bisa karo ing Excel ing wangun iki: baris kosong liwat siji karo data, sel gabungan, kolom ekstra, header multi-level, etc bakal ngganggu.

Mulane, ing jejere file iki ing folder sing padha, kita nggawe file anyar liyane Handler.xlsx, ing ngendi kita bakal nggawe query Power Query sing bakal mbukak data ala saka file unggahan sumber Top 100 produk.xls, lan urutake:

Nggawe panjalukan menyang file eksternal

Mbukak file Handler.xlsx, pilih ing tab Data Command Entuk Data - Saka File - Saka Buku Kerja Excel (Data - Entuk Data - Saka file - Saka Excel), banjur nemtokake lokasi file sumber lan sheet sing kita butuhake. Data sing dipilih bakal dimuat menyang editor Power Query:

Ayo padha bali menyang normal:

1. Busak baris kosong karo Ngarep - Busak baris - Busak baris kosong (Ngarep — Copot Larik — Copot Larik Kosong).
2. Mbusak 4 baris ndhuwur rasah liwat Ngarep — Busak Larik — Busak Larik Ndhuwur (Ngarep — Copot Larik — Copot Larik Ndhuwur).
3. Angkat baris pisanan menyang header tabel kanthi tombol Gunakake baris pisanan minangka header tab Home (Ngarep - Gunakake baris pisanan minangka header).
4. Pisahake artikel limang digit saka jeneng produk ing kolom kapindho nggunakake printah kolom pamisah tab transformasi (Transformasi - Kolom Pisah).
5. Busak kolom sing ora perlu lan ganti jeneng judhul sing isih ana kanggo visibilitas sing luwih apik.

Akibaté, kita kudu entuk gambar ing ngisor iki sing luwih nyenengake:

Iku tetep kanggo ngunggah tabel ennoble iki bali menyang sheet ing file kita Handler.xlsx tim nutup lan download (Ngarep - Tutup & Muat) tab Home:

Nemokake path menyang file ing panyuwunan

Saiki ayo ndeleng kepiye pitakon kita katon "ing tenda", ing basa internal sing dibangun ing Power Query kanthi jeneng ringkes "M". Kanggo nindakake iki, bali menyang pitakonan kita kanthi ngeklik kaping pindho ing panel tengen Panjaluk lan sambungan lan ing tab review milih Editor Lanjut (Deleng - Editor Lanjut):

Ing jendhela sing mbukak, baris kapindho langsung mbukak path hard-coded menyang file upload asli kita. Yen kita bisa ngganti string teks iki karo parameter, variabel, utawa link menyang sel sheet Excel ngendi path iki wis ditulis, banjur kita bisa gampang ngganti mengko.

Tambah tabel pinter karo path file

Ayo nutup Power Query saiki lan bali menyang file kita Handler.xlsx. Ayo nambah lembar kosong anyar lan nggawe tabel "pinter" cilik, ing siji-sijine sel sing path lengkap menyang file data sumber bakal ditulis:

Kanggo nggawe tabel pinter saka sawetara biasa, sampeyan bisa nggunakake trabasan keyboard Ctrl+T utawa tombol Format minangka tabel tab Home (Ngarep - Format minangka Tabel). Judhul kolom (sel A1) bisa apa wae. Uga elinga yen kanggo gamblang aku wis menehi jeneng tabel paramèter tab Constructor (Desain).

Nyalin path saka Explorer utawa malah ngetik kanthi manual, mesthi, ora utamané angel, nanging paling apik kanggo nyilikake faktor manungsa lan nemtokake dalan, yen bisa, kanthi otomatis. Iki bisa ditindakake kanthi nggunakake fungsi lembar kerja Excel standar SEL (SEL), sing bisa menehi akeh informasi migunani babagan sel sing ditemtokake minangka argumen - kalebu path menyang file saiki:

Yen kita nganggep manawa file data sumber mesthi ana ing folder sing padha karo Prosesor, mula path sing dibutuhake bisa dibentuk kanthi rumus ing ngisor iki:

=LEFT(CELL(“jeneng file”);FIND(“[“;CELL(“jeneng file”))-1)&”Top 100 produk.xls”

utawa ing versi Inggris:

=LEFT(CELL(«jeneng file»);GOLEK(«[«;CELL(«jeneng file»))-1)&»Топ-100 товаров.xls»

… endi fungsine LEVIMV (Kiwa) njupuk Piece saka teks saka link lengkap nganti krenjang kothak mbukak (IE path kanggo folder saiki), lan banjur jeneng lan extension saka file data sumber kita glued menyang.

Parameterize path ing query

Sentuhan pungkasan lan paling penting tetep - kanggo nulis path menyang file sumber ing panyuwunan Top 100 produk.xls, nuduhake sel A2 saka tabel "pinter" sing digawe paramèter.

Kanggo nindakake iki, ayo bali menyang pitakon Power Query lan mbukak maneh Editor Lanjut tab review (Deleng - Editor Lanjut). Tinimbang teks string-path ing kuotasi "E: Laporan penjualan 100 produk paling apik.xlsx" Ayo ngenalake struktur ing ngisor iki:

Excel.CurrentWorkbook(){[Name="Setelan"]}[Konten]0 {}[Path menyang sumber data]

Ayo ndeleng apa sing kalebu:

• Excel.CurrentWorkbook() minangka fungsi saka basa M kanggo ngakses isi file saiki
• {[Name="Setelan"]}[Konten] - iki minangka parameter refinement kanggo fungsi sadurunge, nuduhake yen kita pengin entuk isi tabel "pinter" paramèter
• [Path menyang sumber data] iku jeneng kolom ing tabel paramèterkang kita rujuk
  
• 0 {} yaiku nomer baris ing tabel paramètersaka ngendi kita pengin njupuk data. Tutup ora diitung lan nomer kasebut diwiwiti saka nol, ora saka siji.

Iku kabeh, nyatane.

Iku tetep kanggo klik ing Rampung lan mriksa carane panjalukan kita bisa. Saiki, nalika ngirim kabeh folder karo loro file menyang PC liyane, panjaluk kasebut bakal tetep operasional lan nemtokake dalan menyang data kanthi otomatis.

• Apa Power Query lan kenapa dibutuhake nalika nggarap Microsoft Excel
• Cara ngimpor potongan teks ngambang menyang Power Query
• Ngrancang ulang XNUMXD Crosstab menyang Tabel Datar kanthi Power Query