Hur man använder Power Query i Excel: Den kompletta guiden

Power Query är ett kraftfullt business intelligence-verktyg i Excel som gör att du kan importera, rensa och transformera data efter dina behov. Om du någonsin har ägnat timmar åt att rensa upp data (vi har) eller kämpat för att slå samman data från olika källor (det har vi också), kommer den här funktionen att göra ditt liv enklare.

Det bästa sättet att lära sig hur man använder Power Query i Excel är att utföra några vanliga uppgifter som att importera data och använda Power Query Editor för att transformera de resulterande tabellerna och kombinera data.

Den här artikeln kommer igång genom att visa tydliga . Genom att bemästra Power Query kan du lyfta ditt arbete till nästa nivå.

Är du redo? Nu gör vi det.

Innehållsförteckning

• Hur man vet om du kan komma åt Power Query
• Hur man importerar data med Power Query
• Hur man använder Power Query Editor
  • Förhandsgranskning av data
  • Egenskaper
• Hur man arbetar med flera datakällor
• Hur man lägger till data med Power Query
• Hur man sammanfogar data med Power Query
• 5 sätt att transformera data med Power Query
  • 1. Expandera och kondensera data
  • 2. Sortering av data
  • 3. Formatera data
  • 4. Lägga till och ta bort kolumner
  • 5. Ändra datatyper
• Arbeta med formler och M Language
  • Formelbar
  • Att skriva och ändra M-kod
• Vad är M Language Syntax?
• Hur man uppdaterar dataanslutningar
• Hur man integrerar Power Query med datamodellen
• Hur man automatiserar med Power Query och VBA
• Power Query Avancerade funktioner
• Samarbete och delning
• Låt oss avsluta detta

Hur man vet om du kan komma åt Power Query

Innan du börjar använda Power Query måste du vara säker på att du har en version av Excel som har funktionen.

Här är en kort översikt över Power Query per version:

• Excel 2010 och Excel 2013: tillgängligt som ett tillägg som behöver laddas ner och installeras.

• Excel 2016: integrerat i Excel som "Hämta och transformera".

• Excel 2019: förbättrad prestanda och funktionalitet.

• Excel 2021 och Excel för Microsoft 365: integration med molndatakällor.

• Excel för Mac: endast tillgänglig från Microsoft 365 version 16.69 och senare.

För att verifiera att verktyget är integrerat i din Excel-arbetsbok (2016 eller senare), gå till fliken Data och leta efter avsnittet "Hämta och transformera data" i den här bilden:

Om du har Excel 2010 eller 2013 på Windows kan du ladda ner , installera och aktivera Power Query-tillägget.

Hur man importerar data med Power Query

Med Power Query-verktyget kan du importera och ansluta till ett brett utbud av datakällor:

• Filer, inklusive CSV-, XML- och JSON-format

• Webbadresser eller API-slutpunkter

• Databaser, inklusive Access, SQL Server, Oracle och de flesta moderna databaser

• Azure-tjänster, SharePoint-filer och OData-flöden

Den enklaste datakällan att börja med är en enkel CSV-fil med flera rader data. Skapa en textfil och lägg till dessa fyra för- och efternamn, separerade med kommatecken:

• Joe, bloggar

• Anne, Ryan

• James, Stewart

• Mary, Brown

Se till att spara filen med filtillägget .CSV. Din fil kommer att se ut så här:

För att ladda data med Power Query, följ dessa steg:

1. Gå till fliken Data.

2. Klicka på knappen "Hämta filer" på fliken Hämta och transformera data.

3. Välj din exempelfil.

Du kan behöva vänta några sekunder för att verktyget ska hämta data till förhandsgranskningsfönstret. Power Query använder ganska mycket maskinresurser även för en liten import, så ha tålamod människor.

Tips: stäng av andra applikationer för att öka prestandan.

Power Query visar fönstret "Text/CSV-import" med en förhandsgranskning av dina data.

Om data ser korrekta ut kan du fortsätta att ladda eller transformera data:

• knappen Ladda laddar data direkt till Excel.

• Med knappen Transformera data kan du ändra data innan du laddar den.

Har du märkt att exempelfilen inte hade en rubrik? Om det laddas direkt i Excel kommer rubrikerna att vara "Kolumn 1" och "Kolumn 2".

Istället kan du hämta data till Power Query-redigeraren och göra några ändringar. För att göra det, klicka på knappen "Transformera data".

Power Query Editor öppnas nu, vilket tar oss till nästa avsnitt.

Hur man använder Power Query Editor

I det här avsnittet kommer vi att utforska några viktiga aspekter av att arbeta med Power Query Editor.

Förhandsgranskning av data

I området för förhandsgranskning av data kan du visa och interagera med dina importerade data. Du kan filtrera rader, ändra kolumndatatyper och omforma data för att passa dina behov.

När du är klar kommer Excel att ladda de omvandlade data till ett kalkylblad.

Med hjälp av exempelfilen kan du redigera kolumnnamnen direkt i den här rutan.

Att göra så:

1. Högerklicka på kolumnrubriken.

2. Klicka på "Byt namn" i rullgardinsmenyn.

3. Skriv de önskade namnen i rubrikrutorna.

När du gör ändringar uppdateras avsnittet "Tillämpade steg" till höger om förhandsgranskningsfönstret för att spegla varje transformation, så att du kan spåra och ändra dina databearbetningssteg efter behov.

Egenskaper

Att ställa in egenskaper för dina frågor är viktigt för att organisera och spåra ditt arbete.

Frågeinställningar-fönstret finns till höger om Power Query-fönstret.

1. Klicka på länken "Alla egenskaper".

2. Redigera namnet på frågan så att det är beskrivande.

3. Lägg till en beskrivning för att ge mer information.

Det är lätt att glömma vad förra veckans frågor var inställda för att göra. I det här fallet har frågenamnet ändrats och en beskrivning har lagts till som anger syftet med frågan.

Om du vill lära dig mer om de tillgängliga alternativen, kolla in det här.

Hur man arbetar med flera datakällor

Power Query Editor erbjuder två primära metoder för att kombinera data från flera källor: lägga till och sammanfoga.

Att lägga till sammanfogar tabeller vertikalt och staplar dem ovanpå varandra. Genom att slå samman kan du slå samman tabeller baserade på vanliga kolumner.

För att se hur detta fungerar, skapa en andra liten CSV-fil med samma struktur som den första. Den här gången lägger du in två namn (första och sista) i filen:

• Joe, bloggar

• Laura, Cane

Lägg märke till att ett av dessa namn upprepas från föregående fil.

Följ nu dessa steg för att hämta data till din Microsoft Excel-arbetsbok.

1. Stäng Power Query Editor-fönstret och spara dina ändringar.

2. Lägg till ett nytt kalkylblad.

3. Följ stegen i föregående avsnitt för att importera den andra filen.

4. Klicka på knappen Transformera.

5. Byt namn på kolumnrubrikerna till "Förnamn" och "Efternamn".

Den högra rutan i Excel-kalkylbladet visar aktuella frågor och anslutningar. Du bör nu se två frågor i rutan.

Hur man lägger till data med Power Query

I det här avsnittet kommer du att lägga till den andra CSV-exempelfilen till den första.

Följ dessa steg för att lägga till data:

1. Högerklicka på den första tabellen (kundnamn) i rutan Frågor och anslutningar.

2. Välj "Redigera" för att öppna tabellen i Power Query Editor.

3. Klicka på Lägg till frågor i avsnittet Kombinera.

4. Välj den andra tabellen från rullgardinsmenyn med tabeller som ska läggas till.

Resultatet blir två extra rader i den första tabellen.

Lägg märke till att en rad upprepas – så här fungerar append-operationen. Den lägger till data från en tabell till en annan, oavsett om data redan finns.

Du kan ta bort dubblettdata i Power Query Editor med dessa steg:

