Power Query: the 2021 Definitive Beginners’ Guide

Hei, tervetuloa aloittelijoille’ guide to Power Query by UpSlide.
Oletko koskaan miettinyt, mikä Voimakysely on? Miten se toimii? Jos olet, olet oikeassa paikassa!
Pauline, Voimakyselyasiantuntijamme Finance 3.1: stä, antoi meille kaikki parhaat vinkkinsä ja neuvonsa tämän artikkelin kirjoittamiseen.
täällä opit ohjelmiston perusteet ja pääset jopa vähän harjoittelemaan! Oletko valmis?

jos et ehdi lukea sitä nyt, Lataa PDF-versio ja nauti siitä myöhemmin!

mikä on Tehokysely? Milloin minun pitäisi käyttää sitä?

Power Query on Excel-lisäosa, jonka avulla sen käyttäjät voivat tuoda, puhdistaa ja konsolidoida tietoja. Voit käyttää Power Query-kyselyä, kun:

• suurten tietomäärien käsittely (jossa Excel on rajoitettu miljoonaan riviin)
• Tietojen tuominen eri lähteistä (esim. Excel, CSV, SharePoint, SQL ja Salesforce)
• useiden taulukoiden, tiedostojen tai jopa kansioiden käsittely, puhdistaminen ja yhdistäminen!

Power Query on käyttäjäystävällinen ja tehokas työkalu myös aloittelijoille, sillä et tarvitse koodauskokemusta päästäksesi alkuun. Se ei myöskään muuta alkuperäistä tietolähdettä. Lopputulos on kuin Exceliin tuotu tilannekuva muokatusta datasta. Mitä hienoa siinä on? No, koska lähdetiedot ei ole suoraan tuotu, se ei tee Excel-tiedoston raskaampaa (eli se ei hidasta tietokonetta!).

Yhteenvetona voidaan todeta, että Power Query on tietojen analysointityökalu, joka tekee ETL: n (Extract-Transform-Load) prosesseista sekä yksinkertaisempia että tehokkaampia.

Mitä eroa on Tehokyselyn ja Tehokyselyn välillä?

Power Pivot liitetään usein Power-kyselyyn, koska se on Microsoft Office-lisäosa, joka helpottaa tietojen analysointia. On kuitenkin kiinnostavaa, että nämä kaksi välinettä voivat toimia rinnakkain! Power Pivot on yksinkertaisesti seuraava vaihe tietojen muuntaminen: sen avulla käyttäjät voivat luoda tietomalleja aiemmin Power Query-kyselyllä puhdistetuista taulukoista.

vaikka Power Pivot on samankaltainen, sitä käytetään enemmän tietojen analysointiin, aikaan liittyviin laskelmiin ja linkkien luomiseen. Yksinkertaisesti sanottu:

• Power kyselyn avulla voit automaattisesti konsolidoida ja käsitellä tiedostoja (eli standardointi raportointiprosessit tai konsolidointi useita tiedostoja samassa muodossa).
• Power Pivot mahdollistaa useiden miljoonien linjojen tietokantojen analysoinnin keräämällä yhdistettyjä tietoja yrityksesi asettamien sääntöjen mukaisesti.

Mitä eroa on Tehokyselyllä ja Tehobi: llä?

Power-kyselyn taustalla oleva Kyselytekniikka sisältyy myös Power BI: hen.

Power BI-työpöytäohjelmiston käyttöliittymä mahdollistaa kolmen teknologian (Power Query, Power Pivot ja Power View) hyödyntämisen. Power BI, voit tehdä kaikki vaiheet tietojen analysointiketjun, talteen tietoja luoda grafiikka interaktiivinen kojelauta, että voit jakaa ja analysoida.

> lue artikkelimme Kuinka viedä Power BI Exceliin

miksi Power Query on loistava vaihtoehto Excel VBA: lle?

Tehokysely voi helposti korvata VBA: n (Visual Basic for Applications), koska sen avulla voit:

• käsittele pöytäsi yksinkertaisesti nappeja klikkaamalla. Koodaustaitoja ei tarvita!
• visualisoi operaatiosi vaihe vaiheelta ajamatta yhtä makroa.
• voit helposti muokata eri vaiheiden askellusjärjestystä vetämällä ja pudottamalla! Myös, voit lisätä ja poistaa tarpeettomia vaiheita yhdellä napsautuksella.

