Pag-parameter sa mga Dalan sa Data sa Power Query

Kung nagsugod ka na sa paggamit sa mga himan sa libre nga Power Query add-in sa Microsoft Excel, unya sa dili madugay masugatan nimo ang usa ka labi ka espesyalista, apan kanunay ug makalagot nga problema nga adunay kalabotan sa kanunay nga pagbuak sa mga link sa gigikanan nga datos. Ang esensya sa problema mao nga kung sa imong pangutana nag-refer ka sa mga eksternal nga file o folder, nan ang Power Query hardcodes ang hingpit nga agianan padulong kanila sa teksto sa pangutana. Maayo ang tanan sa imong kompyuter, apan kung magdesisyon ka nga magpadala usa ka file nga adunay usa ka hangyo sa imong mga kauban, nan mahigawad sila, tungod kay. naa silay lahi nga dalan padulong sa source data sa ilang computer, ug dili mugana ang among pangutana.

Unsa ang buhaton sa ingon nga kahimtang? Atong tan-awon kini nga kaso sa mas detalyado uban sa mosunod nga pananglitan.

Pagporma sa problema

Ibutang ta nga naa ta sa folder E:Mga taho sa pagpamaligya nahimutang ang file Top 100 nga mga produkto.xls, nga usa ka upload gikan sa among corporate database o ERP system (1C, SAP, etc.) Kini nga payl naglangkob og impormasyon mahitungod sa pinakasikat nga mga butang nga produkto ug ingon niini ang hitsura sa sulod:

Klaro na tingali nga hapit imposible nga magamit kini sa Excel sa kini nga porma: ang mga walay sulod nga linya sa usa nga adunay data, gisagol nga mga cell, dugang nga mga kolum, usa ka multi-level nga header, ug uban pa.

Busa, sunod niini nga file sa samang folder, naghimo kami og laing bag-ong file Handler.xlsx, diin maghimo kami usa ka pangutana sa Power Query nga magkarga ug dili maayo nga datos gikan sa gigikanan nga upload file Top 100 nga mga produkto.xls, ug ibutang kini sa han-ay:

Paghangyo sa usa ka eksternal nga file

Pag-abli sa file Handler.xlsx, pilia sa tab Data Command Pagkuha Data - Gikan sa File - Gikan sa Excel Workbook (Data — Pagkuhag Data — Gikan sa file — Gikan sa Excel), dayon ipiho ang lokasyon sa source file ug ang sheet nga among gikinahanglan. Ang pinili nga datos ikarga sa Power Query editor:

Ibalik nato sila sa normal:

1. Pagtangtang sa mga walay sulod nga linya nga adunay Panimalay — Pagtangtang sa mga linya — Pagtangtang sa mga walay sulod nga linya (Balay — Kuhaa ang mga Laray — Kuhaa ang mga Wala’y Lamang nga Laray).
2. I-delete ang wala kinahanglana nga top 4 lines through Panimalay — Delete Rows — Delete Top Rows (Balay - Kuhaa ang mga Laray - Kuhaa ang Top Rows).
3. Ipataas ang unang laray sa header sa lamesa gamit ang buton Gamita ang unang linya isip mga ulohan tab Home (Balay — Gamita ang unang laray isip ulohan).
4. Ibulag ang lima ka digit nga artikulo gikan sa ngalan sa produkto sa ikaduhang kolum gamit ang sugo gibahin nga kolum tab kausaban (Transform — Split Column).
5. Pagtangtang sa wala kinahanglana nga mga kolum ug pag-ilis sa ngalan sa mga ulohan sa nahabilin alang sa mas maayong panan-aw.

Isip resulta, kinahanglan natong makuha ang mosunod, mas nindot nga hulagway:

Nagpabilin nga i-upload kining enoble nga lamesa balik sa sheet sa among file Handler.xlsx ang grupo pagsira ug pag-download (Balay — Close&Load) tab Home:

Pagpangita sa agianan sa usa ka file sa usa ka hangyo

Karon tan-awon nato kung unsa ang hitsura sa atong pangutana "sa ilawom sa hood", sa internal nga lengguwahe nga gitukod sa Power Query nga adunay mubu nga ngalan nga "M". Aron mahimo kini, balik sa among pangutana pinaagi sa pag-double click niini sa tuo nga pane Mga hangyo ug koneksyon ug sa tab Review pagpili Abanteng Editor (Tan-awa — Advanced Editor):

