Op 29 juni vond er een workshop met de mooie titel "Excel Summer Boost". Eén van de workshops werd gegeven door Henk Vlootman, één van de MVP's die Nederland rijk is.
Hij gaf een basiscursus PowerQuery en vertelde tussen neus en lippen door dat je een datumtabel kon maken die bij elke verversing werd aangevuld tot de dag van vandaag. Hoe dat precies ging heb ik helaas niet onthouden, maar gelukkig is er veel informatie op internet te vinden en ik vond een prachtige uitleg over hoe je dit kunt maken in PowerBI en PowerQuery op exceleratorbi. Op deze pagina wordt duidelijk uitgelegd door Matt Allington hoe je zo'n tabel kunt bouwen. Dus alle kudoos/credits/lof naar Matt.
Wat mij opviel (of wat ik nog niet wist) was dat de volgorde in de taal M niet van belang is. Alleen je moet aan het eind van je code laten zien wat je wilt hebben. En dat je zonder ook maar enige informatie een tabel kunt opbouwen.
Onderaan dit bericht staat de code die je in een lege query kunt plaatsen waarmee je direct de datumtabel gereed is. Maar om te begrijpen wat er allemaal gebeurd staat hieronder puntsgewijs vermeld wat er ingevoerd moet worden.
- Open een lege query (Gegevens > Gegevens ophalen > Van andere bronnen > lege query)
- Geef rechts de nieuwe query een naam b.v. "DatumTabel"
- Vul op de formulebalk de functie "= List.Dates" in en klik op Enter. Er verschijnt een informatiescherm met hoe je deze functie kunt vullen.
- Vul bij "start" een datum in (wordt later aangepast), bij "count" het aantal dagen in (wordt later ook aangepast) en bij "step" het getal 1 in om een tabel te maken voor alle dagen.
- Als er op de button "Aanroepen" wordt geklikt, dan wordt er een nieuwe query aangemaakt met de naam "Functie aangeroepen" en een "Lijst" beginnend met de startdatum en daaronder het aantal dagen dat is opgegeven.
- In plaats van de stappen 3 t/m 5 in te voeren kan ook direct boven in de formulebalk de functie = List.Dates(#date(2018, 8, 1), 10, #duration(1, 0, 0, 0)) worden ingevuld waarbij de datum 1-8-2018 in de "jjjj, m, d"-notatie ingevuld moet worden. Op deze manier wordt er geen extra query aangemaakt.
- Maak van de lijst een tabel via de rechtermuisknop-optie "Naar Tabel" die ook te vinden is op het Transformeren-lint.
- Geef de kolom een nieuwe naam en zet de opmaak op Datum. Let op! Er staat in in het stappenplan de tekst "Type gewijzigd".
- Als nu op de fx-knop voor de functiebalk wordt geklikt, wordt er een extra stap aangemaakt met de naam "Aangepast1" en in de functiebalk komt = #"Type gewijzigd" te staan.
Vervang deze tekst met een datum b.v. 15/5/2018 en wijzig de naam van de stap "Aangepast1" in "Startdatum" In het scherm is nu alleen de gekozen datum zichtbaar. - Ga nu terug naar stap "Bron" en vervang de #Date()-functie door Startdatum. Er staat nu in de functiebalk: = List.Dates(Startdatum, 10, #duration(1, 0, 0, 0)) en na een klik op Enter is opnieuw de tabel met datums te zien vanaf de nieuwe startdatum. (Hier is dus zichtbaar dat de volgorde niet van belang is in de stappen)
- Er kan geen einddatum ingevoerd worden omdat deze steeds wijzigt en de functie accepteert in de stap "count" geen datum-functie. Dus moet er een berekening gemaakt worden die het aantal dagen van startdatum tot nu toe berekend.
- Ga naar het eind van je stappen en klik opnieuw op de fx-knop en vul de functie =DateTime.LocalNow() in en geef Enter. De huidige datum en tijd wordt weergegeven. Omdat alleen de datum nodig is, moet hieromheen de functie DateTime.Date() worden gezet. De totale functie wordt dus: = DateTime.Date(DateTime.LocalNow())
- De datum van vandaag is zichtbaar. De stap "Aangepast1" kan aangepast worden naar "Vandaag".
- Klik opnieuw op de fx-knop en maak de volgende functie: = Vandaag - Startdatum. Er verschijnt een aantal met daarachter een tijd. Omdat alleen het aantal gewenst is, moet om deze formule de functie Duration.Days worden gezet. De totale functie wordt dus:
= Duration.Days(Vandaag - Startdatum) - Wijzig de naam van de stap in "AantalDagen"
- Nu kan de functie in de allereerste stap worden aangepast naar:
= List.Dates(Startdatum, AantalDagen, #duration(1, 0, 0, 0)) - Dit geeft een lijst vanaf de startdatum tot en met de datum van gisteren. Als er een tabel gemaakt moet worden tot en met vandaag dan moet "AantalDagen" vervangen worden door "AantalDagen+1".
- Bij stap "Type gewijzigd" (zie punt 8) wordt de juiste tabel getoond, maar de laatste stap toont het aantal dagen. Er kan nu een laatste stap toegevoegd worden die verwijst naar de stap "Type gewijzigd". Selecteer de onderste stap. Klik op de fx-knop en vul in de formulebalk het volgende in: = #"Type gewijzigd". Hiermee worden de datums weer zichtbaar en wordt stap "Aangepast1" toegevoegd.
Code voor Geavanceerde Editor:
let
Bron = List.Dates(Startdatum, AantalDagen, #duration(1, 0, 0, 0)),
#"Geconverteerd naar tabel" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Geconverteerd naar tabel",{{"Column1", "Datum"}}),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Namen van kolommen gewijzigd",{{"Datum", type date}}),
Startdatum = #date(2017, 1, 1),
Vandaag = DateTime.Date(DateTime.LocalNow()),
AantalDagen = Duration.Days(Vandaag - Startdatum),
#"Tabel tonen" = #"Type gewijzigd",
#"Jaar ingevoegd" = Table.AddColumn(#"Tabel tonen", "Year", each Date.Year([Datum]), type number),
#"Maand ingevoegd" = Table.AddColumn(#"Jaar ingevoegd", "Month", each Date.Month([Datum]), type number),
#"Dag ingevoegd" = Table.AddColumn(#"Maand ingevoegd", "Day", each Date.Day([Datum]), type number),
#"Kwartaal ingevoegd" = Table.AddColumn(#"Dag ingevoegd", "Quarter", each Date.QuarterOfYear([Datum]), type number),
#"Week van jaar ingevoegd" = Table.AddColumn(#"Kwartaal ingevoegd", "WeekOfYear", each Date.WeekOfYear([Datum]), type number),
#"Ingevoegde naam van de maand" = Table.AddColumn(#"Week van jaar ingevoegd", "Naam van de maand", each Date.MonthName([Datum]), type text),
#"Dag van week ingevoegd" = Table.AddColumn(#"Ingevoegde naam van de maand", "DayOfWeek", each Date.DayOfWeek([Datum]), type number),
#"Ingevoegde naam van de dag" = Table.AddColumn(#"Dag van week ingevoegd", "Naam van de dag", each Date.DayOfWeekName([Datum]), type text)
in
#"Ingevoegde naam van de dag"
Ik raad iedereen aan om de volledige uitleg te lezen op exceleratorbi waarbij ook veel afbeeldingen uit PowerBi worden weergegeven (en hopelijk wordt ik niet aangeklaagd voor plagiaat).
En is iets niet duidelijk, stuur dan een berichtje via het contactformulier van mijn website.
