Power Queryn käyttäminen Excelissä: Täydellinen opas

Power Query on tehokas Excelin business intelligence -työkalu, jonka avulla voit tuoda, puhdistaa ja muuttaa tietoja tarpeidesi mukaan. Jos olet joskus käyttänyt tuntikausia tietojen puhdistamiseen (meillä on) tai yrittänyt yhdistää tietoja eri lähteistä (myös meillä), tämä ominaisuus tekee elämästäsi helpompaa.

Paras tapa oppia käyttämään Power Queryä Excelissä on suorittaa joitakin yleisiä tehtäviä, kuten tuoda tietoja ja käyttää Power Query -editoria tuloksena olevien taulukoiden muuntamiseen ja tietojen yhdistämiseen.

Tämän artikkelin avulla pääset alkuun näyttämällä selkeästi . Hallitsemalla Power Queryn voit nostaa työsi seuraavalle tasolle.

Oletko valmis? Tehdään tämä.

Sisällysluettelo

• Kuinka tietää, voitko käyttää Power Queryä
• Tietojen tuominen Power Queryllä
• Power Query -editorin käyttäminen
  • Tietojen esikatselu
  • Ominaisuudet
• Kuinka työskennellä useiden tietolähteiden kanssa
• Tietojen liittäminen Power Queryllä
• Tietojen yhdistäminen Power Queryn kanssa
• 5 tapaa muuntaa tietoja Power Querylla
  • 1. Tietojen laajentaminen ja tiivistäminen
  • 2. Tietojen lajittelu
  • 3. Tietojen muotoilu
  • 4. Sarakkeiden lisääminen ja poistaminen
  • 5. Tietotyyppien muuttaminen
• Työskentely kaavojen ja M-kielen kanssa
  • Formula Bar
  • M-koodin kirjoittaminen ja muokkaaminen
• Mikä on M-kielen syntaksi?
• Tietojen päivittäminen
• Power Queryn integrointi tietomalliin
• Kuinka automatisoida Power Queryn ja VBA:n avulla
• Power Queryn lisäominaisuudet
• Yhteistyö ja jakaminen
• Päätetään tämä loppuun

Kuinka tietää, voitko käyttää Power Queryä

Ennen kuin aloitat Power Queryn käytön, sinun on varmistettava, että sinulla on Excel-versio, jossa tämä ominaisuus on.

Tässä on lyhyt katsaus Power Queryyn versioittain:

• Excel 2010 ja Excel 2013: saatavana apuohjelmana, joka on ladattava ja asennettava.

• Excel 2016: integroitu Exceliin nimellä "Get & Transform".

• Excel 2019: parannettu suorituskyky ja toimivuus.

• Excel 2021 ja Excel for Microsoft 365: integrointi pilvitietolähteisiin.

• Excel for Mac: saatavilla vain Microsoft 365 -versiosta 16.69 ja uudemmista.

Voit varmistaa, että työkalu on integroitu Excel-työkirjaasi (2016 tai uudempi) siirtymällä Tiedot-välilehteen ja etsimällä "Hae ja muunna tiedot" -osio tästä kuvasta:

Jos sinulla on Excel 2010 tai 2013 Windowsissa, voit ladata , asentaa ja ottaa käyttöön Power Query -apuohjelman.

Tietojen tuominen Power Queryllä

Power Query -työkalun avulla voit tuoda ja muodostaa yhteyden monenlaisiin tietolähteisiin:

• tiedostot, mukaan lukien CSV-, XML- ja JSON-muodot

• Web-URL-osoitteet tai API-päätepisteet

• Tietokannat, mukaan lukien Access, SQL Server, Oracle ja useimmat nykyaikaiset tietokannat

• Azure-palvelut, SharePoint-tiedostot ja OData-syötteet

Helpoin tietolähde aloittaa on yksinkertainen CSV-tiedosto, jossa on useita tietorivejä. Luo tekstitiedosto ja lisää nämä neljä etu- ja sukunimeä pilkulla erotettuina:

