Parameterizing Llwybrau Data mewn Pŵer Ymholiad

Os ydych chi eisoes wedi dechrau defnyddio offer yr ategyn Power Query rhad ac am ddim yn Microsoft Excel, yna yn fuan iawn fe fyddwch chi'n dod ar draws un broblem hynod arbenigol, ond aml iawn a blino sy'n gysylltiedig â thorri dolenni i ddata ffynhonnell yn gyson. Hanfod y broblem yw, os ydych yn cyfeirio at ffeiliau neu ffolderi allanol yn eich ymholiad, yna mae Power Query yn gosod codau caled ar y llwybr absoliwt iddynt yn nhestun yr ymholiad. Mae popeth yn gweithio'n iawn ar eich cyfrifiadur, ond os penderfynwch anfon ffeil gyda chais at eich cydweithwyr, yna byddant yn siomedig, oherwydd. mae ganddynt lwybr gwahanol i'r data ffynhonnell ar eu cyfrifiadur, ac ni fydd ein hymholiad yn gweithio.

Beth i'w wneud mewn sefyllfa o'r fath? Gadewch i ni edrych ar yr achos hwn yn fwy manwl gyda'r enghraifft ganlynol.

Ffurfio'r broblem

Tybiwch fod gennym ni yn y ffolder E:Adroddiadau gwerthu gorwedd y ffeil Top 100 products.xls, sy'n uwchlwythiad o'n cronfa ddata gorfforaethol neu system ERP (1C, SAP, ac ati) Mae'r ffeil hon yn cynnwys gwybodaeth am yr eitemau nwyddau mwyaf poblogaidd ac mae'n edrych fel hyn y tu mewn:

Mae'n debyg ei bod yn amlwg yn syth oddi ar yr ystlum ei bod bron yn amhosibl gweithio gydag ef yn Excel ar y ffurf hon: bydd rhesi gwag trwy un gyda data, celloedd wedi'u huno, colofnau ychwanegol, pennawd aml-lefel, ac ati yn ymyrryd.

Felly, wrth ymyl y ffeil hon yn yr un ffolder, rydym yn creu ffeil newydd arall Triniwr.xlsx, lle byddwn yn creu ymholiad Power Query a fydd yn llwytho data hyll o'r ffeil uwchlwytho ffynhonnell Top 100 products.xls, a'u rhoi mewn trefn:

Gwneud cais i ffeil allanol

Wrth agor y ffeil Triniwr.xlsx, dewiswch ar y tab Dyddiad Gorchymyn Cael Data - O Ffeil - O Lyfr Gwaith Excel (Data - Cael Data - O ffeil - O Excel), yna nodwch leoliad y ffeil ffynhonnell a'r daflen sydd ei hangen arnom. Bydd y data a ddewiswyd yn cael ei lwytho i mewn i'r golygydd Power Query:

Gadewch i ni ddod â nhw yn ôl i normal:

1. Dileu llinellau gwag gyda Cartref — Dileu llinellau — Dileu llinellau gwag (Cartref - Tynnu Rhesi - Tynnu Rhesi Gwag).
2. Dileu 4 llinell uchaf diangen drwyddo Hafan — Dileu Rhesi — Dileu Rhesi Uchaf (Cartref - Tynnu Rhesi - Dileu Rhesi Uchaf).
3. Codwch y rhes gyntaf i bennawd y tabl gyda'r botwm Defnyddiwch y llinell gyntaf fel penawdau tab Hafan (Cartref - Defnyddiwch y rhes gyntaf fel pennawd).
4. Gwahanwch yr erthygl pum digid o enw'r cynnyrch yn yr ail golofn gan ddefnyddio'r gorchymyn colofn hollt tab Trawsnewid (Trawsnewid - Colofn Hollti).
5. Dileu colofnau diangen ac ailenwi penawdau'r rhai sy'n weddill i gael gwell gwelededd.

O ganlyniad, dylem gael y llun canlynol, llawer mwy dymunol:

Mae'n aros i uwchlwytho'r tabl ennobledig hwn yn ôl i'r ddalen yn ein ffeil Triniwr.xlsx y tîm cau a llwytho i lawr (Cartref - Cau a Llwyth) tab Hafan:

Dod o hyd i'r llwybr i ffeil mewn cais

Nawr, gadewch i ni weld sut mae ein hymholiad yn edrych “o dan y cwfl”, yn yr iaith fewnol sydd wedi'i hymgorffori yn Power Query gyda'r enw cryno “M”. I wneud hyn, ewch yn ôl at ein hymholiad trwy glicio ddwywaith arno yn y cwarel dde Ceisiadau a chysylltiadau ac ar y tab adolygiad dewis Uwch Olygydd (Gweld - Golygydd Uwch):

