Skapa Power Query-formler i Excel

I Power Query-redigeraren finns en datafråga och formningsupplevelse för Excel som du kan använda för att omforma data från många datakällor. Om du vill visa Power Query-redigeraren fönstret importerar du data från externa datakällori ett Excel-kalkylblad, markerar en cell i data och väljer sedan Fråga > Redigera. Följande är en sammanfattning av huvudkomponenterna.

1. Det Power Query-redigeraren menyfliksområdet som du använder för att forma dina data

2. Fönstret Frågor som du använder för att hitta datakällor och tabeller

3. Snabbmenyer som är praktiska kortkommandon i menyfliksområdet

4. Dataförhandsgranskningen som visar resultatet av de steg som tillämpas på data

5. Fönstret Frågeinställningar med en lista över egenskaper och varje steg i frågan

I bakgrunden baseras varje steg i en fråga på en formel som visas i formelfältet.

Det kan finnas tillfällen när du vill ändra eller skapa en formel. Formler använder Power Query formelspråk, som du kan använda för att skapa både enkla och komplexa uttryck. Mer information om syntax, argument, kommentarer, funktioner och exempel finns i Power Query M-formelspråk.

Använd en lista över fotbollsmästerskap som exempel och använd Power Query för att ta rådata som du hittade på en webbplats och göra den till en välformaterad tabell. Se hur frågesteg och motsvarande formler skapas för varje aktivitet i fönstret Frågeinställningar under Tillämpade steg och i formelfältet.

Procedur

1. Om du vill importera data väljer du Data > Från webben, anger "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" i rutan URL och väljer sedan OK.

2. I dialogrutan Navigatör väljer du tabellen Resultat [Redigera] till vänster och väljer sedan Transformera data längst ned. Den Power Query redigeraren visas.

3. Om du vill ändra standardnamnet för frågan tar du bort "Resultat [Redigera]" under Egenskaper i fönstret Frågeinställningar och anger sedan "UEFA-mästare".

4. Om du vill ta bort oönskade kolumner markerar du den första, fjärde och femte kolumnen och väljer sedan Start > Ta bort kolumn > Ta bort andra kolumner.

5. Om du vill ta bort oönskade värden väljer du Kolumn1, Start > Ersätt värden, anger "information" i rutan Värden som ska sökas och väljer sedan OK.

6. Om du vill ta bort rader som har ordet "År" i sig väljer du filterpilen i Kolumn1, avmarkerar kryssrutan bredvid "År" och väljer sedan OK.

7. Om du vill byta namn på kolumnrubrikerna dubbelklickar du på var och en av dem och ändrar sedan "Kolumn1" till "År", "Kolumn4" till "Vinnare" och "Kolumn5" till "Slutresultat".

8. Om du vill spara frågan väljer du Start > Stäng & Läs in.

Resultat

Följande tabell är en sammanfattning av varje tillämpat steg och motsvarande formel.

Frågesteg och uppgift	Formel
Källa Anslut till en webbdatakälla	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigering Välj tabellen du vill ansluta till	=Source{2}[Data]
Ändrad typ Ändra datatyper (vilket Power Query gör automatiskt)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Borttagna andra kolumner Ta bort andra kolumner för att endast visa kolumner av intresse	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Ersatt värde Ersätta värden för att rensa värden i en markerad kolumn	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrerade rader Filtrera värden i en kolumn	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Namnändrade kolumner Ändrade kolumnrubriker till meningsfulla	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Viktigt    Var försiktig när du redigerar stegen Källa, Navigering  och Ändrad typ eftersom de skapas av Power Query för att definiera och konfigurera datakällan.

Visa eller dölja formelfältet

Formelfältet visas som standard, men om det inte visas kan du visa det igen.

• Välj Visa > Layout > formelfältet.

Edit en formel i formelfältet