(katso osa 2-Jakso 3: Miten pääset alkuun Tehokyselyllä?)

kuitenkin Power Query ei voi korvata VBA: ta, kun se tulee vuorovaikutuksessa Excelin käyttöliittymän kanssa, automatisoimalla prosesseja, luomalla uusia tiedostoja, lähettämällä sähköposteja jne.

onko Microsoftin Power-kysely ilmainen? Onko Macille Power-kysely?

Kyllä. Power Query on Excelin ilmainen laajennus. Tämä on toinen lisäosan tärkein etu.
(lue seuraava kysymys, miten saat sen Excel-työpöydällesi)

Power Query for Mac? Kyllä, suuri uutinen: kyselyn editori on nyt yhteensopiva Excel for Mac! Mutta integrointi on melko rajallinen tällä hetkellä, koska voit vain muuttaa tiedoston polku ja päivittää olemassa olevia kyselyjä.

Osa 2: Aloita Voimakyselyllä!

Kuinka ladata Tehokysely?

Tehokysely on ollut saatavilla Exceliin vuodesta 2010.

näin lisäät Tehokyselyn Excel-versiosi mukaan*:

• hyviä uutisia, Sinun ei tarvitse ladata Power-kyselyä Excel 2019: ään, 2016: een tai Office 365: een. Se löytyy ”Data” – nauhasta painikkeen”Get & Transform” alta.
• Excel 2013 ja 2010, sinun täytyy ladata Power Query-lisäosa. Se ilmestyy uuteen erilliseen välilehteen nimeltä ”Power Query” (Excel 2010: ssä sinun on oltava ammattimainen Plus Software Assurance-lisenssillä).

*näin tarkistat Excel-versiosi:

Siirry tiedostoon > tili > tietoja Excelistä. Sitten ikkuna ilmestyy. Lue suluissa olevat kaksi ensimmäistä numeroa. Alla olevassa esimerkissä Excel-versio on 2016.

lisätietoja, tässä toinen yksityiskohtainen artikkeli Power Query-kyselyn asentamisesta ja aktivoinnista Excelissä.

miten aktivoin Power-kyselyn Excelissä?

Excel-Versiosta riippuen (katso yllä kysymys: Missä on Power Query Excelissä?) sinun tarvitsee vain napsauttaa painiketta ”Get data” ”Get & Transform” – osiossa ”Data” – välilehdessä Excel 2016 ja sen jälkeen. Excel versiot ennen 2013 mukana, yksinkertaisesti mennä Power kysely nauha.

huomaa, että kun syötät Power Query-rajapinnan, et voi enää olla vuorovaikutuksessa Excelin kanssa. Jos haluat palata Exceliin, sinun on suljettava Power-kyselyeditori.

miten Tehokysely toimii?

Excelissä pääset käsiksi seuraaviin toiminnallisuuksiin vain, kun klikkaat ”kyselyt & yhteydet”:

Data ribbon: tietolähteiden etsimiseen (”Power Query” ribbon Excel 2010 ja 2013).

kyselyt & Yhteyspaneeli: nähdäksesi nopeasti kaikki tekemäsi yhteydet (kyselyt).

käynnistettyäsi Tehokyselyn, laskeudut Tehokyselyn muokkaimeen, joka koostuu 4 erillisestä elementistä:

Main ribbon: voit luoda kyselyitä tyhjästä sen painikkeiden ansiosta (jos tunnet m-kielen, voit myös käyttää Advanced editoria säätääksesi niitä manuaalisesti).

kyselyt: seurata kaikkia yhteyksiä tietolähteisiin ja virtoihin.

datataulukon esikatselu: voit visualisoida datatuloksesi tietyn vaiheen jälkeen.

kyselyn Asetukset (sovelletut vaiheet): voit selata kaikkia tekemiäsi vaiheita. Power Query tallentaa automaattisesti kaikki tehdyt muutokset.

• jaksossa 4, opit, miten Power kysely pohjimmiltaan toimii muuttamalla datataulukon.
• jaksossa 5 asia muuttuu entistä kiinnostavammaksi, kun selviää, miten eri lähteistä saatuja tietoja voi edetä ja yhdistää kyselyitä.

miten pääset alkuun Tehokyselyllä?

Kuvittele seuraava tilanne:

