Power Query-formules maken in Excel

Met de Power Query-editor maakt u al die tijd Power Query-formules. Laten we eens kijken hoe Power Query werkt door onder de motorkap te kijken. U kunt leren hoe u formules bijwerkt of toevoegt door de Power Query-editor in actie te bekijken.  U kunt zelfs uw eigen formules rollen met de Geavanceerde editor.           

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 gegevensbronnen in een Excel-werkblad, selecteert u een cel in de gegevens en selecteert u vervolgens Query > Bewerken. Hieronder volgt een overzicht van de belangrijkste onderdelen.

Onderdelen van de queryeditor

  1. Het lint power queryeditor 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 sneltoetsen zijn voor opdrachten op het lint

  4. Het gegevensvoorbeeld met de resultaten van de stappen die zijn toegepast op de gegevens

  5. Het deelvenster Query Instellingen met 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.

Voorbeeld van formule in Queryeditor

Soms wilt u een formule wijzigen of maken. Formules gebruiken de Power Query-formuletaal, waarmee u zowel eenvoudige als complexe expressies kunt maken. Zie Formuletaal van Power Query M voor meer informatie over syntaxis,argumenten, opmerkingen, functies envoorbeelden.

Met behulp van een lijst met voetbaltitels als voorbeeld gebruikt u Power Query om onbewerkte gegevens op een website te maken en deze om te zetten in een goed opgemaakte tabel. Bekijk hoe querystappen en bijbehorende formules worden gemaakt voor elke taak in het deelvenster Query Instellingen onder Toegepaste stappen en op de formulebalk.

Uw browser biedt geen ondersteuning voor video.

Procedure

  1. Als u de gegevens wilt importeren, selecteert u Gegevens> Van web,typt u 'http://en.wikipedia.org/wiki/UEFA_European_Football_Championship' in het vak URL en selecteert u 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 eigenschappen 'Resultaten [Bewerken]' en voert u vervolgens 'UEFA-kampioenen' in.

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

  5. Als u ongewenste waarden wilt verwijderen, selecteert u Kolom1,selecteert u Start > Waardenvervangen, voert u 'details' in het vak Waarden naar zoeken in en selecteert u 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 OK.

  7. Als u de naam van de kolomkoppen wilt wijzigen, dubbelklikt u op elk van deze koppen en wijzigt u vervolgens 'Kolom1' in 'Jaar', 'Kolom4' in 'Winnaar' en 'Kolom5' in 'Eindscore'.

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

Resultaat

Resultaten van de doorloop - de eerste paar rijen

De volgende tabel is 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 (die automatisch worden uitgevoerd in Power Query)

= 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"})

Vervangende 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 gewijzigd om betekenisvol te zijn

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Belangrijk    Pas op met het bewerken van de stappen Bron, Navigatieen Gewijzigd type, omdat deze door Power Query worden gemaakt 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 de formulebalk opnieuw afspelen.

  • Selecteer Weergave > indeling > formulebalk.

Edit een formule in de formulebalk

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

  2. Selecteer in Instellingen queryvenster onder Toegepaste stappende stap die u wilt bewerken.

  3. Zoek en wijzig de parameterwaarden op de formulebalk en selecteer vervolgens het pictogram Enter Het pictogram Enter links van de formulebalk in Power Query of druk op Enter. Wijzig bijvoorbeeld deze formule om kolom2 ook te behouden:

    Vóór: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    Na:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Selecteer het pictogram Enter Het pictogram Enter links van de formulebalk in Power Query 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 op de formulebalk

Voor een voorbeeld van een eenvoudige formule kunnen we een tekstwaarde converteren naar een goed geval met de functie Tekst.Juist.

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

  2. Voer in de formulebalk=Text.Proper("text value")en selecteer vervolgens het pictogram Enter Het pictogram Enter links van de formulebalk in Power Query 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:

De animatie selecteren die u wilt activeren

 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, kunt u mogelijk een query breken.  Controleer altijd de resultaten in Gegevensvoorbeeld.

Belangrijk    Pas op met het bewerken van de stappen Bron, Navigatieen Gewijzigd type, omdat deze door Power Query worden gemaakt 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 kennen.

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

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

  3. Wijzig de wijzigingen in het dialoogvenster en selecteer OK.

Een stap invoegen

