- Power Queryn avulla voit yhdistää useita tiedostoja paikallisesta tai pilvikansiosta, kunhan ne jakavat saman tietorakenteen.
- Esimerkkitiedoston käyttäminen helpottaa joukkomuunnosten tekemistä, kuten ylimpien rivien poistamista tai otsikoiden muuttamista.
- Sarakkeiden täsmälleen vastaavuuksien puutteeseen liittyy rajoituksia, jotka vaativat manuaalista puhdistusta tai VBA-komentosarjojen käyttöä monimutkaisissa tapauksissa.
Kuvittele, että sinulla on joukko kuukausittaisia tai viikoittaisia raportteja hajallaan kansioissa, ja sen sijaan, että tuhlaat aamusi vimmatusti kopioimalla ja liittämällä tietoja, voisit yhdistää kaiken yhdeksi Excel-laskentataulukoksi vain muutamalla napsautuksella. Juuri tässä kohtaa Power Query astuu kuvaan, työkalu, joka on käytännössä kenen tahansa datan kanssa työskentelevän oikea käsi, jonka avulla voimme yhdistää saman rakenteen omaavat tiedostot vaivattomasti.
Tämän järjestelmän hienoin ominaisuus on, että se ei rajoita sinua tietokoneesi paikalliseen kansioon; voit hakea tietoja SharePoint-sivustoista, Azure Data Lakesta tai Azure-blobeista . Avaimena tämän sujuvaan toimintaan on, että tiedostoilla on sama rakenne, eli sarakkeet vastaavat toisiaan, vaikka Power Query onkin riittävän älykäs puhdistamaan tiedot, jos niissä on kohinaa tai tarpeettomia rivejä.
Vaiheittainen yhdistämisprosessi
Kun muodostat yhteyden tietolähteeseen, näet ensimmäisenä tiedostojärjestelmänäkymän. Tässä tärkein sarake on Sisältö, joka sisältää kunkin asiakirjan binääriesityksen . Jos napsautat jotakin näistä binääriarvoista, ohjelma yrittää määrittää, onko kyseessä CSV-, JSON- vai Excel-tiedosto, jotta se voidaan avata. Koko erän yhdistämiseksi on kaksi vaihtoehtoa: napsauttaa suoraan Yhdistä-painiketta tai, jos olet tarkempi, valita Muunna tiedot suodattaaksesi tiedostoluettelon (esimerkiksi ohittaaksesi tietyt kansiot) ennen yhdistämisen jatkamista.
Kun avaat yhdistämisvalintaikkunan, Power Query analysoi rakenteen esimerkkitiedoston avulla . Oletusarvoisesti se valitsee luettelon ensimmäisen tiedoston, mutta voit muuttaa sitä, jos saatavilla on toinen tiedosto, joka edustaa tietojoukkoa paremmin. Tässä voit valita virheellisten tiedostojen ohittamisen , mikä on hyödyllinen apu, kun raportti on vioittunut tai tyhjä, estäen koko prosessin pysähtymisen.
Tulosteiden hallinta ja tietojen puhdistus
Yhdistämisen hyväksymisen jälkeen järjestelmä tekee kulissien takana teknistä taikaa: se luo esimerkkikyselyn ja parametrisoidun funktion. Tämä tarkoittaa, että kaikki esimerkkitiedostoon tekemäsi muutokset replikoidaan automaattisesti kaikkiin muihin kansion tiedostoihin. Jos esimerkiksi CSV-tiedostoissasi on neljä riviä tarpeettomia otsikoita ennen varsinaista dataa, voit siirtyä muunnoskyselyyn ja käyttää ylimpien rivien poistamisvaihtoehtoa siivotaksesi sotkua.
Rivien puhdistamisen jälkeen on tärkeää käyttää " Käytä ensimmäistä riviä otsikoina" -funktiota . Muista, että Power Query lisää yleensä automaattisen tietotyypin muunnosvaiheen, kun teet näin. Jos pääkyselyyn palatessasi näet virheen, jonka mukaan saraketta 'Sarake1' ei löydy, älä huoli; tämä on normaalia, koska olet nimennyt sarakkeet uudelleen esimerkissä , eikä seuraava vaihe enää tunnista vanhoja nimiä. Poista vain virheellinen vaihe Käytetyt vaiheet -ruudusta, niin kaikki palautuu normaaliksi.
Yleisiä haasteita ja edistyneitä vaihtoehtoja
Joskus elämä ei ole niin yksinkertaista, ja kohtaamme tiedostoja, jotka eivät ole identtisiä. Jos sinulla on esimerkiksi ServiceNow-raportteja, joissa yhden otsikon nimi on 'resolved_on' ja toisen 'closed_on', Power Query saattaa aiheuttaa ongelmia, koska se vaatii yhdenmukaisen rakenteen . Jos esimerkkitiedosto ei täsmälleen vastaa muita, työkalu saattaa jättää tiedot pois yhteensopimattomista tiedostoista tai aiheuttaa tuontivirheitä.
Tapauksissa, joissa rakenne on täysin kaoottinen tai datamäärä on valtava (yli miljoona Excel-riviä), on olemassa muita vaihtoehtoja. Jotkut käyttäjät valitsevat VBA-skriptejä CSV-rajapintojen avulla , jotka mahdollistavat tiedostojen yhdistämisen ilman otsikoiden toistamista riippumatta siitä, vaihteleeko kenttien tai tietueiden erotin tiedostosta toiseen. Vaikka Windows Shell on erittäin nopea, se epäonnistuu usein upotettujen rivinvaihtojen käsittelyssä, kun taas vankka VBA-ratkaisu ei ylikuormita RAM-muistia ja pystyy käsittelemään miljoonia tietueita helposti.
Kun tiedot on yhdistetty Power Queryssä, prosessin loppuun saattamiseksi muista määrittää oikea tietotyyppi kullekin sarakkeelle (päivämäärät, valuutta tuloille jne.) ennen lopullisen taulukon lataamista. Voit varmistaa, että kaikki on oikein, suodattamalla Lähteen nimi -sarakkeen varmistaaksesi, että kaikki kansiossa olevat tiedostot on käsitelty onnistuneesti.
Useiden CSV-tiedostojen yhdistämisen automatisointi mahdollistaa siirtymisen työläästä manuaalisesta työstä ammattimaiseen työnkulkuun. Voit hyödyntää Power Queryn joustavuutta tietojen puhdistamiseen tai turvautua VBA:han, kun rakenteet ovat yhteensopimattomia tai määrät ylittävät laskentataulukoiden rajat.