PBi_2_EXTRA_Eigen datumtabel aanmaken

Als we in de queries kijken die we hebben binnengetrokken zien we dat de ‘Calendar’ daar aanwezig is. Deze bevat een heel aantal kolommen die we kunnen gebruiken.

Zo een uitgebreide kalendertabel is een luxe, maar niet altijd nodig.

Van de andere kant kan het gebeuren dat verschillende queries wel datums bevatten maar dat er geen entiteit ‘Dates’, ‘Calendar’, ‘Datums’ of iets dergelijks aanwezig is.

Dan is het een goed idee om er één te voorzien.

Kijk eerst zeker ook of bij de brondata of in het DWH misschien al een kalender-tabel in gebruik is die je kan overnemen.

Moet je toch een eigen tabel maken, dan zijn er 2 manieren:

• Via DAX
• Via de Power Query en M

DAX

Je kan met de DAX-functie CALENDARAUTO() of CALENDAR() jouw algemene datumtabel bouwen.

De functie CALENDAR() retourneert een aaneengesloten reeks datums op basis van een begin- en einddatum die als argumenten in de functie worden ingevoerd.

De functie CALENDARAUTO() geeft een aaneengesloten reeks datums die automatisch het volledige bereik van uw gegevensset omvat: de begindatum wordt gekozen als de vroegste datum die in uw dataset bestaat, en de einddatum is de laatste datum die in uw dataset bestaat.

• Ga naar het tabblad ‘Tabelhulpmiddelen‘ > groep ‘Berekeningen‘ > ‘Nieuwe tabel‘ en voer vervolgens de volgende DAX-formule in:

Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))

Je zal de nieuwe tabel bij de andere tabellen zien staan. Merk het pictogram van het rekenmachientje op bij de tabel onder de velden.

Je kan ook zien dat hier bij de ‘Dates’-tabel eveneens een structuur is toegevoegd, waarmee je kan visualiseren volgens ‘Jaar‘, ‘Kwartaal‘, ‘Maand‘ of ‘Dag‘.

Willen we hier nu een bijkomende kolom toevoegen, waar enkel het jaar in hernomen wordt, of het maandnummer, het weeknummer of de dag van de week… Dat kan!

Tabblad ‘Kolomhulpmiddelen‘ > groep ‘Berekeningen‘ > ‘Nieuwe kolom‘

Year = YEAR(Dates[Date])

Je kan op dezelfde manier de andere kolommen aanmaken. Gebruik onderstaande formules om tot mijn resultaat te komen.

Formules

• MonthNum = MONTH(Dates[Date])
• WeekNum = WEEKNUM(Dates[Date])
• DayoftheWeek = FORMAT(Dates[Date], “DDDD”)

MonthNum = MONTH(Dates[Date])

WeekNum = WEEKNUM(Dates[Date])

DayoftheWeek = FORMAT(Dates[Date], "DDDD")

Resultaat

De nieuw aangemaakte tabel kan je ook terugvinden in jouw relatie-venster, maar je zal deze NIET tussen jouw queries zien staan als je jouw gegevens gaat transformeren.

POWER QUERY – M

Je kan ook de taal ‘M’ gebruiken om jouw queries op te bouwen in de Power Query Editor.

Ga naar jouw Query Editor en klik met de rechtermuisknop in de zijbalk met de verschillende queries en kies ‘Nieuwe query‘ > ‘Lege query‘.

Voeg volgende code toe:

= List.Dates(#date(2011,05,31), 365*12, #duration(1,0,0,0))

= List.Dates(#date(2011,05,31), 365*12, #duration(1,0,0,0))

Zoals je ziet heb je hier nu een lijst met datums, maar we hebben een tabel met datums nodig. Dit doe je via de uiterst rechtse tabblad ‘Transformeren‘ > groep ‘Converteren‘ > opdrachtknop ‘Naar tabel‘.

Je kan nu het datatype van deze kolom aanpassen naar ‘Datum‘.

Nu kan je hier bijkomende kolommen toevoegen voor ‘Jaar’, ‘Maand’,…

Doe dit door het tabblad ‘Kolom toevoegen‘ te selecteren > groep ‘Van datum en tijd‘ > opdrachtknop ‘Datum‘ > ‘Maand‘

De kolom maand wordt toegevoegd. Op dezelfde manier kan je ook andere kolommen toevoegen om tot volgend resultaat te komen.

We hebben nu 3 verschillende tabellen met datums. Afhankelijk van jouw datasets zal je bepaalde velden wél of niet nodig hebben. Je kan ook altijd nog kolommen toevoegen aan de tabel.

Nu moet je nog meegeven dat een specifieke tabel als Data-tabel behandeld moet worden. Dit doe je door met de rechtermuisknop op de tabel te klikken en ‘Als datumtabel markeren‘ te kiezen.