Jekk diġà bdejt tuża l-għodod tas-suppliment b'xejn Power Query f'Microsoft Excel, allura dalwaqt tiltaqa' ma' problema waħda speċjalizzata ħafna, iżda frekwenti ħafna u tedjanti assoċjata ma 'tkissir kostanti ta' links għad-dejta tas-sors. L-essenza tal-problema hija li jekk fil-mistoqsija tiegħek tirreferi għal fajls jew folders esterni, allura Power Query tikkodifika l-mogħdija assoluta għalihom fit-test tal-mistoqsija. Kollox jaħdem tajjeb fuq il-kompjuter tiegħek, imma jekk tiddeċiedi li tibgħat fajl b'talba lill-kollegi tiegħek, allura dawn ikunu diżappuntati, għaliex. għandhom triq differenti għad-dejta tas-sors fuq il-kompjuter tagħhom, u l-mistoqsija tagħna mhux se taħdem.

X'għandek tagħmel f'sitwazzjoni bħal din? Ejja nħarsu lejn dan il-każ f'aktar dettall bl-eżempju li ġej.

Formulazzjoni tal-problema

Ejja ngħidu li għandna fil-folder E:Rapporti tal-bejgħ tinsab il-fajl L-aqwa 100 prodott.xls, li hija upload mid-database korporattiva tagħna jew is-sistema ERP (1C, SAP, eċċ.) Dan il-fajl fih informazzjoni dwar l-aktar oġġetti tal-komoditajiet popolari u jidher bħal dan ġewwa:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Probabbilment huwa ċar mill-ewwel li huwa kważi impossibbli li taħdem magħha f'Excel f'din il-forma: ringieli vojta permezz ta 'wieħed b'dejta, ċelluli magħquda, kolonni żejda, header f'diversi livelli, eċċ se jinterferixxu.

Għalhekk, ħdejn dan il-fajl fl-istess folder, noħolqu fajl ġdid ieħor Handler.xlsx, li fiha se noħolqu query Power Query li se tgħabbi dejta ikrah mill-fajl tat-tlugħ tas-sors L-aqwa 100 prodott.xls, u poġġihom fl-ordni:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Tagħmel talba lil fajl estern

Ftuħ il-fajl Handler.xlsx, agħżel fuq it-tab Data Kmand Ikseb Data - Mill-Fajl - Mill-Ktieb tax-Xogħol ta' Excel (Data — Ikseb id-Data — Mill-fajl — Minn Excel), imbagħad speċifika l-post tal-fajl tas-sors u l-folja li neħtieġu. Id-dejta magħżula titgħabba fl-editur tal-Power Query:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Ejja nġibuhom lura għan-normal:

  1. Ħassar linji vojta bi Home — Ħassar linji — Ħassar linji vojta (Dar — Neħħi r-ringieli — Neħħi r-ringieli vojta).
  2. Ħassar l-aqwa 4 linji bla bżonn Home — Ħassar ir-ringieli — Ħassar ir-ringieli ta' fuq (Dar — Neħħi r-ringieli — Neħħi r-ringieli ta' fuq).
  3. Għolli l-ewwel ringiela għall-intestatura tal-mejda bil-buttuna Uża l-ewwel linja bħala headers tab home (Id-dar — Uża l-ewwel ringiela bħala header).
  4. Separa l-artikolu b'ħames figuri mill-isem tal-prodott fit-tieni kolonna billi tuża l-kmand kolonna maqsuma tab Trasformazzjoni (Trasforma — Kolonna maqsuma).
  5. Ħassar il-kolonni mhux meħtieġa u semmi mill-ġdid l-intestaturi ta 'dawk li fadal għal viżibilità aħjar.

Bħala riżultat, għandna nġibu l-istampa li ġejja, ħafna aktar pjaċevoli:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Fadal biex ittella' din it-tabella ennobbli lura fil-folja fil-fajl tagħna Handler.xlsx it-tim agħlaq u niżżel (Id-Dar — Agħlaq&Tagħbija) tab home:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Sib it-triq għal fajl f'talba

Issa ejja naraw kif il-mistoqsija tagħna tidher “taħt il-barnuża”, fil-lingwa interna mibnija fil-Power Query bl-isem konċiż “M”. Biex tagħmel dan, mur lura għall-mistoqsija tagħna billi tikklikkja darbtejn fuqha fil-pannell tal-lemin Talbiet u konnessjonijiet u fuq it-tab reviżjoni jagħżlu Editur Avvanzat (Ara — Editur Avvanzat):

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Fit-tieqa li tiftaħ, it-tieni linja immedjatament tiżvela mogħdija hard-coded għall-fajl ta 'upload oriġinali tagħna. Jekk nistgħu nissostitwixxu din is-sekwenza ta 'test b'parametru, varjabbli, jew link għal ċellula tal-folja Excel fejn din il-mogħdija hija miktuba minn qabel, allura nistgħu faċilment nibdluha aktar tard.

