Blog Exell: Allocatie van bedragen met Power Query

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 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
Je dient nu alle drie tabellen op te laden. Ga als volgt te werk:

• Plaats de cursor op de tabel.
• Uit het lint kies je: Gegevens > Gegevens ophalen en transformeren > van tabel/bereik.
• Je belandt nu in de Power Query omgeving.
• Uit het lint kies je: Start > Sluiten en laden > Sluiten en laden naar… > Alleen verbinding > Ok

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

Transformeren van de datumtabellen in Power Query
Als je nu op een de van query’s klikt kom je weer in de Power Query omgeving.  We gaan MinDate en MaxDate converteren naar variabelen. Daartoe kies je de gewenste datumquery en klik je met de rechtermuisknop op het veld. Uit het verkorte menu selecteer je: Inzoomen. Daarna verander je de opmaak van het veld in Datum.

Aanmaken van een dynamische kalendertabel
Je 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 voer je de volgende formule in:

= List.Generate(()=>MinDate, each _ <= MaxDate, each Date.AddMonths(_, 1))

• Verander de naam van Query1 in DatumReeks.

Je ziet de volgende tabel verschijnen:

Transformaties DataContract
Je voert de volgende transformaties uit voor de tabel DataContract:

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

• Toevoegen StartDatum: 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 controleer je 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
Je kunt nu op basis van de allocatietabel de volgende draaitabel per jaar en per contract maken:

Bijwerken gegevens
Zodra je de contractentabel hebt aangepast, kun je direct de allocatietabel en draaitabel bijwerken. Daartoe kies je uit het lint: Gegevens > Query’s en verbindingen > Alles vernieuwen.