Power Query - En enkel innføring til et mektig verktøy

Hva er Power Query? En introduksjon for nybegynnere

Å kunne importere, behandle, analysere og trekke verdifull innsikt fra dataene våre er essensielt for enhver som jobber ofte i Microsoft Excel og ønsker å lykkes med data analyse, data science, men også business intelligence. Power Query er en del av Excel som gir oss muligheten til å hente data fra forskjellige kilder (som .txt og .csv filer) og transformere dem etter behov, noe som gir oss en lett håndterlig Excel tabell. Du må da bare sette opp disse stegene en gang. Hver gang dataene dine endres, vil også dataene i Excel tabellen din endres. En helt ny måte å jobbe med eksterne datakilder!

Infografikk: Datakilder som filer og SQL database til høyre, databehandling med Power Query i midten og output i Excel tabell til høyre — Power Query gir oss muligheten til å importere data fra kilder som txt-, csv-, og Excel filer, samt SQL databaser og flere andre kilder. Power Query er også mektig til å behandle data og utgir dem i ønsket format i en lett håndterlig Excel tabell.

Det er verdt å nevne at Power Query er tilgjengelig i Excel 2010 og senere. Men, det er i Excel 2016 og senere versjoner at det virkelig har blitt en integrert del av Excel-brukeropplevelsen, og har fått et eget dedikert felt i båndet.

I denne bloggposten vil vi føre deg gjennom hvordan Power Query kan brukes i praksis med et enkelt eksempel. Vi skal importere og behandle en CSV-fil med salgsdata fra en frukt butikk og samtidig lære noe av de grunnleggende konseptene du kan gjøre i Power Query. Målet er å gi deg en lett forståelig innføring uten å overvelde deg med for mye informasjon.

Det er altså viktig å være klar over at selv om vi i dette innlegget bare vil dekke rundt 10% av Power Query sine muligheter, så er det mye mer utover det du kan gjøre med Power Query. Fra det å håndtere store mengder data, automatisering av oppgaver, til avanserte transformasjoner.

Hvis du etter å ha lest denne artikkelen føler behov for mer avansert Excel konsulent hjelp, er du velkommen til å kontakte oss. Vi står alltid klare til å hjelpe deg å utnytte Excel og Power Query til det fulle!

Åpne Power Query og velge CSV-fil

For å bruke Power Query, må vi først finne datakilden vår. I dette tilfellet, kommer våre data fra en CSV-fil. Her ser du et en liten del av csv filen som vi skal importere og transformere (legg merke til hvor dårlig dataene her er strukturert, med ubrukelige header og footer, kategori og produkt slått sammen til en kolonne, rader uten data, osv.). Du kan last4e ned csv filen og Excel arket helt nederst i innlegget.

CSV fil

Butikk: Lille Frukt Paradis AS
Start dato: 10.07.2023
Slutt dato: 16.07.2023
===============================

Kategori/Produkt;Dato;Enhetpris;SolgtKg
Frukt-Eple;10.07.2023;29;48
Frukt-Banan;10.07.2023;24;58
Frukt-Melon;10.07.2023;15;40
Bær-Blåbær;10.07.2023;119;20
Bær-Jordbær;10.07.2023;99;24
Bær-Bringebær;10.07.2023;149;16
===============================
Frukt-Eple;11.07.2023;29;44
Frukt-Banan;11.07.2023;24;60

...

===============================
Frukt-Eple;16.07.2023;29;56
Frukt-Banan;16.07.2023;24;60
Frukt-Melon;16.07.2023;15;40
Bær-Blåbær;16.07.2023;119;18
Bær-Jordbær;16.07.2023;99;28
Bær-Bringebær;16.07.2023;149;12
===============================
Utskriftsdato 17.07.2023 20:16

Først åpner vi Power Query ved å klikke på "Data" i Excel-båndet, deretter "Hent data", og til slutt "Start Power Query-redigering".

Åpne Power Query i Excel - trykk på Data, Hent data, Start Power Query Redigering — Åpne Power Query i Excel - trykk på "Data" > "Hent data". Deretter kan du enten klikke på "Fra fil" eller "Start Power Query Redigering"

Selv om det er mulig å gå direkte til "Fra fil" i menyen, vil vi i dette eksempelet vise hvordan man velger en kilde når man allerede er inne i Power Query-redigeringsmiljøet.

Når Power Query-redigeringsvinduet åpner seg, vil du se en rekke alternativer for å hente data fra forskjellige kilder. For å laste inn vår CSV-fil, klikker vi på "Ny kilde" under "Hjem" fanen. Deretter velger vi "Fil > Fra CSV".

Valg av ny kilde i Power Query. Hjem > Ny kilde > Fil > Fra CSV — Valg av ny kilde i Power Query. Gå til "Hjem" > "Ny kilde" >. Deretter velger du "Fil" > "Fra CSV"

