Opprett Power Query-formler i Excel

Den Power Query-redigering gir en dataspørring og formingsopplevelse for Excel som du kan bruke til å omforme data fra mange datakilder. Hvis du vil vise Power Query-redigering-vinduet, importerer du data fra eksterne datakilderi et Excel-regneark, merker en celle i dataene og velger deretter Spørring > Rediger. Nedenfor finner du et sammendrag av hovedkomponentene.

1. Båndet Power Query-redigering som du bruker til å forme dataene

2. Spørringer-ruten som du bruker til å finne datakilder og tabeller

3. Hurtigmenyer som er praktiske snarveier til kommandoer på båndet

4. Forhåndsvisning av data som viser resultatene av trinnene som er brukt på dataene

5. Spørringsinnstillinger-ruten som viser egenskaper og hvert trinn i spørringen

Bak kulissene er hvert trinn i en spørring basert på en formel som er synlig på formellinjen.

Noen ganger kan det hende du vil endre eller opprette en formel. Formler bruker Power Query formelspråk, som du kan bruke til å bygge både enkle og komplekse uttrykk. Hvis du vil ha mer informasjon om syntaks, argumenter, merknader, funksjoner og eksempler, kan du se Power Query M-formelspråk.

Hvis du bruker en liste over fotballmesterskap som eksempel, kan du bruke Power Query til å ta rådata som du fant på et nettsted, og gjøre det om til en velformatert tabell. Se hvordan spørringstrinn og tilsvarende formler opprettes for hver oppgave i ruten Spørringsinnstillinger under Brukte trinn og i formellinjen.

Fremgangsmåte

1. Hvis du vil importere dataene, velger du Data > Fra nettet, skriver inn «http://en.wikipedia.org/wiki/UEFA_European_Football_Championship» i nettadresseboksen , og velger deretter OK.

2. Velg Tabellen Resultater [Rediger] til venstre i dialogboksen Navigator, og velg deretter Transformer data nederst. Redigeringsprogrammet for Power Query vises.

3. Hvis du vil endre navnet på standardspørringen, sletter du «Resultater [Rediger]» i spørringsinnstillinger-ruten under Egenskaper, og deretter skriver du inn «UEFA champs».

4. Hvis du vil fjerne uønskede kolonner, merker du den første, fjerde og femte kolonnen, og deretter velger du Hjem > Fjern kolonne > Fjern andre kolonner.

5. Hvis du vil fjerne uønskede verdier, velger du Kolonne1, velger Hjem > Erstatt verdier, skriver inn detaljer i boksen Verdier som skal søkes, og velger deretter OK.

6. Hvis du vil fjerne rader som har ordet «År» i seg, velger du filterpilen i Kolonne1, fjerner merket i avmerkingsboksen ved siden av «År», og velger deretter OK.

7. Hvis du vil gi nytt navn til kolonneoverskriftene, dobbeltklikker du på hver av dem og endrer deretter «Kolonne1» til «År», «Kolonne4» til «Vinner» og «Kolonne5» til «Sluttresultat».

8. Hvis du vil lagre spørringen, velger du Hjem > Lukk & Last inn.

Resultat

Tabellen nedenfor er et sammendrag av hvert brukte trinn og den tilsvarende formelen.

Spørringstrinn og -oppgave	Formel
Kilde Koble til en nettdatakilde	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigasjon Velg tabellen du vil koble til	=Source{2}[Data]
Endret type Endre datatyper (som Power Query gjør automatisk)	= 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}})
Fjernede andre kolonner Fjerne andre kolonner for å bare vise kolonner av interesse	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Erstattet verdi Erstatte verdier for å rydde opp verdier i en merket kolonne	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrerte rader Filtrere verdier i en kolonne	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Kolonner med nytt navn Endret kolonneoverskrifter slik at de gir mening	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Viktig    Vær forsiktig med å redigere trinnene kilde, navigasjon  og endret type fordi de opprettes av Power Query for å definere og konfigurere datakilden.

Vise eller skjule formellinjen

