Na enkele voorbereidende transformaties uitgevoerd te hebben is het tijd om een zoekvraag te definiëren. In dit artikel staan we iets uitgebreider stil bij een simpele feature die we eerder al aanstipten: filteren.

Een zoekvraag is heel snel te verfijnen in Excel dankzij Power Query. Laten we kijken naar een verfijning van Microsofts voorbeeldspreadsheet Financial Sample. Hierin staan producten met onder meer kostprijs, verkoopprijs, verkochte aantallen, et cetera. In diverse voorbeelden gebruiken we deze spreadsheet, dus sla hem vooral op om later terug te keren. Onthoud ook dat als u aan het einde van een oefening kiest voor Sluiten en laden naar… (Close And Load To…) de zoekvraag en verbinding naar de data op een nieuw werkblad (of andere locatie die u dan kiest) wordt neergezet. Als u daarna de spreadsheet opslaat, kunt u eenvoudig terugkeren naar de zoekvraag en eventuele toegepaste stappen eruit halen terwijl u tussenliggende stappen behoudt.

Met filters kunt u snel een vraag beantwoorden op de hoeveelheid gegevens. Bijvoorbeeld door alleen het product Paseo uit Duitsland en Frankrijk weer te geven. Open de spreadsheet en kies voor Gegevens > Gegevens ophalen > Uit andere bronnen > Van tabel/bereik (Data > Get Data > From Other Sources > From Table/Range) (of klik op de knop Van tabel/bereik (From Table/Range) rechts van Gegevens ophalen) om de Power Query-editor te starten.

Dit opent de tabel van 700 rijen en 16 kolommen. Power Query wijst automatisch een datatype toe aan de kolommen, bijvoorbeeld tekst of getal. In dit geval is onze standaardinstelling dat Power Query de kolomnotatie van de spreadsheet zelf overneemt. Automatische detectie noch overname van kolominstellingen klopt niet altijd, dus het verdient aanbeveling om eerst te bekijken welke datatypes zijn toegewezen.

Tip: Als u niet wilt dat Power Query deze detectie uitvoert, gaat u in de editor naar Start > Opties en instellingen > Queryopties > Gegevens laden (Start > Options and settings > Query Options) en kiest u voor Kolomtypen en -koppen voor ongestructureerde bronnen nooit detecteren (Never detect column types and headers for unstructured sources).

Zo ziet u hier dat velden als kostprijs en winst een getalwaarde hebben gekregen, terwijl ze eigenlijk een valutawaarde nodig hebben. In de balk bovenin ziet u de huidige acties die zijn uitgevoerd in de taal M. Door deze balk uit te klappen ziet u in één oogopslag welke kolommen welk type hebben gekregen:

De meeste kolommen hebben we voor de zoekvraag waarschijnlijk niet nodig, maar laat ze vooralsnog even staan, want misschien willen we nog meer weten na de eerste verfijning. De verfijning die we in eerste instantie zoeken, alleen de gegevens van producten die in Duitsland en Frankrijk zijn verkocht.

Laten we voor we verder gaan even een aantal datatypes alvast aanpassen. De kolom Manufacturing Price heeft bijvoorbeeld de waarde Geheel getal (Whole Number, of Int.64 in M) gekregen, maar dat is eigenlijk een prijs in dollars in het formaat $0,00. De prijzen in de spreadsheet zijn allemaal afgerond op een hele prijs, vandaar dat Power Query dit interpreteerde als geheel getal. Dit past u aan in het veld Gegevenstype (Data Type), in het Lint onder Start > Tranformeren > Gegevenstype (Home > Transform > Data Type). De kolom staat nu ingesteld op Geheel getal, maar daar maakt u Valuta van. Power Query vraagt of u het kolomtype wilt aanpassen. Als u hier kiest voor ‘Nieuwe stap toevoegen’ wordt deze transformatie een stap die u ongedaan kunt maken in de Toegepaste stappen (Applied Steps) aan de rechterzijde. Als u kiest voor ‘Huidige vervangen’ wordt deze wijziging meegenomen in de reeds toegepaste stap ‘Type gewijzigd’.

Hetzelfde kunt u doen voor Sale Price, Gross Sales, Discounts, Sales, COGS en Profit die in de tabel allemaal in dollars zijn vermeld. In het veld Date is de machinedatum gepakt, in het formaat D-MM-JJJJ UU:MM:SS. Dat is voor nu niet erg, aanpassingen in cosmetische formattering doet u uiteindelijk weer in de Excel-tabel zelf. Voor Power Query is volledige informatie alleen maar handig, het voorkomt interpretatieproblemen.

