Power Query-formules maken in Excel

De Power Query-editor biedt een gegevensquery en vormgevingservaring voor Excel die u kunt gebruiken om gegevens uit veel gegevensbronnen opnieuw vorm te geven. Als u het venster Power Query-editor wilt weergeven, importeert u gegevens uit externe gegevensbronnenin een Excel-werkblad, selecteert u een cel in de gegevens en selecteert u vervolgens Query > Bewerken. Hier volgt een overzicht van de belangrijkste onderdelen.

1. Het Power Query-editor lint dat u gebruikt om uw gegevens vorm te geven

2. Het deelvenster Query's dat u gebruikt om gegevensbronnen en tabellen te zoeken

3. Contextmenu's die handige snelkoppelingen zijn naar opdrachten op het lint

4. Het gegevensvoorbeeld waarin de resultaten worden weergegeven van de stappen die op de gegevens zijn toegepast

5. Het deelvenster Queryinstellingen met de eigenschappen en elke stap in de query

Achter de schermen is elke stap in een query gebaseerd op een formule die zichtbaar is op de formulebalk.

Het kan gebeuren dat u een formule wilt wijzigen of maken. Formules gebruiken de Power Query Formuletaal, die u kunt gebruiken om zowel eenvoudige als complexe expressies te maken. Zie Power Query M-formuletaal voor meer informatie over syntaxis, argumenten, opmerkingen, functies en voorbeelden.

Gebruik een lijst met voetbalkampioenschappen als voorbeeld om Power Query te gebruiken om onbewerkte gegevens te nemen die u op een website hebt gevonden en deze om te zetten in een goed opgemaakte tabel. Bekijk hoe querystappen en bijbehorende formules voor elke taak worden gemaakt in het deelvenster Query-instellingen onder Toegepaste stappen en op de formulebalk.

Procedure

1. Als u de gegevens wilt importeren, selecteert u Gegevens > Van web, voert u 'http://en.wikipedia.org/wiki/UEFA_European_Football_Championship' in het url-vak in en selecteert u vervolgens OK.

2. Selecteer in het dialoogvenster Navigator de tabel Resultaten [Bewerken] aan de linkerkant en selecteer vervolgens Gegevens transformeren onderaan. De Power Query editor wordt weergegeven.

3. Als u de standaardquerynaam wilt wijzigen, verwijdert u in het deelvenster Query-instellingen onder Eigenschappen 'Resultaten [bewerken]' en voert u 'UEFA champs' in.

4. Als u ongewenste kolommen wilt verwijderen, selecteert u de eerste, vierde en vijfde kolom en selecteert u vervolgens Start > Kolom verwijderen > Andere kolommen verwijderen.

5. Als u ongewenste waarden wilt verwijderen, selecteert u Kolom1, selecteert u Start > Waarden vervangen, voert u 'details' in het vak Te zoeken waarden in en selecteert u vervolgens OK.

6. Als u rijen met het woord 'Jaar' wilt verwijderen, selecteert u de filterpijl in Kolom1, schakelt u het selectievakje naast 'Jaar' uit en selecteert u vervolgens OK.

7. Als u de naam van de kolomkoppen wilt wijzigen, dubbelklikt u op elke kolomkop en wijzigt u 'Kolom1' in 'Jaar', 'Kolom4' in 'Winnaar' en 'Kolom5' in 'Eindscore'.

8. Als u de query wilt opslaan, selecteert u Start > Sluiten & Laden.

Resultaat

De volgende tabel bevat een samenvatting van elke toegepaste stap en de bijbehorende formule.

Querystap en taak	Formule
Bron Verbinding met een webgegevensbron maken	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigatie Selecteer de tabel om verbinding mee te maken	=Source{2}[Data]
Type gewijzigd Gegevenstypen wijzigen (wat Power Query automatisch doet)	= 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}})
Andere kolommen verwijderd Andere kolommen verwijderen, zodat alleen belangrijke kolommen worden weergegeven	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Vervangen waarde Waarden vervangen om waarden in een geselecteerde kolom op te schonen	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Gefilterde rijen Waarden in een kolom filteren	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Naam van kolommen gewijzigd Kolomkoppen zijn gewijzigd zodat ze zinvol zijn	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Belangrijk    Wees voorzichtig met het bewerken van de stappen Bron, Navigatie  en Gewijzigd type, omdat deze worden gemaakt door Power Query om de gegevensbron te definiëren en in te stellen.

De formulebalk weergeven of verbergen

De formulebalk wordt standaard weergegeven, maar als deze niet zichtbaar is, kunt u deze opnieuw weergeven.

