Opprett Power Query-formler i Excel

Redigeringsprogrammet for Power Query gir en dataspørring og formingsopplevelse for Excel du kan bruke til å endre form på data fra mange datakilder. Hvis du vil vise Power Query Editor-vinduet, kan du importere datafra eksterne datakilder i et Excel regneark, merke en celle i dataene og deretter velge Spørring > Rediger. Nedenfor finner du et sammendrag av hovedkomponentene.

1. Båndet i Redigeringsprogram for Power Query 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 brukes på dataene

5. Spørringsruten Innstillinger 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åket, 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 et 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 spørringsruten Innstillinger brukte trinn og på 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 NETTADRESSE-boksen, og velger deretter OK.

2. Velg Resultater [Rediger]-tabellen til venstre i dialogboksen Navigator, og velg deretter Transformer data nederst. Power Query-redigeringsprogrammet vises.

3. Hvis du vil endre standard spørringsnavn, sletter du Resultater [Innstillinger Rediger] under Egenskaper i spørringsruten, og deretter skriver du inn UEFA-mestere.

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

5. Hvis du vil fjerne uønskede verdier, velger du Kolonne1, velg Hjem-> Erstattverdier, skriv inn «detaljer» i boksen Verdier som skal søkes etter, og velg deretter OK.

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

7. Hvis du vil gi nytt navn til kolonneoverskriftene, dobbeltklikker du på hver av dem og endrer 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 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}})
Fjernet 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 i 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 til å være meningsfulle	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Viktig    Vær forsiktig med å redigere trinnene Kilde, Navigasjonog Endret type fordi de er opprettet 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.

Spreen formel på formellinjen

1. Hvis du vil åpne en spørring, finner du en tidligere lastet inn fra Redigeringsprogram for Power Query, 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 Innstillinger du vil redigere, under Brukte trinni spørringsruten.

3. Finn og endre parameterverdiene på formellinjen, og velg deretter 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 Enter -ikonet, 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 store bokstaver ved hjelp av funksjonen Tekst.Riktig.

1. Hvis du vil åpne en tom spørring, velger Excel Data> Hent data >Fra andre kilder > Tom spørring. 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 bryte en spørring.  Kontroller alltid resultatene i Forhåndsvisning av data.

Viktig    Vær forsiktig med å redigere trinnene Kilde, Navigasjonog Endret type fordi de er opprettet 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 tidligere lastet inn fra Redigeringsprogram for Power Query, 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 Innstillinger -ikonet for trinnet du vil redigere, under Brukte trinn i spørringsruten, eller høyreklikk trinnet, 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 det til et spørringstrinn under gjeldende spørringstrinn. men når du setter inn et spørringstrinn midt i trinnene, kan det oppstå en feil i etterfølgende trinn. Power Query viser en advarsel om sett inn trinn 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 trinnet som er satt inn.

1. Velg trinnet du vil Innstillinger skal komme rett foran det nye trinnet og den tilhørende formelen, underBrukte trinn i spørringsruten.

2. Velg ikonet Legg til venstre for formellinjen. Du kan også høyreklikke 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 Innstillinger-ruten underBrukte trinn,og velg deretter Flytt opp eller Flytt ned .

Slett trinn

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

I dette eksemplet konverterer vi teksten i en kolonne til store og små bokstaver 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 store og små bokstaver. 

Før:

Etter:

Når du oppretter en avansert spørring, oppretter du en serie med spørringsformeltrinn basert på la 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 inndelingen 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åket mellom store og små bokstaver.

Fase 1: Åpne Avansert redigering

1. I Excel velger du Data>Hent data >Andre kilder > Tom spørring. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel.

2. I Redigeringsprogrammet for Power Query velger du Hjem->Avansert redigering, som åpnes med en mal for la-uttrykket.

Fase 2: Definere datakilden

1. Opprett la-uttrykket ved hjelp av Excel. CurrentWorkbook fungerer på følgende måte:
   
   let#x1
   
   in
       Source
   
   

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

Resultat:

Fase 3: Heve den første raden til overskrifter

1. Hvis du vil åpne spørringen, merker du en celle i dataene fra regnearket, og deretter velger du > 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> Advanced Editor i Power Query Editor, som åpnes med setningen du opprettet i fase 2: Definer datakilden.

3. I la-uttrykket legger du til #"Første rad som topptekst" og Table.PromoteHeaders fungerer 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 i et regneark, velger du Ferdig ogderetter Hjem> Lukk & Last > Lukk & Last inn.

Resultat:

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

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

2. Velg Home> Advanced Editor i Power Query Editor, 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 forrige formeltrinn for første rad som topptekst, legger til #"Stor forbokstav hvert ord" i datakilden, og tilordner deretter #"Stor bokstav i hvert ord" 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 i et regneark, velger du Ferdig ogderetter Hjem> Lukk & Last > Lukk & Last inn.

Resultat:

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

Vise eller skjule formellinjen

1. Velg Fil>Alternativer og Innstillinger > spørringsalternativer.

2. Velg Power Query Editor under GLOBALi den venstre ruten.

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

Aktivere eller deaktivere M Intellisense

1. Velg Fil>Alternativer og Innstillinger > spørringsalternativer .

2. Velg Power Query Editor under GLOBALi den venstre ruten.

3. Velg eller fjern merket for Aktiver M Intellisensei formellinjen, avansert redigering og dialogboks for egendefinerte kolonner under Formel i ruten til høyre.

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