Datumstabelle mit Power Query erstellen



Wer regelmäßig mit Power Pivot und mehrdimensionalen Modellen arbeitet kennt die Situation:
Man benötigt eine lückenlose Datumstabelle für eine Datumsdimension, doch woher nehmen ?

Mit Power Query kann man diese sehr einfach erstellen.

Reiter Power Query -> aus anderen Datenquellen -> leere Abfrage erstellen

Im Abfrage Editor, Reiter Start (oder Ansicht), in "Erweiterter Editor" wechseln





Hier folgenden von Matt Allington entwickelten Quellcode einfügen:

let
Source = List.Dates,
InvokedSource = Source(#date(2010, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2010,1,1)), #duration(1, 0, 0, 0)),
TableFromList = Table.FromList(InvokedSource, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}),
InsertedCustom = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertedCustom1 = Table.AddColumn(InsertedCustom, "ShortYear", each Date.Year([Date])),
InsertedCustom2 = Table.AddColumn(InsertedCustom1, "MonthNum", each Date.Month([Date])),
InsertedCustom3 = Table.AddColumn(InsertedCustom2, "Day", each Date.Day([Date])),
InsertedCustom8 = Table.TransformColumnTypes(InsertedCustom3,{{"Date", type date}}),
InsertedCustom4 = Table.AddColumn(InsertedCustom8, "DayName", each Date.ToText([Date],"ddd")),
InsertedCustom5 = Table.AddColumn(InsertedCustom4, "MonthName", each Date.ToText([Date],"MMM")),
InsertedCustom6 = Table.AddColumn(InsertedCustom5, "QuarterNumber", each Date.QuarterOfYear([Date])),
ChangedType = Table.TransformColumnTypes(InsertedCustom6,{{"ShortYear", type text}}),
SplitColumnPos = Table.SplitColumn(ChangedType,"ShortYear",Splitter.SplitTextByRepeatedLengths(2),{"ShortYear.1", "ShortYear.2"}),
ChangedType1 = Table.TransformColumnTypes(SplitColumnPos,{{"ShortYear.1", type number}, {"ShortYear.2", type number}}),
RemovedColumns = Table.RemoveColumns(ChangedType1,{"ShortYear.1"}),
RenamedColumns1 = Table.RenameColumns(RemovedColumns,{{"ShortYear.2", "ShortYear"}}),
InsertedCustom7 = Table.AddColumn(RenamedColumns1, "Quarter", each "Q"&Number.ToText([QuarterNumber])&"/"&Number.ToText([ShortYear])),
RemovedColumns1 = Table.RemoveColumns(InsertedCustom7,{"ShortYear", "QuarterNumber"})
in
RemovedColumns1



Falls die Tabelle an einem anderen Startdatum als 01.01.2010 starten soll, einfach



Datumausdruck "#date(2010, 1, 1)"



ändern.




























































Kommentare











Kommentar veröffentlichen



















Beliebte Posts aus diesem Blog









Dropdown Liste mit Mehrfachauswahl




















Bild







Standardmäßig kann in einem Dropdownfeld nur ein Eintrag ausgewählt werden  (Daten -> Datentools -> Datenüberprüfung).   Ein dynamisches Dropdownfeld kann folgendermaßen realisiert werden           abhängige, dynamische Dropdownfelder können folgendermaßen in Excel  realisiert werden         weiterführende Informationen siehe hier    Um eine Mehrfachauswahl zu realisieren, kann der folgende VBA-Code im betreffenden Arbeitsblatt verwendet werden (rechte Maustaste auf Arbeitsblatt -> Code einfügen).    --- SCHNIPP --   Private Sub Worksheet_Change(ByVal Target As Range) '** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung) '** Einfügen im Code-Container des betreffenden Arbeitsblattes  '** Dimensionierung der Variablen Dim rngDV As Range Dim wert_old As String Dim wertnew As String  '** Errorhandling On Error GoTo Errorhandling  '** Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen If Not Application.Intersect(Target, Range("B4:B14&








Mehr anzeigen










Vergleich mit 2 Bedingungen, INDEX(), VERGLEICH()





















 Wenn man 2 Tabellen mit jeweils 2 gleichen Spalten hat (zB Nachnahme, Vorname) und eine 3te Spalte (zB Telefon) von einer Tabelle in eine andere übernehmen will, stellt die Funktion SVERWEIS() keinen adäquaten Lösungsansatz mehr dar.   Mit einer Kombination der Funktionen INDEX() und VERGLEICH() ist es dennoch möglich, diese Aufgabe mit Excel Formeln (Matrixformel) zu lösen.   =INDEX(Tabelle1!$A$2:$C$5;VERGLEICH(A2&B2;Tabelle1!$A$2:$A$5&Tabelle1!$B$2:$B$5;0);3)    wobei gilt:  Tabelle 1 Spalte A zB Nachname  Tabelle 1 Spalte B zB Vorname  Tabelle 1 Spalte C zB Telefon (Wert welcher in Tabelle 2 bei inhaltlicher Gleichheit Spalten A, B übernommen werden soll)   Formel in Tabelle 2 Spalte C aufbauen und mit STRG-SHIFT-RETURN (Matrixformel) abschließen  und runterkopieren.   Ein weiteres Beispiel zu Matrixformeln siehe folgenden Blog Beitrag seit Office 365 -> Microsoft 365 gibt es eine weitere, neue Alternative, Funktion XVERWEIS()  








Mehr anzeigen










nützliche Text Funktionen in Power Query




















Bild







  Im Folgenden werden ein paar in der Praxis nützliche Text Funktionen in Power Query aufgelistet.   Im Voraus soll darauf hingewiesen werden, dass es 2 wesentliche Unterschiede zwischen  Excel und Power Query Formeln / Funktionen gibt:   case sensitivity  Excel Formel unterscheiden nicht zwischen Groß- und Kleinschreibung, Power Query Formeln  indes schon.  Wenn eine Power Query Signatur Text.Range vorgibt, dann wird TEXT.RANGE oder text.range  nicht funktionieren (case sensitive).   Basis 1 versus Basis 0  Excel Formeln / Funktionen beziehen sich immer auf die Basis 1, d.h. man fängt mit 1 an zu zählen.  Auf der anderen Seite startet das Zählen in einer Power Query Funktion immer mit 0, nicht 1.   Vergleich Excel Text mit Power Query Funktionen      Text.Contains(Text,Suchstring)  gibt TRUE zurück, wenn <Suchstring> in <Text> beinhaltet ist,  andernfalls FALSE  z.B. Text.Contains("Power Query","Query")  Rückgabewert = TRUE   Text.Remove([Column],{"








Mehr anzeigen