Skapa Power Query-formler i Excel

Bara med Power Query-redigeraren har du skapat Power Query-formler hela tiden. Låt oss se hur Power Query fungerar genom att titta under motorljuset. Du kan lära dig hur du uppdaterar eller lägger till formler genom att bara titta på Power Query-redigeraren i praktiken.  Du kan till och med skapa egna formler med den avancerade redigeraren.        

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.

Frågeredigeringsdelar

  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.

Exempel på formel i Frågeredigeraren

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.

Använd en lista med fotboll som exempel. Använd Power Query för att ta rådata som du hittar på en webbplats och omvandla dem till en välformaterad tabell.

Din webbläsare har inte stöd för video.

Procedur

  1. Om du vill importera data väljer du Data> Frånwebben , 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 Omvandla data längst ned. Power Query-redigeraren visas.

  3. Om du vill ändra standardfrågans namn tar du bort Resultat [Redigera]" underEgenskaper i fönstret Frågeinställningar och anger sedan "EUROPAM".

  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, >Ersättvärden , anger "information" i rutan Värden att söka efter och väljer sedan OK.

  6. Om du vill ta bort rader där ordet "År" finns i 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 "Final Score".

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

Resultat

Resultatet av genomgången – de första raderna

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 (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 Ikonen Retur till vänster om formelfältet i Power Query 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 Ikonen Retur till vänster om formelfältet i Power Query 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 Ikonen Retur till vänster om formelfältet i Power Query 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:

Text.Proper

 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 Ikon för Inställningar 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 Ikonen Funktion 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.")

Exempel på formel

Ä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 Delete steps till vänster om steget eller högerklicka på steget och välj sedan Ta bort eller Ta bort till slutet. Ikonen ta Delete steps ä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

Före

Efter

Steg 4 – Resultat

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.

Avancerad redigerare 3

Fas 2: Definiera datakällan

  1. Skapa uttrycket let med hjälp av funktionen Excel.CurrentWorkbook enligt följande



    in
    #x1let:     Source

    Steg 1 – Avancerad redigerare

  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:

Steg 1 – 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:

Steg 3 – 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:

Steg 4 – 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.

Se även

Power Query för Excel-hjälp

Använda listan Tillämpade steg (docs.com)

Använda anpassade funktioner (docs.com)

Power Query M-formler (docs.com)

Hantera fel (docs.com)https://docs.microsoft.com/en-us/power-query/dealing-with-errors

Behöver du mer hjälp?

Utöka dina Office-kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×