• Joe, blogit

• Anne, Ryan

• James, Stewart

• Mary, Brown

Muista tallentaa tiedosto .CSV-tunnisteella. Tiedostosi näyttää tältä:

Lataa tiedot Power Queryllä seuraavasti:

1. Siirry Tiedot-välilehteen.

2. Napsauta "Hae tiedostot" -painiketta Hae ja muunna tiedot -välilehdessä.

3. Valitse mallitiedostosi.

Saatat joutua odottamaan muutaman sekunnin, ennen kuin työkalu vetää tiedot esikatseluruutuun. Power Query käyttää melko paljon koneresursseja pienessäkin tuonnissa, joten olkaa kärsivällisiä.

Vihje: sulje muut sovellukset suorituskyvyn nopeuttamiseksi.

Power Query näyttää "Teksti/CSV-tuonti" -ikkunan, jossa on esikatselu tiedoistasi.

Jos tiedot näyttävät oikein, voit jatkaa tietojen lataamista tai muuntamista:

• Lataa-painike lataa tiedot suoraan Exceliin.

• Muunna tiedot -painikkeella voit muokata tietoja ennen niiden lataamista.

Huomasitko, että mallitiedostossa ei ollut otsikkoa? Jos se ladataan suoraan Exceliin, otsikot ovat "Sarake 1" ja "Sarake 2".

Sen sijaan voit vetää tiedot Power Query -editoriin ja tehdä joitain muutoksia. Voit tehdä tämän napsauttamalla "Muunna tiedot" -painiketta.

Power Query Editor avautuu nyt, mikä vie meidät seuraavaan osioon.

Power Query -editorin käyttäminen

Tässä osiossa tutkimme joitain Power Query Editorin kanssa työskentelyn olennaisia ​​näkökohtia.

Tietojen esikatselu

Tietojen esikatselualueella voit tarkastella tuotuja tietojasi ja olla vuorovaikutuksessa niiden kanssa. Voit suodattaa rivejä, muokata sarakkeiden tietotyyppejä ja muokata tietoja tarpeidesi mukaan.

Kun olet valmis, Excel lataa muunnetut tiedot laskentataulukkoon.

Mallitiedoston avulla voit muokata sarakkeiden nimiä suoraan tässä ruudussa.

Tehdä niin:

1. Napsauta sarakkeen otsikkoa hiiren kakkospainikkeella.

2. Napsauta "Nimeä uudelleen" avattavasta valikosta.

3. Kirjoita haluamasi nimet otsikkoruutuihin.

Kun teet muutoksia, esikatseluruudun oikealla puolella oleva "Sovelletut vaiheet" -osio päivittyy vastaamaan jokaista muutosta, jolloin voit seurata ja muokata tietojenkäsittelyvaiheita tarpeen mukaan.

Ominaisuudet

Ominaisuuksien asettaminen kyselyillesi on välttämätöntä työn organisoinnin ja seurannan kannalta.

Kyselyasetukset-ruutu on Power Query -ikkunan oikealla puolella.

1. Napsauta "Kaikki ominaisuudet" -linkkiä.

2. Muokkaa kyselyn nimeä kuvaavaksi.

3. Lisää kuvaus saadaksesi lisätietoja.

On helppo unohtaa, mitä viime viikon kyselyillä oli tarkoitus tehdä. Tässä tapauksessa kyselyn nimi on muutettu ja siihen on lisätty kuvaus, joka kertoo kyselyn tarkoituksen.

Jos haluat lisätietoja käytettävissä olevista vaihtoehdoista, katso tämä .

Kuinka työskennellä useiden tietolähteiden kanssa

Power Query Editor tarjoaa kaksi ensisijaista menetelmää useista lähteistä peräisin olevien tietojen yhdistämiseen: liittäminen ja yhdistäminen.

