In dit tweede deel blijven we bij het begin en werpen we een blik op de Query Editor. Hierin past u gegevens aan om ze weer te geven op de manier die u wenst. Cm: legt uit hoe dat werkt en wat de opties zijn.
In het Nederlands heet Power Query ‘Gegevens ophalen en transformeren’. In het vorige deel hebben we uitgelegd wat de tool is en wat u ermee kunt, nu kijken we praktisch waar de elementen zitten en wat ze doen. Het basisidee is dat u een set van gegevens selecteert, bijvoorbeeld een werkblad in Excel, en deze bewerkt in een apart venster, de editor. In het volgende deel van deze serie staan we uitgebreid stil bij welke databronnen allemaal mogelijk zijn en hoe u deze gegevens importeert.
Query editor
Voor nu blijven we even bij spreadsheets binnen Excel zelf. Onder het tabblad Gegevens staan diverse opties voor het gebruiken van zoekvragen. De categorie ‘Gegevens ophalen en transformeren’ (Get & Transform Data) is de sectie voor het starten van de editor. Hier selecteert u gegevens. Als u eenmaal een query (zoekvraag) hebt gebruikt, zijn er opent ook de tab Query helemaal rechts in het lint, waar u acties kunt uitvoeren zoals het vernieuwen van gegevens voor de zoekvraag en andere functies om de query te gebruiken.
Als u gegevens selecteert, in dit voorbeeld gebruiken we een dataset van het CBS, kiest u bijvoorbeeld Gegevens ophalen en transformeren > Van tabel/bereik (Get Data > From Table/Range) om de editor te starten. Die ziet er als volgt uit:
Het lint
Hier kunt u ook overstappen naar de geavanceerde editor, waar u code kunt bewerken in de M-taal, maar dat is voor gevorderden. Kijk eerst eens naar de opties die u hier ziet in het lint. Onder ‘Start’ (Home) ziet u basale opties die we gaan benutten in latere delen. Dat zijn bijvoorbeeld knoppen om eenvoudige dubbele waarden te verwijderen, kolommen te splitsen op bijvoorbeeld scheidingstekens of kolommen te sorteren.
U zult hier vooral dingen zien die ook prima met Excel kunnen, maar vaak daar vaak op een omslachtiger. Bovendien zal binnenkort in deze reeks blijken hoe handig het is om deze acties voortaan met Power Query uit te voeren. Een plek verder is het tabblad in het lint genaamd ‘Transformeren’ (Transform). Hier ziet u ook enkele van deze opties, maar ook geavanceerdere trucs als het transponeren van gegevens, het toewijzen van een custom gegevenstype op basis van data uit de selectie, enkele wiskundige features en opties voor het interpreteren van XML-bestanden.
In het tabblad daarnaast vindt u meer opties voor het manipuleren van nieuwe kolommen onder de kop ‘Kolom toevoegen’ (Add Column). Daarmee kunt u bijvoorbeeld een kolom toevoegen op basis van enkele randvoorwaarden of een voorwaardelijke kolom maken op basis van selectiecriteria uit het originele gegevensblad. In het laatste tabblad, ‘Weergeven’ (View), ziet u meer over de data en hoe de gegevens worden gepresenteerd.
Balk met M-code
Onder het lint de formulebalk die u wellicht ook in Excel zelf heeft geactiveerd en daarna een balk met de query uitgeschreven in M. Daar hoeft u zich niet mee bezig te houden. Elke actie die u via een knop toevoegt, bijvoorbeeld zoiets simpels als het aanpassen van een kolomkop, ziet u hierin ook terug in de achterliggende querytaal. Het aanpassen van een kolomkop levert dan bijvoorbeeld de M-code =Table.RenameColumns({{“oudenaam”, “nieuwenaam”}}) op. Dat heeft u nu absoluut niet nodig, maar deze feature gaat handig worden zodra u wat meer bedreven raakt in Power Query.
Queryinstellingen
Ten slotte staat rechts van de tabelweergave de Queryinstellingen (Query settings). Behalve de titel en omschrijving is er een venster Toegepaste stappen (Applied Steps). Dit is een functie om acties die later bij nader inzien toch niet zo handig waren alsnog ongedaan te maken. Of om fouten met één klik ongedaan te maken. Werken met de query en experimenteren met verschillende opties heeft zo een lage impact, omdat ze makkelijk terug te draaien zijn.
Power Query detecteert automatisch de gegevenstypes: bijvoorbeeld als een kolom uit enkel valuta bestaat of een andere uit datums. Deze detectie werkt vrij goed, maar niet altijd. Met de knop Gegevenstype (Data Type) bovenin kunt u bijvoorbeeld een kolom die de waarde Tekst heeft gekregen omzetten naar bijvoorbeeld een percentage, datum of booleaanse waarde. Daarom staat er rechts in de Toegepaste stappen vrijwel altijd ‘Type gewijzigd’ (Changed Type) direct na de stap van het koppelen van de bron.
U kunt kiezen om een reeks vanaf een bepaald moment te verwijderen, of om alleen een misstap eruit te halen. Dat eerste heeft wel de voorkeur, want het verwijderen van een stap kan een impact hebben als deze in de rest van de query fundamenteel is. Stel dat u een gegevensveld met decimale getallen aanpast van tekst naar getal en u maakt deze stap in een later stadium ongedaan. Een tussenliggende stap die rekent met getallen loopt daardoor mis en dat veroorzaakt een kettingreactie in de query.
In deel 3
Nu we weten waar alle elementen zitten richten we ons in het volgende artikel op de eerste stap om aan de slag te kunnen gaan: importeren van gegevens. Het toevoegen van een tabel uit een spreadsheet wijst zich bijna vanzelf, maar Power Query heeft tal van opties om gegevens te verbinden, bijvoorbeeld uit een externe database of een website. Deze kunt u dan aanpassen naar wens in de editor om te gebruiken met uw bestaande spreadsheet of dashboard.
Eerder in deze reeks: Excel Power Query voor beginners (1): Wat is Power Query?
cm:Excel
Dé nieuwsbrief voor de financial die meer wil weten over Excel. In de maandelijkse nieuwsbrief staat informatie over nieuwe toepassingen, handigheidjes, tips en trucs en opleidingen.Schrijf u vandaag nog in voor onze gratis Excel nieuwsbrief!
