Een parameterquery maken (Power Query)

U kunt een parameter gebruiken om automatisch een waarde in een query te wijzigen en te voorkomen dat de query elke keer wordt bewerkt om de waarde te wijzigen. U wijzigt alleen de parameterwaarde. Zodra u een parameter hebt gemaakt, wordt deze opgeslagen in een speciale parameterquery die u gemakkelijk rechtstreeks vanuit Excel kunt wijzigen.

1. Selecteer Gegevens > Gegevens ophalen > andere bronnen > start Power Query-editor.

2. Selecteer in de Power Query-editor Start > Parameters beheren > Nieuwe parameters.

3. Selecteer in het dialoogvenster Parameter beheren de optie Nieuw.

4. Stel indien nodig het volgende in:

   Naam	Dit moet de functie van de parameter weerspiegelen, maar deze zo kort mogelijk houden.
   Beschrijving	Dit kan alle details bevatten die mensen helpen de parameter correct te gebruiken.
   Vereist	Voer een van de volgende handelingen uit: Elke waarde U kunt elke waarde van elk gegevenstype invoeren in de parameterquery. Lijst met waarden    U kunt de waarden beperken tot een specifieke lijst door ze in het kleine raster in te voeren. U moet ook een standaardwaarde en een huidige waarde hieronder selecteren. Query Selecteer een lijstquery, die lijkt op een gestructureerde lijstkolom, gescheiden door komma's en tussen accolades. Een statusveld Problemen kan bijvoorbeeld drie waarden bevatten: {"Nieuw", "Doorlopend", "Gesloten"}. U moet de lijstquery vooraf maken door de Geavanceerde editor te openen (selecteer Start > Geavanceerde editor), de codesjabloon te verwijderen, de lijst met waarden in de querylijstindeling in te voeren en vervolgens Gereed te selecteren. Zodra u klaar bent met het maken van de parameter, wordt de lijstquery weergegeven in de parameterwaarden.
   Type	Hiermee geeft u het gegevenstype van de parameter op.
   Voorgestelde waarden	Voeg desgewenst een lijst met waarden toe of geef een query op om suggesties voor invoer te geven.
   Standaardwaarde	Dit wordt alleen weergegeven als Voorgestelde waarden is ingesteld op Lijst met waarden en opgeeft welk lijstitem het standaarditem is. In dit geval moet u een standaardinstelling kiezen.
   Huidige waarde	Afhankelijk van waar u de parameter gebruikt, als deze leeg is, retourneert de query mogelijk geen resultaten. Als Vereist is geselecteerd, mag de huidige waarde niet leeg zijn.

5. Als u de parameter wilt maken, selecteert u OK.

Hier volgt een manier om wijzigingen in gegevensbronlocaties te beheren en vernieuwingsfouten te voorkomen. Als u bijvoorbeeld een soortgelijk schema en een vergelijkbare gegevensbron gebruikt, maakt u een parameter om eenvoudig een gegevensbron te wijzigen en fouten bij het vernieuwen van gegevens te voorkomen. Soms verandert de server, database, map, bestandsnaam of locatie. Misschien verwisselt een databasebeheerder af en toe een server, gaat er maandelijks een aantal CSV-bestanden naar een andere map of moet u eenvoudig schakelen tussen een ontwikkel-/test-/productieomgeving.

Stap 1: een parameterquery maken

In het volgende voorbeeld hebt u verschillende CSV-bestanden die u importeert met behulp van de bewerking Map importeren (Selecteer Gegevens > Gegevens ophalen > Uit bestanden > uit map) uit map C:\DataFilesCSV1. Maar soms wordt een andere map gebruikt als locatie om de bestanden te verwijderen, C:\DataFilesCSV2. U kunt een parameter in een query gebruiken als vervangingswaarde voor de andere map.

1. Selecteer Start > Parameters beheren > Nieuwe parameter.

2. Voer de volgende informatie in het dialoogvenster Parameter beheren in:

   Naam	CSVFileDrop
   Beschrijving	Alternatieve locatie voor het verwijderen van bestanden
   Vereist	Ja
   Type	Sms-bericht
   Voorgestelde waarden	Elke waarde
   Huidige waarde	C:\DataFilesCSV1

3. Selecteer OK.

Stap 2: voeg de parameter toe aan de gegevensquery

1. Als u de mapnaam als parameter wilt instellen, selecteert u in Query-instellingen onder Querystappende optie Bron en vervolgens Instellingen bewerken.

2. Zorg ervoor dat de optie Bestandspad is ingesteld op Parameter en selecteer vervolgens de parameter die u zojuist hebt gemaakt in de vervolgkeuzelijst.

3. Selecteer OK.

Stap 3: de parameterwaarde bijwerken

De locatie van de map is zojuist gewijzigd, dus u kunt nu gewoon de parameterquery bijwerken.

1. Selecteer Gegevens > Verbindingen & Query's > tabblad Query's , klik met de rechtermuisknop op de parameterquery en selecteer bewerken.

2. Voer de nieuwe locatie in het vak Huidige waarde in, bijvoorbeeld C:\DataFilesCSV2.

3. Selecteer Start > & Laden sluiten.

4. Als u uw resultaten wilt bevestigen, voegt u nieuwe gegevens toe aan de gegevensbron en vernieuwt u vervolgens de gegevensquery met de bijgewerkte parameter (Selecteer Gegevens > Alles vernieuwen).