1. Klicka på kolumnrubriken "Förnamn".

2. Håll ned "Ctrl"-tangenten (eller "Cmd"-tangenten på Mac).

3. Klicka på båda kolumnrubrikerna.

4. Klicka på "Ta bort rader" på fliken Hem i menyfliksområdet Power Query Editor.

5. Välj "Ta bort dubbletter" från rullgardinsmenyn.

Hur man sammanfogar data med Power Query

När du slår samman data måste du välja de matchande kolumnerna mellan frågorna.

Anta att du har två kommaavgränsade filer där den ena innehåller kundtelefonnummer och den andra innehåller adresser. De två filerna ser ut så här:

Följ instruktionerna i de föregående avsnitten för att ladda varje fil i sin egen power-fråga. Sedan kan du öva på att slå samman tabellerna.

När det gäller våra exempeldata ska båda kolumnerna (Förnamn och Efternamn) väljas som matchande kolumner. Håll ned Ctrl- tangenten för att välja flera kolumner.

Följ dessa steg:

1. Högerklicka på den första tabellen i rutan Frågor och anslutningar.

2. Välj "Redigera" för att öppna tabellen i Power Query Editor.

3. Välj "Sammanfoga frågor" från gruppen "Kombinera".

4. Välj den andra tabellen i rullgardinsmenyn med tabeller.

5. Klicka på den första kolumnen i den första tabellen.

6. Håll ned Ctrl-tangenten och klicka på den andra kolumnen i den första tabellen.

7. Håll ned Ctrl-tangenten och klicka på båda kolumnerna i den andra tabellen.

8. Behåll standardanslutningsoperatören som "Left Outer".

9. Klicka på "OK" för att tillämpa ändringarna.

"Left Outer"-anslutningsoperatören säkerställer att dubbletter av data elimineras.

I Power Query Editor ser du en ny kolumn med namnet "Tabell" eller "Inkapslad tabell" som innehåller sammanslagna data från den andra frågan.

1. Klicka på expanderingsikonen (två pilar) i rubriken i denna kolumn.

2. Avmarkera rutan "använd originalkolumn som prefix" (detta undviker långa kolumnnamn).

3. Klicka på "OK".

4. Klicka på "Stäng och ladda".

Den sammanslagna tabellen hade fyra kolumner, med telefon- och adressuppgifter sammanslagna. Du kanske fortfarande har lite att göra i ordning.

Du kanske till exempel vill redigera namnet på tabellen (Merge1) och adresskolumnen i bilden nedan.

5 sätt att transformera data med Power Query

Det är enkelt att transformera data i din Excel-fil med Power Query, vilket gör att du kan rensa, organisera och manipulera dina data effektivt.

Några av de vanligaste sätten att transformera data är:

• Expandera och kondensera data

• Sortering av data

• Formatera data

• Lägga till och ta bort kolumner

• Ändra datatyper

1. Expandera och kondensera data

Att expandera och kondensera data är viktigt för att göra dina datauppsättningar mer hanterbara. Power Query låter dig expandera eller komprimera tabeller för en mer organiserad vy.

Det föregående avsnittet visar hur du expanderar genom att inkludera data från relaterade tabeller.

Du kan också kondensera den till att endast inkludera sammanfattad data baserat på en valfri sammanställning som summa, antal eller procent.

2. Sortering av data

För att sortera data kan du snabbt ändra ordningen genom stigande eller fallande värden.

Klicka på nedåtpilen bredvid ett kolumnnamn och välj "Sortera stigande" eller "Sortera fallande".

3. Formatera data

Fliken Transform i Power Query Editor innehåller många alternativ.

Till exempel låter rullgardinsmenyn Format dig ändra all data i en kolumn eller hela tabellen till versaler eller gemener.

4. Lägga till och ta bort kolumner

Power Query förenklar processen att lägga till, ta bort och ändra kolumner.