olet tietokoneita ja älypuhelimia myyvän teknologiayrityksen varainhoidon valvoja. Tällä hetkellä toimit 3 maassa: Ranskassa, Isossa-Britanniassa ja Kiinassa. Joka kuukausi tuotat managerillesi konsolidoidun raportin, jossa on KPI: t, kuten kokonaistulot, tulot maittain tai tuotot tuotteittain. Sitä varten jokainen maajohtaja – Dennis, Douglas ja Karen-lähettää Sinulle tiedoston.

ongelma: jokainen tiedosto listaa myynnit maan omalla nimityksellä. On liian aikaavievää muuttaa ne euroiksi ja koota ne sitten lopulliseen kertomukseen.

mitä jos voisit automatisoida prosessin ja laskea raporttisi muutamassa minuutissa käyttämällä vain Tehokyselyä?

eikö se olisi mahtavaa? No, Power kysely, voit! Aloitetaan laatimalla ensimmäinen taulukko 🙂

ensin tarvitaan dataa. Lataa Power Query-esimerkkimme ja tallenna se paikalliselle palvelimelle.

noudata sitten näitä helppoja ohjeita:

• Pura tiedosto ja tallenna se paikalliseen palvelimeen.
• avaa nyt uusi laskentataulukko Excelissä ja siirry Data-välilehteen > Hae tiedot > tiedostosta > työkirjasta, kuten alla on esitetty.

(jos sinulla on Excel-versio 2013 tai 2010, siirry vain Power Query-välilehteen)

• Siirry polulle, johon tallennit juuri lataamasi viilaamattomat tiedostot ja valitse Excel-tiedosto nimeltä ”Exchange Rates”.

• Napsauta Transform Data.

olet saattanut myös huomata, että joitakin sovellettuja vaiheita on jo ilmestynyt kyselyn Asetusruudussa. Nämä tehdään automaattisesti.

nyt harjoitellaan vähän, alkaen datataulukon puhdistamisesta:

• Poista 3 ylintä riviä, siirry kohtaan poista rivit > Poista ylimmät rivit ja kirjoita ”3”. Näet heti muutoksen datataulukon esikatselussa sekä sovelletuissa vaiheissa (kyselyn Asetukset).

• Poista tyhjät rivit: poista rivit > Poista tyhjät rivit (kuten alla).

• Mainosta ensimmäistä riviä otsakkeena: napsauta ”Käytä ensimmäistä riviä Otsakkeina”. Kuten Olet ehkä huomannut, Power Query lisäsi automaattisesti askeleen joidenkin tietojen tyypin muuttamiseksi (ensimmäisen sarakkeen arvot on nyt muotoiltu päivämääriksi).

• Poista tyhjät sarakkeet: valitse ”Valitse sarakkeet” ja poista valinta sarakkeet 7, 8, 9 ja 10.

hyvin menee! Kolme askelta vielä ja olet valmis!

• sarakkeita ei palauteta: Valitse” valuuttakurssi ”- sarake ja siirry muunto-välilehdessä Unpivot-sarakkeisiin > Unpivot-sarakkeisiin (voit myös valita viisi muuta saraketta ja valita”Unpivot-sarakkeet”).

• nimeä sarakkeet uudelleen: klikkaa otsikkoa soluja, kirjoita ” päivämäärä ”ensimmäisessä,” valuutta ”toisessa ja” korko ” kolmannessa.

• Käytä suodatinta: kuten Excelissä, klikkaa ”Date”-sarakkeen pudotusvalikkoa ja valitse ”Dec-18”.

• napsauta Koti-välilehdessä Sulje ja lataa, Jos haluat ladata finaalipöydän uudelle arkille.

hyvin tehty! Muokkasit ensimmäisen datataulukkosi.

mennään vielä pidemmälle Tehokyselyllä

(uudelleen)kuvitelkaa seuraava tilanne:

Tehokyselytaitojenne ansiosta olet nyt teknologiayrityksen vanhempi talousvalvoja. Nyt, sinun täytyy konsolidoida joulukuun 2018 tiedot, jotka maajohtajat lähettivät sinulle.

ongelma: jokainen tiedosto listaa myynnit maan omalla nimityksellä. Niiden muuttaminen euroiksi on aikaa vievää, ja vielä enemmän niiden yhdistäminen lopulliseen kertomukseen.

