Sådan bruges Power Query i Excel: Den komplette vejledning

Power Query er et kraftfuldt business intelligence-værktøj i Excel, der sætter dig i stand til at importere, rense og transformere data efter dine behov. Hvis du nogensinde har brugt timer på at rydde op i data (vi har) eller kæmpet for at flette data fra forskellige kilder (det har vi også), vil denne funktion gøre dit liv lettere.

Den bedste måde at lære at bruge Power Query i Excel er at udføre nogle almindelige opgaver som at importere data og bruge Power Query Editor til at transformere de resulterende tabeller og kombinere dataene.

Denne artikel får dig i gang ved at vise klart . Ved at mestre Power Query kan du løfte dit arbejde til næste niveau.

Er du klar? Lad os gøre det.

Indholdsfortegnelse

• Sådan ved du, om du kan få adgang til Power Query
• Sådan importeres data med Power Query
• Sådan bruger du Power Query Editor
  • Data Preview
  • Ejendomme
• Sådan arbejder du med flere datakilder
• Sådan tilføjer du data med Power Query
• Sådan flettes data med Power Query
• 5 måder at transformere data med Power Query
  • 1. Udvidelse og kondensering af data
  • 2. Sortering af data
  • 3. Formatering af data
  • 4. Tilføjelse og fjernelse af kolonner
  • 5. Ændring af datatyper
• Arbejde med formler og M-sprog
  • Formel Bar
  • Skrivning og ændring af M-kode
• Hvad er M-sprogsyntaks?
• Sådan opdateres dataforbindelser
• Sådan integreres Power Query med datamodellen
• Sådan automatiseres med Power Query og VBA
• Power Query avancerede funktioner
• Samarbejde og deling
• Lad os afslutte dette

Sådan ved du, om du kan få adgang til Power Query

Før du begynder at bruge Power Query, skal du være sikker på, at du har en version af Excel, der har funktionen.

Her er en kort oversigt over Power Query efter version:

• Excel 2010 og Excel 2013: tilgængelig som et tilføjelsesprogram, der skal downloades og installeres.

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

• Excel 2019: forbedret ydeevne og funktionalitet.

• Excel 2021 og Excel til Microsoft 365: integration med cloud-datakilder.

• Excel til Mac: kun tilgængelig fra Microsoft 365 version 16.69 og nyere.

For at bekræfte, at værktøjet er integreret i din Excel-projektmappe (2016 eller nyere), skal du gå til fanen Data og se efter afsnittet "Hent og transformer data" på dette billede:

Hvis du har Excel 2010 eller 2013 på Windows, kan du downloade , installere og aktivere tilføjelsesprogrammet Power Query.

Sådan importeres data med Power Query

Power Query-værktøjet giver dig mulighed for at importere og oprette forbindelse til en lang række datakilder:

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

• Web-URL'er eller API-endepunkter

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

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

Den nemmeste datakilde at starte med er en simpel CSV-fil med flere datalinjer. Opret en tekstfil og tilføj disse fire for- og efternavne, adskilt af et komma:

• Joe, Blogs

• Anne, Ryan

• James, Stewart

• Mary, Brown

Sørg for at gemme filen med filtypenavnet .CSV. Din fil vil se sådan ud:

Følg disse trin for at indlæse dataene med Power Query:

1. Gå til fanen Data.

2. Klik på knappen "Hent filer" på fanen Hent og transformer data.

3. Vælg din prøvefil.

Du skal muligvis vente et par sekunder på, at værktøjet henter dataene til forhåndsvisningsruden. Power Query bruger temmelig mange maskinressourcer selv til en lille import, så vær tålmodige folk.

Tip: Luk andre programmer ned for at øge ydeevnen.

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

Hvis dataene ser korrekte ud, kan du fortsætte med at indlæse eller transformere dataene:

• knappen Indlæs indlæser data direkte i Excel.

• knappen Transform Data lader dig ændre dataene, før de indlæses.