Etter at du har funnet og valgt CSV-filen du ønsker å bruke, klikk "Åpne". Når filen er lastet inn, vises en forhåndsvisning av dataene i CSV-filen. Siden dataene våre er skilt med semikolon, velger vi "Semikolon" som skilletegn. Hvis alt ser greit ut i forhåndsvisningen, klikker vi på "OK".

Forhåndsvisning av import data i Power Query — Velg dataformat og skilletegn. Du ser en forhåndsvisning. Dersom alt ser greit ut, kan du klikke på "Ok".

Det er også viktig å merke seg at Power Query automatisk prøver å gjenkjenne datatypene i CSV-filen. Dette virker som en nyttig funksjon, men i noen tilfeller vil du kanskje deaktivere denne funksjonen. For å gjøre dette, kan du klikke på "x" ved siden av "Endret datatype" i "Brukte trinn"-listen.

Data når det er lastet inn i Power Query. Oversikt over verktøyet med rådata. — Nå er dataene lastet inn i Power Query. Du kan klikke på det lille "x" ved siden av "Endret datatyp" for å unngå at Power Query automatisk gjenkjenner datatyper. Importen blir mer "Future proof" når vi setter datatypene manuelt senere.

Rydd opp: Fjern unødvendige rader og promoter overskrifter

Når du importerer data, kan det hende at det er unødvendige rader i begynnelsen eller slutten av datasettet ditt. I vårt tilfelle inneholder CSV-filen vår fem unødvendige rader på toppen og to rader på bunnen. For å rydde opp i dette, bruker vi Power Querys "Fjern rader"-funksjon.

Vi begynner med å fjerne de øverste radene. Dette gjøres ved å klikke på "Hjem" i menyen, deretter "Fjern rader", og til slutt "Fjern de øverste radene". Etter å ha klikket på "Fjern de øverste radene", vil en dialogboks dukke opp der vi taster inn antallet rader vi ønsker å fjerne - i dette tilfellet, 5.

Fjerning av øverste og nederste rader i Power Query — Det ligger en del unødvendige rader øver og nederst i csv filen. Disse kan lett fjernes i menyen "Fjern rader".

Neste steg er å fjerne de to nederste radene. Dette gjøres på samme måte, men denne gangen velger vi "Fjern nederste rader" i stedet. I dialogboksen som dukker opp, taster vi inn 2, ettersom vi har to unødvendige rader under dataene våres.

Etter at vi har fjernet de unødvendige radene, er det på tide å promotere raden med overskrifter til å være kolonneoverskrifter i datasettet vårt. Dette gjøres enkelt ved å klikke på "Bruk første rad som overskrifter" i "Hjem"-menyen.

Promotere overskriftene fra data til Power Query kolonnetitel — Når overskriftene ligger øverst i data, så kan disse promoteres til å bli kolonneoverskrift.

Nå ser datasettet vårt mye mer organisert og forståelig ut.

Promoterte kolonneoverskrifter i Power Query og brukte trinn. — Overskriftene ble promotert og alle trinnene vi har utført er listet opp under "Brukte trinn". Nå begynner det å se mye bedre ut.

Og det beste av alt? Hvert steg vi har tatt, blir nøye registrert i "Brukte trinn"-listen på høyre side av redigeringsvinduet. Dette gjør det enkelt å se og endre handlingene vi har tatt.

Når vi arbeider med rådata, er det vanlig å støte på uønsket eller ugyldig informasjon. I vårt eksempel ser vi at det er en del rader som inneholder ==================== eller tom informasjon ;;;. Disse radene tilfører skaper alt mulig av hodebry i datasettet vårt og vi vil gjerne fjerne dem.

Hvis vi for eksempel velger kolonnen "Dato" og klikker på "pilen" for å åpne filteret, vil vi se noe data som er "(null)". Dette betyr at det er rader i denne kolonnen som ikke inneholder noen data. Ved å fjerne krysset for "(null)", vil vi effektivt fjerne alle rader som har tomme datoer.

Rensing og filtrering av data i Power Query — Ved å klikke på "pilen" i et av kolonnene, kan du filtrere bort uønsket data. Disse filtrene ligner veldig på Excel sin vanlig filtrering. Tekst-, tall-, og datofilter, samt sortering er også tilgjengelig.

Av og til kan det være at du ser rader som inneholder ordet "error". Dette indikerer et problem med dataene i denne raden. For å bli kvitt disse radene, kan du bruke funksjonen "Fjern rader" og deretter "Fjern feil". På denne måten sørger vi for at datasettet vårt kun inneholder gyldige og korrekte data.

Håndtering av datatyper i Power Query

