Slik bruker du Power Query i Excel: Den komplette veiledningen

Power Query er et kraftig business intelligence-verktøy i Excel som lar deg importere, rense og transformere data i henhold til dine behov. Hvis du noen gang har brukt timer på å rydde opp i data (vi har) eller slitt med å slå sammen data fra ulike kilder (vi har også), vil denne funksjonen gjøre livet ditt enklere.

Den beste måten å lære hvordan du bruker Power Query i Excel er å utføre noen vanlige oppgaver som å importere data og bruke Power Query Editor til å transformere de resulterende tabellene og kombinere dataene.

Denne artikkelen kommer deg i gang ved å vise klar . Ved å mestre Power Query kan du heve arbeidet ditt til neste nivå.

Er du klar? La oss gjøre dette.

Innholdsfortegnelse

• Hvordan vite om du har tilgang til Power Query
• Slik importerer du data med Power Query
• Slik bruker du Power Query Editor
  • Forhåndsvisning av data
  • Egenskaper
• Hvordan jobbe med flere datakilder
• Slik legger du til data med Power Query
• Slik slår du sammen data med Power Query
• 5 måter å transformere data med Power Query
  • 1. Utvide og kondensere data
  • 2. Sortering av data
  • 3. Formatering av data
  • 4. Legge til og fjerne kolonner
  • 5. Endre datatyper
• Arbeid med formler og M Language
  • Formel Bar
  • Skrive og endre M-kode
• Hva er M Language Syntax?
• Slik oppdaterer du datatilkoblinger
• Hvordan integrere Power Query med datamodellen
• Hvordan automatisere med Power Query og VBA
• Power Query Avanserte funksjoner
• Samarbeid og deling
• La oss avslutte dette

Hvordan vite om du har tilgang til Power Query

Før du begynner å bruke Power Query, må du være sikker på at du har en versjon av Excel som har funksjonen.

Her er en kort oversikt over Power Query etter versjon:

• Excel 2010 og Excel 2013: tilgjengelig som et tillegg som må lastes ned og installeres.

• Excel 2016: integrert i Excel som "Get & Transform".

• Excel 2019: forbedret ytelse og funksjonalitet.

• Excel 2021 og Excel for Microsoft 365: integrasjon med skydatakilder.

• Excel for Mac: kun tilgjengelig fra Microsoft 365 versjon 16.69 og nyere.

For å bekrefte at verktøyet er integrert i Excel-arbeidsboken din (2016 eller senere), gå til Data-fanen og se etter "Hent og transformer data"-delen i dette bildet:

Hvis du har Excel 2010 eller 2013 på Windows, kan du laste ned , installere og aktivere Power Query-tillegget.

Slik importerer du data med Power Query

Power Query-verktøyet lar deg importere og koble til et bredt spekter av datakilder:

• Filer, inkludert CSV-, XML- og JSON-formater

• Nettadresser eller API-endepunkter

• Databaser, inkludert Access, SQL Server, Oracle og de fleste moderne databaser

• Azure-tjenester, SharePoint-filer og OData-feeder

Den enkleste datakilden å starte med er en enkel CSV-fil med flere linjer med data. Lag en tekstfil og legg til disse fire for- og etternavnene, atskilt med komma:

• Joe, blogger

• Anne, Ryan

• James, Stewart

• Mary, Brown

Sørg for å lagre filen med filtypen .CSV. Filen din vil se slik ut:

Følg disse trinnene for å laste inn dataene med Power Query:

1. Gå til fanen Data.

2. Klikk på "Hent filer"-knappen i kategorien Hent og transformer data.

3. Velg eksempelfilen din.

Du må kanskje vente noen sekunder før verktøyet henter dataene til forhåndsvisningsruten. Power Query bruker ganske mye maskinressurser selv for en liten import, så vær tålmodige folkens.

Tips: slå av andre programmer for å øke ytelsen.

Power Query viser vinduet "Tekst/CSV-import" med en forhåndsvisning av dataene dine.

Hvis dataene ser riktige ut, kan du fortsette å laste eller transformere dataene:

• Last inn-knappen vil laste data direkte inn i Excel.

• Transform Data-knappen lar deg endre dataene før du laster dem.

La du merke til at eksempelfilen ikke hadde en overskrift? Hvis den lastes rett inn i Excel, vil overskriftene være "Kolonne 1" og "Kolonne 2".

I stedet kan du trekke dataene inn i Power Query-redigeringsprogrammet og gjøre noen endringer. For å gjøre det, klikk på "Transformer data"-knappen.

Power Query Editor åpnes nå, som bringer oss til neste seksjon.

Slik bruker du Power Query Editor

I denne delen skal vi utforske noen viktige aspekter ved å jobbe med Power Query Editor.

Forhåndsvisning av data

I dataforhåndsvisningsområdet kan du se og samhandle med de importerte dataene dine. Du kan filtrere rader, endre kolonnedatatyper og omforme dataene for å passe dine behov.

Når du er ferdig, vil Excel laste de transformerte dataene inn i et regneark.

Ved å bruke eksempelfilen kan du redigere kolonnenavnene direkte i denne ruten.

Å gjøre slik:

1. Høyreklikk på kolonneoverskriften.

2. Klikk på "Gi nytt navn" i rullegardinmenyen.

3. Skriv inn de foretrukne navnene i overskriftsboksene.

Når du gjør endringer, oppdateres delen "Anvendte trinn" til høyre i forhåndsvisningsruten for å gjenspeile hver transformasjon, slik at du kan spore og endre databehandlingstrinnene dine etter behov.

Egenskaper

Å angi egenskaper for søkene dine er avgjørende for å organisere og spore arbeidet ditt.

Ruten Spørringsinnstillinger er til høyre for Power Query-vinduet.

1. Klikk på koblingen "Alle egenskaper".

2. Rediger navnet på søket for å være beskrivende.

3. Legg til en beskrivelse for å gi mer informasjon.

Det er lett å glemme hva forrige ukes søk ble satt opp til å gjøre. I dette tilfellet er spørringsnavnet endret og en beskrivelse er lagt til som gir hensikten med spørringen.

Hvis du vil lære mer om de tilgjengelige alternativene, sjekk ut denne .

Hvordan jobbe med flere datakilder

Power Query Editor tilbyr to primære metoder for å kombinere data fra flere kilder: legge til og slå sammen.

Ved å legge sammen tabeller vertikalt, stables de oppå hverandre. Sammenslåing lar deg slå sammen tabeller basert på vanlige kolonner.

For å se hvordan dette fungerer, lag en annen liten CSV-fil med samme struktur som den første. Denne gangen legger du inn to navn (første og siste) i filen:

• Joe, blogger

• Laura, Cane

Legg merke til at ett av disse navnene gjentas fra forrige fil.

Følg nå disse trinnene for å trekke dataene inn i Microsoft Excel-arbeidsboken.

1. Lukk Power Query Editor-vinduet og lagre endringene.

2. Legg til et nytt regneark.

3. Følg trinnene i forrige del for å importere den andre filen.

4. Klikk på Transformer-knappen.

5. Gi nytt navn til kolonneoverskriftene til "Fornavn" og "Etternavn".

Den høyre ruten i Excel-regnearket viser gjeldende spørringer og tilkoblinger. Du skal nå se to spørringer i ruten.

Slik legger du til data med Power Query

I denne delen vil du legge til den andre eksempel-CSV-filen til den første.

Følg disse trinnene for å legge til data:

1. Høyreklikk på den første tabellen (kundenavn) i Queries & Connections-ruten.

2. Velg "Rediger" for å åpne tabellen i Power Query Editor.

3. Klikk på Legg til spørringer i kombiner-delen.

4. Velg den andre tabellen fra rullegardinlisten over tabeller som skal legges til.

Resultatet blir to ekstra rader i den første tabellen.

Legg merke til at én rad gjentas – slik fungerer append-operasjonen. Den legger til data fra en tabell til en annen, uavhengig av om dataene allerede eksisterer.

Du kan fjerne dupliserte data i Power Query Editor med disse trinnene:

