Oprette formler til Power-forespørgsler i Excel

Den Power Query-editor indeholder en dataforespørgsels- og -formateringsoplevelse i Excel, som du kan bruge til at omforme data fra mange datakilder. Hvis du vil have vist vinduet Power Query-editor, skal du importere data fra eksterne datakilderi et Excel-regneark, markere en celle i dataene og derefter vælge Forespørgsel > Rediger. Følgende er en oversigt over hovedkomponenterne.

1. Det Power Query-editor bånd, du bruger til at forme dine data

2. Ruden Forespørgsler, som du bruger til at finde datakilder og tabeller

3. Genvejsmenuer, der er praktiske genveje til kommandoer på båndet

4. Dataeksempel, der viser resultaterne af de trin, der er anvendt på dataene

5. Ruden Forespørgselsindstillinger, der viser egenskaber og hvert trin i forespørgslen

I baggrunden er hvert trin i en forespørgsel baseret på en formel, der er synlig på formellinjen.

Der kan være tidspunkter, hvor du vil ændre eller oprette en formel. Formler bruger Power Query Formelsprog, som du kan bruge til at opbygge både enkle og komplekse udtryk. Du kan få mere at vide om syntaks, argumenter, bemærkninger, funktioner og eksempler i Power Query M-formelsprog.

Hvis du bruger en liste over fodboldmesterskaber som eksempel, kan du bruge Power Query til at tage rå data, som du har fundet på et websted, og omdanne det til en velformateret tabel. Se, hvordan forespørgselstrin og tilsvarende formler oprettes for hver opgave i ruden Forespørgselsindstillinger under Anvendte trin og på formellinjen.

Procedure

1. Hvis du vil importere dataene, skal du vælge Data > Fra internettet, skrive "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" i feltet URL-adresse og derefter vælge OK.

2. I dialogboksen Navigator skal du vælge tabellen Resultater [Rediger] til venstre og derefter vælge Transformér data nederst. Redigeringsprogrammet til Power Query vises.

3. Hvis du vil ændre standardforespørgselsnavnet , skal du i ruden Forespørgselsindstillinger under Egenskaber slette "Resultater [Rediger]" og derefter angive "UEFA-mestre".

4. Hvis du vil fjerne uønskede kolonner, skal du markere den første, fjerde og femte kolonne og derefter vælge Hjem > Fjern kolonne > Fjern andre kolonner.

5. Hvis du vil fjerne uønskede værdier, skal du vælge Kolonne1, vælge Hjem > Erstat værdier, skrive "detaljer" i feltet Værdier, der skal søges efter og derefter vælge OK.

6. Hvis du vil fjerne rækker med ordet "År" i dem, skal du vælge filterpilen i Kolonne1, fjerne markeringen i afkrydsningsfeltet ud for "År" og derefter vælge OK.

7. Hvis du vil omdøbe kolonneoverskrifterne, skal du dobbeltklikke på hver af dem og derefter ændre "Kolonne1" til "År", "Kolonne4" til "Vinder" og "Kolonne5" til "Endeligt resultat".

8. Hvis du vil gemme forespørgslen, skal du vælge Hjem > Luk & Indlæs.

Resultat

Følgende tabel er en oversigt over hvert anvendt trin og den tilsvarende formel.

Forespørgselstrin og opgave	Formel
Kilde Oprette forbindelse til en webdatakilde	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigation Vælg tabellen for at oprette forbindelse	=Source{2}[Data]
Ændret type Ændre datatyper (som Power Query automatisk gør)	= 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}})
Andre kolonner fjernet Fjern andre kolonner for kun at vise kolonner af interesse	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Erstattet værdi Erstatte værdier for at rydde op i værdier i en markeret kolonne	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrerede rækker Filtrer værdier i en kolonne	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Omdøbte kolonner Ændrede kolonneoverskrifter, så de giver mening	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Vigtigt    Vær forsigtig med at redigere trinnene Kilde, Navigation og Ændret type,  da de oprettes af Power Query til at definere og konfigurere datakilden.

Vise eller skjule formellinjen

Formellinjen vises som standard, men hvis den ikke er synlig, kan du få den vist igen.

• Vælg Vis > layout > formellinjen.