Soms wilt u het filter van een query eenvoudig wijzigen om verschillende resultaten te verkrijgen zonder de query te bewerken of iets andere kopieën van dezelfde query te maken. In dit voorbeeld wijzigen we een datum om een gegevensfilter gemakkelijk te wijzigen.

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 de filterpijl in een kolomkop om uw gegevens te filteren en selecteer vervolgens een filteropdracht, zoals Datum-/tijdfilters > Na. Het dialoogvenster Rijen filteren wordt weergegeven.
   
   

3. Selecteer de knop links van het vak Waarde en voer een van de volgende handelingen uit:

   • Als u een bestaande parameter wilt gebruiken, selecteert u Parameter en selecteert u vervolgens de gewenste parameter in de lijst die aan de rechterkant wordt weergegeven.

   • Als u een nieuwe parameter wilt gebruiken, selecteert u Nieuwe parameter en maakt u vervolgens een parameter.

4. Voer de nieuwe datum in het vak Huidige waarde in en selecteer vervolgens Start > Sluiten & Laden.

5. Als u uw resultaten wilt bevestigen, voegt u nieuwe gegevens toe aan de gegevensbron en vernieuwt u vervolgens de gegevensquery met de bijgewerkte parameter (Selecteer Gegevens > Alles vernieuwen). Wijzig bijvoorbeeld de filterwaarde in een andere datum om nieuwe resultaten te zien.

6. Voer de nieuwe datum in het vak Huidige waarde in.

7. Selecteer Start > & Laden sluiten.

8. Als u uw resultaten wilt bevestigen, voegt u nieuwe gegevens toe aan de gegevensbron en vernieuwt u vervolgens de gegevensquery met de bijgewerkte parameter (Selecteer Gegevens > Alles vernieuwen).

In dit voorbeeld wordt de waarde in de queryparameter gelezen uit een cel in uw werkmap. U hoeft de parameterquery niet te wijzigen, u werkt alleen de celwaarde bij. U wilt bijvoorbeeld een kolom filteren op de eerste letter, maar de waarde eenvoudig wijzigen in een willekeurige letter van A tot Z.

1. Maak op het werkblad in een werkmap waarin de query die u wilt filteren is geladen een Excel-tabel met twee cellen: een koptekst en een waarde.
    

   MyFilter
   G

2. Selecteer een cel in de Excel-tabel en selecteer vervolgens Gegevens > Gegevens ophalen > Uit tabel/bereik. De Power Query-editor wordt weergegeven.

3. Wijzig in het vak Naam van het deelvenster Queryinstellingen aan de rechterkant de naam van de query zodat deze duidelijker is, zoals FilterCellValue. 

4. Als u de waarde in de tabel en niet in de tabel zelf wilt doorgeven, klikt u met de rechtermuisknop op de waarde in Gegevensvoorbeeld en selecteert u vervolgens Inzoomen.

   U ziet dat de formule is gewijzigd in = #"Changed Type"{0}[MyFilter]

   Wanneer u in stap 10 de Excel-tabel als filter gebruikt, verwijst Power Query naar de tabelwaarde als de filtervoorwaarde. Een directe verwijzing naar de Excel-tabel zou een fout veroorzaken.

5. Selecteer Start > & Laden sluiten > Sluiten & Laden naar. U hebt nu een queryparameter met de naam FilterCellValue die u in stap 12 gebruikt.

6. Selecteer in het dialoogvenster Gegevens importeren de optie Alleen verbinding maken en selecteer vervolgens OK.

7. Open de query die u wilt filteren met de waarde in de tabel FilterCellValue, een die eerder is geladen vanuit de Power Query-editor, door een cel in de gegevens te selecteren en vervolgens Query > Bewerken te selecteren. Zie Een query maken, laden of bewerken in Excel voor meer informatie.

8. Selecteer de filterpijl in een kolomkop om uw gegevens te filteren en selecteer vervolgens een filteropdracht, zoals Tekstfilters > Begint met. Het dialoogvenster Rijen filteren wordt weergegeven. 

9. Voer een waarde in het vak Waarde in, zoals 'G' en selecteer VERVOLGENS OK. In dit geval is de waarde een tijdelijke tijdelijke aanduiding voor de waarde in de tabel FilterCellValue die u in de volgende stap invoert.

10. Selecteer de pijl aan de rechterkant van de formulebalk om de hele formule weer te geven. Hier volgt een voorbeeld van een filtervoorwaarde in een formule:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Selecteer de waarde van het filter. Selecteer G in de formule.

12. Voer met M Intellisense de eerste paar letters in van de tabel FilterCellValue die u hebt gemaakt en selecteer deze vervolgens in de lijst die wordt weergegeven.

13. Selecteer Start > Sluiten > & laden sluiten.

Resultaat

Uw query gebruikt nu de waarde in de Excel-tabel die u hebt gemaakt om de queryresultaten te filteren. Als u een nieuwe waarde wilt gebruiken, bewerkt u de celinhoud in de oorspronkelijke Excel-tabel in stap 1, wijzigt u 'G' in 'V' en vernieuwt u de query.

U kunt bepalen of parameterquery's wel of niet zijn toegestaan.

1. Selecteer in de Power Query-editor Bestand > Opties en instellingen > Queryopties > Power Query-editor.

2. Selecteer in het deelvenster aan de linkerkant onder ALGEMEENde optie Power Query-editor.

3. Schakel in het deelvenster aan de rechterkant, onder Parameters, Parameterisatie altijd toestaan in dialoogvensters voor gegevensbron en transformatie in of uit.