String oder Number mit DAX in ein Datum konvertieren

Das Datum ist unbestreitbar eine der wichtigsten Spalten in jeder Power BI – Datentabelle.

Leider liefern viele Quell-Systeme das Datum nicht in der Form an, die wir ohne Weiteres weiterverarbeiten können. Sei es ein antiquarisches ERP-System oder die Zeiterfassung, die das Datum oft in einer CSV-Datei als einfachen Text liefert.

Weiterlesen: String oder Number mit DAX in ein Datum konvertieren

Genau so erging es mir kürzlich. Das ERP-System lieferte in der CSV-Datei lediglich die Zeichenkette 110520 für den 11.05.2020 und sogar nur 80520 für den 8. Mai 2020. Hier hatte sich das System sogar die führende Null gespart.

Mein erster naheliegender Impuls: im Bereich „Daten“ von Power BI Desktop den Datentyp von „Ganze Zahl“ in „Datum“ umwandeln. Power BI nimmt hier auch eine Umwandlung vor. Allerdings wurde auf diese Weise der String 110520 in den 04.08.2202 umgewandelt und die Zeichenkette 80520 in den 14.06.2120. Wir sind ja nicht bei Raumschiff Enterprise!
Offenbar wird hier intern irgendeine Berechnung vorgenommen, die zu einem falschen Ergebnis führt (möglicherweise irgendetwas in der Art „addiere soundsoviele Tage zum 01.01.1900 hinzu“ oder etwas ähnliches. Ich habe es nicht näher untersucht).

Konvertierung mit DAX

Die für mich am nächsten liegende Lösung ist, die Zeichenkette (bzw. die Ganze Zahl) mittels DAX in ein Datum zu konvertieren.

Hier möchte ich Euch meine Lösung vorstellen. Ich stelle zunächst die einzelnen Schritte vor und packe am Schluss alles in einen einzigen DAX-Ausdruck.

Als erstes habe ich die Spalte mit den ursprünglichen Datumswerten in „DateOrig“ umbenannt.

• Das Jahr extrahieren:
  In meinen beiden Beispielen 80520 und 110520 steht das Jahr (2020) ganz rechts im Ausdruck. Das ERP-System hat sich auf zwei Stellen für das Jahr beschränkt, wir müssen es also noch auf 4 Stellen auffüllen.
  Ich habe folgenden Ausdruck verwendet:

Jahr = "20" & RIGHT ([DateOrig], 2)

Erläuterung: nehme die Zahl 20 und hänge dann die 2 Stellen von rechts an.
Diese Formel funktioniert ab dem 01.01.2100 so nicht mehr. Aber meine Power BI-Lösung wird voraussichtlich nicht so lange leben…

• Den Monat extrahieren:
  Der Monat ist in jedem Fall immer die die 3. und 4. Stelle von rechts gelesen (von links ist es uneinheitlich, da die Zeichenkette mal 5 und mal 6 Stellen hat).
  Ich habe folgende Lösung gefunden:

Monat = LEFT ( RIGHT ([DateOrig], 4), 2)

Liest sich erst einmal kompliziert, ist aber relativ einfach:
Man muss die Klammern von innen nach außen lesen. Dann bedeutet es: Nimm die 4 Stellen von rechts und nehme dann im zweiten Schritt davon die 2 ersten Stellen von links.

• Den Tag extrahieren:
  Hier muss man erneut tricksen, den die Zeichenkette hat ja mal 5 und mal 6 Zeichen.
  Letztendlich habe ich es so gelöst:

Tag = LEFT ([DateOrig], LEN ( [DateOrig] ) - 4)

Hier muss man ja von links ausgehend mal eine Stelle, ein anderes Mal zwei Stellen nehmen.
LEN liefert hier die Länge des gesamten Ausdrucks – also mal 5 und mal 6. Substrahiert man vom Ergebnis von LEN dann 4, erhält man entweder 1 oder 2. Dann lässt man mit dem Ausdruck LEFT entweder eine oder zwei Stellen auslesen und erhält so den Tag.

In ein Datum wandeln

Wir haben nun also das Jahr, den Monat und den Tag. Nun müssen wir diese drei Angaben in ein Datum wandeln. Ich mache das mit der Funktion Date:

DATE ( Jahr, Monat, Tag )

Ich übergebe der Funktion Date ein Jahr, einen Monat und einen Tag und die Funktion macht ein Datum daraus.

Alles in einem einzigen DAX-Ausdruck

Wir haben nun 4 einzelne DAX-Ausdrücke. Wir könnten nun für das Jahr, den Monat, den Tag und das Datum 4 neue Spalten anlegen. Das ist aber nicht effektiv. Wir legen daher nur eine neue Spalte an und packen alle vier Ausrücke in einen einzigen DAX-Ausdruck:

Datum = 
VAR Jahr =
    "20" & RIGHT ([DateOrig], 2 )
VAR Monat =
    LEFT ( RIGHT ([DateOrig], 4 ), 2 )
VAR Tag =
    LEFT ([DateOrig], LEN ( [DateOrig] ) - 4 )
RETURN
    DATE ( Jahr, Monat, Tag )

Wir legen also für Jahr, Monat und Tag jeweils eine Variable an (Ausdruck VAR) und „returnen“ am Schluss das Ergebnis der Funktion DATE.

Die neue Spalte „Datum“ muss ich nun nur noch über die Spaltentools über „Format“ in das richtge Format bringen und habe nun ein Datum, mit dem ich weiterarbeiten kann.

In meinem Power BI – Projekt brauche ich das nun sehr oft. Allerdings habe ich mir nur ein mal die Gedanken dazu gemacht und kann den resultierenden DAX-Ausdruck (vielleicht mit leichten Modifikationen) immer wieder verwenden (copy und paste 😉 ).

Habt Ihr Ideen, Fragen oder Anregungen? Oder gar eine Lösung in M?
Schreibt es gerne in die Kommentare!

[Update 22.09.2021]

Was passiert, wenn DateOrig leer (null) ist? In diesem Fall funktioniert der DAX-Ausdruck nicht.
Wenn ich nun die leeren Einträge trotzdem behalten will, hilft diese Ergänzung:

Datum = if(NOT(ISBLANK([DateOrig])),
VAR Jahr =
    "20" & RIGHT ([DateOrig], 2 )
VAR Monat =
    LEFT ( RIGHT ([DateOrig], 4 ), 2 )
VAR Tag =
    LEFT ([DateOrig], LEN ( [DateOrig] ) - 4 )
RETURN
    DATE ( Jahr, Monat, Tag )

Erläuterung:

Der DAX-Ausdruck wird mit dieser Änderung nur ausgeführt, wenn DateOrig nicht „null“, also nicht leer ist. Wenn DateOrig null ist, ist auch die neue Spalte Datum an dieser Stelle leer.