Andmeteede parameetrite määramine Power Querys

Kui olete juba alustanud Microsoft Exceli tasuta Power Query lisandmooduli tööriistade kasutamist, siis varsti puutute kokku ühe väga spetsiifilise, kuid väga sagedase ja tüütu probleemiga, mis on seotud pideva lähteandmete linkide katkemisega. Probleemi olemus seisneb selles, et kui oma päringus viitate välistele failidele või kaustadele, kodeerib Power Query päringu tekstis nende absoluutse tee. Teie arvutis töötab kõik hästi, kuid kui otsustate oma kolleegidele palvega faili saata, peavad nad pettuma, sest. neil on arvuti lähteandmeteni erinev tee ja meie päring ei tööta.

Mida teha sellises olukorras? Vaatleme seda juhtumit üksikasjalikumalt järgmise näite abil.

Probleemi sõnastamine

Oletame, et meil on kaustas E:Müügiaruanded peitub fail Top 100 toodet.xls, mis on üleslaaditud meie ettevõtte andmebaasist või ERP-süsteemist (1C, SAP jne). See fail sisaldab teavet kõige populaarsemate kaubaartiklite kohta ja näeb seest välja selline:

Ilmselt on kohe selge, et sellisel kujul on sellega Excelis peaaegu võimatu töötada: tühjad read andmetega, liidetud lahtrid, lisaveerud, mitmetasandiline päis jne segavad.

Seetõttu loome selle faili kõrvale samas kaustas veel ühe uue faili Handler.xlsx, milles loome Power Query päringu, mis laadib allika üleslaadimisfailist inetuid andmeid Top 100 toodet.xlsja seadke need järjekorda:

Päringu esitamine välisele failile

Faili avamine Handler.xlsx, valige vahekaardil kuupäev käsk Andmete hankimine – failist – Exceli töövihikust (Andmed — hangi andmed — failist — Excelist), seejärel määrake lähtefaili asukoht ja meile vajalik leht. Valitud andmed laaditakse Power Query redaktorisse:

Toome need tagasi normaalseks:

1. Kustuta tühjad read nupuga Avaleht — ridade kustutamine — tühjade ridade kustutamine (Avaleht – eemalda read – eemalda tühjad read).
2. Kustutage mittevajalikud 4 ülemist rida Avaleht — ridade kustutamine — ülemiste ridade kustutamine (Avaleht – eemalda read – eemalda ülemised read).
3. Tõstke esimene rida nupuga tabeli päisesse Kasutage esimest rida päistena tab Avaleht (Kodu – kasutage päisena esimest rida).
4. Eraldage viiekohaline artikkel tootenimest teises veerus käsuga jagatud veerg tab Transformation (Teisendus – jagatud veerg).
5. Parema nähtavuse huvides kustutage mittevajalikud veerud ja nimetage ülejäänud pealkirjad ümber.

Selle tulemusena peaksime saama järgmise, palju meeldivama pildi:

Jääb see täiustatud tabel meie faili lehele tagasi üles laadida Handler.xlsx meeskond sulgege ja laadige alla (Avaleht – sulge ja laadi) tab Avaleht:

Taotluses faili tee leidmine

Nüüd vaatame, kuidas näeb meie päring Power Querysse sisseehitatud sisekeeles "katuse all" välja lühinimega "M". Selleks minge tagasi meie päringu juurde, topeltklõpsates sellel paremal paanil Taotlused ja ühendused ja vahekaardil Ülevaade valima Täiustatud redaktor (Vaade – täiustatud redaktor):

Avanevas aknas kuvatakse teisel real kohe meie algse üleslaadimisfaili kõvasti kodeeritud tee. Kui saame selle tekstistringi asendada parameetri, muutuja või lingiga Exceli lehe lahtrile, kus see tee on eelnevalt kirjutatud, saame seda hiljem hõlpsasti muuta.

Lisage failiteega nutikas tabel

Sulgeme praegu Power Query ja naaseme oma faili juurde Handler.xlsx. Lisame uue tühja lehe ja teeme sellele väikese “nutika” tabeli, mille ainsasse lahtrisse kirjutatakse meie lähteandmete faili täielik tee:

Nutika tabeli loomiseks tavalisest vahemikust saate kasutada kiirklahvi Ctrl+T või nuppu Vorminda tabelina tab Avaleht (Avaleht — vorminda tabelina). Veeru pealkiri (lahter A1) võib olla absoluutselt ükskõik milline. Pange tähele ka seda, et selguse huvides olen andnud tabelile nime parameetrid tab Ehitaja (Kujundus).

Teekonna kopeerimine Explorerist või isegi käsitsi sisestamine pole muidugi eriti keeruline, kuid kõige parem on inimfaktorit minimeerida ja määrata tee, kui võimalik, automaatselt. Seda saab rakendada standardse Exceli töölehe funktsiooni abil KAMBER (KAMBER), mis võib anda argumendina määratud lahtri kohta hulga kasulikku teavet, sealhulgas praeguse faili tee:

Kui eeldame, et lähteandmefail asub alati meie protsessoriga samas kaustas, saab meile vajaliku tee moodustada järgmise valemiga:

=LEFT(CELL("failinimi");LEIA("[";CELL("failinimi")-1)&"100 populaarseimat toodet.xls"

või ingliskeelses versioonis:

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

… kus on funktsioon LEVSIMV (VASAK) võtab tekstiosa täislingist kuni avanurksuluni (st praeguse kausta tee) ja seejärel liimitakse sellele meie lähteandmete faili nimi ja laiend.

Parameetristage päringus olev tee

Jääb viimane ja kõige olulisem puudutus – kirjutada päringule lähtefaili tee Top 100 toodet.xls, viidates meie loodud "nutika" tabeli lahtrile A2 parameetrid.

Selleks pöördume tagasi Power Query päringu juurde ja avame selle uuesti Täiustatud redaktor tab Ülevaade (Vaade – täiustatud redaktor). Jutumärkides oleva tekstistringi tee asemel "E: Müügiaruanded 100 populaarseimat toodet.xlsx" Tutvustame järgmist struktuuri:

Excel.CurrentWorkbook(){[Name="Seaded"]}[Sisu]0 {}[Tee lähteandmeteni]

Vaatame, millest see koosneb:

• Excel.CurrentWorkbook() on M-keele funktsioon aktiivse faili sisule juurdepääsuks
• {[Name="Seaded"]}[Sisu] - see on eelmise funktsiooni täpsustamise parameeter, mis näitab, et tahame saada "nutika" tabeli sisu parameetrid
• [Tee lähteandmeteni] on tabeli veeru nimi parameetridmillele me viitame
  
• 0 {} on tabeli rea number parameetridkust tahame andmeid võtta. Kork ei loe ja nummerdamine algab nullist, mitte ühest.

See on tegelikult kõik.

Jääb üle klõpsata lõpp ja kontrollige, kuidas meie taotlus töötab. Nüüd, kui saadate kogu kausta mõlema failiga teise arvutisse, jääb päring tööle ja määrab andmete tee automaatselt.

• Mis on Power Query ja miks seda Microsoft Excelis töötamiseks vaja on
• Ujuva tekstilõigu importimine Power Querysse
• XNUMXD Crosstabi ümberkujundamine tasaseks tabeliks Power Queryga