• Selecteer Weergave > indeling > formulebalk.

Ditis een formule op de formulebalk

1. Als u een query wilt openen, zoekt u er een die eerder is geladen vanuit de Power Query-editor, selecteert u een cel in de gegevens en selecteert u vervolgens Query > Bewerken. Zie Een query maken, laden of bewerken in Excel voor meer informatie.

2. Selecteer in het deelvenster Query-instellingen onder Toegepaste stappen de stap die u wilt bewerken.

3. Zoek en wijzig de parameterwaarden op de formulebalk en selecteer vervolgens het pictogram Enter of druk op Enter. Wijzig bijvoorbeeld deze formule zodat Kolom2:
   
   Voor: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Na:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Selecteer het pictogram Enter of druk op Enter om de nieuwe resultaten weer te geven die worden weergegeven in het gegevensvoorbeeld.

5. Als u het resultaat in een Excel-werkblad wilt zien, selecteert u Start > Sluiten & Laden.

Een formule maken in de formulebalk

Voor een eenvoudig formulevoorbeeld gaan we een tekstwaarde converteren naar de juiste hoofdletters met behulp van de functie Text.Proper.

1. Als u een lege query wilt openen, selecteert u in Excel Gegevens > Gegevens ophalen > Uit andere bronnen > Lege query. Zie Een query maken, laden of bewerken in Excel voor meer informatie.

2. Voer in de formulebalk=Text.Proper("text value")in en selecteer het pictogram Enter of druk op Enter.
   
   De resultaten worden weergegeven in Gegevensvoorbeeld .

3. Als u het resultaat in een Excel-werkblad wilt zien, selecteert u Start > Sluiten & Laden.

Resultaat:

 Wanneer u een formule maakt, valideert Power Query de syntaxis van de formule. Wanneer u echter een tussenliggende stap in een query invoegt, opnieuw rangschikt of verwijdert, kan een query mogelijk worden onderbroken.  Controleer altijd de resultaten in Data Preview.

Belangrijk    Wees voorzichtig met het bewerken van de stappen Bron, Navigatie  en Gewijzigd type, omdat deze worden gemaakt door Power Query om de gegevensbron te definiëren en in te stellen.

Een formule bewerken met behulp van een dialoogvenster

Deze methode maakt gebruik van dialoogvensters die variëren, afhankelijk van de stap. U hoeft de syntaxis van de formule niet te weten.

1. Als u een query wilt openen, zoekt u er een die eerder is geladen vanuit de Power Query-editor, selecteert u een cel in de gegevens en selecteert u vervolgens Query > Bewerken. Zie Een query maken, laden of bewerken in Excel voor meer informatie.

2. Selecteer in het deelvenster Queryinstellingen onder Toegepaste stappen het pictogram Instellingen bewerken van de stap die u wilt bewerken of klik met de rechtermuisknop op de stap en selecteer Vervolgens Instellingen bewerken.

3. Breng de wijzigingen aan in het dialoogvenster en selecteer VERVOLGENS OK.

Een stap invoegen

Nadat u een querystap hebt voltooid waarmee uw gegevens worden gewijzigd, wordt een querystap toegevoegd onder de huidige querystap. maar wanneer u een querystap in het midden van de stappen invoegt, kan er een fout optreden in de volgende stappen. Power Query geeft een waarschuwing Stap invoegen weer wanneer u een nieuwe stap probeert in te voegen en de nieuwe stap wijzigt velden, zoals kolomnamen, die worden gebruikt in een van de stappen die volgen op de ingevoegde stap.

1. Selecteer in het deelvenster Query-instellingen onder Toegepaste stappen de stap die u direct wilt voorafgaan aan de nieuwe stap en de bijbehorende formule.

2. Selecteer het pictogram Stap toevoegen links van de formulebalk. U kunt ook met de rechtermuisknop op een stap klikken en vervolgens Stap na invoegen selecteren. Er wordt een nieuwe formule gemaakt in de indeling :
   
   = <nameOfTheStepToReference>, zoals =Production.WorkOrder.

3. Typ de nieuwe formule in de notatie:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Stel dat u een tabel hebt met de kolom Gender en dat u een kolom wilt toevoegen met de waarde 'Ms'. of 'Mr.', afhankelijk van het geslacht van de persoon. De formule is:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

De volgorde van een stap wijzigen

• Klik in het deelvenster Query-instellingen onder Toegepaste stappen met de rechtermuisknop op de stap en selecteer vervolgens Omhoog of Omlaag.

Stap verwijderen