För att ta bort en kolumn, högerklicka helt enkelt på rubriken i den oönskade kolumnen och välj "Ta bort." Detta tar permanent bort kolumnen och all dess data från tabellen.

Om du vill lägga till en ny kolumn väljer du den befintliga kolumnen där du vill att den nya ska visas. Använd sedan funktionen "Lägg till kolumn från exempel" för att skapa en anpassad kolumn eller för att utföra beräkningar mellan två eller flera kolumner.

5. Ändra datatyper

Att arbeta med konsekventa och korrekta datatyper är avgörande för korrekt analys. Med Power Query kan du enkelt ändra datatyper:

1. Högerklicka på kolumnrubriken.

2. Välj "Ändra typ".

3. Välj önskad datatyp från listan.

Att ändra datatyper kan vara särskilt användbart när man hanterar procentdata.

Till exempel kan du behöva konvertera decimaler till procenttal eller konvertera textbaserade procentsatser till numeriska för enklare beräkning och jämförelse.

Arbeta med formler och M Language

När du använder Power Query i Excel kommer du att stöta på M Language, som är hjärtat i att skapa avancerade formler, manipulera data och producera kraftfulla frågor. I det här avsnittet får du lära dig om:

• Formelbaren

• M-kod

• de väsentliga delarna av M Language-syntax

Formelbar

Formelfältet i Excels Power Query-redigerare är det område där du matar in dina formler och M-kod.

För att använda det, gå till Power Query-redigeraren genom att välja en fråga från rutan Frågor och anslutningar. Formelfältet finns ovanför förhandsgranskningsfönstret för data.

Att skriva och ändra M-kod

För att skapa mångsidiga och kraftfulla frågor i Excel måste du skriva och ändra Power Query-språket som kallas M-kod. Här är några tips om hur du kommer igång:

• Använd rutan Tillämpade steg för att skriva steg-för-steg-kod, vilket gör det enkelt att spåra och ändra din frågas logik.

• Kombinera M-kod med en Excel-tabell, pivottabeller och externa datakällor, som Azure och textfiler, för att skapa dynamiska och automatiserade rapporter.

• Justera frågealternativen för att förfina dina preferenser för dataladdning och förbättra prestandan.

När du bekantar dig med M-kod kommer du att upptäcka att du kan automatisera och optimera många processer, vilket gör din dataanalys i Excel mer effektiv och kraftfull.

För att se koden bakom en fråga, följ dessa steg:

1. Öppna frågan i Power Query Editor.

2. Klicka på "Advanced Editor" på fliken Hem.

Det här är koden bakom vårt exempel på sammanslagna tabell (din kan se annorlunda ut):

Vad är M Language Syntax?

Att förstå M Language-syntaxen är avgörande för att skapa effektiva Power Query-formler. Tänk på följande:

• M Språk är skiftlägeskänsligt, så var försiktig med versaler.

• Använd hakparenteser [] för att referera till kolumner, poster och listor.

• För att referera till ett tidigare steg i din fråga, använd dess variabelnamn (t.ex. #”Stegnamn”).

• Skriv ut ett frågeformelsteg med "in"-satsen.

När du får erfarenhet av kommer du att upptäcka nya sätt att transformera och analysera dina data, vilket gör dina Excel-arbetsböcker till en ännu mer värdefull tillgång.

Kolla in videon nedan, det är dags att ta fram skurborsten och rensa upp i din röriga data.

Hur man uppdaterar dataanslutningar

En av nyckelfunktionerna i Power Query är möjligheten att enkelt uppdatera din Power Query-anslutning.

När dina datakällor har uppdaterats och du behöver den senaste informationen i din arbetsbok, kan du uppdatera dataanslutningarna i rutan Frågor och anslutningar.

Om du inte ser rutan till höger om ditt kalkylblad kan du öppna den genom att gå till datatabellen och klicka på "Frågor och anslutningar".

När rutan Frågor och anslutningar är öppen listar den alla frågor. Följ dessa steg:

1. Högerklicka på den fråga du vill uppdatera.

2. Välj Uppdatera från snabbmenyn.

Detta kommer att uppdatera din arbetsbok med de senaste data från dina anslutna datakällor.

Hur man integrerar Power Query med datamodellen

Excels datamodell är en kraftfull funktion som låter dig skapa business intelligence-verktyg, som pivottabeller, baserat på datarelationer.

Datamodellen är i grunden en dold databas som innehåller importerad data och relationer mellan tabeller.

Det är en del av Power Pivot-funktionen som är inbyggd i nuvarande versioner av Excel. Du kan behöva installera Power Pivot som ett tillägg för att komma åt det i tidigare versioner).

