Handmatig knippen en plakken van Excel-sheets om ze samen te voegen is niet alleen tijdrovend, het is ook foutgevoelig. In deze tutorial pakken we Power Query om dergelijke consolidatie snel en betrouwbaar uit te voeren.

Een tijd vergende klus is het consolideren van diverse uniforme Excel-bestanden in één bestand welke dient als basis voor verdere rapportages en analyses. Denkt u daarbij aan het consolideren van dochtermaatschappijen, filialen of budgethouders. Het handmatig kopiëren en plakken kost de nodige tijd en kan tot fouten leiden. Het is mogelijk om voor een dergelijke klus een VBA-macro te schrijven, maar deze vaardigheid is niet voor iedereen weggelegd. Gelukkig hebben we tegenwoordig Power Query waarmee u interactief de consolidatie met enkele stappen heeft uitgevoerd.

Uitgangspunt

U dient voor elk bestand een uniforme lay-out aan te maken waarin de cijfers worden ingevoerd of berekend. Een dergelijk bestand kan er als volgt uitzien:

In dit bestand worden de omzetrecords per dag bijgehouden. U dient de gegevens als tabel op te maken via het commando: Invoegen → Tabel.
U dient de tabel de naam DataSales toe te kennen. Dat gaat via het Lint: Hulpmiddelen voor Tabellen → Ontwerpen → Eigenschappen → Tabelnaam.

Let erop dat u na het intypen van de naam de Entertoets indrukt om de invoer te bevestigen. De naam mag geen spaties bevatten. Om de leesbaarheid te verhogen maak ik gebruik van de Camel case notatie, waarbij hoofdletters afgewisseld worden met kleine letters. Het tabblad zelf heb ik data genoemd. U maag daarvoor een willekeurig andere naam voor gebruiken. In ons voorbeeld hebben we vier bestanden: Oost, West, Zuid en Noord.

Deze vier bestanden dienen in een afzonderlijke map te worden opgeslagen waar geen andere Excel bestanden in voorkomen.

Werken met Power Query

Om de bestanden te consolideren gaat u als volgt te werk:

• Open een nieuw bestand
• Uit het Lint kiest u het commando: Gegevens → Gegevens ophalen en transformeren → Gegevens ophalen → Uit bestand → Uit map

• Er verschijnt een dialoogvenster waarmee u via de Bladeren-knop de betreffende map kunt selecteren waar de afzonderlijke bestanden zijn opgeslagen. Daarna klikt u op OK.

• Het volgende scherm verschijnt waarin u alle bestanden in de gekozen map kunt zien:

• Als u op de knop Combineren klikt heeft u de volgende mogelijkheden:

• Met de eerste optie kunt u de query aanpassen. De derde optie stelt u in staat om de gegevens naar het Datamodel te laden. We kiezen de tweede optie waardoor alle gegevens naar een nieuw Excel-blad worden overgebracht.
• In het volgende dialoogscherm klikt u bij Weergave-opties op de tabel DataSales

• Als je op OK-knop klikt zal het enige seconden duren voordat alle gegevens in een tabel bij elkaar zijn gevoegd. Het resultaat ziet er als volgt uit:

• Naast de reeds bekende kolommen maakt het systeem een eerste nieuwe kolom aan waarin de naam van het bestand wordt vermeld, zodat u altijd kunt traceren waar de gegevens vandaan komen. De tabel krijgt automatisch de naam toegekend van de sub map, in casu Filialen.

Aan de rechterkant ziet u de stappen die de Query Editor heeft gebouwd.

Als u de muis boven Filialen beweegt, ziet u de daarbij behorende gegevens, waaronder de laatst vernieuwde datum.

Toevoegen nieuwe bestanden

Stel u voegt een nieuw bestand genaamd Midden toe in de map, dan kunt u deze gegevens direct zien als u de Query gaat vernieuwen. Dat kan op 3 manieren:

1. In de tabel kunt u met de rechtermuisknop klikken en uit het verkorte menu kiest u Vernieuwen
2. Uit het Lint selecteert u: Gegevens -> Query’s en verbindingen -> Alles vernieuwen -> Vernieuwen
3. Uit het rechterpaneel Query’s en verbindingen klikt u met de muis op het symbool in de laatste regel

Dit artikel is eerder verschenen in cm: 2018, afl. 7

Auteur: Tony de Jonker

Volg CMWeb op LinkedIn!