Liittäminen yhdistää taulukot pystysuunnassa pinoamalla ne päällekkäin. Yhdistäminen mahdollistaa taulukoiden yhdistämisen yleisten sarakkeiden perusteella.

Jos haluat nähdä, miten tämä toimii, luo toinen pieni CSV-tiedosto, jonka rakenne on sama kuin ensimmäinen. Tällä kertaa laita tiedostoon kaksi nimeä (etu- ja sukunimi):

• Joe, blogit

• Laura, Cane

Huomaa, että yksi näistä nimistä toistuu edellisestä tiedostosta.

Noudata nyt näitä ohjeita vetääksesi tiedot Microsoft Excel -työkirjaasi.

1. Sulje Power Query Editor -ikkuna ja tallenna muutokset.

2. Lisää uusi laskentataulukko.

3. Tuo toinen tiedosto noudattamalla edellisen osan vaiheita.

4. Napsauta Muunna-painiketta.

5. Nimeä sarakeotsikot uudelleen muotoiksi "Etunimi" ja "Sukunimi".

Excel-laskentataulukon oikeanpuoleisessa ruudussa näkyvät nykyiset Kyselyt ja yhteydet. Sinun pitäisi nyt nähdä kaksi kyselyä ruudussa.

Tietojen liittäminen Power Queryllä

Tässä osiossa liität toisen CSV-mallitiedoston ensimmäiseen.

Liitä tiedot seuraavasti:

1. Napsauta hiiren kakkospainikkeella ensimmäistä taulukkoa (asiakkaiden nimet) Kyselyt ja yhteydet -ruudussa.

2. Valitse "Muokkaa" avataksesi taulukon Power Query -editorissa.

3. Napsauta Yhdistä-osiossa Liitä kyselyt.

4. Valitse toinen taulukko lisättävien taulukkojen pudotusvalikosta.

Tuloksena on kaksi ylimääräistä riviä ensimmäiseen taulukkoon.

Huomaa, että yksi rivi toistuu – näin liittämistoiminto toimii. Se lisää tiedot taulukosta toiseen riippumatta siitä, onko data jo olemassa.

Voit poistaa päällekkäiset tiedot Power Query Editorissa seuraavasti:

1. Napsauta "Etunimi" -sarakkeen otsikkoa.

2. Pidä "Ctrl"-näppäintä painettuna (tai "Cmd"-näppäintä Macissa).

3. Napsauta molempia sarakkeiden otsikoita.

4. Napsauta "Poista rivit" Power Query Editor -nauhan Koti-välilehdellä.

5. Valitse avattavasta valikosta "Poista kaksoiskappaleet".

Tietojen yhdistäminen Power Queryn kanssa

Kun yhdistät tietoja, sinun on valittava vastaavat sarakkeet kyselyiden välillä.

Oletetaan, että sinulla on kaksi pilkuilla eroteltua tiedostoa, joista toinen sisältää asiakkaiden puhelinnumerot ja toinen osoitteita. Kaksi tiedostoa näyttävät tältä:

Noudata edellisten osien ohjeita ladataksesi jokainen tiedosto omaan tehokyselyynsä. Sitten voit harjoitella taulukoiden yhdistämistä.

Esimerkkitietojemme tapauksessa molemmat sarakkeet (etunimi ja sukunimi) tulee valita vastaaviksi sarakkeiksi. Valitse useita sarakkeita pitämällä Ctrl- näppäintä painettuna .

Toimi seuraavasti:

1. Napsauta hiiren kakkospainikkeella ensimmäistä taulukkoa Kyselyt ja yhteydet -ruudussa.

2. Valitse "Muokkaa" avataksesi taulukon Power Query -editorissa.

3. Valitse Yhdistä-ryhmästä "Yhdistä kyselyt".

4. Valitse toinen taulukko avattavasta taulukkoluettelosta.

