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!