• Selecteer het pictogram verwijderen links van de stap of klik met de rechtermuisknop op de stap en selecteer vervolgens Verwijderen of Verwijderen tot einde. Het pictogram verwijderen is ook beschikbaar aan de linkerkant van de formulebalk.

In dit voorbeeld gaan we de tekst in een kolom converteren naar de juiste hoofdletters met behulp van een combinatie van formules in de Geavanceerde editor. 

U hebt bijvoorbeeld een Excel-tabel met de naam Orders, met een kolom ProductName die u wilt converteren naar de juiste hoofdletters. 

Voor:

Na:

Wanneer u een geavanceerde query maakt, maakt u een reeks queryformulestappen op basis van de let-expressie. Gebruik de let-expressie om namen toe te wijzen en waarden te berekenen waarnaar vervolgens wordt verwezen door de in-component , waarmee de stap wordt gedefinieerd. In dit voorbeeld wordt hetzelfde resultaat geretourneerd als in de sectie 'Een formule maken in de formulebalk'.

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

U ziet dat elke stap voortbouwt op een vorige stap door te verwijzen naar een stap op naam. Ter herinnering: de Power Query Formuletaal is hoofdlettergevoelig.

Fase 1: De Geavanceerde editor openen

1. Selecteer in Excel Gegevens > Gegevens ophalen > Andere bronnen > Lege query. Zie Een query maken, laden of bewerken in Excel voor meer informatie.

2. Selecteer in de Power Query-editor Start > Geavanceerde editor, die wordt geopend met een sjabloon van de let-expressie.

Fase 2: de gegevensbron definiëren

1. Maak de let-expressie als volgt met de functie Excel.CurrentWorkbook :
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Als u de query naar een werkblad wilt laden, selecteert u Gereed en selecteert u vervolgens Start > Sluiten & Laden > & Laden sluiten.

Resultaat:

Fase 3: de eerste rij promoveren naar kopteksten

1. Als u de query wilt openen, selecteert u in het werkblad een cel in de gegevens en selecteert u vervolgens Query > Bewerken. Zie Een query maken, laden of bewerken in Excel (Power Query) voor meer informatie.

2. Selecteer in de Power Query-editor Start > Geavanceerde editor, die wordt geopend met de instructie die u hebt gemaakt in fase 2: De gegevensbron definiëren.

3. Voeg in de let-expressie #"First Row as Header" en Table.PromoteHeaders als volgt toe:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. Als u de query naar een werkblad wilt laden, selecteert u Gereed en selecteert u vervolgens Start > Sluiten & Laden > & Laden sluiten.

Resultaat:

Fase 4: elke waarde in een kolom wijzigen in de juiste hoofdletters

1. Als u de query wilt openen, selecteert u in het werkblad een cel in de gegevens en selecteert u vervolgens Query > Bewerken. Zie Een query maken, laden of bewerken in Excel voor meer informatie.

2. Selecteer in de Power Query-editor Start > Geavanceerde editor, die wordt geopend met de instructie die u hebt gemaakt in Fase 3: De eerste rij promoveren tot kopteksten.

3. Converteer in de let-expressie elke ProductName-kolomwaarde naar de juiste tekst met behulp van de functie Table.TransformColumns, waarbij u verwijst naar de vorige queryformulestap 'Eerste rij als koptekst', waarbij u #"Elke Word met hoofdletters" toevoegt aan de gegevensbron en vervolgens #"Elke Word met hoofdletters" toewijst aan het in resultaat.
   
   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. Als u de query naar een werkblad wilt laden, selecteert u Gereed en selecteert u vervolgens Start > Sluiten & Laden > & Laden sluiten.

Resultaat:

U kunt het gedrag van de formulebalk bepalen in de Power Query-editor voor al uw werkmappen.

De formulebalk weergeven of verbergen

1. Selecteer Bestand > Opties en instellingen > Queryopties.

2. Selecteer in het linkerdeelvenster onder ALGEMEENde optie Power Query-editor.

3. Schakel in het rechterdeelvenster onder Indelingde formulebalk weergeven in of uit.

M Intellisense in- of uitschakelen

1. Selecteer Bestand > Opties en instellingen > Queryopties .

2. Selecteer in het linkerdeelvenster onder ALGEMEENde optie Power Query-editor.

3. Selecteer in het rechterdeelvenster onder Formulede optie M Intellisense inschakelen in de formulebalk, geavanceerde editor en dialoogvenster aangepaste kolommen.

Opmerking   Als u deze instelling wijzigt, wordt de volgende keer dat u het Power Query-editor-venster opent van kracht.