Når vi jobber med Power Query, er det viktig å velge riktig datatype for dataene våre. Datatypen forteller programmet hvordan det skal tolke og behandle dataene vi gir det. I vårt tilfelle vil vi fokusere på kolonnene "Dato", "Enhetspris" og "SolgtKg".

For å endre datatypen til en kolonne, kan du klikke på det lille symbolet som vises i kolonnetitelen. Dette vil åpne en rullegardinmeny hvor du kan velge blant forskjellige datatyper.

Valg av datatype ved å trykke kolonnesymbolet og velge datatype i nedtrekksmenyen — Dataypen velger du ved å trykke på symbolet til venstre for kolonnen.

I vårt datasett ser "Enhetspris" og "SolgtKg" ut til å være heltall, så det kan vi sette som heltall. Men for å fremtidssikre datasettet vårt, kan det være lurt å velge desimaltall som datatype. På denne måten, hvis vi i fremtiden får data med desimaltall, vil datasettet vårt allerede være klart til å håndtere dette.

Det er verdt å merke seg at hvis vi velger feil datatype, vil vi sannsynligvis merke det når vi prøver å analysere dataene våre. En feil datatype vil vanligvis (men ikke alltid!) resultere i en feilmelding.

Til slutt, det er viktig å huske at hvis du jobber med data som har forskjellige, internasjonale format (f.eks. amerikansk dato eller desimaltall med "." som skilletegn), kan du bruke funksjonen "Med nasjonale innstillinger" for å konvertere dataene til en godkjent type.

Enkel kolonneoppsplitting i Power Query

Når vi arbeider med datasett i Power Query, er det ikke uvanlig å støte på kolonner der flere forskjellige typer data er slått sammen, som i vårt tilfelle der vi har Kategori/Produkt-kolonnen. Men det er viktig å adskille disse dataene for å lette analysen.

For å dele opp en kolonne, kan vi markere den aktuelle kolonnen, i dette tilfellet Kategori/Produkt. Deretter går vi til "Del kolonne" og velger "Med skilletegn". Her skal vi bruke - som skilletegn, ettersom det er dette tegnet som er brukt for å skille kategori og produkt i vårt datasett.

Del kolonne i to i Power Query — Marker en kolonne og velg "Del kolonne" for å dele kolonnen i to for bedre analyse.

Når kolonnen er delt opp, får vi to nye kolonner. Vi kan gi disse kolonnene mer fornuftige navn for å gjøre det lettere å forstå dataene. Dette kan gjøres ved å dobbeltklikke på kolonnetitlene og deretter skrive inn de nye navnene.

Når vi analyserer f.eks. salgsdata, kan det være nyttig å kunne beregne totalt salg eller omsetning per produkt. Med Power Query kan vi gjøre dette ved å opprette en kalkulert kolonne. En kalkulert kolonne bruker en formel som bruker verdier fra andre kolonner for å beregne en ny verdi.

I vårt tilfelle ønsker vi å beregne omsetning per produkt. Vi vil gjøre dette ved å multiplisere Enhetspris med SolgtKg. Først markerer vi disse to kolonnene. Deretter går vi til "Legg til kolonne" i menyen, og velger "Standard" > "Multiplikasjon". Dette vil generere en ny kolonne som viser produktet av de to utvalgte kolonnene.

Legg til nye kolonner i Power Query. Beregnet kolonne. Standard. Multiplikasjon — Det er mulig å legge inn nye, beregnete kolonner. Marker kolonnene som skal beregnes, klikk på "Stardard" og velg deretter ønsket operasjon. Her velger vi "Multiplikasjon"

Den nye kolonnen blir automatisk navngitt "Multiplikasjon". Men for at dette skal være mer meningsfylt for oss, vil vi endre navnet til "Omsetning". Dette kan gjøres ved å dobbeltklikke på kolonnenavnet og skrive inn det nye navnet.

Effektiv dataanalyse: Hvordan gruppere data i Power Query

For å få en mer konsolidert oversikt over salget vårt, kan vi bruke grupperingsfunksjonen i Power Query. Gruppering kan hjelpe oss med å samle data basert på en bestemt kolonne. I vårt tilfelle ønsker vi å se total omsetning per produkt.

For å gjøre dette, starter vi med å markere Produkt kolonnen. Vi høyreklikker deretter på den og velger "Grupper etter".

Fremgangsmåte for gruppering i Power Query — Marker kolonnen du ønsker å gruppere etter. Deretter høyre-klikker du på kolonnen og velger "Grupper etter".

I dialogboksen som dukker opp, vil Produkt kolonnen allerede være valgt. Neste trinn er å velge navnet på den nye, grupperte kolonnen, som vi kaller "Total omsetning". Operasjonen vi velger er "Sum", fordi vi ønsker å oppsummere omsetning per produkt. Kolonnen vi velger til høyre er derfor "Omsetning". Vi bekrefter våre valg ved å klikke "Ok".

