Oprette formler til Power-forespørgsler i Excel

Power-forespørgselseditor indeholder en dataforespørgsels- og -formningsoplevelse for Excel, som du kan bruge til at omforme data fra mange datakilder. Hvis du vil have vist vinduet Power Query Editor, skal du importere datafra eksterne datakilder i et Excel-regneark, markere en celle i dataene og derefter vælge Forespørgsel > Rediger. Følgende er en oversigt over de vigtigste komponenter.

1. Båndet i Power Query Editor, som 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. Datavisning, der viser resultaterne af de trin, der er anvendt på dataene

5. Ruden Indstillinger forespørgsel, 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 formelsproget i Power-forespørgsel, som du kan bruge til at opbygge både enkle og komplekse udtryk. Du kan finde flere oplysninger om syntaks, argumenter, bemærkninger, funktioner og eksempler i Formelsprog i Power-forespørgselM.

Brug en liste over fodboldturneringer som eksempel til at bruge Power-forespørgsel til at tage rå data, 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 Indstillinger under Anvendte trin og på formellinjen.

Procedure

1. Hvis du vil importere dataene, skal du> Data frainternettet, 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. Power-forespørgselseditoren vises.

3. Hvis du vil ændre standardforespørgslens navn, skal du i ruden forespørgsels Indstillinger underEgenskaber slette "Resultater [Rediger]" og derefter angive "EUROPAPælger".

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 ,angive "detaljer" i feltet Værdier, der skal findes og derefter vælge OK.

6. Hvis du vil fjerne rækker, der indeholder 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. For at 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-forespørgsel 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 Erstat 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 til at give 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 er oprettet af Power-forespørgsel for   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 > på > på formellinjen.

Edit a formula in the formula bar

1. Hvis du vil åbne en forespørgsel, skal du finde en, der tidligere er indlæst fra Power-forespørgselseditoren, 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 Indstillinger forespørgsel under Anvendte trinskal du vælge det trin, du vil redigere.

3. Find og rediger parameterværdierne på formellinjen, og vælg derefter ikonet 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 se de nye resultater, der vises i datavisningen.

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

Oprette en formel på formellinjen

For et eksempel på en simpel formel kan vi f.eks. konvertere en tekstværdi til at have stort forældende ved hjælp af funktionen Text.Proper.

1. Hvis du vil åbne en tom forespørgsel, Excel du 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. På formellinjen skal du skrive=Text.Proper("text value")og derefter vælge ikonet eller trykke på Enter.
   
   Resultaterne vises i Datavisning .

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

Resultat:

 Når du opretter en formel, validerer Power-forespørgsel formelsyntaksen. Men når du indsætter, omarranger eller sletter et mellemliggende trin i en forespørgsel, kan du komme til at 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 er oprettet af Power-forespørgsel for   at definere og konfigurere datakilden.

Redigere 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-forespørgselseditoren, 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 Indstillinger forespørgsel underAnvendte 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ætte 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-forespørgsel viser en Indsæt trin-advarsel, når du forsøger at indsætte et nyt trin, og det nye trin ændrer felter, f.eks. kolonnenavne, der bruges i et af de trin, der følger efter det indsatte trin.

1. I ruden forespørgsel Indstillinger under Anvendte trin skaldu vælge det trin, der skal umiddelbart foran det nye trin og den tilsvarende formel.

2. Vælg ikonet Tilføj til venstre for formellinjen. Alternativt kan du 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 i 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 du vil tilføje en kolonne med værdien "Frk." 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 Forespørgsler Indstillinger under Anvendtetrin 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 vises også til venstre for formellinjen.

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

Du har f.eks. en Excel, kaldet Ordrer, med en ProductName-kolonne, som du vil konvertere til stort fornavn. 

Før:

Efter:

Når du opretter en avanceret forespørgsel, opretter du en række trin til forespørgselsformlen, der er baseret på lad-udtrykket.  Brug udtrykket let til at tildele navne og beregne værdier, der derefter refereres til i delsætningen in, som definerer trinnet. 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 ved navn. Formelsproget i Power-forespørgsel gør opmærksom på 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 af udtrykket let.

Fase 2: Definer datakilden

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

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-forespørgsel).

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

3. I udtrykket let skal du tilføje #"første række som overskrift" og funktionen Table.PromoteHeaders 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: Rediger hver værdi i en kolonne til stort for stort fornavn

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-forespørgselseditoren skal du vælge Hjem > Avanceret editor, som åbnes med den sætning, du oprettede i Fase 3:Hæve den første række til overskrifter.

3. I lad-udtrykket skal du konvertere hver kolonneværdi af typen ProductName til stort forbogstav ved hjælp af funktionen Table.TransformColumns, der refererer til det forrige trin i forespørgselsformlen "Første række som overskrift", tilføje #"hvert ord med stort" til datakilden og derefter tildele #"hvert ord med stort" 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 funktionaliteten af formellinjen i Power-forespørgselseditoren for alle dine projektmapper.

Vise eller skjule formellinjen

1. Vælg Indstillinger> filer, Indstillinger > Indstillinger for forespørgsel.

2. I venstre rude under GLOBAL skal du vælgePower Query Editor.

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

Slå M IntelliSense til eller fra

1. Vælg Indstillinger> filer, Indstillinger > Indstillinger for forespørgsel .

2. I venstre rude under GLOBAL skal du vælgePower Query Editor.

3. I højre rude underFormel skal du markere eller fjerne markeringen i Aktivér M IntelliSense i formellinjen, avanceret editor og dialogboksen Brugerdefineret kolonne.

Bemærk   Hvis du ændrer denne indstilling, træder det i kraft, næste gang du åbner vinduet Power Query Editor.