Har du bemærket, at eksempelfilen ikke havde en header? Hvis det er indlæst direkte i Excel, vil overskrifterne være "Kolonne 1" og "Kolonne 2".

I stedet kan du trække dataene ind i Power Query-editoren og foretage nogle ændringer. For at gøre det skal du klikke på knappen "Transformer data".

Power Query Editor åbner nu, hvilket bringer os til næste afsnit.

Sådan bruger du Power Query Editor

I dette afsnit vil vi undersøge nogle væsentlige aspekter af arbejdet med Power Query Editor.

Data Preview

I området Data Preview kan du se og interagere med dine importerede data. Du kan filtrere rækker, ændre kolonnedatatyper og omforme dataene, så de passer til dine behov.

Når du er færdig, indlæser Excel de transformerede data i et regneark.

Ved at bruge eksempelfilen kan du redigere kolonnenavnene direkte i denne rude.

For at gøre det:

1. Højreklik på kolonneoverskriften.

2. Klik på "Omdøb" i rullemenuen.

3. Indtast de foretrukne navne i overskriftsfelterne.

Når du foretager ændringer, opdateres afsnittet "Anvendte trin" til højre for forhåndsvisningsruden for at afspejle hver transformation, så du kan spore og ændre dine databehandlingstrin efter behov.

Ejendomme

Indstilling af egenskaber for dine forespørgsler er afgørende for at organisere og spore dit arbejde.

Ruden Forespørgselsindstillinger er til højre for vinduet Power Query.

1. Klik på linket "Alle egenskaber".

2. Rediger navnet på forespørgslen, så det er beskrivende.

3. Tilføj en beskrivelse for at give flere oplysninger.

Det er nemt at glemme, hvad sidste uges forespørgsler blev sat op til at gøre. I dette tilfælde er forespørgselsnavnet blevet ændret, og der er tilføjet en beskrivelse, der angiver formålet med forespørgslen.

Hvis du vil vide mere om de tilgængelige muligheder, så tjek denne .

Sådan arbejder du med flere datakilder

Power Query Editor tilbyder to primære metoder til at kombinere data fra flere kilder: tilføjelse og fletning.

Ved at tilføje sammenføjes tabeller lodret og stable dem oven på hinanden. Sammenfletning giver dig mulighed for at forbinde tabeller baseret på fælles kolonner.

For at se, hvordan dette virker, skal du oprette en anden lille CSV-fil med samme struktur som den første. Denne gang skal du indsætte to navne (første og sidste) i filen:

• Joe, Blogs

• Laura, Cane

Bemærk, at et af disse navne gentages fra den forrige fil.

Følg nu disse trin for at trække dataene ind i din Microsoft Excel-projektmappe.

1. Luk Power Query Editor-vinduet, og gem dine ændringer.

2. Tilføj et nyt regneark.

3. Følg trinene i det foregående afsnit for at importere den anden fil.

4. Klik på knappen Transformer.

5. Omdøb kolonneoverskrifterne til "Fornavn" og "Efternavn".

Den højre rude i Excel-regnearket viser de aktuelle forespørgsler og forbindelser. Du skulle nu se to forespørgsler i ruden.

Sådan tilføjer du data med Power Query

I dette afsnit vil du tilføje den anden eksempel-CSV-fil til den første.

Følg disse trin for at tilføje data:

1. Højreklik på den første tabel (kundenavne) i ruden Forespørgsler og forbindelser.

2. Vælg "Rediger" for at åbne tabellen i Power Query Editor.

3. Klik på Tilføj forespørgsler i sektionen Kombiner.

4. Vælg den anden tabel fra rullelisten over tabeller, der skal tilføjes.

Resultatet bliver to ekstra rækker i den første tabel.

Bemærk, at en række gentages - sådan fungerer tilføj-handlingen. Det tilføjer data fra en tabel til en anden, uanset om dataene allerede eksisterer.

Du kan fjerne de duplikerede data i Power Query Editor med disse trin:

1. Klik på kolonneoverskriften "Fornavn".

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

