Sisällys
Jos olet jo aloittanut Microsoft Excelin ilmaisen Power Query -apuohjelman työkalujen käytön, kohtaat hyvin pian yhden erittäin erikoistuneen, mutta erittäin yleisen ja ärsyttävän ongelman, joka liittyy jatkuvasti katkeaviin linkkeihin lähdetietoihin. Ongelman ydin on, että jos kyselyssäsi viittaat ulkoisiin tiedostoihin tai kansioihin, Power Query koodaa niiden absoluuttisen polun kyselytekstissä. Kaikki toimii hyvin tietokoneellasi, mutta jos päätät lähettää pyynnön tiedoston kollegoillesi, he pettyvät, koska. heillä on eri polku tietokoneen lähdetietoihin, eikä kyselymme toimi.
Mitä tehdä tällaisessa tilanteessa? Tarkastellaan tätä tapausta yksityiskohtaisemmin seuraavan esimerkin avulla.
Ongelman muotoilu
Oletetaan, että meillä on kansiossa E:Myyntiraportit valehtelee tiedosto Top 100 tuotetta.xls, joka on ladattu yritystietokannastamme tai ERP-järjestelmästämme (1C, SAP jne.) Tämä tiedosto sisältää tietoa suosituimmista hyödyketuotteista ja näyttää sisältä tältä:
On luultavasti selvää heti, että sen kanssa on lähes mahdotonta työskennellä Excelissä tässä muodossa: tyhjät rivit, joissa on tietoja, yhdistetyt solut, ylimääräiset sarakkeet, monitasoinen otsikko jne., häiritsevät.
Siksi tämän tiedoston viereen samassa kansiossa luomme toisen uuden tiedoston Käsittelijä.xlsx, jossa luomme Power Query -kyselyn, joka lataa rumia tietoja lähdelataustiedostosta Top 100 tuotetta.xlsja laita ne järjestykseen:
Pyynnön tekeminen ulkoiseen tiedostoon
Tiedoston avaaminen Käsittelijä.xlsx, valitse välilehdeltä Päiväys Komento Hae tiedot – tiedostosta – Excel-työkirjasta (Data — Hae tiedot — Tiedostosta — Excelistä), määritä sitten lähdetiedoston sijainti ja tarvitsemamme arkki. Valitut tiedot ladataan Power Query -editoriin:
Palautetaan ne normaaliksi:
- Poista tyhjät rivit komennolla Etusivu — Poista rivejä — Poista tyhjät rivit (Etusivu - Poista rivit - Poista tyhjät rivit).
- Poista tarpeettomat 4 ylintä riviä Etusivu — Poista rivit — Poista ylimmät rivit (Etusivu - Poista rivit - Poista ylimmät rivit).
- Nosta ensimmäinen rivi taulukon otsikkoon painikkeella Käytä ensimmäistä riviä otsikoina kieleke Koti (Etusivu — Käytä ensimmäistä riviä otsikkona).
- Erota viisinumeroinen artikkeli tuotteen nimestä toisessa sarakkeessa komennolla jaettu sarake kieleke Muutos (Muunnosta – jaettu sarake).
- Poista tarpeettomat sarakkeet ja nimeä jäljellä olevien otsikot uudelleen näkyvyyden parantamiseksi.
Tuloksena meidän pitäisi saada seuraava, paljon miellyttävämpi kuva:
Jäljellä oleva jalostettu taulukko on ladattava takaisin tiedostomme taulukkoon Käsittelijä.xlsx joukkue sulje ja lataa (Koti - Sulje&Lataa) kieleke Koti:
Pyynnössä olevan tiedoston polun etsiminen
Katsotaan nyt, miltä kyselymme näyttää "hupun alla" Power Queryyn sisäänrakennetulla sisäisellä kielellä ytimekkäällä nimellä "M". Voit tehdä tämän palaamalla kyselyymme kaksoisnapsauttamalla sitä oikeanpuoleisessa ruudussa Pyynnöt ja yhteydet ja välilehdellä Arvostelu valita Edistynyt editori (Näytä — Advanced Editor):
Avautuvassa ikkunassa toinen rivi paljastaa välittömästi koodatun polun alkuperäiseen lataustiedostoomme. Jos voimme korvata tämän tekstijonon parametrilla, muuttujalla tai linkillä Excel-taulukon soluun, jossa tämä polku on valmiiksi kirjoitettu, voimme helposti muuttaa sitä myöhemmin.
Lisää älytaulukko tiedostopolulla
Suljetaan Power Query toistaiseksi ja palataan tiedostoomme Käsittelijä.xlsx. Lisätään uusi tyhjä taulukko ja tehdään siihen pieni "älykäs" taulukko, jonka ainoaan soluun kirjoitetaan koko polku lähdetietotiedostoomme:
Voit luoda älykkään taulukon tavallisesta valikoimasta käyttämällä pikanäppäintä Ctrl+T tai painiketta Muotoile taulukkona kieleke Koti (Etusivu — Muotoile taulukkona). Sarakeotsikko (solu A1) voi olla mitä tahansa. Huomaa myös, että olen antanut taulukolle nimen selvyyden vuoksi parametrit kieleke Rakentaja (Design).
Polun kopioiminen Explorerista tai jopa manuaalinen syöttäminen ei tietenkään ole erityisen vaikeaa, mutta on parasta minimoida inhimillinen tekijä ja määrittää polku, jos mahdollista, automaattisesti. Tämä voidaan toteuttaa tavallisella Excel-taulukkofunktiolla CELL (SOLU), joka voi antaa joukon hyödyllistä tietoa argumentiksi määritetystä solusta – mukaan lukien polku nykyiseen tiedostoon:
Jos oletetaan, että lähdetietotiedosto on aina samassa kansiossa kuin prosessorimme, niin tarvitsemamme polku voidaan muodostaa seuraavalla kaavalla:
=VASEN(SOLU("tiedostonimi");FIND("[";SOLU("tiedostonimi")-1)&"100 suosituinta tuotetta.xls"
tai englanninkielinen versio:
=LEFT(CELL(«tiedostonimi»);FIND(«[«;SOLU(«tiedostonimi»))-1)&»Топ-100 товаров.xls»
… missä on funktio LEVSIMV (VASEN) ottaa tekstin koko linkistä avaavaan hakasulkeeseen (eli polun nykyiseen kansioon), ja sitten siihen liimataan lähdetietotiedostomme nimi ja pääte.
Parametroi polku kyselyssä
Jäljelle jää viimeinen ja tärkein kosketus – polun kirjoittaminen pyyntöön lähdetiedostoon Top 100 tuotetta.xls, viittaa luomamme "älykäs" taulukon soluun A2 parametrit.
Voit tehdä tämän palaamalla Power Query -kyselyyn ja avaamalla se uudelleen Edistynyt editori kieleke Arvostelu (Näytä — Advanced Editor). Lainausmerkeissä olevan merkkijonopolun sijaan "E: Myyntiraportit 100 suosituinta tuotetta.xlsx" Esitellään seuraava rakenne:
Excel.CurrentWorkbook(){[Name="Asetukset"]}[Sisältö]0 {}[Polku lähdetietoihin]
Katsotaanpa, mistä se koostuu:
- Excel.CurrentWorkbook() on M-kielen funktio, jolla pääsee käsiksi nykyisen tiedoston sisältöön
- {[Name="Asetukset"]}[Sisältö] - tämä on edellisen funktion tarkennusparametri, joka osoittaa, että haluamme saada "älykkään" taulukon sisällön parametrit
- [Polku lähdetietoihin] on taulukon sarakkeen nimi parametritjohon viittaamme
- 0 {} on taulukon rivinumero parametritjosta haluamme ottaa tietoja. Korkki ei laske ja numerointi alkaa nollasta, ei yhdestä.
Siinä kaikki, itse asiassa.
Jäljelle jää klikata Suorittaa loppuun ja tarkista, kuinka pyyntömme toimii. Nyt kun lähetät koko kansion molemmilla tiedostoilla toiselle tietokoneelle, pyyntö pysyy toiminnassa ja määrittää polun tietoihin automaattisesti.
- Mikä on Power Query ja miksi sitä tarvitaan, kun työskentelet Microsoft Excelissä
- Kelluvan tekstinpätkän tuominen Power Queryyn
- XNUMXD Crosstabin uudelleensuunnittelu tasaiseksi taulukoksi Power Queryllä