Een veel voorkomende taak van een financial is het vergelijken van twee bestanden. U bent geïnteresseerd welke records in het tweede bestand voorkomen, maar niet in het eerste bestand en andersom. Of u wilt weten welke records in beide bestanden voorkomen. Er zijn 6 mogelijke varianten om bestanden met elkaar te vergelijken. Met behulp van Power Query (Gegevens ophalen en transformeren/Get Data and transform) is dat in een handomdraai geregeld. In deze bijdrage maakt u kennis met deze mogelijkheden.

Uitleg bestanden

We hebben twee tabellen die er als volgt uitzien:

In het voorbeeld  is elke tabel in een afzonderlijk blad opgeslagen. Het is ook mogelijk dat de tabellen in afzonderlijke bestanden worden opgeslagen. Elk van de tabellen heeft eigen records en er zijn ook records die in beide tabellen voorkomen.

Het is zaak dat beide tabellen als tabel zijn opgemaakt. Dat kan via het Lint: Start à Stijlen à Opmaken als tabel of via Invoegen à Tabellen à Tabel.

De eerste tabel heeft als naam DataSales1 en de tweede tabel heeft DataSales2.

Mogelijke vergelijkingen

Schematisch voorgesteld zijn de volgende 6 vergelijkingen mogelijk:

De gebezigde termen komt u ook tegen als u de tabellen wilt vergelijken met behulp van Power Query.

Laden van bestanden

U plaatst de cursor in de eerste tabel en kiest uit het Lint: Gegevens à Gegevens ophalen en transformeren à van tabel/bereik. De tabel wordt nu in Power Query weergegeven.

U kunt het gegevenstype van Sales veranderen door met de rechtermuisknop op de koptekst Sales te klikken en uit het verkorte menu te kiezen: Type wijzigen à Decimaal getal.

U dient deze tabel als verbinding vast te leggen door uit het Lint te kiezen: Start à Sluiten en Laden à Sluiten en Laden naar… à Alleen verbinding maken à OK

Om de tweede tabel te laden gaat u met cursor naar de tweede tabel en doorloopt dezelfde stappen als voor de eerste tabel.

In Power Query heeft u nu twee tabellen geladen.

Selecteren gewenste vergelijkingsmethode

Dubbelklik op DataSales1 om in Power Query de tabellen met elkaar te vergelijken. Kies uit het menu: Startpaginaà Combineren à Query’s samenvoegen à Query’s samenvoegen als nieuw.

Als eerste bestand wordt DataSales1 weergegeven. Kies als tweede bestand DataSales2 en markeer in beide tabellen de gemeenschappelijke kolom.

Onder Type Join ziet u de 6 mogelijkheden voor vergelijking:

Kies als voorbeeld Right anti join en klik op OK. U ziet direct hoeveel records worden uitgesloten.

Vervolgens ontstaat in Power Query het volgende venster:

Klik op de dubbele pijl en uit het menu deselecteert u de laatste optie.

Klik dan op OK en het volgende beeld verschijnt:

U kunt de eerste twee kolommen verwijderen en de overgebleven kolommen herbenoemen in Country en Sales.

Kies dan Sluiten en Laden à Sluiten en Laden. Excel zal dan een nieuw blad aanmaken met de betreffende records. Als de bron gewijzigd is, kunt door rechtsklikken op de nieuwe tabel en uit het verkorte menu te kiezen voor Vernieuwen, de tabel verversen met nieuwe gegevens.

Het is aan te bevelen om de diverse merge-opties een nieuwe naam te geven, zodat u de onderscheidende query’s makkelijk kunt herkennen.

Dit artikel is verschenen in cm: 2019, afl. 9

Auteur: Tony de Jonker
Excel-deskundige Tony de Jonker geeft tips en trucs, afkomstig van vragen uit de praktijk. Hij wil deze graag met u delen, zodat ook u er uw voordeel mee kunt doen.

Hier leest u meer Excel tips en trucs door Tony De Jonker

cm: Excel Experience Day 2019

Tony De Jonker is consultant en eigenaar van De Jonker Consultancy. Hij helpt bedrijven met specifieke kennis op het snijvlak van business/finance en het ontwikkelen van Office applicaties. Hij zal één van de Excel-experts zijn op de cm: Excel Experience day op 12 december 2019 te Utrecht. De cm: Excel Experience Day is dé jaarlijkse place-to-be voor de startende én ervaren Excelgebruikers die alles uit Excel willen halen!

U werkt dagelijks met Excel en weet hoe oneindig veel mogelijkheden en toepassingen deze tool biedt. Hoe kunt u nog sneller, slimmer en efficiënter omgaan met Excel?

Grijp uw kans om in één dag uw Excelkennis een boost te geven en ontdek nieuwe mogelijkheden, toepassingen en tips!

>> Bekijk hier het programma van de cm: Excel Experience Day 2019