Power Query-n datu-bideak parametrizatzea

Dagoeneko Microsoft Excel-eko Power Query doako gehigarriaren tresnak erabiltzen hasi bazara, laster oso espezializatua, baina oso maiz eta gogaikarria den arazo batekin topo egingo duzu iturburu-datuetarako estekak etengabe haustearekin. Arazoaren funtsa hauxe da: zure kontsultan kanpoko fitxategi edo karpetei erreferentzia egiten bazaizu, Power Query-k horietarako bide absolutua kodetzen du kontsultaren testuan. Dena ondo funtzionatzen du zure ordenagailuan, baina zure lankideei eskaera batekin fitxategi bat bidaltzea erabakitzen baduzu, orduan etsita egongo dira, zeren. iturburuko datuetarako beste bide bat dute ordenagailuan, eta gure kontsultak ez du funtzionatuko.

Zer egin horrelako egoera batean? Ikus dezagun kasu hau zehatzago ondoko adibidearekin.

Arazoaren formulazioa

Demagun karpetan dugula E:Salmenten txostenak fitxategia datza 100 produkturik onenak.xls, hau da, gure datu-base korporatibotik edo ERP sistematik (1C, SAP, etab.) kargatzen dena. Fitxategi honek produktu ezagunenei buruzko informazioa du eta honen itxura du barruan:

Seguruenik, argi dago ia ezinezkoa dela Excel-en inprimaki honetan lan egitea: errenkada hutsak datuekin, gelaxka bateratuak, zutabe gehigarriak, maila anitzeko goiburuak, etab.

Horregatik, karpeta berean dagoen fitxategi honen ondoan, beste fitxategi berri bat sortzen dugu Kudeatzailea.xlsx, eta bertan Power Query kontsulta bat sortuko dugu, datu itsusiak kargatuko dituen iturriko kargatze-fitxategitik 100 produkturik onenak.xls, eta ordenatu:

Kanpoko fitxategi bati eskaera egitea

Fitxategia irekitzea Kudeatzailea.xlsx, hautatu fitxan Data Komandoa Lortu datuak - Fitxategitik - Excel Lan-koadernotik (Datuak — Lortu datuak — Fitxategitik — Exceletik), ondoren zehaztu iturburu-fitxategiaren kokapena eta behar dugun orria. Hautatutako datuak Power Query editorean kargatuko dira:

Ekar ditzagun normaltasunera:

1. Ezabatu lerro hutsak honekin Hasiera — Ezabatu lerroak — Ezabatu lerro hutsak (Hasiera — Kendu errenkadak — Kendu errenkadak hutsik).
2. Ezabatu behar ez diren goiko 4 lerroak Hasiera — Ezabatu errenkadak — Ezabatu goiko errenkadak (Hasiera — Kendu errenkadak — Kendu goiko errenkadak).
3. Igo lehen errenkada mahaiaren goiburura botoiarekin Erabili lehen lerroa goiburu gisa fitxa Hasiera (Hasierako — Erabili lehen errenkada goiburu gisa).
4. Bereizi bost digituko artikulua bigarren zutabeko produktuaren izenetik komandoa erabiliz zatitu zutabea fitxa Eraldaketa (Eraldaketa — Zatitu zutabea).
5. Ezabatu behar ez diren zutabeak eta izena aldatu gainerakoen goiburuei ikusgarritasun hobea izateko.

Ondorioz, hurrengo argazkia, askoz atseginagoa, lortu beharko genuke:

Taula nobletu hau berriro gure fitxategiko fitxara igotzea geratzen da Kudeatzailea.xlsx taldea itxi eta deskargatu (Hasiera — Itxi&Kargatu) fitxa Hasiera:

Eskaera batean fitxategi baterako bidea aurkitzea

Orain ikus dezagun nola dagoen gure kontsultak “kanpaiaren azpian”, Power Query-n barneko hizkuntzan “M” izen zehatzarekin. Horretarako, itzuli gure kontsultara eskuineko panelean klik bikoitza eginez Eskaerak eta konexioak eta fitxan Review aukeratu Editore aurreratua (Ikusi — Editore aurreratua):

Irekitzen den leihoan, bigarren lerroak berehala kodetutako bide gogor bat erakusten du gure jatorrizko kargatzeko fitxategirako. Testu-kate hau parametro, aldagai edo bide hau aldez aurretik idatzita dagoen Excel orri-gelaxka baterako esteka batekin ordezkatu badugu, gero erraz alda dezakegu.