mitä jos voisit automatisoida prosessin ja laskea raporttisi muutamassa minuutissa vain Power-kyselyn avulla?

nyt katsotaan, miten eri tietolähteet saadaan yhdistettyä ja yhdistettyä edelliseen osioon laatimaamme taulukkoon.

tuodaan ensin koko kansio sisältäen kolme eri lähdetiedostoa:

• jos olet yhä Tehokyselyssä, siirry uuteen lähdekoodiin > tiedosto > kansioon.

(jos palaat Exceliin, Hae tiedot > kansiosta)

• Valitse kansio ”Sales_201812” olet aiemmin ladannut ja tallentanut. Sen jälkeen klikkaa OK.

uuden ikkunan pitäisi ilmestyä.

sitten sinun täytyy yhdistää kyselyt:

• valitse” Combine & Edit ” pudotusvalikosta (uuden ikkunan alalaidasta).

• valitse esimerkkitaulukko ”Dec-18” (huomaa, että kaikki arkit on nimettävä samalla tavalla).

• voit tarkistaa ensimmäisessä sarakkeessa, että kaikki lähteet ovat mukana (klikkaa pudotusvalikosta), ja valitse sitten OK.

olet nyt Tehokyselyn muokkaimessa.

• Siirry muunto-välilehteen > korvaa arvot.

• korvaa nyt”€ ”sanalla”EUR”.

• Siirry Koti-välilehteen > Yhdistä kyselyt.

uusi ikkuna ilmestyy:

1. Valitse sarake ”valuutta”.
2. valitse” Exchange Rates ” – tiedosto pudotusvalikosta.
3. Valitse sarake ”valuutta”.
4. klikkaa OK.

• nyt, sinun täytyy näyttää valuuttakurssitiedot, jotka juuri hait:
  1. valitse” valuuttakurssi ” sarake.
  2. Napsauta Laajenna-painiketta.
  3. tarkista vain ”korko”.
  4. Poista ”Use original …”.
  5. klikkaa OK.

Onneksi olkoon! Latasit juuri toisen tietolähteen, yhdistit kyselyt ja hait vastaavan valuuttakurssin. Ei enää VLOOKUPIA tai INDEX+matchia!

loppuun luomme” yksikköhinta EUR ” – sarakkeen, joka muuntaa kaikki hinnat euroiksi:

• siirry kohtaan Lisää sarakkeen välilehti > mukautettu sarake.

• uusi ikkuna ilmestyy. Kirjoitetaan hinnan kaava euroina:
  1. nimeä se ”yksikköhinta EUR”.
  2. kaksoisnapsauta kohtaa” yksikköhinta paikallinen valuutta ” (tai valitse se ja valitse sitten lisää).
  3. Tyyppi”/”.
  4. Tuplaklikkaa ”korko”.
  5. klikkaa OK.

• kun sarake ilmestyy, klikkaa sen tyyppisymbolia (juuri ennen nimeä) ja valitse ”desimaaliluku”.

meidän on tehtävä sama tuottaaksemme” Revenue ” – sarakkeen:

• siirry kohtaan Lisää sarakkeen välilehti > mukautettu sarake.
• uusi ikkuna ilmestyy. Kirjoitetaan Tulokaava:
  1. nimeä se ”tuloksi”.
  2. kaksoisnapsauta ”yksikköhinta EUR” (tai valitse se ja napsauta sitten lisää).
  3. Tyyppi”*”.
  4. Tuplaklikkaa ”määrä”.
  5. klikkaa OK.

• Vaihda jälleen saraketyyppi ”desimaalilukuun”.

loppuun, viedään se uuteen datataulukkoon Excelissä!

• mene kotiin välilehteen > Sulje & kuormitus > Sulje & kuormitus.

Nyt voit hyödyntää Excel-ominaisuuksia tietojen analysointiin, kääntötaulukon tekemiseen jne.

Vau! Jos selvisit loppuun asti, Onneksi olkoon! Nyt tiedät, miten Power Query toimii.

nyt on sinun vuorosi!

Haluan luovuttaa sen sinulle: ymmärsitkö kaikki edellä mainitut kysymykset Voimakyselystä?
latasitko opetustiedostomme ja noudatit yllä olevia ohjeita kokeillaksesi sitä itse?
kerro meille jättämällä lyhyt kommentti alle juuri nyt!

lisätietoja Tehokyselystä: