Bij het maken van maandrapportages en budgetten dienen bedragen per grootboekrekening of project verdeeld te worden over de maanden. Dit heeft nogal voeten in de aarde aangezien er diverse berekeningen bij betrokken zijn en het veel tijd vergt indien er aanvullingen komen. Met behulp van Power Query kan dit proces vrijwel worden geautomatiseerd.

Uitgangspunt

We beginnen met de volgende contractentabel, die als een Excel-tabel is opgemaakt. De naam van de tabel is DataContract.

Het verschil tussen start- en einddatum wordt in maanden berekend door middel van de volgende formule: =DATUMVERSCHIL([@Start];[@Eind];”M”)

Berekenen van minimum- en maximumdatum

In de volgende cellen berekenen we minimum- en maximumdatum voor de aanwezige contracten.

Beide bereiken worden als Excel-tabellen opgemaakt. De namen hiervoor zijn: MinDate en MaxDate.

De formule voor MinDate is: =DATUM(JAAR(MIN(DataContract[[Start]:[Eind]]));MAAND(MIN(DataContract[[Start]:[Eind]]));1)

De formule voor MaxDate is: =DATUM(JAAR(MAX(DataContract[[Start]:[Eind]]));MAAND(MAX(DataContract[[Start]:[Eind]]));1)

Tabellen opladen naar transformeren

U dient nu alle drie de tabellen op te laden. Ga als volgt te werk:

• Plaats  de cursor op de tabel.
• Uit het lint kiest u: Gegevens > Gegevens ophalen en transformeren > Van tabel/bereik.
• U belandt nu in de Power Query omgeving.
• Uit het lint kiest u: Start > Sluiten en laden > Sluiten en laden naar… > Alleen verbinding > OK.

Aan de rechterkant zult een Query-paneel zien met de bijbehorende query’s.

Transformeren van de datumtabellen in Power Query

Als u nu op een de van query’s klikt komt u weer in de Power Query omgeving. Converteer MinDate en MaxDate naar variabelen. Daartoe kiest u de gewenste datumquery en klik met de rechtermuisknop op het veld. Uit het verkorte menu kiest u: Inzoomen. Daarna verandert u de opmaak van het veld in Datum.

Aanmaken van een dynamische kalendertabel

U dient nu een kalendertabel te maken van alle maanden beginnend bij de minimumdatum en doorlopend per maand tot en met de einddatum.

• Kies uit het Power Query Lint: Start > Nieuwe query > Nieuwe bron > Andere bronnen > Lege query.
• In de formulebalk voert u de volgende formule in:
  = List.Generate(()=>MinDate, each _ <= MaxDate, each Date.AddMonths(_, 1))
• Verander de naam van Query1 in DatumReeks.

U ziet de volgende tabel verschijnen:

Transformaties DataContract

U voert de volgende transformaties uit voor de tabel DataContract:

• Aanpassen opmaak datumkolommen. Markeer kolommen Start en Eind. Uit het Lint kiest u dan: Start > Transformeren > Gegevenstype > Datum.
• Toevoegen Allocatiebedrag: kies uit het lint: Kolom toevoegen > Algemeen > Aangepaste kolom en geef als formule in: [Contractbedrag]/[Maanden].

• Toevoegen Datumreeks: kies uit het lint: Kolom toevoegen > Algemeen > Aangepaste kolom en geef als formule in: = List.FirstN(DatumReeks,[Maanden])
• Klik op de dubbele pijl bij StartDatum en kies: Uitvouwen naar nieuwe rijen.
• Pas de opmaak van StartDatum aan naar datum.
• Toevoegen EindDatum: kies uit het lint: Kolom toevoegen > Algemeen > Aangepaste kolom en geef als formule in: = Date.EndOfMonth([StartDatum])
• Pas de opmaak van EindDatum aan naar datum.
• Kies uit het lint: Start > Sluiten en laden > Sluiten en laden > Tabel > Nieuw werkblad > OK.

De allocatietabel ziet er als volgt uit:

Aansluiten allocatietabel met oorspronkelijke contractentabel

Tenslotte controleert u of het totaal van de allocatietabel aansluit met de oorspronkelijke contractentabel.

De formules zijn als volgt:

• Totaal contractbedrag: =SOM(DataContract[Contractbedrag])
• Totaalbedrag gealloceerd: =SOM(DataContract_2[Allocatiebedrag])

Draaitabel

U kunt nu op basis van de allocatietabel de volgende draaitabel per jaar en per contract maken:

Bijwerken gegevens

Zodra u de contractentabel heeft aangevuld, kunt u direct de allocatietabel en draaitabel bijwerken. Daartoe kiest u uit het lint: Gegevens > Query’s en verbindingen > Alles vernieuwen.

Auteur: Tony De Jonker

Dit artikel is verschenen in cm: 2022, afl. 4.

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

Volg CMWeb op LinkedIn!