5. Napsauta ensimmäisen taulukon ensimmäistä saraketta.

6. Pidä Ctrl-näppäintä painettuna ja napsauta ensimmäisen taulukon toista saraketta.

7. Pidä Ctrl-näppäintä painettuna ja napsauta molempia sarakkeita toisessa taulukossa.

8. Säilytä oletusliitosoperaattori "Left Outer".

9. Napsauta "OK" ottaaksesi muutokset käyttöön.

"Left Outer" -liitosoperaattori varmistaa, että päällekkäiset tiedot poistetaan.

Power Query Editorissa näet uuden sarakkeen nimeltä "Taulukko" tai "Sisäkkäinen taulukko", joka sisältää yhdistetyt tiedot toisesta kyselystä.

1. Napsauta laajennuskuvaketta (kaksi nuolta) tämän sarakkeen otsikossa.

2. Poista valinta Käytä alkuperäistä saraketta etuliitteenä -valintaruudusta (tämä vältetään pitkät sarakkeiden nimet).

3. Napsauta "OK".

4. Napsauta "Sulje ja lataa".

Yhdistetyssä taulukossa oli neljä saraketta, joissa puhelinnumero- ja osoitetiedot yhdistettiin. Sinulla saattaa olla vielä vähän siivoamista.

Voit esimerkiksi muokata alla olevan kuvan taulukon nimeä (Yhdistä1) ja osoitesaraketta.

5 tapaa muuntaa tietoja Power Querylla

Excel-tiedoston tietojen muuntaminen on helppoa Power Queryn avulla, jonka avulla voit puhdistaa, järjestää ja käsitellä tietojasi tehokkaasti.

Jotkut yleisimmistä tavoista muuttaa tietoja ovat:

• Tietojen laajentaminen ja tiivistäminen

• Tietojen lajittelu

• Tietojen muotoilu

• Sarakkeiden lisääminen ja poistaminen

• Tietotyyppien muuttaminen

1. Tietojen laajentaminen ja tiivistäminen

Tietojen laajentaminen ja tiivistäminen on tärkeää, jotta tietojoukkojasi voidaan hallita paremmin. Power Queryn avulla voit laajentaa tai tiivistää taulukoita järjestetymmän näkymän saamiseksi.

Edellisessä osiossa näytetään, kuinka voit laajentaa tietoja liittämällä tietoja asiaan liittyvistä taulukoista.

Voit myös tiivistää sen sisältämään vain yhteenvetotiedot, jotka perustuvat valitsemaasi aggregaatioon, kuten summaan, määrään tai prosenttiosuuteen.

2. Tietojen lajittelu

Voit lajitella tietoja nopeasti muuttamalla järjestystä nousevien tai laskevien arvojen mukaan.

Napsauta sarakkeen nimen vieressä olevaa alanuolta ja valitse "Lajittele nousevasti" tai "Lajittele laskevaan järjestykseen".

3. Tietojen muotoilu

Power Query Editorin Muunna-välilehti tarjoaa monia vaihtoehtoja.

Esimerkiksi avattavan Muoto-valikon avulla voit muuttaa sarakkeen tai koko taulukon kaikki tiedot isoiksi tai pieniksi kirjaimille.

4. Sarakkeiden lisääminen ja poistaminen

Power Query yksinkertaistaa sarakkeiden lisäämistä, poistamista ja muokkaamista.

Voit poistaa sarakkeen napsauttamalla hiiren kakkospainikkeella ei-toivotun sarakkeen otsikkoa ja valitsemalla "Poista". Tämä poistaa sarakkeen ja kaikki sen tiedot pysyvästi taulukosta.

Lisää uusi sarake valitsemalla olemassa oleva sarake, jossa haluat uuden sarakkeen näkyvän. Käytä sitten "Lisää sarake esimerkeistä" -ominaisuutta luodaksesi mukautetun sarakkeen tai suorittaaksesi laskelmia kahden tai useamman sarakkeen välillä.