3. Klik på begge kolonneoverskrifter.

4. Klik på "Fjern rækker" på fanen Hjem på Power Query Editor-båndet.

5. Vælg "Fjern dubletter" fra rullemenuen.

Sådan flettes data med Power Query

Når du flette data, skal du vælge de matchende kolonner mellem forespørgslerne.

Antag, at du har to kommaseparerede filer, hvor den ene indeholder kundetelefonnumre, og den anden indeholder adresser. De to filer ser således ud:

Følg instruktionerne i de foregående afsnit for at indlæse hver fil i sin egen strømforespørgsel. Derefter kan du øve dig i at slå tabellerne sammen.

I tilfælde af vores eksempeldata skal begge kolonner (Fornavn og Efternavn) vælges som de matchende kolonner. Hold Ctrl- tasten nede for at vælge flere kolonner.

Følg disse trin:

1. Højreklik på den første tabel i ruden Forespørgsler og forbindelser.

2. Vælg "Rediger" for at åbne tabellen i Power Query Editor.

3. Vælg "Merge Queries" fra gruppen "Kombiner".

4. Vælg den anden tabel i rullelisten over tabeller.

5. Klik på den første kolonne i den første tabel.

6. Hold Ctrl-tasten nede, og klik på den anden kolonne i den første tabel.

7. Hold Ctrl-tasten nede, og klik på begge kolonner i den anden tabel.

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

9. Klik på "OK" for at anvende ændringerne.

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

I Power Query Editor vil du se en ny kolonne med navnet "Tabel" eller "Indlejret tabel", der indeholder de flettede data fra den anden forespørgsel.

1. Klik på udvidelsesikonet (to pile) i overskriften i denne kolonne.

2. Fjern markeringen i feltet "brug original kolonne som præfiks" (dette undgår lange kolonnenavne).

3. Klik på "OK".

4. Klik på "Luk og indlæs".

Den flettede tabel havde fire kolonner, hvor telefon- og adresseoplysningerne var slået sammen. Du har muligvis stadig lidt at rydde op.

For eksempel vil du måske redigere navnet på tabellen (Merge1) og adressekolonnen på billedet nedenfor.

5 måder at transformere data med Power Query

Det er nemt at transformere data i din Excel-fil med Power Query, som giver dig mulighed for at rense, organisere og manipulere dine data effektivt.

Nogle af de mest almindelige måder at transformere data på er:

• Udvide og kondensere data

• Sortering af data

• Formatering af data

• Tilføjelse og fjernelse af kolonner

• Ændring af datatyper

1. Udvidelse og kondensering af data

Det er vigtigt at udvide og kondensere data for at gøre dine datasæt mere overskuelige. Power Query giver dig mulighed for at udvide eller skjule tabeller for en mere organiseret visning.

Det forrige afsnit viser dig, hvordan du udvider ved at inkludere data fra relaterede tabeller.

Du kan også kondensere det til kun at inkludere opsummerede data baseret på en aggregering efter eget valg, såsom sum, antal eller procent.

2. Sortering af data

For at sortere data kan du hurtigt ændre rækkefølgen med stigende eller faldende værdier.

Klik på pil ned ved siden af ​​et kolonnenavn og vælg "Sorter stigende" eller "Sorter faldende".

3. Formatering af data

Fanen Transform i Power Query Editor giver en masse muligheder.

For eksempel lader rullemenuen Format dig ændre alle data i en kolonne eller hele tabellen til store eller små bogstaver.

4. Tilføjelse og fjernelse af kolonner

Power Query forenkler processen med at tilføje, slette og ændre kolonner.

For at fjerne en kolonne skal du blot højreklikke på overskriften på den uønskede kolonne og vælge "Fjern". Dette vil permanent slette kolonnen og alle dens data fra tabellen.

For at tilføje en ny kolonne skal du vælge den eksisterende kolonne, hvor du vil have den nye til at blive vist. Brug derefter funktionen "Tilføj kolonne fra eksempler" til at oprette en brugerdefineret kolonne eller til at udføre beregninger mellem to eller flere kolonner.