Sa bintana nga nagbukas, ang ikaduhang linya nagpadayag dayon sa usa ka hard-coded nga agianan sa among orihinal nga upload file. Kung mahimo natong ilisan kini nga string sa teksto sa usa ka parameter, variable, o usa ka link sa usa ka Excel sheet cell diin kini nga agianan gisulat nang daan, nan dali ra naton kini usbon sa ulahi.

Pagdugang usa ka maalamon nga lamesa nga adunay agianan sa file

Atong tapuson ang Power Query sa pagkakaron ug mobalik sa atong file Handler.xlsx. Atong idugang ang usa ka bag-ong walay sulod nga sheet ug maghimo usa ka gamay nga "smart" nga lamesa sa ibabaw niini, sa usa ra nga cell diin ang tibuuk nga agianan sa among gigikanan nga data file isulat:

Aron makahimo og usa ka maalamon nga lamesa gikan sa usa ka regular nga range, mahimo nimong gamiton ang keyboard shortcut Ctrl+T o butones Format isip usa ka lamesa tab Home (Balay — Format isip Talaan). Ang ulohan sa kolum (cell A1) mahimong bisan unsa. Timan-i usab nga alang sa katin-awan gihatagan nako ang lamesa og ngalan lantugi tab Magtutukod (Laraw).

Ang pagkopya sa usa ka agianan gikan sa Explorer o bisan ang pagsulod niini nga mano-mano, siyempre, dili labi ka lisud, apan labing maayo nga maminusan ang hinungdan sa tawo ug mahibal-an ang agianan, kung mahimo, awtomatiko. Mahimo kining ipatuman gamit ang standard Excel worksheet function SULOD (CELL), nga makahatag ug daghang mapuslanong impormasyon bahin sa cell nga gipiho isip argumento – lakip ang dalan paingon sa kasamtangang file:

Kung atong hunahunaon nga ang source data file kanunay anaa sa samang folder sa atong Processor, nan ang dalan nga atong gikinahanglan mahimong maporma pinaagi sa mosunod nga pormula:

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

o sa English nga bersyon:

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

… asa ang function LEVSIMV (SA WALA) nagkuha ug usa ka piraso sa teksto gikan sa bug-os nga link hangtod sa pangbukas nga square bracket (ie ang agianan sa kasamtangan nga folder), ug dayon ang ngalan ug extension sa among source data file gipapilit niini.

I-parameter ang dalan sa pangutana

Ang katapusan ug labing hinungdanon nga paghikap nagpabilin - aron isulat ang agianan sa gigikanan nga file sa hangyo Top 100 nga mga produkto.xls, nga nagtumong sa cell A2 sa among gibuhat nga "smart" nga lamesa lantugi.

Aron mahimo kini, mobalik kita sa pangutana sa Power Query ug ablihan kini pag-usab Abanteng Editor tab Review (Tan-awa — Advanced Editor). Imbis sa usa ka text string-path sa mga kinutlo "E: Mga taho sa pagbaligyaTop 100 nga mga produkto.xlsx" Atong ipaila ang mosunod nga istruktura:

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

Atong tan-awon kon unsa ang naglangkob niini:

• Excel.CurrentWorkbook() usa ka function sa M nga pinulongan para sa pag-access sa mga sulod sa kasamtangan nga file
• {[Name="Settings"]}[Content] - kini usa ka parametro sa pagpino sa miaging function, nga nagpaila nga gusto namon makuha ang sulud sa "smart" nga lamesa lantugi
• [Path to source data] mao ang ngalan sa kolum sa lamesa lantuginga atong gitumong
  
• 0 {} mao ang row number sa table lantugidiin gusto namong kuhaon ang datos. Ang cap dili maihap ug ang pag-numero magsugod gikan sa zero, dili gikan sa usa.

Kana lang, sa pagkatinuod.

Kini nagpabilin sa pag-klik sa Humana ug susiha kung giunsa ang among hangyo molihok. Karon, kung ipadala ang tibuuk nga folder nga adunay duha nga mga file sa sulod sa lain nga PC, ang hangyo magpabilin nga magamit ug awtomatiko nga mahibal-an ang agianan sa data.

• Unsa ang Power Query ug ngano nga gikinahanglan kini kung nagtrabaho sa Microsoft Excel
• Giunsa ang pag-import sa usa ka naglutaw nga snippet sa teksto sa Power Query
• Pagdisenyo pag-usab sa XNUMXD Crosstab sa usa ka Flat Table nga adunay Power Query