Formellinjen vises som standard, men hvis den ikke er synlig, kan du vise den på nytt.

• Velg Vis > oppsett > formellinjen.

Edit en formel på formellinjen

1. Hvis du vil åpne en spørring, finner du en som tidligere er lastet inn fra Power Query-redigering, merker en celle i dataene og velger deretter Spørring > Rediger. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel.

2. Velg trinnet du vil redigere, under Brukte trinn i spørringsinnstillinger-ruten.

3. Finn og endre parameterverdiene på formellinjen, og velg deretter ikonet enter , eller trykk enter. Du kan for eksempel endre denne formelen slik at den også beholder Kolonne2:
   
   Før: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Etter:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Velg ikonet Enter , eller trykk enter for å se de nye resultatene som vises i forhåndsvisningen av data.

5. Hvis du vil se resultatet i et Excel-regneark, velger du Hjem > Lukk & Last inn.

Opprette en formel på formellinjen

For et enkelt formeleksempel kan vi konvertere en tekstverdi til stor forbokstav ved hjelp av Text.Proper-funksjonen.

1. Hvis du vil åpne en tom spørring, velger du Data > Hent data > fra andre kilder > tom spørring i Excel. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel.

2. Skriv inn=Text.Proper("text value")på formellinjen, og velg deretter enter- -ikonet, eller trykk enter.
   
   Resultatene vises i Forhåndsvisning av data .

3. Hvis du vil se resultatet i et Excel-regneark, velger du Hjem > Lukk & Last inn.

Resultat:

 Når du oppretter en formel, validerer Power Query formelsyntaksen. Når du setter inn, endrer rekkefølgen på eller sletter et mellomliggende trinn i en spørring, kan du imidlertid potensielt bryte en spørring.  Bekreft alltid resultatene i forhåndsvisning av data.

Viktig    Vær forsiktig med å redigere trinnene kilde, navigasjon  og endret type fordi de opprettes av Power Query for å definere og konfigurere datakilden.

Redigere en formel ved hjelp av en dialogboks

Denne metoden bruker dialogbokser som varierer avhengig av trinnet. Du trenger ikke å vite syntaksen for formelen.

1. Hvis du vil åpne en spørring, finner du en som tidligere er lastet inn fra Power Query-redigering, merker en celle i dataene og velger deretter Spørring > Rediger. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel.

2. Velg Rediger innstillinger- ikonet for trinnet du vil redigere, under Brukte trinn i spørringsinnstillinger-ruten, og velg deretter Rediger innstillinger.

3. Gjør endringene i dialogboksen, og velg deretter OK.

Sette inn et trinn

Når du har fullført et spørringstrinn som endrer formen på dataene, legges et spørringstrinn til under det gjeldende spørringstrinnet. men når du setter inn et spørringstrinn midt i trinnene, kan det oppstå en feil i de påfølgende trinnene. Power Query viser en advarsel om innsettingstrinn når du prøver å sette inn et nytt trinn, og det nye trinnet endrer felt, for eksempel kolonnenavn, som brukes i alle trinnene som følger det innsatte trinnet.

1. Velg trinnet du vil skal rett før det nye trinnet og tilhørende formel, under Brukte trinn i spørringsinnstillinger-ruten.

2. Velg ikonet Legg til trinn til venstre for formellinjen. Du kan også høyreklikke på et trinn og deretter velge Sett inn trinn etter. En ny formel opprettes i formatet :
   
   = <nameOfTheStepToReference>, for eksempel =Production.WorkOrder.

3. Skriv inn den nye formelen ved hjelp av formatet:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Anta for eksempel at du har en tabell med kolonnen Kjønn, og du vil legge til en kolonne med verdien «Ms». eller "Mr.", avhengig av personens kjønn. Formelen vil være:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Endre rekkefølgen på et trinn

• Høyreklikk trinnet i Ruten Innstillinger for spørringer under Brukte trinn, og velg deretter Flytt opp eller Flytt ned.

Slett trinn

