Ik kreeg deze week een interessante vraag van een van de deelnemers aan een Power BI advanced training. Hij had op een rapport met Power Query een Query gemaakt die de tijd en tijd van de laatste gegevensverversing toont.
In de Power BI service kan de rapporteigenaar op meerdere manieren zien wanneer een dataset voor het laatste ververst is. Maar voor sommige rapportages kan het inderdaad handig zijn deze datum en tijd binnen het rapport te kunnen gebruiken. Bijvoorbeeld in een berekening of gewoon om op het rapport te tonen.
Hij liep echter tegen een vreemd probleem aan. Want in Power BI desktop werkte zijn oplossing perfect. Maar het gepubliceerde rapport liep altijd twee uur achter.
Wat was er hier aan de hand? Hoe los je het op?
Hij had op een rapport een blanco query gemaakt met daarin de M functie DateTime.LocalNow
Dit kun je omzetten naar een tabel in Power Query en in Power BI desktop lijkt dit dan de perfecte oplossing te geven.
De query haalt tijdens het verversen de juiste systeemdatum en tijd op (de tijd van je computer dus):
Maar wanneer de data op de Power BI service ververst wordt ontstaat er een verschil. Op de gegevensset staat de wel juiste verversingstijd.
Maar waarde die uit de query komt en op het rapport getoond wordt komt hiermee nu niet overeen:
Zoals je kan zien loopt deze tijd nu 2 uur achter.
Tijdens het uitvoeren van query (de verversing van de dataset) gebruikt de Power BI service namelijk altijd de UTC-tijd. Dit is een internationale standaardtijd die gelijk is aan de Engelse wintertijd.
Dit kon ik gelijk uitleggen, alleen een juiste oplossing bedenken was lastiger.
TLDR: Ben je niet geïnteresseerd in de uitwerking, onderaan dit artikel staat de complete code om te koppieren
De oplossing:
Een eerste idee was dat je eenvoudigweg 2 uur optelt bij de tijd.
Dit werk op korte termijn wel, maar heeft twee grote nadelen.
- De tijd in Power BI desktop klopt niet meer (die loopt nu 2 uur voor)
- Deze oplossing houdt geen rekening met Zomer en Wintertijd en de functie moet dus twee keer per jaar aangepast worden.
Deze oplossing is dus niet goed genoeg als je echt nauwkeurig wil zijn.
Ik wil hier juist de UTC tijd als basis nemen (die veranderd namelijk nooit) en afhankelijk van de datum daar dan 1 of 2 uur bij optellen. In de zomer dus 2 uur, in de winter 1 uur.
Dit wordt een complexere query van een aantal stappen. Ik moet die dus zelf gaan schrijven in de M taal.
Stap 1
Iedere M query begint met de Let Expressie, hierna kunnen de variabelen (stappen) bepaald worden.
De eerste stap die ik hier nodig heb is de UTC tijd. Hiervoor gebruik ik de functie DateTimeZone.UtcNow.
Deze functie geeft een Datumtijd waarde inclusief de tijdzone. Die zone is niet nodig dus gebruik ik ook de functie DateTimeZone.RemoveZone, die de zone verwijderd.
Stap 2
In de volgende 2 variabelen wil ik de datum en de tijdwaarde los van elkaar uit de ‘UTC’ stap halen, die heb ik namelijk later in de query nodig om de zomertijd te berekenen.
Hiervoor gebruik ik de functies. DateTime.Date en DateTime.Time.
Stap 3
In de volgende stap wil ik het begin en het einde van de zomertijd vaststellen. In Europa is dat altijd de laatste zondag van maart en de laatste zondag van oktober. Dit is dus ieder jaar een andere datum.
Om deze te bepalen gebruik ik de functie Date.StartOfWeek op de laatste dag van maart en oktober van het jaar. Dit jaar moet ik halen uit de ‘UTC’ stap.
De argumenten in Date.StartOfWeek bestaan hier uit een datum die samengesteld wordt met de #date functie (in combinatie met Date.Year) en de Day.Sunday expressie die de eerste dag van de week bepaald.
Stap 4
De volgende stap bepaald of een gegeven UTC-waarde wel of geen zomertijd is. Dit is dus:
- De eerste dag van de zomertijd na 02:00 a.m.
- Iedere dag na de eerste dag en voor de laatste dag
- De laatste dag van de zomertijd voor 03:00 a.m..
Mijn functies worden dus:
Stap 5
De uitkomst van stap 4 is een boolean, deze geeft True als de UTC in de zomertijd valt, en False als dat niet zo is.
Het mooie hieraan is dat True ook als het getal 1 kan worden weergeven. Terwijl False gelijk is aan 0. Dit doe je met de functie Number.From
Hiermee wordt bepaald of er 1 of 2 uur bij de UTC-tijd opgeteld moet worden.
Stap 6
De laatste stap is de uiteindelijke correcte Nederlandse tijd berekenen.
Deze Query kunnen we vervolgens omzetten naar een tabel en het datatype goed zetten.
Compleet
De uiteindelijk query geeft je dan altijd de juiste Datumtijd waarde.
Code om te kopiëren:
let
// huidige datum tijd
UTC = DateTimeZone.RemoveZone(DateTimeZone.UtcNow()),
// Huidige datum
Datum = DateTime.Date(UTC),
// Huidige UTC tijd
Tijd = DateTime.Time(UTC),
// begin zomertijd dit jaar
Beginzomertijd =
Date.StartOfWeek(
#date ( Date.Year (UTC), 3, 31),
Day.Sunday),
// Einde zomertijd dit jaar
Eindzomertijd =
Date.StartOfWeek(
#date ( Date.Year (UTC), 10, 31),
Day.Sunday),
// Is de huidge datum de zomertijd?
IsZomertijd =
(Datum = Beginzomertijd and Tijd > #time(2,0,0))
or
(Datum > Beginzomertijd and Datum < Eindzomertijd )
or
(Datum = Eindzomertijd and Tijd < #time(3,0,0)),
// Wat is het verschil tussen UTC en NL tijd op huidige datum
Verschil = 1 + Number.From(IsZomertijd),
LaasteUpdate = UTC + #duration(0 ,Verschil, 0, 0),
Tabelvanwaarde = #table(1, {{LaasteUpdate}}),
Datumtype = Table.TransformColumnTypes(Tabelvanwaarde,{{"Column1", type datetime}}),
Resultaat = Table.RenameColumns(Datumtype,{{"Column1", "Laaste Refresh"}})
in
Resultaat