Nu komen we bij de eerste zoekvraag: enkel het product Paseo uit Duitsland en Frankrijk voor 10 dollar of lager, zonder hoge korting. U kent de stappen waarschijnlijk al wanneer u de vorige artikelen hebt gelezen: u filtert de tweede kolom op randvoorwaarde met twee parameters. Dat doet u door in de kolom op het pijltje naar beneden te klikken. Daar vinkt u Alles selecteren (Select All) uit en vinkt u alleen Germany en France aan. Klik op OK om de resultaten te verfijnen. Helemaal onderaan ziet u dat het overzicht is gegaan van 16 kolommen, 700 rijen naar 16 kolommen, 280 rijen. Doe vervolgens hetzelfde op de kolom Product enkel product Paseo te kiezen (nog 80 rijen over).

U kunt getalfilters gebruiken, om bijvoorbeeld te zoeken op Paseo in Duitsland en Frankrijk die verkocht zijn voor een lager bedrag dan de fabrieksprijs. Aangezien dat 10 dollar is, maakt u een filter om alle waardes lager dan 10 te selecteren in de kolom Sale Price. Kies Getalfilters > Kleiner dan (Number Filters > Less Than) en vul 10 in. Nou ziet u al aan de filters dat er maar één waarde wordt genoemd die lager ligt, en dat is 7, dus in dit geval is het ook mogelijk om op de simpelste manier te filteren. Er blijven nu 14 records over.

De laatste stap uit onze zoekvraag is om de verkochte units Paseo met hoge korting eruit te filteren. Dat doet u met een filter op de kolom Discount Band. Daar vinkt u ‘High’ uit, zodat ook deze resultaten worden uitgefilterd. Alternatief kunt u ook kiezen voor Tekstfilters (Text Filters) en dan te kiezen voor Bevat niet (Does Not Contain) met de waarde High. Dit levert een tabel met 11 records op:

Dit is een uitgelezen moment om de Toegepaste stappen (Applied steps) toe te lichten. In dit overzicht ziet u alle stappen die nu zijn uitgevoerd. Dat zijn in dit geval de gewijzigde datatypes (de originele wijzigingen van Power Query zelf en onze eigen aanpassingen) en de rijfilters. Met dit venster is het eenvoudig een stap ongedaan te maken en terug te keren naar een eerdere versie van de zoekvraag. In het laatst toegepaste filter kunt u zelfs op het tandwiel klikken om het dialoogvenster Rijen filteren opnieuw te openen om parameters te wijzigen. Door op een stap hoger te klikken, ziet u de situatie van voor de toegepaste stap. Wat gebeurt er als u een eerder genomen stap uit deze lijst trekt? U krijgt ten eerste een waarschuwing van Power Query zelf dat dit gevolgen kan hebben voor vervolgstappen die afhankelijk waren van deze stap:

We zien in het overzicht de volgende stappen (mits u deze met Nieuwe stap toevoegen heeft aangemaakt):

U begrijpt dat dit een onoverzichtelijke boel wordt als de standaardnaamgeving maar doorgaat met Rijen gefilterd7 en Rijen gefilterd8. Het is daarom aan te bevelen om voor de overzichtelijkheid op een stap te gaan staan en met de Rechtermuisknop ‘Naam wijzigen’ te selecteren om bijvoorbeeld ‘Rijen gefilterd2’ te veranderen naar ‘Getalfilter: < 10’. Als u de uiteindelijke tabel op een later moment als query opnieuw opent in de Power Query-editor, ziet u ook meteen wat er precies is gedaan. Dat is niet alleen wenselijk, maar vereist wanneer u met meerdere personen in dezelfde Excel-sheet werkt, zodat zij ook meteen snappen wat Rijen gefilterd7 precies is.

In dit geval is de vervolgstap waar we naar kijken – het toepassen van een filter op kleiner dan 10 – niet afhankelijk van deze stap. Dus als we deze weghalen, betekent dit dat het getalfilter kleiner dan 10 wordt toegepast op alles behalve de stap met het eerste tekstfilter: de landen Duitsland en wordt niet langer gefilterd. Als we nu weer naar de laatste stap bewegen, zien we alle producten Paseo die voor minder dan 10 dollar zijn verkocht maar geen hoge korting hadden, ongeacht het land:

Hierna slaat u deze zoekvraag op met Sluiten en laden naar… (Close And Load To) waarna de query als tabel wordt weergegeven. Deze kunt u op een later tijdstip verder bewerken door rechts op de query te klikken onder Query’s en verbindingen (Queries and Connections) en te kiezen voor Bewerken (Edit). Dat opent wederom de Power Query-editor.

Hierna: Groeperen

Volgende keer in deze reeks kijken we naar het groeperen van gegevens. Dat is een belangrijk onderdeel van Power Query waar we in latere delen nog vaker op terug zullen komen en vooral een investering is in de middenlangetermijn. Maar in eerste instantie kunt u al voordeel behalen met het verkennen van Groeperen (Group) om bijvoorbeeld delen van tabellen direct te sommeren of mediaan te bepalen zonder daarvoor een formule in te hoeven voegen.

Volg CMWeb op LinkedIn!