1. Om du vill öppna en fråga letar du reda på en som tidigare lästs in från Power Query-redigeraren, markerar en cell i data och väljer sedan Fråga > Redigera. Mer information finns i Skapa, läsa in eller redigera en fråga i Excel.

2. Välj det steg du vill redigera under Tillämpade steg i fönstret Frågeinställningar.

3. Leta reda på och ändra parametervärdena i formelfältet och välj sedan ikonen Ange eller tryck på Retur. Ändra till exempel den här formeln för att även behålla Kolumn2:
   
   Före: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Efter:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Välj ikonen Ange eller tryck på Retur för att se de nya resultaten som visas i förhandsgranskningen av data.

5. Om du vill se resultatet i ett Excel-kalkylblad väljer du Start > Stäng & Läs in.

Skapa en formel i formelfältet

För ett enkelt formelexempel kan vi konvertera ett textvärde till inledande versaler med hjälp av funktionen Text.Initial.

1. Om du vill öppna en tom fråga i Excel väljer du Data > Hämta data > Från andra källor > Tom fråga. Mer information finns i Skapa, läsa in eller redigera en fråga i Excel.

2. Skriv=Text.Proper("text value")i formelfältet och välj sedan ikonen Ange eller tryck på Retur.
   
   Resultatet visas i dataförhandsgranskningen.

3. Om du vill se resultatet i ett Excel-kalkylblad väljer du Start > Stäng & Läs in.

Resultat:

 När du skapar en formel verifierar Power Query formelsyntaxen. Men när du infogar, ändrar ordning på eller tar bort ett mellanliggande steg i en fråga kan en fråga brytas.  Kontrollera alltid resultatet i dataförhandsgranskningen.

Viktigt    Var försiktig när du redigerar stegen Källa, Navigering  och Ändrad typ eftersom de skapas av Power Query för att definiera och konfigurera datakällan.

Redigera en formel med hjälp av en dialogruta

Den här metoden använder dialogrutor som varierar beroende på steg. Du behöver inte veta syntaxen för formeln.

1. Om du vill öppna en fråga letar du reda på en som tidigare lästs in från Power Query-redigeraren, markerar en cell i data och väljer sedan Fråga > Redigera. Mer information finns i Skapa, läsa in eller redigera en fråga i Excel.

2. I fönstret Frågeinställningar under Tillämpade steg väljer du ikonen Redigera inställningar för det steg du vill redigera eller högerklicka på steget och väljer sedan Redigera inställningar.

3. Gör ändringarna i dialogrutan och välj sedan OK.

Infoga ett steg

När du har slutfört ett frågesteg som omformar dina data läggs ett frågesteg till under det aktuella frågesteget. men när du infogar ett frågesteg mitt i stegen kan ett fel uppstå i efterföljande steg. Power Query visar en varning för Infoga steg när du försöker infoga ett nytt steg och det nya steget ändrar fält, till exempel kolumnnamn, som används i något av stegen som följer det infogade steget.

1. I fönstret Frågeinställningar , under Tillämpade steg, väljer du det steg du vill omedelbart föregå det nya steget och dess motsvarande formel.

2. Välj ikonen Lägg till steg till vänster om formelfältet. Du kan också högerklicka på ett steg och sedan välja Infoga steg efter. En ny formel skapas i formatet :
   
   = <nameOfTheStepToReference>, till exempel =Production.WorkOrder.

3. Skriv in den nya formeln med formatet:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Anta till exempel att du har en tabell med kolumnen Kön och vill lägga till en kolumn med värdet "Ms". eller "Herr", beroende på personens kön. Formeln skulle vara:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Ändra ordning på ett steg

• I fönstret Frågeinställningar under Tillämpade steg högerklickar du på steget och väljer Flytta upp eller Flytta ned.

Delete steps

• Välj ikonen Ta bort till vänster om steget eller högerklicka på steget och välj sedan Ta bort eller Ta bort till slutet. Ikonen Ta bort är också tillgänglig till vänster om formelfältet.