Du kan integrera dina Power Query-dataanslutningar med datamodellen med dessa steg:

1. Ladda dina data med Power Query enligt beskrivningen i tidigare avsnitt.

2. Istället för att ladda den direkt i arbetsboken väljer du Ladda till från menyn Ladda.

3. Markera kryssrutan Lägg till denna data till datamodellen i dialogrutan Importera data.

Hur man automatiserar med Power Query och VBA

Med VBA-integrering (Visual Basic for Applications) kan du skapa anpassade dataanslutningar och automatisera datatransformationssteg i Power Query.

Du kan utnyttja dina befintliga VBA-kunskaper för att bygga avancerade datamodeller genom att införliva data från plattformar som Microsoft Access, SharePoint och Analysis Services.

Även om Power Query inte har inbyggt stöd för VBA, kan du använda VBA-kod för att öppna, uppdatera och manipulera Power Query-frågor indirekt.

Detta gör att du kan skapa kraftfulla, automatiserade dataarbetsflöden och effektivisera din dataanalysprocess.

Här är lite exempelkod som uppdaterar en power-fråga i den aktuella arbetsboken.

:

Dim wb As Workbook, pq As Object

Ställ in wb = ThisWorkbook

Ställ in pq = wb.Queries(“Kundnamn”)

 pq. Uppdatera

Här är koden i ett Excel VBA-makro:

Power Query Avancerade funktioner

Excels Power Query erbjuder en uppsjö av avancerade funktioner för att förenkla och förbättra din dataanalysprocess. Genom att lära dig hur du effektivt använder dessa funktioner kan du ta dina dataanalysfärdigheter till en helt ny nivå.

De inbyggda dataanalysverktygen inkluderar:

• Villkorliga kolumner för att tillämpa komplex logik på dina datatransformationer.

• Grupperings- och aggregeringsfunktioner för att sammanfatta din data på ett meningsfullt sätt.

• Pivot och Unpivot operationer för att omforma din data för enklare analys.

Du hittar många av dessa funktioner på fliken Transform i Power Query Editor.

Dessutom kan du ansluta Power Query till Business Intelligence för mer avancerade dataanalys- och visualiseringsalternativ .

Samarbete och delning

Att dela dina dataanslutningar, frågor och transformationer med dina teammedlemmar är avgörande för effektivt samarbete.

Du kan dela en arbetsbok med frågor genom att spara den på en nätverksenhet, SharePoint eller någon annan delad lagringsplats.

Du kan också exportera M-koden bakom en enda fråga med dessa steg:

1. Redigera frågan i Power Query Editor.

2. Klicka på "Advanced Editor" på fliken Hem.

3. Kopiera hela M-koden i fönstret Advanced Editor.

4. Öppna en textredigerare (t.ex. Anteckningar) och klistra in den kopierade M-koden i textredigeraren.

5. Spara textfilen och skicka den till din kollega.

Låt oss avsluta detta

Med en rad funktioner och dess användarvänliga gränssnitt är Power Query ett viktigt verktyg för Excel-användare som vill arbeta effektivt med externa data.

Denna värdefulla funktion hjälper till att effektivisera processen med att skapa rapporter, datarensning och utföra komplexa analyser av dina datatabeller. Använder sig av

Power Query sparar i slutändan tid och ökar effektiviteten när du arbetar med Excel-filer.