Pag-parameter ng Mga Path ng Data sa Power Query

Kung nagsimula ka nang gumamit ng mga tool ng libreng Power Query add-in sa Microsoft Excel, sa lalong madaling panahon makakatagpo ka ng isang napaka-espesyalisado, ngunit napakadalas at nakakainis na problema na nauugnay sa patuloy na pagsira ng mga link sa pinagmulang data. Ang kakanyahan ng problema ay kung sa iyong query ay sumangguni ka sa mga panlabas na file o folder, pagkatapos ay i-hardcode ng Power Query ang ganap na landas patungo sa kanila sa teksto ng query. Ang lahat ay gumagana nang maayos sa iyong computer, ngunit kung magpasya kang magpadala ng isang file na may kahilingan sa iyong mga kasamahan, kung gayon sila ay mabibigo, dahil. mayroon silang ibang landas patungo sa pinagmulang data sa kanilang computer, at hindi gagana ang aming query.

Ano ang gagawin sa ganoong sitwasyon? Tingnan natin ang kasong ito nang mas detalyado sa sumusunod na halimbawa.

Pagbubuo ng problema

Ipagpalagay na mayroon kami sa folder E:Mga ulat sa pagbebenta kasinungalingan ang file Nangungunang 100 produkto.xls, na isang pag-upload mula sa aming corporate database o ERP system (1C, SAP, atbp.) Ang file na ito ay naglalaman ng impormasyon tungkol sa mga pinakasikat na item ng kalakal at ganito ang hitsura sa loob:

Malamang na malinaw na sa simula pa lang na halos imposible na gamitin ito sa Excel sa form na ito: ang mga walang laman na row sa pamamagitan ng isa na may data, pinagsamang mga cell, mga karagdagang column, isang multi-level na header, atbp. ay makagambala.

Samakatuwid, sa tabi ng file na ito sa parehong folder, lumikha kami ng isa pang bagong file Handler.xlsx, kung saan gagawa kami ng Power Query query na maglo-load ng pangit na data mula sa source upload file Nangungunang 100 produkto.xls, at ayusin ang mga ito:

Paggawa ng isang kahilingan sa isang panlabas na file

Pagbukas ng file Handler.xlsx, piliin sa tab data Utos Kumuha ng Data – Mula sa File – Mula sa Excel Workbook (Data — Kumuha ng Data — Mula sa file — Mula sa Excel), pagkatapos ay tukuyin ang lokasyon ng source file at ang sheet na kailangan namin. Ilo-load ang napiling data sa editor ng Power Query:

Ibalik natin sila sa normal:

1. Tanggalin ang mga walang laman na linya na may Home — Tanggalin ang mga linya — Tanggalin ang mga walang laman na linya (Home — Alisin ang Mga Hanay — Alisin ang Mga Walang Lamang Hanay).
2. Tanggalin ang hindi kinakailangang nangungunang 4 na linya Home — Tanggalin ang Mga Hanay — Tanggalin ang Mga Nangungunang Hanay (Home — Alisin ang Mga Hanay — Alisin ang Mga Nangungunang Hanay).
3. Itaas ang unang hilera sa header ng talahanayan gamit ang pindutan Gamitin ang unang linya bilang mga header tab Tahanan (Home — Gamitin ang unang hilera bilang header).
4. Paghiwalayin ang limang-digit na artikulo mula sa pangalan ng produkto sa pangalawang column gamit ang command hating hanay tab Pagbabagong-anyo (Transform — Hatiin ang Haligi).
5. Tanggalin ang mga hindi kinakailangang column at palitan ang pangalan ng mga heading ng mga natitira para sa mas mahusay na visibility.

Bilang resulta, dapat nating makuha ang sumusunod, mas kaaya-ayang larawan:

Ito ay nananatiling i-upload ang enobleng talahanayang ito pabalik sa sheet sa aming file Handler.xlsx ang koponan isara at i-download (Tahan — Isara at I-load) tab Tahanan:

Paghahanap ng path sa isang file sa isang kahilingan

Ngayon tingnan natin kung ano ang hitsura ng aming query "sa ilalim ng hood", sa panloob na wika na binuo sa Power Query na may maigsi na pangalan na "M". Upang gawin ito, bumalik sa aming query sa pamamagitan ng pag-double click dito sa kanang pane Mga kahilingan at koneksyon at sa tab Pagsusuri piliin Advanced na Editor (Tingnan — Advanced na Editor):

