Het is mogelijk dat u goed bekend bent met parameterquery's met het gebruik ervan in SQL of Microsoft Query. Er zijn echter belangrijke verschillen tussen de parameters van Power Query:
- Parameters kunnen in elke querystap worden gebruikt. Parameters kunnen niet alleen worden gebruikt als een gegevensfilter, maar ook om zaken op te geven zoals een bestandspad of een servernaam.
- Parameters vragen niet om invoer. In plaats daarvan kunt u de waarde snel wijzigen met behulp van Power Query. U kunt de waarden in cellen zelfs opslaan en ophalen in Excel.
- Parameters worden opgeslagen in een eenvoudige parameterquery, maar staan los van de gegevensquery's waarin ze worden gebruikt. Wanneer deze is gemaakt, kunt u desgewenst een parameter toevoegen aan query's.
Notitie Als u op een andere manier parameterquery's wilt maken, raadpleegt u Een parameterquery maken in Microsoft Query.
Een parameter maken
U kunt een parameter gebruiken om automatisch een waarde in een query te wijzigen en te voorkomen dat u de query telkens moet bewerken om de waarde te wijzigen. U hoeft alleen de parameterwaarde te wijzigen. Wanneer u een parameter hebt gemaakt, wordt deze opgeslagen in een speciale parameterquery die u rechtstreeks vanuit Excel kunt wijzigen.
Selecteer Gegevens>>ophalenAndere bronnen>Start de Power Query-editor.
Selecteer in de Power Query-editor de optie Parameters>> beheren Nieuwe parameters.
Selecteer Nieuw in het dialoogvenster Parameter beheren.
Stel zo nodig het volgende in:
Naam Dit moet de functie van de parameter weerspiegelen, maar houd deze zo kort mogelijk. Beschrijving Dit kan alle details bevatten die mensen helpen de parameter correct te gebruiken. Vereist Ga op een van de volgende manieren te werk:
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 deze 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 kolom met lijsten, gescheiden door komma's en tussen accolades.
Een veld Status van probleem kan bijvoorbeeld drie waarden hebben: {"Nieuw", "Lopend", "Gesloten"}. U moet de lijstquery vooraf maken door de Geavanceerde editor te openen (selecteer Home-Geavanceerde> editor), de codesjabloon te verwijderen, de lijst met waarden in de querylijstindeling in te voeren en vervolgens Gereed te selecteren.
Wanneer u de parameter hebt gemaakt, wordt de lijstquery weergegeven in uw parameterwaarden.type_getal Hiermee wordt het gegevenstype van de parameter aangegeven. 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 de standaardwaarde is. In dit geval moet u een standaardwaarde kiezen. Huidige waarde Afhankelijk van waar u de parameter gebruikt, retourneert de query mogelijk geen resultaten als deze leeg is. Als Vereist is geselecteerd, mag Huidige waarde niet leeg zijn. Selecteer OK om de parameter te maken.
Een gegevensbron wijzigen met behulp van een parameter
Hier volgt een manier om wijzigingen in gegevensbronlocaties te beheren en vernieuwingsfouten te voorkomen. Stel bijvoorbeeld dat een schema en gegevensbron vergelijkbaar zijn en maak een parameter om eenvoudig een gegevensbron te wijzigen en fouten bij het vernieuwen van gegevens te voorkomen. Soms verandert de naam van de server, database, map, bestandsnaam of locatie. Misschien verwisselt een databasebeheerder af en toe een server, gaat er elke maand een druppel 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 importeert u verschillende CSV-bestanden met de bewerking voor het importeren van mappen (Gegevens>ophalen>uit map FilesFrom>Selecteer) uit map C:\DataFilesCSV1. Maar soms wordt een andere map gebruikt als locatie om de bestanden neer te zetten, C:\DataFilesCSV2. U kunt een parameter in een query gebruiken als een vervangende waarde voor de andere map.
Selecteer Start:>parameters beheren>,nieuwe parameter.
Voer in het dialoogvenster Parameter beheren de volgende gegevens in:
Naam CSVFileDrop Beschrijving Alternatieve locatie voor het neerzetten van bestanden Vereist Ja type_getal Text Voorgestelde waarden Elke waarde Huidige waarde C:\DataFilesCSV1 Selecteer OK.
Stap 2: De parameter toevoegen aan de gegevensquery
- Als u de mapnaam wilt instellen als parameter, selecteert u in Query-instellingen onder Querystappende optie Bron en selecteert u Instellingen bewerken.
- Zorg ervoor dat de optie Bestandspad is ingesteld op Parameter en selecteer vervolgens in de vervolgkeuzelijst de parameter die u zojuist hebt gemaakt.
- Selecteer OK.
Stap 3: De parameterwaarde bijwerken
De maplocatie is net gewijzigd, zodat u de parameterquery nu gewoon kunt bijwerken.
- Selecteer Gegevensverbindingen>& het tabblad Query's>, klik met de rechtermuisknop op de parameterquery en selecteer vervolgens Bewerken.
- Voer de nieuwe locatie in het vak Huidige waarde in, bijvoorbeeld C:\DataFilesCSV2.
- Selecteer Start,>sluiten & laden.
- U bevestigt uw resultaten door nieuwe gegevens aan de gegevensbron toe te voegen en vervolgens de gegevensquery te vernieuwen met de bijgewerkte parameter (Selecteer >Alles vernieuwen).
Een parameter gebruiken om gegevens te filteren
Soms wilt u een eenvoudige manier om het filter van een query te wijzigen zodat verschillende resultaten worden verkregen zonder de query te bewerken of enigszins andere kopieën van dezelfde query te maken. In dit voorbeeld wijzigen we een datum om zo handig een gegevensfilter te wijzigen.
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 of bewerken in Excel voor meer informatie.
Selecteer de filterpijl in een kolomkop om uw gegevens te filteren en selecteer vervolgens een filteropdracht, zoals Datum/tijd-filters>na. Het dialoogvenster Rijen filteren wordt weergegeven.
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 aan de rechterkant.
- Als u een nieuwe parameter wilt gebruiken, selecteert u Nieuwe parameter en maakt u een parameter.
Voer de nieuwe datum in het vak Huidige waarde in en selecteer vervolgens Start>sluiten & laden.
U bevestigt uw resultaten door nieuwe gegevens aan de gegevensbron toe te voegen en vervolgens de gegevensquery te vernieuwen met de bijgewerkte parameter (Selecteer >Alles vernieuwen). Wijzig bijvoorbeeld de filterwaarde in een andere datum als u nieuwe resultaten wilt zien.
Voer de nieuwe datum in het vak Huidige waarde in.
Selecteer Start,>sluiten & laden.
U bevestigt uw resultaten door nieuwe gegevens aan de gegevensbron toe te voegen en vervolgens de gegevensquery te vernieuwen met de bijgewerkte parameter (Selecteer >Alles vernieuwen).
Een celwaarde gebruiken om gegevens te filteren
In dit voorbeeld wordt de waarde in de queryparameter gelezen uit een cel in de werkmap. U hoeft de parameterquery niet te wijzigen, u hoeft alleen de celwaarde bij te werken. U wilt bijvoorbeeld een kolom filteren op de eerste letter, maar de waarde eenvoudig wijzigen in een willekeurige letter van A tot Z.
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.
MijnFilter G Selecteer een cel in de Excel-tabel en selecteer vervolgens Gegevens>ophalen>uit tabel/bereik. De Power Query-editor wordt weergegeven.
Wijzig in het vak Naam van het deelvenster Query-instellingen aan de rechterkant de naam van de query in een duidelijkere naam, zoals FilterCelwaarde.
Als u de waarde in de tabel wilt doorgeven en niet de tabel zelf, klikt u met de rechtermuisknop op de waarde in Voorbeeld van gegevens 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 hebt gebruikt, verwijst Power Query naar de tabelwaarde als filtervoorwaarde. Een rechtstreekse verwijzing naar de Excel-tabel zou een fout veroorzaken.Selecteer Start,>sluiten & laden>, sluiten & laden. U hebt nu een queryparameter met de naam FilterCellValue, die u in stap 12 hebt gebruikt.
Selecteer in het dialoogvenster Gegevens importerenAlleen verbinding maken en selecteer vervolgens OK.
Open de query die u wilt filteren met de waarde in de tabel FilterCellValue, een waarde die eerder is geladen vanuit de Power Query-editor, door een cel in de gegevens te selecteren en vervolgens Querybewerking> teselecteren. Zie Een query maken, laden of bewerken in Excel voor meer informatie.
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.
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.
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"))
Selecteer de waarde van het filter. Selecteer G in de formule.
Met behulp van M Intellisense voert u de eerste letter in van de tabel FilterCellValue die u hebt gemaakt en selecteert u deze vervolgens in de lijst die wordt weergegeven.
Selecteer Start,>Sluiten,>Sluiten & laden.
Resultaat
De 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 vervolgens de query.
Het gebruik van parameterquery's beheren
U kunt bepalen of parameterquery's wel of niet zijn toegestaan.
- Selecteer in de Power Query-editor Bestandsopties>en Instellingen>Queryopties>Power Query-editor.
- Selecteer in het deelvenster aan de linkerkant, onder GLOBAL,de optie Power Query-editor.
- Schakel in het deelvenster aan de rechterkant onder Parametersaltijd parametrisering toestaan in dialoogvensters voor gegevensbron en transformatie in of uit.