Skapa Power Query-formler i Excel

I Power Query-redigeraren finns en datafråga och formning för Excel som du kan använda för att forma data från många datakällor. Du visar fönstret Power Query-redigeraren genom att importera data från externa datakällori ett Excel-kalkylblad, markera en cell i dina data och sedan välja Fråga > Redigera. Nedan följer en sammanfattning av huvudkomponenterna.

1. Menyfliksområdet i Power Query-redigeraren som du använder för att forma data

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

3. Snabbmenyer som är praktiska genvägar till kommandon i menyfliksområdet

4. Förhandsgranskningen som visar resultatet av stegen som tillämpats på data

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

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

Det kan finnas tillfällen då du vill ändra eller skapa en formel. Formler använder formelspråket Power Query, 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 Formelspråk i Power Query M.

I fönstret Frågeinställningar, under Tillämpade steg,skapas frågesteg och motsvarande formler för varje uppgift. Nedan följer en visning av vissa frågedata och ett detaljerat exempel på relationen mellan steg och formler i den frågan.

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 (som 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}})
Andra kolumner borttagna 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"))
Kolumner som har bytt namn Ä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 syns kan du visa det igen.

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

En formel i formelfältet visas i ette-postmeddelande

1. Om du vill öppna en fråga letar du upp en tidigare inläst från Power Query-redigeraren, markerar en cell i dina 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 det steg du vill redigera.

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

4. Välj returikonen eller tryck på Retur för att se det nya resultatet som visas i dataförhandsgranskningen.

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

Som exempel på en enkel formel kommer vi att konvertera ett textvärde till första fodral med hjälp av funktionen Text.Proper.

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. I formelfältet anger du=Text.Proper("text value")och väljer sedan returikonen 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 du potentiellt bryta en fråga.  Verifiera alltid resultaten 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 i en dialogruta

Med den här metoden används dialogrutor som varierar beroende på steget. Du behöver inte känna till syntaxen för formeln.

1. Om du vill öppna en fråga letar du upp en tidigare inläst från Power Query-redigeraren, markerar en cell i dina 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,underTillämpade steg, väljer du ikonen för för det steg du vill redigera, eller högerklickar 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 det uppstå ett fel i efterföljande steg. I Power Query visas varningen Infoga steg när du försöker infoga ett nytt steg och i det nya steget ändras 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ämpadesteg, väljer du det steg som du vill ska följa omedelbart före det nya steget och dess formel.

2. Välj ikonen Lägg 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. Ange den nya formeln i följande format:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Anta till exempel att du har en tabell med kolumnen Kön och du 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 Inställningar för frågor under Tillämpade steghögerklickar du på steget och väljer sedan Flytta upp eller Flytta ned.

Delete steps

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

I det här exemplet ska vi konvertera texten i en kolumn till ett första fodral med hjälp av en kombination av formler i den avancerade redigeraren. 

Du har till exempel en Excel-tabell med namnet Order, med en ProductName-kolumn som du vill konvertera till ett första fall. 

Före

Efter

När du skapar en avancerad fråga skapar du en serie frågeformelsteg som baseras på let-uttrycket.  Använd let expression 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 i avsnittet "Skapa en formel i formelfältet".

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

Du ser att varje steg bygger på ett tidigare steg genom att referera till ett stegs namn. Som en påminnelse är formelspråket Power Query case-sensitive.

Fas 1: Öppna den avancerade redigeraren

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 let med hjälp av funktionen Excel.CurrentWorkbook enligt följande
   
   
   
   in
   #x1let:     Source
   
   

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

Resultat:

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

1. Du öppnar frågan genom att markera en cell i dina data i kalkylbladet och sedan välja 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 uttrycket som du skapade i Fas 2: Definiera datakällan.

3. I let-uttrycket lägger du till #"First Row as Header" och Table.PromoteHeaders så här
   
   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 Klaroch sedan Start> Stäng & Läs > Stäng & Läs in.

Resultat:

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

1. Du öppnar frågan genom att markera en cell i dina data i kalkylbladet och sedan välja 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 som du skapade i fas 3:Höja den första raden till rubriker .

3. I let-uttrycket konverterar du varje ProductName-kolumnvärde till egen text med hjälp av funktionen Table.TransformColumns, refererar till den föregående frågeformelsteget "First Row as Header", lägger till #"Capitalized Each Word" i datakällan och tilldelar sedan in resultatet med #"Capitalized Each Word".
   
   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 Klaroch sedan Start> Stäng & Läs > Stäng & Läs in.

Resultat:

Du kan styra formelfältets beteende i Power Query-redigeraren för alla dina arbetsböcker.

Visa eller dölja formelfältet

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

2. I den vänstra rutan under GLOBAL väljerdu Power Query-redigeraren.

3. I det högra fönstret under Layoutmarkerar eller avmarkerar du Visa formelfältet.

Aktivera eller inaktivera M IntelliSense

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

2. I den vänstra rutan under GLOBAL väljerdu Power Query-redigeraren.

3. I det högra fönstret under Formelmarkerar eller avmarkerar du Aktivera M Intellisense i formelfältet, den avancerade redigeraren och dialogrutan Anpassad kolumn.

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