Edit en formel på formellinjen

1. Hvis du vil åbne en forespørgsel, skal du finde en, der tidligere er indlæst fra Power Query-editor, markere en celle i dataene og derefter vælge Forespørgsel > Rediger. Få mere at vide under Opret, indlæs eller rediger en forespørgsel i Excel.

2. Vælg det trin, du vil redigere, under Anvendte trin i ruden Forespørgselsindstillinger.

3. Find og rediger parameterværdierne på formellinjen, og vælg derefter ikonet Enter , eller tryk på Enter. Du kan f.eks. ændre denne formel til også at beholde Kolonne2:
   
   Før: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Efter:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Vælg ikonet Enter , eller tryk på Enter for at få vist de nye resultater i Datavisning.

5. Hvis du vil se resultatet i et Excel-regneark, skal du vælge Hjem > Luk & Indlæs.

Opret en formel på formellinjen

I et eksempel på en simpel formel konverterer vi en tekstværdi til stort forbogstav ved hjælp af funktionen Text.Proper.

1. Hvis du vil åbne en tom forespørgsel, skal du i Excel vælge Data > Hent data > Fra andre kilder > Tom forespørgsel. Få mere at vide under Opret, indlæs eller rediger en forespørgsel i Excel.

2. Skriv=Text.Proper("text value")på formellinjen, og vælg derefter ikonet Enter , eller tryk på Enter.
   
   Resultaterne vises i Datavisning .

3. Hvis du vil se resultatet i et Excel-regneark, skal du vælge Hjem > Luk & Indlæs.

Resultat:

 Når du opretter en formel, validerer Power Query formelsyntaksen. Men når du indsætter, omarrangerer eller sletter et mellemliggende trin i en forespørgsel, kan du muligvis bryde en forespørgsel.  Bekræft altid resultaterne i Datavisning.

Vigtigt    Vær forsigtig med at redigere trinnene Kilde, Navigation og Ændret type,  da de oprettes af Power Query til at definere og konfigurere datakilden.

Rediger en formel ved hjælp af en dialogboks

Denne metode gør brug af dialogbokse, der varierer afhængigt af trinnet. Du behøver ikke at kende formlens syntaks.

1. Hvis du vil åbne en forespørgsel, skal du finde en, der tidligere er indlæst fra Power Query-editor, markere en celle i dataene og derefter vælge Forespørgsel > Rediger. Få mere at vide under Opret, indlæs eller rediger en forespørgsel i Excel.

2. I ruden Forespørgselsindstillinger under Anvendte trin skal du vælge ikonet Rediger indstillinger for det trin, du vil redigere, eller højreklikke på trinnet og derefter vælge Rediger indstillinger.

3. Foretag dine ændringer i dialogboksen, og vælg derefter OK.

Indsæt et trin

Når du har fuldført et forespørgselstrin, der omformer dine data, tilføjes et forespørgselstrin under det aktuelle forespørgselstrin. men når du indsætter et forespørgselstrin midt i trinnene, kan der opstå en fejl i efterfølgende trin. Power Query viser advarslen Indsæt trin, når du forsøger at indsætte et nyt trin, og det nye trin ændrer felter, f.eks. kolonnenavne, der bruges i de trin, der følger det indsatte trin.

1. I ruden Forespørgselsindstillinger under Anvendte trin skal du vælge det trin, du vil have umiddelbart foran det nye trin og dets tilsvarende formel.

2. Vælg ikonet Tilføj trin til venstre for formellinjen. Du kan også højreklikke på et trin og derefter vælge Indsæt trin efter. Der oprettes en ny formel i formatet :
   
   = <nameOfTheStepToReference>, f.eks. =Production.WorkOrder.

3. Skriv den nye formel ved hjælp af formatet:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Antag f.eks., at du har en tabel med kolonnen Køn, og at du vil tilføje en kolonne med værdien "Ms". eller "Hr.", afhængigt af personens køn. Formlen ville være:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Omarranger et trin

• I ruden Indstillinger for forespørgsler under Anvendte trin skal du højreklikke på trinnet og derefter vælge Flyt op eller Flyt ned.

Slette trin