5. Ændring af datatyper

At arbejde med konsistente og nøjagtige datatyper er afgørende for præcis analyse. Power Query giver dig mulighed for nemt at ændre datatyper:

1. Højreklik på kolonneoverskriften.

2. Vælg "Skift type".

3. Vælg den ønskede datatype fra den viste liste.

Ændring af datatyper kan være særligt nyttigt, når man håndterer procentdata.

Det kan for eksempel være nødvendigt at konvertere decimaler til procenter eller konvertere tekstbaserede procenter til numeriske for lettere beregning og sammenligning.

Arbejde med formler og M-sprog

Når du bruger Power Query i Excel, vil du støde på M-sproget, som er hjertet i at skabe avancerede formler, manipulere data og producere kraftfulde forespørgsler. I dette afsnit lærer du om:

• Formelbaren

• M kode

• de væsentlige elementer i M Language syntaks

Formel Bar

Formellinjen i Excels Power Query-editor er det område, hvor du skal indtaste dine formler og M-kode.

For at bruge det skal du gå til Power Query-editoren ved at vælge en forespørgsel fra ruden Forespørgsler og forbindelser. Formellinjen er placeret over dataeksempelruden.

Skrivning og ændring af M-kode

For at oprette alsidige og kraftfulde forespørgsler i Excel skal du skrive og ændre Power Query-sproget kendt som M-kode. Her er nogle tips til, hvordan du kommer i gang:

• Brug ruden Anvendte trin til at skrive trin-for-trin kode, hvilket gør det nemt at spore og ændre din forespørgsels logik.

• Kombiner M-kode med en Excel-tabel, pivottabeller og eksterne datakilder, såsom Azure og tekstfiler, for at oprette dynamiske og automatiserede rapporter.

• Juster forespørgselsindstillingerne for at finjustere din præference for dataindlæsning og forbedre ydeevnen.

Når du bliver fortrolig med M-kode, vil du opdage, at du kan automatisere og optimere mange processer, hvilket gør din dataanalyse i Excel mere effektiv og kraftfuld.

Følg disse trin for at se koden bag en forespørgsel:

1. Åbn forespørgslen i Power Query Editor.

2. Klik på "Avanceret editor" på fanen Hjem.

Dette er koden bag vores eksempel flettede tabel (din kan se anderledes ud):

Hvad er M-sprogsyntaks?

At forstå M Language-syntaksen er afgørende for at skabe effektive Power Query-formler. Husk følgende punkter:

• M Sprog skelner mellem store og små bogstaver, så vær forsigtig med brug af store og små bogstaver.

• Brug firkantede parenteser [] til at referere til kolonner, poster og lister.

• For at referere til et tidligere trin i din forespørgsel, brug dets variabelnavn (f.eks. #”Trinnavn”).

• Udskriv et forespørgselsformeltrin med "in"-sætningen.

Efterhånden som du får erfaring med , vil du opdage nye måder at transformere og analysere dine data på, hvilket gør dine Excel-projektmapper til et endnu mere værdifuldt aktiv.

Se videoen nedenfor, det er tid til at få skrubbebørsten frem og rydde op i dine rodede data.

Sådan opdateres dataforbindelser

En af nøglefunktionerne i Power Query er muligheden for nemt at opdatere din Power Query-forbindelse.

Når dine datakilder er blevet opdateret, og du har brug for de seneste oplysninger i din projektmappe, kan du opdatere dataforbindelserne i ruden Forespørgsler og forbindelser.

Hvis du ikke kan se ruden til højre for dit regneark, kan du åbne den ved at gå til datatabellen og klikke på "Forespørgsler og forbindelser".

Når ruden Forespørgsler og forbindelser er åben, viser den alle forespørgslerne. Følg disse trin:

1. Højreklik på den forespørgsel, du vil opdatere.

2. Vælg Opdater fra kontekstmenuen.

Dette vil opdatere din projektmappe med de seneste data fra dine tilsluttede datakilder.

Sådan integreres Power Query med datamodellen

Excels datamodel er en kraftfuld funktion, der giver dig mulighed for at skabe business intelligence-værktøjer, såsom pivottabeller, baseret på datarelationer.

Datamodellen er grundlæggende en skjult database, der indeholder de importerede data og relationer mellem tabeller.

Det er en del af Power Pivot-funktionen, som er indbygget i nuværende versioner af Excel. Du skal muligvis installere Power Pivot som et tilføjelsesprogram for at få adgang til det i tidligere versioner).