1. Klikk på kolonneoverskriften "Fornavn".

2. Hold nede "Ctrl"-tasten (eller "Cmd"-tasten på Mac).

3. Klikk på begge kolonneoverskriftene.

4. Klikk på "Fjern rader" i Hjem-fanen på Power Query Editor-båndet.

5. Velg "Fjern duplikater" fra rullegardinmenyen.

Slik slår du sammen data med Power Query

Når du slår sammen data, må du velge de samsvarende kolonnene mellom spørringene.

Anta at du har to kommadelte filer der den ene inneholder kundetelefonnumre og den andre inneholder adresser. De to filene ser slik ut:

Følg instruksjonene i de forrige avsnittene for å laste hver fil inn i sin egen strømspørring. Deretter kan du øve på å slå sammen tabellene.

Når det gjelder våre eksempeldata, bør begge kolonnene (Fornavn og Etternavn) velges som samsvarende kolonner. Hold nede Ctrl- tasten for å velge flere kolonner.

Følg disse instruksjonene:

1. Høyreklikk på den første tabellen i Queries & Connections-ruten.

2. Velg "Rediger" for å åpne tabellen i Power Query Editor.

3. Velg "Merge Queries" fra "Kombiner"-gruppen.

4. Velg den andre tabellen i rullegardinlisten over tabeller.

5. Klikk på den første kolonnen i den første tabellen.

6. Hold nede Ctrl-tasten og klikk på den andre kolonnen i den første tabellen.

7. Hold nede Ctrl-tasten og klikk på begge kolonnene i den andre tabellen.

8. Behold standard join-operatør som "Left Outer".

9. Klikk "OK" for å bruke endringene.

"Left Outer" join-operatøren sikrer at dupliserte data elimineres.

I Power Query Editor vil du se en ny kolonne kalt "Tabell" eller "Nested Table" som inneholder de sammenslåtte dataene fra den andre spørringen.

1. Klikk på utvidelsesikonet (to piler) i overskriften til denne kolonnen.

2. Fjern merket for "bruk original kolonne som prefiks" (dette unngår lange kolonnenavn).

3. Klikk "OK".

4. Klikk "Lukk og last inn".

Den sammenslåtte tabellen hadde fire kolonner, med telefon- og adressedetaljer slått sammen. Du har kanskje fortsatt litt å rydde opp.

Det kan for eksempel være lurt å redigere navnet på tabellen (Merge1) og adressekolonnen i bildet nedenfor.

5 måter å transformere data med Power Query

Det er enkelt å transformere data i Excel-filen din med Power Query, som lar deg rense, organisere og manipulere dataene dine effektivt.

Noen av de vanligste måtene å transformere data på er:

• Utvide og kondensere data

• Sortering av data

• Formatering av data

• Legge til og fjerne kolonner

• Endre datatyper

1. Utvide og kondensere data

Å utvide og kondensere data er viktig for å gjøre datasettene dine mer håndterbare. Power Query lar deg utvide eller skjule tabeller for en mer organisert visning.

Den forrige delen viser deg hvordan du utvider ved å inkludere data fra relaterte tabeller.

Du kan også kondensere den til bare å inkludere oppsummerte data basert på en aggregering etter eget valg, for eksempel sum, antall eller prosent.

2. Sortering av data

For å sortere data kan du raskt endre rekkefølgen med stigende eller synkende verdier.

Klikk på nedoverpilen ved siden av et kolonnenavn og velg "Sorter stigende" eller "Sorter synkende".

3. Formatering av data

Transform-fanen i Power Query Editor gir deg mange alternativer.

For eksempel lar rullegardinmenyen Format deg endre alle data i en kolonne eller hele tabellen til store eller små bokstaver.

4. Legge til og fjerne kolonner

Power Query forenkler prosessen med å legge til, slette og endre kolonner.

For å fjerne en kolonne, høyreklikk ganske enkelt på overskriften til den uønskede kolonnen og velg "Fjern". Dette vil permanent slette kolonnen og alle dens data fra tabellen.