Yn y ffenestr sy'n agor, mae'r ail linell ar unwaith yn datgelu llwybr cod caled i'n ffeil uwchlwytho wreiddiol. Os gallwn ddisodli'r llinyn testun hwn â pharamedr, newidyn, neu ddolen i gell ddalen Excel lle mae'r llwybr hwn wedi'i ysgrifennu ymlaen llaw, yna gallwn ei newid yn hawdd yn nes ymlaen.

Ychwanegu tabl smart gyda llwybr ffeil

Gadewch i ni gau Power Query am y tro a dychwelyd i'n ffeil Triniwr.xlsx. Gadewch i ni ychwanegu dalen wag newydd a gwneud tabl “clyfar” bach arno, yn yr unig gell y bydd y llwybr llawn i'n ffeil data ffynhonnell yn cael ei ysgrifennu:

I greu tabl smart o ystod reolaidd, gallwch ddefnyddio'r llwybr byr bysellfwrdd Ctrl+T neu botwm Fformat fel bwrdd tab Hafan (Cartref - Fformat fel Tabl). Gall pennawd y golofn (cell A1) fod yn unrhyw beth o gwbl. Sylwch hefyd fy mod wedi rhoi enw i'r tabl er eglurder paramedrau tab Constructor (Dylunio).

Wrth gwrs, nid yw'n arbennig o anodd copïo llwybr o Explorer neu hyd yn oed mynd i mewn iddo â llaw, ond mae'n well lleihau'r ffactor dynol a phenderfynu ar y llwybr, os yn bosibl, yn awtomatig. Gellir gweithredu hyn gan ddefnyddio swyddogaeth taflen waith safonol Excel CELL (CELL), a all roi llawer o wybodaeth ddefnyddiol am y gell a nodir fel dadl - gan gynnwys y llwybr i'r ffeil gyfredol:

Os tybiwn fod y ffeil ddata ffynhonnell bob amser yn gorwedd yn yr un ffolder â'n Prosesydd, yna gellir ffurfio'r llwybr sydd ei angen arnom gan y fformiwla ganlynol:

=LEFT(CELL(“enw ffeil”);FIND("[";CELL("enw ffeil"))-1)&"100 cynnyrch gorau.xls"

neu mewn fersiwn Saesneg:

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

… ble mae'r swyddogaeth LEVSIMV (CHWITH) yn cymryd darn o destun o'r ddolen lawn hyd at y braced sgwâr agoriadol (hy y llwybr i'r ffolder gyfredol), ac yna mae enw ac estyniad ein ffeil data ffynhonnell yn cael ei gludo iddo.

Paramedrwch y llwybr yn yr ymholiad

Erys y cyffyrddiad olaf a phwysicaf - ysgrifennu'r llwybr i'r ffeil ffynhonnell yn y cais Top 100 products.xls, gan gyfeirio at gell A2 ein tabl “clyfar” a grëwyd paramedrau.

I wneud hyn, gadewch i ni fynd yn ôl at yr ymholiad Power Query a'i agor eto Uwch Olygydd tab adolygiad (Gweld - Golygydd Uwch). Yn lle llinyn testun-llwybr mewn dyfyniadau “E: Adroddiadau gwerthu Y 100 cynnyrch gorau.xlsx” Gadewch i ni gyflwyno'r strwythur canlynol:

Excel.CurrentWorkbook(){[Name="Gosodiadau"]}[Cynnwys]0 {}[Llwybr i ddata ffynhonnell]

Gadewch i ni weld beth mae'n ei gynnwys:

• Excel.CurrentWorkbook() yn swyddogaeth yr iaith M ar gyfer cyrchu cynnwys y ffeil gyfredol
• {[Name="Gosodiadau"]}[Cynnwys] - mae hwn yn baramedr mireinio i'r swyddogaeth flaenorol, sy'n nodi ein bod am gael cynnwys y tabl “clyfar” paramedrau
• [Llwybr i ddata ffynhonnell] yw enw'r golofn yn y tabl paramedrauat yr hwn y cyfeiriwn
  
• 0 {} yw rhif y rhes yn y tabl paramedrauyr ydym am gymryd data ohonynt. Nid yw'r cap yn cyfrif ac mae'r rhifo'n dechrau o sero, nid o un.

Dyna i gyd, mewn gwirionedd.

Mae'n parhau i fod i glicio ar Gorffen a gwirio sut mae ein cais yn gweithio. Nawr, wrth anfon y ffolder gyfan gyda'r ddwy ffeil y tu mewn i gyfrifiadur personol arall, bydd y cais yn parhau i fod yn weithredol ac yn pennu'r llwybr at y data yn awtomatig.

• Beth yw Power Query a pham mae ei angen wrth weithio yn Microsoft Excel
• Sut i fewnforio pyt testun arnofiol i Power Query
• Ailgynllunio Crosstab XNUMXD i Fwrdd Fflat gydag Ymholiad Pŵer