I det här exemplet ska vi konvertera texten i en kolumn till inledande versaler med hjälp av en kombination av formler i Avancerad redigerare. 

Du har till exempel en Excel-tabell med namnet Order med en ProductName-kolumn som du vill konvertera till inledande versaler. 

Före

Efter

När du skapar en avancerad fråga skapar du en serie frågeformelsteg baserat på let-uttrycket. Använd uttrycket let för att tilldela namn och beräkna värden som sedan refereras av in-satsen , som definierar Steget. Det här exemplet returnerar samma resultat som det i avsnittet "Skapa en formel i formelfältet".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Du ser att varje steg bygger på ett föregående steg genom att hänvisa till ett steg för namn. Som en påminnelse är Power Query formelspråk skiftlägeskänsligt.

Fas 1: Öppna Avancerad redigerare

1. I Excel väljer du Data > Hämta data > Andra källor > tom fråga. Mer information finns i Skapa, läsa in eller redigera en fråga i Excel.

2. I Power Query-redigeraren väljer du Start > Avancerad redigerare, som öppnas med en mall för let-uttrycket.

Fas 2: Definiera datakällan

1. Skapa uttrycket med hjälp av excel.currentworkbook-funktionen på följande sätt:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Om du vill läsa in frågan i ett kalkylblad väljer du Klar och sedan Start > Stäng & Läs in > Stäng & Läs in.

Resultat:

Fas 3: Höj den första raden till rubriker

1. Om du vill öppna frågan markerar du en cell i data i kalkylbladet och väljer sedan Fråga > Redigera. Mer information finns i Skapa, läsa in eller redigera en fråga i Excel (Power Query).

2. I Power Query-redigeraren väljer du Start > Avancerad redigerare, som öppnas med det uttryck som du skapade i fas 2: Definiera datakällan.

3. I uttrycket let lägger du till #"First Row as Header" och Table.PromoteHeaders på följande sätt:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. Om du vill läsa in frågan i ett kalkylblad väljer du Klar och sedan Start > Stäng & Läs in > Stäng & Läs in.

Resultat:

Fas 4: Ändra varje värde i en kolumn till inledande versaler

1. Om du vill öppna frågan markerar du en cell i data i kalkylbladet och väljer sedan Fråga > Redigera. Mer information finns i Skapa, läsa in eller redigera en fråga i Excel.

2. I Power Query-redigeraren väljer du Start > Avancerad redigerare, som öppnas med det uttryck du skapade i fas 3: Höj den första raden till rubriker.

3. I let-uttrycket konverterar du varje ProductName-kolumnvärde till rätt text med hjälp av funktionen Table.TransformColumns, refererar till det föregående frågeformelsteget "Första raden som rubrik", lägger till #"Versaliserade varje Word" till datakällan och tilldelar sedan #"Versaliserade varje Word" till resultatet.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. Om du vill läsa in frågan i ett kalkylblad väljer du Klar och sedan Start > Stäng & Läs in > Stäng & Läs in.

Resultat:

Du kan styra hur formelfältet fungerar i Power Query-redigeraren för alla arbetsböcker.

Visa eller dölja formelfältet

1. Välj Fil > Alternativ och Inställningar > Frågealternativ.

2. Välj Power Query-redigeraren under GLOBAL i den vänstra rutan.

3. I den högra rutan under Layout markerar eller avmarkerar du Visa formelfältet.

Aktivera eller inaktivera M Intellisense

1. Välj Fil > Alternativ och Inställningar > Frågealternativ .

2. Välj Power Query-redigeraren under GLOBAL i den vänstra rutan.

3. I det högra fönstret under Formel markerar eller avmarkerar du Aktivera M Intellisense i formelfältet, dialogrutan Avancerad redigerare och anpassad kolumn.

Obs!    När du ändrar den här inställningen börjar den gälla nästa gång du öppnar Power Query-redigeraren fönstret.