Nadat u een querystap hebt voltooid die de gegevens een andere vorm heeft geven, wordt onder de huidige querystap een querystap toegevoegd. maar wanneer u een querystap in het midden van de stappen invoegt, kan er een fout optreden in de volgende stappen. In Power Query wordt een waarschuwing voor stap invoegen weergegeven wanneer u een nieuwe stap probeert in te voegen en de nieuwe stap velden, zoals kolomnamen, wijzigt die worden gebruikt in een van de stappen die volgen op de ingevoegde stap.

  1. Selecteer in Instellingen queryvenster onder Toegepaste stappen de stap die u direct vóór de nieuwe stap en de bijbehorende formule wilt zetten.

  2. Selecteer het pictogram Stap toevoegen pictogram Functie 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 met de opmaak:

    =Class.Function(ReferenceStep[,otherparameters])

    Stel dat u een tabel met de kolom Geslacht hebt en dat u een kolom met de waarde 'Ms' wilt toevoegen. of 'Mr', afhankelijk van het geslacht van de persoon. De formule zou zijn:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Voorbeeldformule

Een stap opnieuw ordenen

  • Klik in het deelvenster Query'Instellingen onder Toegepaste stappen metde rechtermuisknop op de stap en selecteer vervolgens Omhoog ofOmlaag gaan.

Stap verwijderen

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

Laten we in dit voorbeeld de tekst in een kolom converteren naar de juiste hoofdtekst met behulp van een combinatie van formules in de geavanceerde editor. 

U hebt bijvoorbeeld een tabel Excel orders, met een kolom ProductNaam die u wilt converteren naar de juiste case. 

Voor:

Een stroomdiagram met rode verbindingspunten

Na:

Stap 4 - resultaat

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

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

U ziet dat elke stap voortbouwt op een vorige stap door te verwijzen naar een stap voor naam. Ter herinnering: de Formuletaal van Power Query is case-sensitive.

Fase 1: De geavanceerde editor openen

  1. Selecteer Excel Gegevens >Gegevens > andere bronnen > lege query. Zie Een query maken, laden ofbewerken in een Excel voor meer informatie.

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

Werknemersrapport in afdrukvoorbeeld

Fase 2: de gegevensbron definiëren

  1. Maak de let-expressie met de Excel. CurrentWorkbook, functie als volgt:

    let#x1

    in
        Source

    Het dialoogvenster In- en uitzoomen

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

Resultaat:

Wiskundig symbool

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) voormeer informatie.

  2. Selecteer in power queryeditor de optie 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 Klaar en selecteert u vervolgens Start> Sluiten & Laden > Sluiten & Laden.

Resultaat:

Stap 3 - resultaat

Fase 4: Elke waarde in een kolom wijzigen in de juiste case

  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 ofbewerken in een Excel voor meer informatie.

  2. Selecteer in power queryeditor de optie Start> Geavanceerde editor, die wordt geopend met de instructie die u hebt gemaakt in fase 3: De eerste rij promoveren naar kopteksten.

  3. Converteer in de let-expressie elke waarde van de kolom ProductName naar de juiste tekst met de functie Table.TransformColumns, verwijzend naar de vorige querystap 'Eerste rij als koptekst', voeg #"Hoofdletters van elk Woord" toe aan de gegevensbron en wijs vervolgens #"Hoofdletters elk Woord" toe aan het 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 Klaar en selecteert u vervolgens Start> Sluiten & Laden > Sluiten & Laden.

Resultaat:

Stap 4 - resultaat

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

De formulebalk weergeven of verbergen

  1. Selecteer Bestandsopties> opties en Instellingen > Queryopties.

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

  3. Selecteer of schakel in het rechterdeelvensteronder Indeling de formulebalk weergeven in of uit.

M Intellisense in- of uitschakelen

  1. Selecteer Bestandsopties> opties en Instellingen > Queryopties .

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

  3. Selecteer of schakel in het rechterdeelvenster onder Formule het dialoogvenster M Intellisense inschakelen in de formulebalk,geavanceerde editor en aangepaste kolom uit.

Opmerking   Het wijzigen van deze instelling wordt van kracht wanneer u het power queryeditorvenster de volgende keer opent.

Zie ook

Help voor Power Query voor Excel

Een aangepaste functie maken en aanroepen

De lijst Toegepaste stappen gebruiken (docs.com)

Aangepaste functies gebruiken (docs.com)

Power Query M-formules (docs.com)

Omgaan met fouten (docs.com)

Meer hulp nodig?

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

×