5. Tietotyyppien muuttaminen

Työskentely johdonmukaisten ja tarkkojen tietotyyppien kanssa on ratkaisevan tärkeää tarkan analyysin kannalta. Power Queryn avulla voit vaihtaa tietotyyppejä helposti:

1. Napsauta hiiren kakkospainikkeella sarakkeen otsikkoa.

2. Valitse "Muuta tyyppiä".

3. Valitse haluamasi tietotyyppi tarjotusta luettelosta.

Tietotyyppien muuttaminen voi olla erityisen hyödyllistä prosenttitietojen käsittelyssä.

Saatat esimerkiksi joutua muuttamaan desimaalipisteet prosentteina tai muuntaa tekstipohjaiset prosenttiluvut numeerisiksi laskennan ja vertailun helpottamiseksi.

Työskentely kaavojen ja M-kielen kanssa

Kun käytät Power Queryä Excelissä, kohtaat M-kielen, joka on kehittyneiden kaavojen luomisen, tietojen käsittelyn ja tehokkaiden kyselyjen luomisen sydän. Tässä osiossa opit:

• Formula Bar

• M koodi

• M-kielen syntaksin olennaiset elementit

Formula Bar

Excelin Power Query -editorin kaavapalkki on alue, jonne syötät kaavosi ja M-koodisi.

Voit käyttää sitä siirtymällä Power Query -editoriin valitsemalla kyselyn Kyselyt ja yhteydet -ruudusta. Kaavapalkki sijaitsee tietojen esikatseluruudun yläpuolella.

M-koodin kirjoittaminen ja muokkaaminen

Jos haluat luoda monipuolisia ja tehokkaita kyselyitä Excelissä, sinun on kirjoitettava ja muokattava Power Query -kieli, joka tunnetaan nimellä M-koodi. Tässä on vinkkejä alkuun pääsemiseen:

• Käytä Applied Steps -ruutua kirjoittaaksesi vaiheittaisen koodin, mikä helpottaa kyselysi logiikan seurantaa ja muokkaamista.

• Yhdistä M-koodi Excel-taulukkoon, pivot-taulukoihin ja ulkoisiin tietolähteisiin, kuten Azure- ja tekstitiedostoihin, luodaksesi dynaamisia ja automaattisia raportteja.

• Säädä kyselyasetuksia tarkentaaksesi tietojen latausasetuksia ja parantaaksesi suorituskykyä.

Kun tutustut M-koodiin, huomaat, että voit automatisoida ja optimoida monia prosesseja, mikä tekee Excelin tietojen analysoinnista tehokkaampaa ja tehokkaampaa.

Voit tarkastella kyselyn takana olevaa koodia seuraavasti:

1. Avaa kysely Power Query -editorissa.

2. Napsauta Aloitus-välilehdellä "Advanced Editor".

Tämä on yhdistetyn esimerkkitaulukon takana oleva koodi (omasi voi näyttää erilaiselta):

Mikä on M-kielen syntaksi?

M-kielen syntaksin ymmärtäminen on ratkaisevan tärkeää tehokkaiden Power Query -kaavojen luomisessa. Pidä seuraavat seikat mielessä:

• M Kieli on isojen ja pienten kirjainten välinen ero, joten ole varovainen isojen kirjainten kanssa.

• Käytä hakasulkeita [] sarakkeisiin, tietueisiin ja luetteloihin viittaamiseen.

