Kuinka käyttää Power Queryä Excelissä?
Excel Power Queryä käytetään tietolähteiden hakemiseen, yhteyksien luomiseen tietolähteisiin ja tietojen muokkaamiseen analysointivaatimuksemme mukaan. Kun olemme muokanneet tietoja tarpeidemme mukaan, voimme myös jakaa havaintomme ja luoda erilaisia raportteja käyttämällä enemmän kyselyjä.
Askeleet
Pohjimmiltaan on 4 vaihetta, ja näiden 4 vaiheen järjestys Power Queryssä on seuraava:
- Yhdistä: Yhdistämme ensin dataan, joka voi olla missä tahansa, pilvessä, palvelussa tai paikallisesti.
- Muunna: Toinen vaihe olisi muuttaa tietojen muotoa käyttäjän vaatimusten mukaisesti.
- Yhdistä: Tässä vaiheessa suoritamme muutos- ja yhdistämisvaiheet ja yhdistämme molempien lähteiden tiedot yhdistetyn raportin tuottamiseksi.
- Hallinta: Tämä yhdistää ja lisää kyselyn sarakkeet vastaaviin sarakkeisiin työkirjan muissa kyselyissä.
Excel Power Queryssä on monia erittäin tehokkaita ominaisuuksia.
Oletetaan, että meillä on viimeisten 15 vuoden ostotiedot 180 tiedostossa. Nyt organisaation johto edellyttäisi lukujen yhdistämistä ennen niiden analysointia. Johto voi käyttää mitä tahansa seuraavista tavoista:
- He avaavat kaikki tiedostot ja kopioivat ja liittävät ne yhteen tiedostoon.
- Toisaalta he voivat käyttää viisasta ratkaisua, joka on kaavojen soveltaminen, koska se on altis virheille.
Minkä menetelmän tahansa he valitsevat, se sisältää paljon manuaalista työtä, ja muutaman kuukauden kuluttua uusia myyntitietoja olisi lisätty kestolle. Heidän on tehtävä sama harjoitus uudelleen.
Power Query voi kuitenkin auttaa heitä olemaan tekemättä tätä tylsiä ja toistuvia töitä. Ymmärretään tämä Excel-tehokysely esimerkillä.
Esimerkki
Oletetaan, että meillä on tekstitiedostoja myyntitietoja sisältävässä kansiossa, ja haluamme saada nämä tiedot Excel-tiedostoihimme.
Kuten voimme nähdä alla olevasta kuvasta, että kansiossa on kahden tyyppisiä tiedostoja, mutta haluamme saada tiedot vain tekstitiedostoista Excel-tiedostoon.
Voit tehdä samoin vaiheet:
Vaihe 1: Ensinnäkin meidän on hankittava tiedot Power Query -palveluun, jotta voimme tehdä tarvittavat muutokset tietoihin tuodaksemme ne Excel-tiedostoon.
Tekemään samoin, me valitsemme ”kansiosta” vaihtoehto ”From File” valikosta jälkeen klikkaamalla komento ”Get Data” alkaen ”Get & Transform” ryhmä ”Data” välilehti.
Vaihe 2: Valitse kansion sijainti selaamalla.
Napsauta OK
Vaihe 3: Näyttöön tulee valintaikkuna, joka sisältää luettelon kaikista valitun kansion tiedostoista, sarakeotsikoilla 'Sisältö', 'Nimi', 'Laajennus', 'Pääsypäivä', 'Muokkauspäivä', 'Luontipäivä'. 'Attribuutit' ja 'Kansion polku'.
On 3 vaihtoehtoa, ts. Yhdistä , Lataa ja Muunna tiedot .
- Yhdistä : Tätä vaihtoehtoa käytetään siirtymään näyttöön, jossa voimme valita yhdistettävät tiedot. Muokkausvaihe ohitetaan tälle asetukselle, eikä se anna meille mahdollisuutta yhdistää tiedostoja. Yhdistä-toiminto vie kaikki kansion tiedostot konsolidoitavaksi, mikä voi johtaa virheisiin.
- Lataa: Tämä vaihtoehto vain lataa taulukon, kuten yllä olevassa kuvassa näkyy, Excel-laskentataulukkoon tiedostojen todellisten tietojen sijaan.
- Muunna tiedot: Toisin kuin Yhdistä- komento, jos käytämme tätä komentoa, voimme valita yhdistettävät tiedostot, ts. Voimme yhdistää vain yhden tyyppisen tiedoston (sama laajennus).
Kuten meidän tapauksessamme, haluamme yhdistää vain tekstitiedostot (.txt); valitsemme "Transform Data" -komennon.
Näemme "Sovelletut vaiheet" -ikkunan oikealla puolella. Toistaiseksi on tehty vain yksi vaihe, joka on ottaa tiedostotiedot kansiosta.
Vaihe 4: On sarake nimeltä "Laajennus", josta voimme nähdä, että sarakkeen arvot kirjoitetaan molemmissa tapauksissa, eli isot ja pienet kirjaimet.
Meidän on kuitenkin muunnettava kaikki arvot pieniksi kirjaimiksi, koska suodatin erottaa molemmat. Tekemään samoin, meidän täytyy valita sarakkeen ja valitsemalla ”pieni” alkaen ”Format” komento valikosta.
Vaihe 5: Suodatamme tiedot tekstitiedostojen Laajennus- sarakkeella.
Vaihe 6: Meidän on yhdistettävä molempien tekstitiedostojen tiedot nyt käyttämällä ensimmäistä saraketta Sisältö. Napsautamme sarakkeen nimen oikealla puolella olevaa kuvaketta.
Vaihe 7: Valintaikkuna, jonka otsikko on 'Yhdistä tiedostot' , avautuu, jossa meidän on valittava erotin välilehdeksi tekstitiedostoille (tiedostot, joiden tunniste on .txt ') ja voimme valita tietotyypin havaitsemisen perustan. Napsauta sitten OK.
Napsautettuamme OK-painiketta, saamme tekstitiedostojen yhdistetyt tiedot Power Query -ikkunassa.
Voimme muuttaa sarakkeiden tietotyyppiä tarpeen mukaan. Sillä 'Tulot' sarakkeeseen, muutamme tietotyypin 'valuutta.'
Voimme nähdä tietoihin sovelletut vaiheet käyttämällä virtakyselyä ikkunan oikealla puolella.
Suoritettuamme kaikki tarvittavat muutokset tietoihin voimme ladata tiedot Excel-laskentataulukkoon käyttämällä Koti- välilehden Sulje- ryhmän Sulje ja lataa -komentoa .
Meidän on valittava, haluatko ladata tiedot taulukkona vai yhteytenä. Napsauta sitten OK.
Nyt voimme nähdä tiedot taulukkona laskentataulukossa.
Ja oikealla puolella oleva 'Workbook Queries' -ruutu, jota voimme käyttää kyselyjen muokkaamiseen, kopioimiseen, yhdistämiseen, liittämiseen ja moniin muihin tarkoituksiin.
Excel Power Query on erittäin hyödyllinen, koska voimme nähdä, että 601612 riviä on ladattu muutamassa minuutissa.
Muistettavaa
- Power Query ei muuta alkuperäisiä lähdetietoja. Alkuperäisten lähdetietojen muuttamisen sijaan se tallentaa jokaisen vaiheen, jonka käyttäjä on suorittanut yhdistäessään tai muunnellessaan tietoja, ja kun käyttäjä on viimeistellyt tietojen muotoilun, se ottaa tarkennetun tietojoukon ja tuo sen työkirjaan.
- Power Query erottaa isot ja pienet kirjaimet.
- Konsolidoidessamme tiedostoja määritettyyn kansioon, meidän on varmistettava, että käyttämällä 'Laajennus' -saraketta, ja meidän on suljettava pois väliaikaiset tiedostot (joiden tunniste on .tmp ja näiden tiedostojen nimi alkaa '~' -merkillä) Power Query voi myös tuoda nämä tiedostot.
