Soms moet je de maanden van een project weten. Dus als een project 4 jaar duurt, wil je graag een periode van 48 maanden hebben. In deze blog leggen we uit hoe je dit kunt doen met Power Query. In deze blog gebruiken we de standaard Power Query-opties: je hoeft niet te rommelen in de […]
Soms moet je de maanden van een project weten. Dus als een project 4 jaar duurt, wil je graag een periode van 48 maanden hebben. In deze blog leggen we uit hoe je dit kunt doen met Power Query. In deze blog gebruiken we de standaard Power Query-opties: je hoeft niet te rommelen in de geavanceerde Editor. Houd er rekening mee dat Power Query altijd een statische tabel retourneert in uw gegevensmodel. Als u meerdere projecten heeft met verschillende startdatums, kunt u beter een dynamische DAX-formule in uw gegevensmodel maken om die situatie op te lossen.
Ik heb een datumtabel gemaakt die vier jaar bestrijkt (2017-2020). Ik heb een kolom met het jaartal (getal) en een kolom met de maand (getal) toegevoegd. De definitie van een datumtabel in Power BI is een tabel met ten minste één kolom, die alle datums op unieke wijze omvat van datums die in uw Power BI-gegevensmodel voorkomen. Heeft u dus een feitentabel die slechts twee data bevat: 1 januari 2010 en 14 april 2012, dan heeft u toch een datumtabel nodig met minimaal alle data tussen 1-1-2010 en 14-4-2012.
Dit zijn de stappen die we nemen om het projectmaandnummer toe te voegen:
Begin met het maken van de Datumtabel. Je kunt deze tabel in allerlei programma’s aanmaken, bijvoorbeeld Excel of Power Query. Ik heb de query SourceDate aangeroepen.
De eerste stap is het kopiëren van de query. Om dat te doen heb je twee opties: Dupliceren en Verwijzen.
De eerste optie is een exacte kopie. Dat betekent dat alle gemaakte stappen beschikbaar zijn in de gedupliceerde tabel. De tweede verwijst naar de vraag. In dit geval bevinden de stappen in de oorspronkelijke query zich niet in de kopieerquery, maar begint de nieuwe query gewoon aan het einde van de eerste query. De Reference lijkt dus de snellere oplossing, omdat de stappen slechts één keer worden uitgevoerd. Maar er zijn ook nadelen. Het is bijvoorbeeld niet mogelijk om een zoekopdracht waarnaar wordt verwezen samen te voegen met de zoekopdracht waarvan deze is afgeleid. Dus in dit geval dupliceren we de query.
We moeten de nieuwe zoekopdracht in – wederom – vier eenvoudige stappen transformeren. Eerst verander je de datum in een jaar, daarna verwijder je duplicaten. Vervolgens sorteert u zodat de datums altijd in de juiste volgorde staan en voegt u vervolgens een indexkolom toe, beginnend bij 0.
Ik heb de query hernoemd in YearIndex. De volgende stap is het samenvoegen van het nieuw gemaakte indexnummer met onze datumtabel. Begin met de lintoptie Start, query’s samenvoegen als nieuw.
De volgende stap is het uitvouwen van de kolom YearIndex.
Nu kunnen we een kolom toevoegen met de berekening. Om het juiste maandnummer te weten, begint u met het nummer van de maand en telt u vervolgens het indexnummer vermenigvuldigd met 12 op (het nummer van de maand in een bepaald jaar). Omdat de index met nul begint, wordt het maandnummer het eerste jaar niet beïnvloed. Voeg gewoon een berekende kolom toe met de formule.
Hernoem de query naar Datum. Dit werkt prima als het project in de eerste maand van het jaar start, maar hoe corrigeer ik dit getal als het project op een andere maand(nummer) begint? In dit geval moet u de formule aanpassen met het aantal verstreken maanden. Om te laten zien hoe het werkt, heb ik de startdatum van de Datumtabel gewijzigd in augustus 2017.
Dit zijn de stappen die wij ondernemen om de start van de maand aan te passen:
Om dit te bereiken, begint u met een duplicaat uit de DataSource Query. Hernoem deze kolom in StartMaand. Begin met het verwijderen van de kolom Datum. Vanuit de kolomjaar moet u het eerste jaar filteren. Verwijder nu de kolom Jaar. Vanuit de kolom Maand wil je de laagste (minimale) waarde weten. Gebruik de knop Statistieken, Minimum. Het resultaat is een waarde. Keer nu terug naar de datumquery.
Het startmaandnummer voegen we toe vóór de stap met de berekening, zodat we de formule kunnen aanpassen met de nieuwe toegevoegde waarde.
Keur goed dat u een stap ertussen wilt toevoegen en voeg het nummer toe als een aangepaste kolom.
Nu pas je de berekening aan door op het tandwiel aan de rechterkant van de laatste stap te klikken, genaamd Aangepast toegevoegd. Vergeet niet de 1 erbij te zetten, anders zit je een maand laag.
Deze eenvoudige manier is gemakkelijk als u de M-taal niet kent. Als u de code zelf kunt schrijven, kunnen de query’s efficiënter worden gemaakt.