Sa window na bubukas, ang pangalawang linya ay agad na nagpapakita ng isang hard-coded path sa aming orihinal na upload file. Kung maaari nating palitan ang text string na ito ng isang parameter, variable, o isang link sa isang Excel sheet cell kung saan ang path na ito ay paunang nakasulat, pagkatapos ay madali natin itong mababago sa ibang pagkakataon.

Magdagdag ng smart table na may file path

Isara natin ang Power Query sa ngayon at bumalik sa aming file Handler.xlsx. Magdagdag tayo ng isang bagong walang laman na sheet at gumawa ng isang maliit na "matalinong" na talahanayan dito, sa tanging cell kung saan isusulat ang buong landas sa aming source data file:

Upang gumawa ng matalinong talahanayan mula sa isang regular na hanay, maaari mong gamitin ang keyboard shortcut Ctrl+T o pindutan I-format bilang isang talahanayan tab Tahanan (Home — Format bilang Talahanayan). Ang heading ng column (cell A1) ay maaaring maging anumang bagay. Tandaan din na para sa kalinawan ay binigyan ko ng pangalan ang talahanayan parameter tab Tagagawa (Disenyo).

Ang pagkopya ng isang landas mula sa Explorer o kahit na manu-manong pagpasok nito ay, siyempre, hindi partikular na mahirap, ngunit ito ay pinakamahusay na mabawasan ang kadahilanan ng tao at matukoy ang landas, kung maaari, nang awtomatiko. Ito ay maaaring ipatupad gamit ang karaniwang Excel worksheet function CELL (cell), na maaaring magbigay ng isang grupo ng mga kapaki-pakinabang na impormasyon tungkol sa cell na tinukoy bilang isang argumento - kasama ang landas sa kasalukuyang file:

Kung ipagpalagay namin na ang source data file ay palaging nasa parehong folder ng aming Processor, kung gayon ang path na kailangan namin ay maaaring mabuo ng sumusunod na formula:

=LEFT(CELL(“filename”); FIND(“[“;CELL(“filename”))-1)&”Top 100 products.xls”

o sa Ingles na bersyon:

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

… nasaan ang function LEVSIMV (KALIWA) kumukuha ng isang piraso ng text mula sa buong link hanggang sa pambungad na square bracket (ibig sabihin, ang path sa kasalukuyang folder), at pagkatapos ay ang pangalan at extension ng aming source data file ay nakadikit dito.

I-parameter ang path sa query

Ang huli at pinakamahalagang pagpindot ay nananatili - upang isulat ang path sa source file sa kahilingan Nangungunang 100 produkto.xls, na tumutukoy sa cell A2 ng aming ginawang "smart" na talahanayan parameter.

Upang gawin ito, bumalik tayo sa query ng Power Query at buksan itong muli Advanced na Editor tab Pagsusuri (Tingnan — Advanced na Editor). Sa halip na isang text string-path sa mga quote “E:Mga ulat sa pagbebentaTop 100 na produkto.xlsx” Ipakilala natin ang sumusunod na istraktura:

Excel.CurrentWorkbook(){[Name="Settings"]}[Content]0 {}[Path sa source data]

Tingnan natin kung ano ang binubuo nito:

• Excel.CurrentWorkbook() ay isang function ng wikang M para sa pag-access sa mga nilalaman ng kasalukuyang file
• {[Name="Settings"]}[Content] – ito ay isang parameter ng pagpipino sa nakaraang function, na nagpapahiwatig na gusto naming makuha ang mga nilalaman ng "matalinong" talahanayan parameter
• [Path sa source data] ay ang pangalan ng column sa table parameterna aming tinutukoy
  
• 0 {} ay ang row number sa table parameterkung saan gusto naming kumuha ng data. Ang takip ay hindi binibilang at ang pagnunumero ay nagsisimula sa zero, hindi mula sa isa.

Iyon lang, sa katunayan.

Ito ay nananatiling mag-click sa Tapusin at tingnan kung paano gumagana ang aming kahilingan. Ngayon, kapag ipinapadala ang buong folder na may parehong mga file sa loob sa isa pang PC, mananatiling gumagana ang kahilingan at awtomatikong matukoy ang landas patungo sa data.

• Ano ang Power Query at bakit ito kailangan kapag nagtatrabaho sa Microsoft Excel
• Paano mag-import ng lumulutang na snippet ng text sa Power Query
• Muling pagdidisenyo ng XNUMXD Crosstab sa isang Flat Table na may Power Query