Für zeitraumbezogene Datenanalysen mit Power Query in Excel und Power BI Desktop sind Datumsfelder von zentraler Bedeutung. Beim Import von Daten aus SQL- oder Access-Datenbanken können Datumsfelder in der Regel ohne Umwandlungen direkt übernommen werden. Falls Sie die Daten aus CSV- bzw. Textdateien oder von Internetseiten importieren, sollten Sie vor der Übernahme als Tabelle in Excel oder ins Datenmodell von Excel oder Power BI Desktop prüfen, ob die Datumswerte automatisch richtig erkannt werden. In diesem Tipp erfahren Sie, welche Datumsformate automatisch erkannt werden und bei welchen Formaten Sie selbst Umwandlungen vornehmen sollten.

Automatische Erkennung von Datumswerten

Beim Import von Text- bzw. CSV-Dateien werden folgenden Werte automatisch als Datumswerte erkannt, d.h. nach dem Import ist die jeweilige Spalte vom Datentyp Datum.

In der zu importierenden Textdatei sind beispielhaft die folgenden Daten enthalten:

Nachdem Sie in Power Query oder Power BI die Textdatei im Explorer ausgewählt haben, wird ein Assistent gestartet, der die Textdatei prüft und versucht, die Datentypen jeder Spalte zu erkennen. In dem folgenden Bild sehen Sie, dass in allen Spalten ein gültiges Datum erscheint:

Nach dem Klick auf die Schaltfläche Bearbeiten werden die Dateien in den Abfrageeditor geladen. Die Daten sehen folgendermaßen aus:

Im Spaltenkopf kennzeichnet das Kalendersymbol links dem der Spaltenbezeichnung den Datentyp Datum.

Zahlen- und Textwerte in Datumswerte umwandeln

In den folgenden Fällen erkennt der Importassistent von Power Query Datumswerte nicht richtig.

• 20170315
• 15032017
• 15.03.2017 (!)
• 150317
• 170315
• 03/15/2017
• 3’2017
• 3.2017

Der Ausdruck 15.03.2017 wird beim Import nicht automatisch als Datum erkannt. Die beiden Dezimalpunkte werden als Tausenderpunkte interpretiert und als solche beim Import entfernt. Das Ergebnis ist eine Ganzzahl mit dem Wert 15032017. Zur Illustration soll die folgende Textdatei mit Datumswerten übernommen werden:

Wenn Sie eine neue Abfrage starten und die Textdatei importieren, zeigt der Importassistent von Power Query bzw. Power BI Desktop folgende Werte an:

Im dem obigen Dialogbild können Sie bereits erkennen, dass in keiner Spalte gültige Datumswerte erkennt worden sind. Wenn Sie auf die Schaltfläche Bearbeiten klicken, werden die Daten in den Abfrageeditor geladen. Entweder wurde den Spalten der Datentyp Ganze Zahl oder Text zugewiesen.

Änderung des Datentyps

Einige Spalten wie Datum 1, Datum 3 und Datum 8 können durch Ändern des Datentyps in Datumsspalten umgewandelt werden. Um aus den Werten in diesen Spalten gültige Datumswerte zu erzeugen, können Sie folgendermaßen vorgehen:

1. Markieren Sie mit der linken Maustaste die Spalten Datum 1, Datum 3 und Datum 8, während Sie die <STRG>-Taste gedrückt halten.
2. Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Transformieren auf den Pfeil neben dem Datentyp Text. Das folgende Menü erscheint:

   

3. Klicken Sie auf den Menüpunkt Datum.
4. Alle Werte in den 3 Spalten werden fehlerfrei in Datumswerte umgewandelt. Alle Spalten erhalten den Datentyp Datum. Der Abfrageeditor sieht danach folgendermaßen aus:

   

Das beschriebene Vorgehen funktioniert bei den anderen 5 Spalten nicht. In den betroffenen Spalten würden Fehlerwerte bzw. falsch umgewandelte Werte erscheinen.

Obwohl die Werte in den Spalten Datum 2 und Datum 3 in dem Abfrageeditor identisch aussehen, werden nur die Werte in Spalte Datum 3 in korrekte Datumswerte umgewandelt. In der Textdatei selbst unterscheiden sich die Werte in den beiden Spalten.

Datentyp ändern mit Gebietsschema

Um die Werte in der Spalte Datum 6 in gültige Datumswerte umwandeln, müssen Sie das englische Datumsformat in ein deutsches Datumsformat umwandeln.

1. Markieren Sie dazu die Spalte Datum 6.
2. Rufen Sie mit der rechten Maustaste das Kontextmenü auf.
3. Klicken Sie auf den MenüpunktTyp ändern und dann auf den MenüpunktMit Gebietsschema. Das folgende Dialogbild erscheint:

   

4. Wähen Sie im Kombinationsfeld Datentyp den Eintrag Datum und im Kombinationsfeld Gebietsschema den Eintrag Englisch (USA). Klicken Sie abschließend auf die Schaltfläche OK. Alle Werte in der Spalte Datum 6 erhalten gültige Datumswerte.

Datentyp ändern durch vorheriges Ersetzen von Werten

Die Werte in Spalte Datum 7 mit dem Format Monat’Jahr (z.B. 3‘2017) können Sie mit zwei Umwandlungsschritten in Datumswerte konvertieren:

1. Markieren Sie die Spalte Datum 7.
2. Rufen Sie mit der rechten Maustaste das Kontextmenü auf.
3. Klicken Sie auf den Menüpunkt Werte ersetzen. Das folgende Dialogbild erscheint:

   