• Vælg ikonet Slet til venstre for trinnet, eller højreklik på trinnet, og vælg derefter Slet eller Slet indtil slutningen. Ikonet Slet er også tilgængeligt til venstre for formellinjen.

I dette eksempel konverterer vi teksten i en kolonne til stort forbogstav ved hjælp af en kombination af formler i Avanceret editor. 

Du har f.eks. en Excel-tabel med navnet Ordrer med en Produktnavn-kolonne, som du vil konvertere til stort forbogstav. 

Før:

Efter:

Når du opretter en avanceret forespørgsel, opretter du en række trin til forespørgselsformlen baseret på udtrykket lad. Brug udtrykket Lad til at tildele navne og beregne værdier, der derefter refereres til i delsætningen , som definerer Trin. I dette eksempel returneres det samme resultat som det i afsnittet "Opret en formel på formellinjen".

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

Du kan se, at hvert trin bygger på et tidligere trin ved at referere til et trin efter navn. Som en påmindelse skelner Power Query formelsprog mellem store og små bogstaver.

Fase 1: Åbn Avanceret editor

1. I Excel skal du vælge Data > Hent data > Andre kilder > Tom forespørgsel. Få mere at vide under Opret, indlæs eller rediger en forespørgsel i Excel.

2. I Power Query-editor skal du vælge Hjem > Avanceret editor, som åbnes med en skabelon med udtrykket lad.

Fase 2: Definer datakilden

1. Opret udtrykket lad ved hjælp af funktionen Excel.CurrentWorkbook på følgende måde:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Hvis du vil indlæse forespørgslen i et regneark, skal du vælge Udført og derefter vælge Hjem > Luk & Indlæs > Luk & Indlæs.

Resultat:

Fase 3: Hæve den første række til overskrifter

1. Hvis du vil åbne forespørgslen, skal du markere en celle i dataene i regnearket og derefter vælge Forespørgsel > Rediger. Få mere at vide under Opret, indlæs eller rediger en forespørgsel i Excel (Power Query).

2. I Power Query-editor skal du vælge Hjem > Avanceret editor, som åbnes med den sætning, du oprettede i Fase 2: Definer datakilden.

3. Tilføj #"Første række som overskrift" og Funktionen Table.PromoteHeaders i lad-udtrykket på følgende måde:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. Hvis du vil indlæse forespørgslen i et regneark, skal du vælge Udført og derefter vælge Hjem > Luk & Indlæs > Luk & Indlæs.

Resultat:

Fase 4: Skift hver værdi i en kolonne til stort forbogstav

1. Hvis du vil åbne forespørgslen, skal du markere en celle i dataene i regnearket og derefter vælge Forespørgsel > Rediger. Få mere at vide under Opret, indlæs eller rediger en forespørgsel i Excel.

2. I Power Query-editor skal du vælge Hjem > Avanceret editor, som åbnes med den sætning, du oprettede i Fase 3: Hæv den første række til overskrifter.

3. I lad-udtrykket skal du konvertere hver Produktnavn-kolonneværdi til korrekt tekst ved hjælp af funktionen Table.TransformColumns, der refererer til forespørgselsformlen "Første række som overskrift", tilføjer #"Hvert Word" til datakilden og tildeler derefter #"Hvert Word" med stort 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 indlæse forespørgslen i et regneark, skal du vælge Udført og derefter vælge Hjem > Luk & Indlæs > Luk & Indlæs.

Resultat:

Du kan styre funktionsmåden for formellinjen i Power Query-editor for alle dine projektmapper.

Vise eller skjule formellinjen

1. Vælg Indstillinger > Filer >Forespørgselsindstillinger.

2. I venstre rude under GLOBAL skal du vælge Power Query-editor.

3. I højre rude under Layout skal du markere eller fjerne markeringen i Vis formellinjen.

Slå M Intellisense til eller fra

1. Vælg Filer > Indstillinger og indstillinger > Forespørgselsindstillinger .

2. I venstre rude under GLOBAL skal du vælge Power Query-editor.

3. I højre rude under Formel skal du markere eller fjerne markeringen i Aktivér M Intellisense på formellinjen, avanceret editor og dialogboksen brugerdefineret kolonne.

Bemærk   Ændring af denne indstilling træder i kraft, næste gang du åbner vinduet Power Query-editor.