Gehitu taula adimendun bat fitxategi-bide batekin

Itxi dezagun Power Query oraingoz eta itzul gaitezen gure fitxategira Kudeatzailea.xlsx. Gehitu dezagun orri huts berri bat eta egin dezagun taula "adimentsua" gainean, zeinaren gelaxka bakarrean gure iturburu-datuen fitxategirako bide osoa idatziko den:

Barruti arrunt batetik taula adimendun bat sortzeko, teklatuko lasterbidea erabil dezakezu Ctrl+T edo botoia Taula gisa formatua fitxa Hasiera (Hasiera — Taula gisa formatu). Zutabeen goiburua (A1 gelaxka) edozer izan daiteke. Kontuan izan, gainera, argitasunerako mahaiari izena jarri diodala parametroak fitxa Constructor (Diseinua).

Explorer-etik bide bat kopiatzea edo eskuz sartzea ere ez da zaila, noski, baina hobe da giza faktorea gutxitzea eta bidea, ahal bada, automatikoki zehaztea. Hau Excel lan-orriaren funtzio estandarra erabiliz inplementa daiteke CELL (ZELULA), eta horrek argumentu gisa zehaztutako gelaxkari buruzko informazio erabilgarria eman dezake, uneko fitxategirako bidea barne:

Iturburuko datu-fitxategia beti gure prozesadorearen karpeta berean dagoela suposatzen badugu, behar dugun bidea formula honen bidez osatu daiteke:

=LEFT(CELL(“fitxategi-izena”);FIND(“[“;CELL(“fitxategi-izena”)-1)&”100 produktu nagusiak.xls”

edo ingelesezko bertsioan:

=EZKERRA(GELAXA(«fitxategi-izena»);AURKITU(«[«;GELAXA(«fitxategi-izena»))-1)&»Топ-100 товаров.xls»

… non dago funtzioa LEVSIMV (EZKERRA) esteka osotik irekitako kortxeteraino (hau da, uneko karpetarako bidea) hartzen du testu zati bat, eta, ondoren, gure iturburu-datuen fitxategiaren izena eta luzapena itsatsi zaizkio.

Parametrizatu bidea kontsultan

Azken ukitu eta garrantzitsuena geratzen da: eskaeran iturburu fitxategirako bidea idaztea 100 produkturik onenak.xls, sortu dugun taula "adimentsua"ko A2 gelaxkari erreferentzia eginez parametroak.

Horretarako, itzul gaitezen Power Query kontsultara eta ireki dezagun berriro Editore aurreratua fitxa Review (Ikusi — Editore aurreratua). Testu-kate-bide baten ordez komatxo artean "E: Salmenten txostenakTop 100 products.xlsx" Sar dezagun honako egitura hau:

Excel.CurrentWorkbook(){[Name=”Ezarpenak”]}[Edukia]0 {}[Iturburuko datuetarako bidea]

Ikus dezagun zertan datzan:

• Excel.CurrentWorkbook() M hizkuntzaren funtzio bat da uneko fitxategiaren edukietara sartzeko
• {[Name=”Ezarpenak”]}[Edukia] – aurreko funtzioaren fintze-parametroa da, taula "adimentsua"ren edukia lortu nahi dugula adierazten duena parametroak
• [Iturburuko datuetarako bidea] taulako zutabearen izena da parametroakaipatzen duguna
  
• 0 {} taulako errenkada-zenbakia da parametroakbertatik datuak hartu nahi ditugu. Txapelak ez du zenbatzen eta numerazioa zerotik hasten da, ez batetik.

Hori da guztia, hain zuzen ere.

Klik egiteko geratzen da Amaitu eta egiaztatu nola funtzionatzen duen gure eskaera. Orain, bi fitxategiak barruan dituen karpeta osoa beste PC batera bidaltzean, eskaerak funtzionatzen jarraituko du eta datuen bidea automatikoki zehaztuko du.

• Zer da Power Query eta zergatik behar da Microsoft Excel-en lan egitean
• Nola inportatu mugikorren testu zati bat Power Query-ra
• XNUMXD gurutze-taula birdiseinatzea mahai lau batera Power Query-rekin