• Jos haluat viitata kyselysi edelliseen vaiheeseen, käytä sen muuttujan nimeä (esim. #”Step Name”).

• Anna kyselykaavan vaihe "in"-käskyllä.

Kun hankit kokemusta -sovelluksesta, löydät uusia tapoja muuttaa ja analysoida tietojasi, jolloin Excel-työkirjoista tulee entistä arvokkaampi.

Katso alla oleva video, on aika ottaa pois hankausharja ja siivota sotkuiset tietosi.

Tietojen päivittäminen

Yksi Power Queryn tärkeimmistä ominaisuuksista on kyky päivittää Power Query -yhteytesi helposti.

Kun tietolähteesi on päivitetty ja tarvitset uusimmat tiedot työkirjaasi, voit päivittää tietoyhteydet Kyselyt ja yhteydet -osiossa.

Jos et näe taulukkoa laskentataulukon oikealla puolella, voit avata sen siirtymällä Tietotaulukkoon ja napsauttamalla "Kyselyt ja yhteydet".

Kun Kyselyt ja yhteydet -ruutu on auki, siinä luetellaan kaikki kyselyt. Toimi seuraavasti:

1. Napsauta hiiren kakkospainikkeella kyselyä, jonka haluat päivittää.

2. Valitse pikavalikosta Päivitä.

Tämä päivittää työkirjasi uusimmilla tiedoilla yhdistetyistä tietolähteistäsi.

Power Queryn integrointi tietomalliin

Excel’s data model is a powerful feature that allows you to create business intelligence tools, like pivot tables, based on data relationships.

The data model is basically a hidden database that holds the imported data and relationships between tables.

It is part of the Power Pivot feature which is in-built into current versions of Excel. You may have to install Power Pivot as an add-in to access it in previous versions).

You can integrate your Power Query data connections with the data model with these steps:

1. Load your data using Power Query as described in previous sections.

2. Instead of loading it directly into the workbook, choose Load To from the Load menu.

3. Select Add this data to the Data Model checkbox in the Import Data dialog box.

How to Automate With Power Query And VBA

With VBA (Visual Basic for Applications) integration, you can create custom data connections and automate data transformation steps in Power Query.

You can leverage your existing VBA skills to build advanced data models by incorporating data from platforms like Microsoft Access, SharePoint, and Analysis Services.

Although Power Query does not have native support for VBA, you can use VBA code to open, refresh, and manipulate Power Query queries indirectly.

This allows you to create powerful, automated data workflows and streamline your data analysis process.

Here is some sample code that refreshes a power query in the current workbook.

:

Dim wb As Workbook, pq As Object

Set wb = ThisWorkbook

Set pq = wb.Queries(“Customer Names”)

 pq.Refresh

Here is the code within an Excel VBA macro:

Power Query Advanced Features

Excel’s Power Query offers a plethora of advanced features to simplify and enhance your data analysis process. By learning how to effectively use these features, you can take your data analysis skills to a whole new level.

The built-in data analysis tools include:

• Conditional columns for applying complex logic to your data transformations.

• Grouping and aggregation functions to summarize your data in a meaningful way.

• Pivot and Unpivot operations to reshape your data for easier analysis.

You’ll find many of these features in the Transform tab in the Power Query Editor.

Additionally, you can connect Power Query to Business Intelligence for more advanced data analysis and visualization options.

Collaboration and Sharing

Sharing your data connections, queries, and transformations with your team members is essential for effective collaboration.

You can share a workbook with queries by saving it to a network drive, SharePoint, or some other shared storage location.

You can also export the M code behind a single query with these steps:

1. Edit the query in the Power Query Editor.

2. Click on “Advanced Editor” in the Home tab.

3. Copy the entire M code in the Advanced Editor window.

4. Open a text editor (e.g., Notepad) and paste the copied M code into the text editor.

5. Save the text file and send it to your colleague.

Let’s Wrap This Up

With an array of features and its user-friendly interface, Power Query is an essential tool for Excel users looking to work effectively with external data.

Tämä arvokas ominaisuus auttaa virtaviivaistamaan raporttien luomista, tietojen puhdistamista ja monimutkaisten analyysien suorittamista tietotaulukoissasi. Käyttämällä

Power Query säästää viime kädessä aikaa ja lisää tehokkuutta Excel-tiedostojen käsittelyssä.