For å legge til en ny kolonne, velg den eksisterende kolonnen der du vil at den nye skal vises. Bruk deretter funksjonen "Legg til kolonne fra eksempler" for å lage en egendefinert kolonne eller for å utføre beregninger mellom to eller flere kolonner.

5. Endre datatyper

Å jobbe med konsistente og nøyaktige datatyper er avgjørende for nøyaktig analyse. Power Query lar deg enkelt endre datatyper:

1. Høyreklikk på kolonneoverskriften.

2. Velg "Endre type".

3. Velg ønsket datatype fra listen.

Endring av datatyper kan være spesielt nyttig når du arbeider med prosentvise data.

For eksempel kan det hende du må konvertere desimaler til prosenter eller konvertere tekstbaserte prosenter til numeriske for enklere beregning og sammenligning.

Arbeid med formler og M Language

Når du bruker Power Query i Excel, vil du møte M Language, som er hjertet i å lage avanserte formler, manipulere data og produsere kraftige spørringer. I denne delen lærer du om:

• Formelbaren

• M-kode

• de essensielle elementene i M Language-syntaksen

Formel Bar

Formellinjen i Excels Power Query-editor er området der du skal legge inn formlene og M-koden.

For å bruke det, gå til Power Query-redigeringsprogrammet ved å velge en spørring fra Queries & Connections-ruten. Formellinjen er plassert over dataforhåndsvisningsruten.

Skrive og endre M-kode

For å lage allsidige og kraftige spørringer i Excel, må du skrive og endre Power Query-språket kjent som M-kode. Her er noen tips om hvordan du kommer i gang:

• Bruk Applied Steps-ruten til å skrive trinn-for-trinn-kode, noe som gjør det enkelt å spore og endre spørringens logikk.

• Kombiner M-kode med en Excel-tabell, pivottabeller og eksterne datakilder, som Azure og tekstfiler, for å lage dynamiske og automatiserte rapporter.

• Juster søkealternativene for å avgrense datainnlastingspreferansen og forbedre ytelsen.

Når du gjør deg kjent med M-kode, vil du oppdage at du kan automatisere og optimere mange prosesser, noe som gjør dataanalysen din i Excel mer effektiv og kraftig.

Følg disse trinnene for å se koden bak en spørring:

1. Åpne spørringen i Power Query Editor.

2. Klikk på "Advanced Editor" i Hjem-fanen.

Dette er koden bak den sammenslåtte eksempeltabellen vår (din kan se annerledes ut):

Hva er M Language Syntax?

Å forstå M Language-syntaksen er avgjørende for å lage effektive Power Query-formler. Husk følgende punkter:

• M Språk skiller mellom store og små bokstaver, så vær forsiktig med store bokstaver.

• Bruk firkantede parenteser [] for å referere til kolonner, poster og lister.

• For å referere til et tidligere trinn i spørringen, bruk variabelnavnet (f.eks. #”Trinnnavn”).

• Skriv ut et spørringsformeltrinn med "in"-setningen.

Etter hvert som du får erfaring med , vil du oppdage nye måter å transformere og analysere dataene dine på, noe som gjør Excel-arbeidsbøkene dine til en enda mer verdifull ressurs.

Sjekk ut videoen nedenfor, det er på tide å ta ut skurebørsten og rydde opp i rotete data.

Slik oppdaterer du datatilkoblinger

En av nøkkelfunksjonene til Power Query er muligheten til å enkelt oppdatere en Power Query-tilkobling.

Når datakildene dine er oppdatert, og du trenger den nyeste informasjonen i arbeidsboken, kan du oppdatere datatilkoblingene i ruten Spørringer og tilkoblinger.

Hvis du ikke ser ruten til høyre i regnearket, kan du åpne den ved å gå til datatabellen og klikke på "Spørringer og tilkoblinger".

Når ruten Spørring og tilkoblinger er åpen, viser den alle spørringene. Følg disse instruksjonene:

1. Høyreklikk på søket du vil oppdatere.

2. Velg Oppdater fra hurtigmenyen.

Dette vil oppdatere arbeidsboken med de nyeste dataene fra de tilkoblede datakildene.

Hvordan integrere Power Query med datamodellen

Excels datamodell er en kraftig funksjon som lar deg lage business intelligence-verktøy, som pivottabeller, basert på datarelasjoner.

Datamodellen er i utgangspunktet en skjult database som inneholder importerte data og relasjoner mellom tabeller.

Det er en del av Power Pivot-funksjonen som er innebygd i gjeldende versjoner av Excel. Du må kanskje installere Power Pivot som et tillegg for å få tilgang til det i tidligere versjoner).