Du kan integrere dine Power Query-dataforbindelser med datamodellen med disse trin:

1. Indlæs dine data ved hjælp af Power Query som beskrevet i tidligere afsnit.

2. I stedet for at indlæse det direkte i projektmappen, skal du vælge Indlæs til fra menuen Indlæs.

3. Vælg Tilføj disse data til afkrydsningsfeltet Datamodel i dialogboksen Importer data.

Sådan automatiseres med Power Query og VBA

Med VBA-integration (Visual Basic for Applications) kan du oprette brugerdefinerede dataforbindelser og automatisere datatransformationstrin i Power Query.

Du kan udnytte dine eksisterende VBA-færdigheder til at bygge avancerede datamodeller ved at inkorporere data fra platforme som Microsoft Access, SharePoint og Analysis Services.

Selvom Power Query ikke har indbygget understøttelse af VBA, kan du bruge VBA-kode til at åbne, opdatere og manipulere Power Query-forespørgsler indirekte.

Dette giver dig mulighed for at skabe kraftfulde, automatiserede dataarbejdsgange og strømline din dataanalyseproces.

Her er et eksempel på kode, der opdaterer en power-forespørgsel i den aktuelle projektmappe.

:

Dim wb As Workbook, pq As Object

Indstil wb = ThisWorkbook

Indstil pq = wb.Queries(“Kundenavne”)

 pq. Opdater

Her er koden i en Excel VBA-makro:

Power Query avancerede funktioner

Excels Power Query tilbyder et væld af avancerede funktioner til at forenkle og forbedre din dataanalyseproces. Ved at lære, hvordan du effektivt bruger disse funktioner, kan du tage dine dataanalysefærdigheder til et helt nyt niveau.

De indbyggede dataanalyseværktøjer omfatter:

• Betingede kolonner til at anvende kompleks logik på dine datatransformationer.

• Grupperings- og aggregeringsfunktioner for at opsummere dine data på en meningsfuld måde.

• Pivot og Unpivot operationer for at omforme dine data for lettere analyse.

Du finder mange af disse funktioner på fanen Transformer i Power Query Editor.

Derudover kan du forbinde Power Query med Business Intelligence for mere avancerede dataanalyse- og visualiseringsmuligheder .

Samarbejde og deling

At dele dine dataforbindelser, forespørgsler og transformationer med dine teammedlemmer er afgørende for effektivt samarbejde.

Du kan dele en projektmappe med forespørgsler ved at gemme den på et netværksdrev, SharePoint eller en anden delt lagerplacering.

Du kan også eksportere M-koden bag en enkelt forespørgsel med disse trin:

1. Rediger forespørgslen i Power Query Editor.

2. Klik på "Avanceret editor" på fanen Hjem.

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

4. Åbn en teksteditor (f.eks. Notesblok), og indsæt den kopierede M-kode i teksteditoren.

5. Gem tekstfilen og send den til din kollega.

Lad os afslutte dette

Med en række funktioner og dens brugervenlige grænseflade er Power Query et vigtigt værktøj for Excel-brugere, der ønsker at arbejde effektivt med eksterne data.

Denne værdifulde funktion hjælper med at strømline processen med oprettelse af rapporter, datarensning og udførelse af komplekse analyser på dine datatabeller. Ved brug af

Power Query sparer dig i sidste ende tid og øger effektiviteten, når du arbejder med Excel-filer.