Żid tabella intelliġenti b'mogħdija tal-fajl

Ejja nagħlqu Power Query għalissa u nerġgħu lura għall-fajl tagħna Handler.xlsx. Ejja nżidu folja vojta ġdida u nagħmlu tabella żgħira "intelliġenti" fuqha, fl-unika ċellula li fiha se tinkiteb it-triq sħiħa għall-fajl tad-dejta tas-sors tagħna:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Biex toħloq tabella intelliġenti minn firxa regolari, tista 'tuża l-shortcut tat-tastiera Ctrl+T jew buttuna Format bħala tabella tab home (Id-dar — Format bħala Tabella). L-intestatura tal-kolonna (ċellula A1) tista 'tkun assolutament xejn. Innota wkoll li għaċ-ċarezza tajt isem it-tabella parametri tab kostruttur (Disinn).

Li tikkopja triq mill-Explorer jew saħansitra li tidħol manwalment hija, ovvjament, mhux partikolarment diffiċli, iżda l-aħjar huwa li timminimizza l-fattur uman u tiddetermina t-triq, jekk possibbli, awtomatikament. Dan jista 'jiġi implimentat bl-użu tal-funzjoni standard tal-worksheet Excel ĊELLULA (Ċellula), li jista 'jagħti mazz ta' informazzjoni utli dwar iċ-ċellula speċifikata bħala argument - inkluża l-mogħdija għall-fajl kurrenti:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Jekk nassumu li l-fajl tad-dejta tas-sors dejjem jinsab fl-istess folder bħall-Proċessur tagħna, allura l-mogħdija li neħtieġu tista 'tiġi ffurmata bil-formula li ġejja:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

=XELLUG(CELL(“isem tal-fajl”); ISSIB (“[“;ĊELLA (“isem tal-fajl”))-1)&”L-aqwa 100 prodott.xls”

jew fil-verżjoni bl-Ingliż:

=XELLUG(CELL(«isem tal-fajl»); ISSIB(«[«;ĊELLA(«isem tal-fajl»))-1)&»Топ-100 товаров.xls»

… fejn hi l-funzjoni LEVSIMV (XELLUG) jieħu biċċa test mill-link sħiħa sal-parentesi kwadra tal-ftuħ (jiġifieri l-mogħdija għall-folder attwali), u mbagħad l-isem u l-estensjoni tal-fajl tad-dejta tas-sors tagħna huwa inkollat ​​miegħu.

Parametrizza l-mogħdija fil-mistoqsija

L-aħħar kuntatt u l-aktar importanti jibqa ' - li tikteb it-triq għall-fajl tas-sors fit-talba L-aqwa 100 prodott.xls, b'referenza għaċ-ċellula A2 tat-tabella "intelliġenti" maħluqa tagħna parametri.

Biex tagħmel dan, ejja mmorru lura għall-mistoqsija dwar il-Power Query u erġa' tiftaħha Editur Avvanzat tab reviżjoni (Ara — Editur Avvanzat). Minflok test string-path fi kwotazzjonijiet "E:Rapporti tal-BejgħL-Awt 100 prodott.xlsx" Ejja nintroduċu l-istruttura li ġejja:

Parametrizzar ta' Mogħdijiet tad-Data f'Mistoqsija dwar l-Enerġija

Excel.Ktieb tax-xogħol kurrenti(){[Name="Settings"]}[Kontenut]0 {}[Passaġġ għad-dejta tas-sors]

Ejja naraw fiex tikkonsisti:

  • Excel.Ktieb tax-xogħol kurrenti() hija funzjoni tal-lingwa M għall-aċċess għall-kontenut tal-fajl kurrenti
  • {[Name="Settings"]}[Kontenut] – dan huwa parametru ta 'raffinament għall-funzjoni preċedenti, li jindika li rridu nġibu l-kontenut tat-tabella "intelliġenti" parametri
  • [Passaġġ għad-dejta tas-sors] huwa l-isem tal-kolonna fit-tabella parametrili għaliha nirreferu
  • 0 {} huwa n-numru tar-ringiela fit-tabella parametriminn fejn irridu nieħdu data. Il-limitu ma jgħoddx u n-numerazzjoni tibda minn żero, mhux minn wieħed.

Dak kollu, fil-fatt.

Fadal biex tikklikkja fuq Finatura u ċċekkja kif taħdem it-talba tagħna. Issa, meta tibgħat il-folder kollu biż-żewġ fajls ġewwa għal PC ieħor, it-talba tibqa 'operazzjonali u tiddetermina t-triq għad-data awtomatikament.

  • X'inhu Power Query u għaliex hija meħtieġa meta taħdem fil-Microsoft Excel
  • Kif timporta snippet ta' test f'wiċċ l-ilma fil-Power Query
  • Iddisinjar mill-ġdid ta 'XNUMXD Crosstab għal Tabella Ċatta b'Mistoqsija dwar l-Enerġija

Ħalli Irrispondi