DimDate mit PowerQuery erzeugen
Mit Power Query eine dynamische Datums-/Zeitdimension (DimDate) erzeugen
Auf einem meiner Streifzüge durchs Technet-Forum bin ich über eine Frage gestolpert, bei der eine Datums-/Zeitdimension benötigt wird, die 2-Minuten-Intervalle erzeugt. Da musste ich nun endlich mal ausprobieren, was ich schon lange vor mir her geschoben hatte: Eine dynamische Zeitdimension mit PowerQuery erzeugen, die vom Nutzer in Excel mit den Parametern gefüllt werden kann, ohne dass er den Abfrage-Entwurf ändern muss.
[wpfilebase tag=file id=25 tpl=filebrowser /]
Auf diesem Blatt trägt man die gewünschten Parameter ein:
und aktualisiert dann nur rechts die entsprechende Abfrage (ganz rechts: Dokument-Kreispfeil-Symbol).
In den Blättern „DimDate“ und „DimDateTime“ werden daraufhin die Werte entsprechend aktualisiert. Übrigens habe ich mich diesbezüglich hier inspirieren lassen. Interessant dabei ist das Verwenden der List.Dates-Funktion. Wenn man sie standardmäßig aufruft, erscheint folgender Dialog:
Aus diesen Parametern erstellt diese Funktion nun eine Tabelle mit einer Datumsspalte. Da wir dies aber dynamisch halten wollen und aus dem Excel-Blatt übergeben wollen, nutzen wir zusätzlich noch diese Technik. Hier werden die einzelnen Parameterwerte aus der Tabelle ausgelesen und der Power Query Abfrage übergeben.
So sieht der Code aus:
let
Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
StartDate= Parameter{0}[Value],
Duration= Parameter{6}[Value],
StepsDays= Parameter{2}[Value],
StepsHours= Parameter{3}[Value],
StepsMinutes= Parameter{4}[Value],
StepsSeconds= Parameter{5}[Value],
Source = List.DateTimes,
InvokedFunction = Source(StartDate, Duration, duration(StepsDays, StepsHours, StepsMinutes, StepsSeconds)),
TableFromList = Table.FromList(InvokedFunction, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddIndex = Table.AddIndexColumn(TableFromList, "Index", 1, 1),
RenameCols = Table.RenameColumns(AddIndex,{{"Column1", "DateTime"}}),
ChangeDatetime = Table.TransformColumnTypes(RenameCols,{{"DateTime", type datetime}}),
AddDate = Table.AddColumn(ChangeDatetime, "Date", each [DateTime]),
ChangeDate = Table.TransformColumnTypes(AddDate,{{"Date", type date}}),
AddYear = Table.AddColumn(ChangeDate, "Year", each Date.Year([Date])),
AddMonthNo = Table.AddColumn(AddYear, "Month Number", each Date.Month([Date])),
AddMonthName = Table.AddColumn(AddMonthNo, "Month Name", each Date.ToText([Date],"MMM")),
AddDayNo = Table.AddColumn(AddMonthName, "Day", each Date.Day([Date])),
AddDay = Table.AddColumn(AddDayNo, "Day Name", each Date.ToText([Date],"ddd")),
AddQuarter = Table.AddColumn(AddDay, "Quarter Number", each Date.QuarterOfYear([Date])),
AddShortYear = Table.AddColumn(AddQuarter, "ShortYear", each Text.End(Text.From([Year]),2)),
ChangeFormat = Table.TransformColumnTypes(AddShortYear,{{"ShortYear", Int64.Type}}),
AddHour = Table.AddColumn(ChangeFormat, "Hour", each Time.Hour([DateTime])),
AddMinute = Table.AddColumn(AddHour, "Minute", each Time.Minute([DateTime])),
AddSecond = Table.AddColumn(AddMinute, "Second", each Time.Second([DateTime])),
AddTime = Table.AddColumn(AddSecond, "Time", each Time.From([DateTime])),
ChangeTimeFormat = Table.TransformColumnTypes(AddTime,{{"Time", type time}})
in
ChangeTimeFormat
Außerdem gibt es dort noch eine einfache Variante, die fest auf 1-Tagesintervalle eingestellt ist.
Diese sollte man verwenden, wenn man mit Zeitintelligenzfunktionen arbeiten möchte:
let
Parameter = Excel.CurrentWorkbook(){[Name="ParameterDate"]}[Content],
StartDate= Parameter{0}[Value],
Duration= Parameter{2}[Value],
Source = List.Dates,
InvokedFunction = Source(DateTime.Date(StartDate), Duration, duration(1,0,0,0)),
TableFromList = Table.FromList(InvokedFunction, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddIndex = Table.AddIndexColumn(TableFromList, "Index", 1, 1),
RenameCols = Table.RenameColumns(AddIndex,{{"Column1", "Date"}}),
ChangeDate = Table.TransformColumnTypes(RenameCols,{{"Date", type date}}),
AddYear = Table.AddColumn(ChangeDate, "Year", each Date.Year([Date])),
AddMonthNo = Table.AddColumn(AddYear, "Month Number", each Date.Month([Date])),
AddMonthName = Table.AddColumn(AddMonthNo, "Month Name", each Date.ToText([Date],"MMM")),
AddDayNo = Table.AddColumn(AddMonthName, "Day", each Date.Day([Date])),
AddDay = Table.AddColumn(AddDayNo, "Day Name", each Date.ToText([Date],"ddd")),
AddQuarter = Table.AddColumn(AddDay, "Quarter Number", each Date.QuarterOfYear([Date])),
AddShortYear = Table.AddColumn(AddQuarter, "ShortYear", each Text.End(Text.From([Year]),2)),
ChangeFormat = Table.TransformColumnTypes(AddShortYear,{{"ShortYear", Int64.Type}})
in
ChangeFormat
Viel Spaß damit und stay querious! 🙂
Weitere Tutorials?
Natürlich haben wir noch mehr wertvolle Tipps rund um Microsoft BI für Sie!
