Tijdzonecorrectie met Power Query M en DAX

Visualiseren van meetwaarden gaat niet goed wanneer bij metingen op hetzelfde moment niet dezelfde tijdstippen (timestamps) staan genoteerd. Er zijn verschillende manieren om een tijdzonecorrectie door te voeren met Power Query M en DAX. In de video en blogpost laat ik er drie zien.

Onlangs werd ik door een collega gevraagd om twee databronnen met logging te combineren. Er waren wat uitdagingen met betrekking tot performance van de werkplek en door inzicht te krijgen in meetwaarden en logbestanden werd gehoopt meer grip te krijgen op de situatie.

De datum- en tijdnotatie in de logbestanden verschilden van elkaar aangezien deze van verschillende servers in verschillende tijdzones afkomsting waren. De kans is groot dat je zoiets hebt meegemaakt wanneer je werkt met een complexe infrastructuur.

Tijdzonecorrectie met Power Query M in Excel of Power BI – Pentiago 365 op YouTube

DAX versus Power Query M

Wanneer je geen verdere verwerking van de gegevens hoeft te doen en de volgende stap visualisatie in Power BI is, is de beste en snelste methode om DAX te gebruiken. Het aanpassen van alle waarden in een kolom heeft negatieve impact op de duur van de query. Probeer dit daarom zo laat mogelijk in je verwerkingsproces te doen en het liefst pas na de query.

Power Query M is bedoeld om de data te vervormen tot een werkbaar formaat. DAX is met name de taal en methode om daadwerkelijk te gaan werken met de gegevens. Meer informatie hierover lees je in deze post van radacad.

Tijdzonecorrectie in DAX

Om de tijdzone te corrigeren met behulp van DAX in Power BI schakel je over naar de dataview.

Dataview icon

1. Klik hiervoor op het icoon voor de dataview.

2. Klik daarna op het tabblad Table tools en kies voor New column.

Tabblad Table tools – New column 3. In de formulebalk die nu verschijnt type je allereerst de naam voor de nieuwe kolom met daarachter ‘= [kolomnaam met huidige datum]+TIME(aantal uren vooruit, 0, 0)

In dit geval is de naam van de kolom die ik wil corrigeren [CollectedDate] en gaat het om een verschil van 2 uur. Wil je terug in de tijd? Kies dan voor een negatief getal.

4. Klik op de V van accepteren of druk op enter. In de nieuwe kolom staat nu de gecorrigeerde tijd.

_New column met de formule + TIME(Hour, Minute, Second) _ Dit is de meest simpele methode en voegt alleen een bepaald aantal uren toe aan de waarde. Een betere methode is om aan het datamodel de juiste tijdzone toe te voegen en deze daarna in de lokale tijdzone weer te geven.

Tijdzonecorrectie in Power Query M

Basis Datum/Tijd waarde (timestamp

In een simpele databron staat wellicht wel een datum/tijd waarde, maar zit in deze waarde geen tijdzone verwerkt.

Wanneer je weet in welke tijdzone de gegevens zijn geregistreerd is het gemakkelijk om dit toe te voegen aan de waarde. Wanneer je dit bij alle datum/tijd-kolommen doet, kun je daarna alles weergeven in de lokale tijdzone.

Kolom toevoegen in Power Query 1. Open de Query Editor, kies de juiste query en voeg een nieuwe kolom toe. Kies hiervoor het tabblad ‘Kolom toevoegen’ en daarna de knop ‘Aangepaste kolom’.

Het scherm wat daarna opent wordt hieronder weergegeven:

Nieuwe aangepaste kolom met de formule voor tijdzonecorrectie

2. Geef de nieuwe kolom een naam en typ deze formule in het formulevak:

= DateTime.AddZone([kolomnaam], oorspronkelijke tijdzone in uren)

In mijn geval gaat het om een ‘timestamp’ vanuit de zone GMT+0. Daarom heb ik een 0 als tijdzonewaarde ingevuld. Indien je een waarde hebt uit de Nederlandse zomertijd, vul je een 2 in.

3. Kies voor OK. Een nieuwe kolom is met de gekozen tijdzone is toegevoegd:

Datum/tijd-waarde met tijdzone

In veel gevallen ben je nu klaar. Power Query begrijpt het principe van tijdzones, waardoor je nu kunt rekenen met verschillende waarden. Met één simpele knop kun je de waarden nog omzetten naar je lokale tijd.

4. Klik op het tabblad ‘Transformeren’ en kies voor ‘Tijd’ en dan ‘Lokale tijd’.

Tijd toevoegen aan datum/tijd waarde in Power Query M

Een derde methode om tijd te corrigeren is ook aan de hand van Power Query. Tijdzonenotaties toevoegen aan waarden kan ook minder overzichtelijk werken. Met deze methode voeg je alleen tijd toe. Dit kan ook een negatieve waarde zijn waardoor je terug gaat in tijd.

1. Open de Query Editor, kies de juiste query en voeg een nieuwe kolom toe. Kies hiervoor het tabblad ‘Kolom toevoegen’ en daarna de knop ‘Aangepaste kolom’.

Kolom toevoegen in Power Query

Het scherm wat daarna opent wordt hieronder weergegeven:

Nieuwe aangepaste kolom met de formule voor het toevoegen van tijd 2. Geef de nieuwe kolom een naam en typ deze formule in het formulevak:

= [CollectedDate] + #duration(0,2,0,0)

In dit geval is [CollectedDate] de oorspronkelijke kolom. Aan de hand van de formule #duration(dagen,uren,minuten,seconden) voeg je een tijdsduur toe aan de oorspronkelijke tijdswaarde.

3. Kies voor OK. Een nieuwe kolom is met de aangepaste tijd is toegevoegd.