4. Geben Sie im Eingabefeld Zu suchender Wert das Hochkommazeichen ‘ und im Eingabefeld Ersetzen durch den Punkt . ein.
5. Klicken Sie auf OK.
6. Klicken Sie im Spaltenkopf von Spalte Datum 7 auf das Symbol mit den 3 Buchstaben ABC. Es erscheint das folgende Kontextmenü:

   

7. Klicken Sie auf den Menüpunkt Datum. Alle Werte in der Spalte Datum 7 werden jetzt als Datumswerte angezeigt.

Nach den bisherigen Umwandlungen sehen die Spalten im Abfrageeditor folgendermaßen aus:

Die Umwandlungen der noch fehlenden Spalten Datum 2, Datum 4 und Datum 5 von Zahlenfeldern in Datumsfelder sind etwas aufwändiger.

Datentyp ändern durch vorheriges Einfügen eines Präfixes

Die Datumswerte in Spalte Datum 5 haben das Format jjmmtt. Um diese Werte in ein Datumsformat zu bringen, gehen Sie bitte folgendermaßen vor:

1. Markieren Sie die Spalte Datum 5.
2. Klicken Sie im Menüband auf der Registerkarte Transformieren in der Gruppe Textspalte auf die Schaltfläche Formatieren.
3. Klicken Sie auf den Menüpunkt Präfix hinzufügen. Das folgende Dialogbild erscheint:

   

4. Geben Sie im Textfeld Wert die Zahl 20 ein, damit alle Angaben in der Spalte Datum 5 vierstellige Jahreszahlen aufwiesen.
5. Klicken Sie im Spaltenkopf von Spalte Datum 5 auf das Symbol mit den 3 Buchstaben ABC.
6. Klicken Sie auf den Menüpunkt Datum. Alle Werte in der Spalte Datum 5 werden jetzt als Datumswerte angezeigt.

   

Datentyp ändern durch vorheriges Einfügen einer benutzerdefinierten Spalte

Die Werte in den Spalten Datum 2 und Datum 4 sollen jeweils durch Hinzufügen einer benutzerdefinierten Spalte in Datumsangaben umgewandelt werden. Gehen Sie bitte folgendermaßen vor:

1. Markieren Sie die Spalten Datum 2 und Datum 4.
2. Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Transformieren auf den Pfeil neben dem Datentyp Ganze Zahl.
3. Klicken Sie auf den Menüpunkt Text, um die Werte in beiden Spalten vom Zahlen- ins Textformat zu konvertieren.
4. Klicken Sie im Menüband auf die Registerkarte Spalte hinzufügen in der Gruppe Allgemein auf die Schaltfläche Benutzerdefinierte Spalte. Das folgende Dialogbild erscheint:

   

5. Für die Umwandlung der Spalte Datum 2 geben Sie im Textfeld Neuer Spaltenname z. B. den Ausdruck Datum 2_Neu ein. Im Textfeld Benutzerdefinierte Spaltenformel
   geben Sie folgende Formel ein:

   =if Text.Length([Datum 2]) = 7 then Text.Start([Datum 2],1) & “.” & Text.Range([Datum 2],1,2) & “.” & Text.End([Datum 2],4)

   else

   Text.Start([Datum 2],2) & “.” & Text.Range([Datum 2],2,2) & “.” & Text.End([Datum 2],4)

   In der Formel wird geprüft, ob die Einträge in der Spalte 7 Zeichen enthalten. Falls dies der Fall ist, wird in der neuen Spalte ein Datumswert aus der einstelligen Tages-, der zweistelligen Monats- und der vierstelligen Jahresangabe erzeugt. Falls die Einträge 8 Zeichen umfassen, wird der Datumswert aus der zweistelligen Tages-, der zweistelligen Monats- und der vierstelligen Jahresangabe abgeleitet.
   Achten Sie bitte darauf, dass die Funktion Text.Range 0-basiert ist, d.h. wenn Sie aus einem Textausdruck in der Spalte Datum 2 ab der 2. Stelle 2 Zeichen herausziehen möchten, folgenden Ausdruck angeben:

   Text.Range([Datum2],1,2)

6. Klicken Sie auf die Schaltfläche OK. Die neue Spalte Datum 2_Neu wird rechts angefügt.
7. Für die Umwandlung der Spalte Datum 4 geben Sie im Textfeld Neuer Spaltenname z. B. den Ausdruck Datum 4_Neu ein. Im Textfeld Benutzerdefinierte Spaltenformel
   geben Sie analog folgende Formel ein:

   =if Text.Length([Datum 4]) = 5 then Text.Start([Datum 4],1) & “.” & Text.Range([Datum 4],1,2) & “.20” & Text.End([Datum 4],2)

   else

   Text.Start([Datum 4],2) & “.” & Text.Range([Datum 4],2,2) & “.20” & Text.End([Datum 4],2)

   Da in der Spalte Datum 4 nur zweistellige Jahreszahlen erscheinen, wird in der Formel zusätzlich noch die Zahl 20 eingefügt.

8. Klicken Sie auf die Schaltfläche OK. Die neue Spalte Datum 4_Neu wird rechts angefügt. Der Abfrageeditor weist danach folgende Spalten auf:

   

9. Markieren Sie die Spalten Datum 2_Neu und Datum 4_neu.
10. Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Transformieren auf den Pfeil neben dem Datentyp Text.
11. Klicken Sie auf den Menüpunkt Datum, um die Werte in beiden Spalten vom Text- ins Datumsformat zu konvertieren.
12. Löschen Sie anschließend die Spalten Datum 2 und Datum 4.
13. Benennen Sie die Spalte Datum 2_Neu in Datum 2 und die Spalte Datum 4_Neu in Datum 4 um.
14. Abschließend verschieben Sie die Spalten Datum 2 und Datum 4 an die passenden Positionen. Das Ergebnis aller durchgeführten Datumstransformationen sieht folgendermaßen aus: