Power BI tip: De juiste “Last Refresh time” berekenen met Power Query

Onze Power BI trainingen worden vanaf nu gegeven in de Blended Learning lesvorm. Hierdoor kan je tot een jaar na de training Power BI vragen stellen aan je trainer. Deze week ontving Power BI trainer Jeroen een hele interessante vraag van een van de deelnemers aan de Power BI Advanced training die hij graag deelt.

De vraag: Waarom loopt mijn gepubliceerd Power BI rapport altijd 2 uur achter?

De deelnemer had op een rapport een query gemaakt die de datum en tijd van de laatste gegevensverversing toont. In de Power BI service kan een rapporteigenaar op meerdere manieren zien wanneer een dataset voor het laatste ververst is. Maar voor sommige rapportages kan het handig zijn om 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.

Onze Power BI specialist en trainer Jeroen zag direct wat het probleem en ging voor hem op zoek naar de beste oplossing.

 

Wat was het probleem?

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 wel de juiste verversingstijd:

Maar de waarde die uit de query komt en op het rapport getoond wordt komt hiermee 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.

Niet geïnteresseerd in de uitwerking? Scroll dan naar beneden. Daar vind je code van de oplossing die je direct kan kopiëren.

 

Mogelijke oplossingen voor dit probleem

Oplossing 1

Een eerste idee was dat je eenvoudigweg 2 uur optelt bij de tijd:

Dit werk wel op korte termijn. Maar heeft twee grote nadelen:

1. De tijd in Power BI Desktop klopt niet meer (die loopt nu 2 uur voor)
2. Deze oplossing houdt geen rekening met zomer- en wintertijd. De functie moet dus 2 x per jaar aangepast worden.

Oplossing 2 (de beste oplossing!)

Neem de UTC-tijd als basis. Omdat deze namelijk nooit verandert. En afhankelijk van de datum tel je daar 1 of 2 uur bij op. Dit wordt een complexere query van een aantal stappen en moet zelf worden geschreven in de M taal.

Volg onderstaand stappenplan voor deze oplossing

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 bepaalt 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.

Eindresultaat

De uiteindelijk query geeft je altijd de juiste Datumtijd waarde.

 

Kopieer de code gebruikt in het bovenstaande stappenplan:

Onderstaand vind je de code zoals uitgelegd bij oplossing 2. Deze kan je direct kopiëren en gebruiken. Succes!

---

let
UTC = DateTimeZone.RemoveZone(DateTimeZone.UtcNow()),

Datum = DateTime.Date(UTC),
Tijd = DateTime.Time(UTC),

Beginzomertijd =
Date.StartOfWeek(
#date( Date.Year (UTC), 3, 31),
Day.Sunday),

Eindzomertijd =
Date.StartOfWeek(
#date( Date.Year (UTC), 10, 31),
Day.Sunday),

IsZomertijd =

(Datum = Beginzomertijd and Tijd > #time(2,0,0))
or
(Datum > Beginzomertijd and Datum < Eindzomertijd)
or
(Datum = Eindzomertijd and Tijd < #time(3,0,0)),

Verschil = 1 + Number.From(IsZomertijd),

LaatsteUpdate = UTC + #duration(0,Verschil,0,0),

Tabel = #table(1, {{LaatsteUpdate}}),
Datatype = Table.TransformColumnTypes(Tabel,{{“Column1”, type datetime}}),
Resultaat = Table.RenameColumns(Datatype,{{“Column1”, “Laatste Update”}})

in
Resultaat

---

 

 

Volg ons YouTube kanaal voor meer handige Power BI tips en updates!