• Velg ikonet Slett til venstre for trinnet, eller høyreklikk trinnet, og velg deretter Slett eller Slett til slutten. Ikonet Slett er også tilgjengelig til venstre for formellinjen.

I dette eksemplet konverterer vi teksten i en kolonne til stor forbokstav ved hjelp av en kombinasjon av formler i avansert redigering. 

Du har for eksempel en Excel-tabell, kalt Ordrer, med en ProductName-kolonne som du vil konvertere til stor forbokstav. 

Før:

Etter:

Når du oppretter en avansert spørring, oppretter du en serie med spørringsformeltrinn basert på let-uttrykket. Bruk la-uttrykket til å tilordne navn og beregne verdier som deretter refereres av in-setningen , som definerer trinnet. Dette eksemplet returnerer det samme resultatet som det i delen «Opprett en formel på formellinjen».

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

Du vil se at hvert trinn bygger på et tidligere trinn ved å referere til et trinn for navn. Som en påminnelse skiller Power Query formelspråk mellom store og små bokstaver.

Fase 1: Åpne avansert redigering

1. Velg Data > Hent data > andre kilder > tom spørring i Excel. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel.

2. Velg Hjem > > avansert redigering i Power Query-redigering, som åpnes med en mal for la-uttrykket.

Fase 2: Definer datakilden

1. Opprett la-uttrykket ved hjelp av Excel.CurrentWorkbook-funksjonen som følger:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Hvis du vil laste inn spørringen til et regneark, velger du Ferdig, og deretter velger du Hjem > Lukk & Last inn > Lukk & Last inn.

Resultat:

Fase 3: Heve den første raden til overskrifter

1. Hvis du vil åpne spørringen, velger du en celle i dataene fra regnearket, og deretter velger du Spørring > Rediger. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel (Power Query).

2. Velg Home > avansert redigering i Power Query-redigering, som åpnes med setningen du opprettet i fase 2: Definer datakilden.

3. Legg til #"Første rad som topptekst" og Table.PromoteHeaders-funksjonen som følger:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3    #"First Row as Header"
   

4. Hvis du vil laste inn spørringen til et regneark, velger du Ferdig, og deretter velger du Hjem > Lukk & Last inn > Lukk & Last inn.

Resultat:

Fase 4: Endre hver verdi i en kolonne til stor forbokstav

1. Hvis du vil åpne spørringen, velger du en celle i dataene fra regnearket, og deretter velger du Spørring > Rediger. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel.

2. Velg Hjem > avansert redigering i Power Query-redigering, som åpnes med setningen du opprettet i fase 3: Hev den første raden til overskrifter.

3. I la-uttrykket konverterer du hver ProductName-kolonneverdi til riktig tekst ved hjelp av Table.TransformColumns-funksjonen, som refererer til det forrige formeltrinnet «Første rad som overskrift», legger til #«Stor forbokstav for hver Word» i datakilden, og deretter tilordner #«Stor forbokstav hver Word» til 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. Hvis du vil laste inn spørringen til et regneark, velger du Ferdig, og deretter velger du Hjem > Lukk & Last inn > Lukk & Last inn.

Resultat:

Du kan kontrollere virkemåten til formellinjen i Power Query-redigering for alle arbeidsbøkene.

Vise eller skjule formellinjen

1. Velg alternativer og innstillinger for fil> > spørringsalternativer.

2. Velg Power Query-redigering under GLOBAL i den venstre ruten.

3. Velg eller fjern merket for Vis formellinjen under Oppsett i ruten til høyre.

Aktivere eller deaktivere M Intellisense

1. Velg > Alternativer og innstillinger > Spørringsalternativer .

2. Velg Power Query-redigering under GLOBAL i den venstre ruten.

3. Velg eller fjern merket for Aktiver M Intellisense i formellinjen, avansert redigering og egendefinert kolonnedialogboks underFormel i høyre rute.

Obs!    Hvis du endrer denne innstillingen, trer det i kraft neste gang du åpner Power Query-redigering-vinduet.