Dialogboksen grupper etter i Microsoft Power Query — I dialogboksen som åpnes velger du kolonnen som skal grupperes etter ("Produkt"), navn på ny summeringskolonne, operasjon og hvilken kolonne som skal summeres ("Omsetning")

Nå vil vi få en ny liste som viser hvert av de seks produktene med total omsetning for alle dager.

Gruppert og oppsummert resultat i Power Query — Vi er ferdig og har fått en ryddig oppsummering per produkt fra en veldig uryddig strukturert csv fil.

Det som er veldig flott: Når dataene dine i csv filen endrer seg, så klikker du bare på "Oppdater" i Excel og import samt oppsummering vil oppdatere seg også. Du trenger altså ikke å gå gjennom alle disse stegene hver gang du får nye data. Det kan spare deg mengder med tid!

Tilbake til Excel: Laste inn data fra Power Query

Etter å ha gjennomført alle stegene i Power Query, er det på tide å laste dataene tilbake til Excel. Vi avslutter håndteringen i Power Query ved å klikke på "Lukk og last inn" > "Lukk og last inn til".

Lukk og last inn fra Power Query til Excel tabell — Ved å trykke på "Lukk og last inn" kan du velge hva som skal skje med dataene dine.

En dialogboks vil dukke opp, og her velger vi "Tabell" og "Eksisterende regneark". Ved å bekrefte disse valgene, oppretter vi en tabell i regnearket.

Power Query dialogbosken som spør hvor vi ønsker å laste dataene til — Vi kan nå enten laste dataene til en ny tabell, en eksisterende tabell eller bare lage en tilkobling som vi kan bruke senere.

Denne tabellen vil oppdateres automatisk når vi trykker på "Oppdater" i menyen "Tabellutforming". Dette betyr at vi bare trenger et enkelt tastetrykk for å få en oppdatert analyse når dataene endres.

Dataene er ferdig lastet inn til en formatert Excel tabell og synlig under Spørringer i Excel — Dataene ligger nå i en formatert tabell. Til høyre ser du menyen "Spørringer" der du kan klikke deg tilbake til Power Query.

Veien videre: Lær deg mer om mulighetene i Power Query

Etter å ha fulgt denne veiledningen, er du nå i stand til å gjennomføre enkle dataimporter og transformasjoner i Power Query. Du har lært å åpne Power Query, velge en datakilde, fjerne unødvendig informasjon, behandle feil og ugyldig data, velge riktig datatyper, dele opp kolonner, lage kalkulerte kolonner, gruppere data, og til slutt laste bearbeidede data tilbake til Excel. Dette er grunnleggende ferdigheter som vil hjelpe deg med mange oppgaver innen datahåndtering og analyse.

Men det er viktig å merke seg at vi bare har skrapet litt på overflaten og at Power Query har mye mer å by på. Power Query er et kraftig verktøy som kan utføre mange avanserte transformasjoner og analyser. Her er noen eksempler på hva du kan gjøre i Power Query som vi ikke har dekket i denne artikkelen:

Koble sammen flere datakilder: Du kan importere data fra flere kilder og kombinere dem i Power Query.
Utføre avanserte data-transformasjoner: Du kan for eksempel pivotere og unpivotere tabeller, fusjonere tabeller, og utføre mange andre avanserte transformasjoner.
Skape tilpassede funksjoner: Du kan lage dine egne funksjoner for å utføre bestemte operasjoner på dataene dine.
Bruke M-forespørselsspråket: Power Query bruker M-forespørselsspråket, og du kan skrive dine egne M-forespørsler for å gjøre komplekse data-transformasjoner og analyser.
Behandle store mengder data: Power Query kan håndtere store datamengder, noe som gjør det til et flott verktøy for store datadrevne prosjekter.
Og mye mer!

Vi håper at vi har klart å vekke begeistringen for Power Query. Med litt øvelse vil du finne at Power Query kan være en uvurderlig ressurs for dine dataanalyseprosjekter. Og trenger du konsulenthjelp, så er vi bare en telefonsamtale eller epost unna.

Last ned eksemplet her:

Last ned Excel fil — power-query-demo.xlsx

Dersom du tester eksemplet - vær oppmerksom på at du må tilpasse filstien til csv filen! Det gjør du i Power Query > Brukte trinn > Kilde. Der kan du endre filstien.

Ønsker du hjelp med dine Power Query utfordringer?

Excellence Utvikling hjelper bedrifter med alle oppgaver rundt Microsoft Excel og Power Query, blant annet utvikling av kalkyler, dashboards, makroer og selvsagt proff håndtering av store datamengder. Ta gjerne kontakt med alle typer spørsmål eller finn ut mer om våre Excel konsulent tjenester. Lik gjerne siden på Facebook, så får du alle nyheter.

Power Query i Excel: Kom i gang med et enkelt eksempel!