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.

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!