Du kan integrere Power Query-datatilkoblingene dine med datamodellen med disse trinnene:

1. Last inn dataene dine ved hjelp av Power Query som beskrevet i forrige avsnitt.

2. I stedet for å laste den direkte inn i arbeidsboken, velg Last til fra Last-menyen.

3. Velg Legg til disse dataene i datamodellen i dialogboksen Importer data.

Hvordan automatisere med Power Query og VBA

Med VBA-integrering (Visual Basic for Applications) kan du opprette tilpassede datatilkoblinger og automatisere datatransformasjonstrinn i Power Query.

Du kan utnytte dine eksisterende VBA-ferdigheter til å bygge avanserte datamodeller ved å inkludere data fra plattformer som Microsoft Access, SharePoint og Analysis Services.

Selv om Power Query ikke har innebygd støtte for VBA, kan du bruke VBA-kode til å åpne, oppdatere og manipulere Power Query-spørringer indirekte.

Dette lar deg lage kraftige, automatiserte dataarbeidsflyter og strømlinjeforme dataanalyseprosessen.

Her er noen eksempelkode som oppdaterer en power-spørring i gjeldende arbeidsbok.

:

Dim wb As Workbook, pq As Object

Sett wb = ThisWorkbook

Sett pq = wb.Queries(“Kundenavn”)

 pq. Refresh

Her er koden i en Excel VBA-makro:

Power Query Avanserte funksjoner

Excels Power Query tilbyr en mengde avanserte funksjoner for å forenkle og forbedre dataanalyseprosessen. Ved å lære hvordan du effektivt bruker disse funksjonene, kan du ta dataanalyseferdighetene dine til et helt nytt nivå.

De innebygde dataanalyseverktøyene inkluderer:

• Betingede kolonner for å bruke kompleks logikk på datatransformasjonene dine.

• Grupperings- og aggregeringsfunksjoner for å oppsummere dataene dine på en meningsfull måte.

• Pivot- og Unpivot-operasjoner for å omforme dataene dine for enklere analyse.

Du finner mange av disse funksjonene i Transform-fanen i Power Query Editor.

I tillegg kan du koble Power Query til Business Intelligence for mer avanserte alternativer for dataanalyse og visualisering .

Samarbeid og deling

Å dele dataforbindelser, forespørsler og transformasjoner med teammedlemmene dine er avgjørende for effektivt samarbeid.

Du kan dele en arbeidsbok med spørringer ved å lagre den på en nettverksstasjon, SharePoint eller en annen delt lagringsplass.

Du kan også eksportere M-koden bak en enkelt spørring med disse trinnene:

1. Rediger spørringen i Power Query Editor.

2. Klikk på "Advanced Editor" i Hjem-fanen.

3. Kopier hele M-koden i Advanced Editor-vinduet.

4. Åpne et tekstredigeringsprogram (f.eks. Notisblokk) og lim inn den kopierte M-koden i tekstredigeringsprogrammet.

5. Lagre tekstfilen og send den til din kollega.

La oss avslutte dette

Med en rekke funksjoner og det brukervennlige grensesnittet er Power Query et viktig verktøy for Excel-brukere som ønsker å jobbe effektivt med eksterne data.

Denne verdifulle funksjonen hjelper til med å strømlinjeforme prosessen med å lage rapporter, datarensing og utføre komplekse analyser på datatabellene dine. Ved hjelp av

Power Query sparer deg til slutt tid og øker effektiviteten når du arbeider med Excel-filer.