Datumsfelder in Power Query und Power BI Desktop

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.

Inhalt Textdatei Datum nach Umwandlung
Januar 2017 01.01.2017
12 Januar 12.01.2017
Jan 2017 01.01.2017
Jan 17 01.01.2017
12. Januar 2017 12.01.2017
2017-01-12 12.01.2017
01/2017 01.01.2017
2017-01 01.01.2017

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

Textdatei mit Datumswerten

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:

Importassistent Power Query

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

Abfrageeditor mit Datumsfelder

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:

Textdatei mit Datumswerten

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

Importassistent Power Query

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.

Abfrageeditor mit nicht konvertierten Datumsfeldern

Ä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:

    Datentypen in Power Query

  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:

    Erfolgreiche Datumsumwandlung in Power Query

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

Fehlerhafte Datumsumwandlung in Power Query

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:

    Datentyp mit Gebietsschema in Power Query ändern

  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:

    Werte ersetzen in Power Query

  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ü:

    Datentyp ändern in Power Query

  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:

Datumsumwandlung in Power Query Teil 1

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:

    Präfix hinzufügen in Power Query

  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.

    Datenumwandlung mit Präfix in Power Query

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:

    Benutzerdefinierte Spalte in Power Query

  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:

    Benutzerdefinierte Spalte in Power Query

  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:

    Datumsumwandlung in Power Query Teil 2

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

5 Kommentare

  1. Sehr geehrter Hr Richter,
    vielen Dank für diesen sehr informative Erläuterung.

    In meiner Grunddatenmenge treffe ich auf den Datumstyp “Datum 2”. Um diesen in ein von Excel erkannten Datumstyp umzuwandeln, folge ich Ihrer obigen Anleitung.

    Bei der Eingabe der längeren Formel lt Schritt 5 (beginnend mit “=if Text.Length([Datum 2]) = 7 then Text.Start([Datum 2],1) & „.“ & Text.Range([Datum 2],1,2)…..” erhalte ich indes für den letzten Teil hiervon (& Text.Range([Datum 2],1,2)) folgende Fehlermeldung: “ungültiger Bezeichner”, und darüber steht noch “Text.Range(text as nullable text, offset as number, count as nullable number) => …”’ ” sowie “Gibt die am Offset gefundene Teilzeichenfolge zurück”.

    Meine Frage: Können Sie diese Fehlermeldung nachvollziehen, und einen Lösungsvorschlag geben?

    Vielen Dank im Voraus und Viele Grüße,

    Antworten
    • Sehr geehrter Herr Elfert,

      es könnte mit der Darstellung der Anführungszeichen auf der Webseite zusammenhängen. Das vordere und hintere Anführungszeichen müssen oben stehen.

      Antworten
      • Sehr geehrter Herr Richter,

        tatsächlich hatte ich das gleiche Problem. Nachdem ich die Anführungszeichen neu eingeben habe, hat die Formel wunderbar funktioniert. Vielen Dank für die ausführliche Hilfestellung.

        Viele Grüße

        Antworten
  2. Sehr geehrter Herr Richter,
    vielen Dank für Ihre ausführlichen Erläuterungen zu den nicht ganz alltäglichen Aufgabenstellungen.
    Beim Arbeiten mit dem Datum in Power Query beschäftigt uns die Fragen, ob man der Abfrage eine Parameter (wie z.B. Startdatum und/ oder Enddatum) mitgeben kann.
    Vielleicht haben Sie an anderer Stelle derartiges bereits erläutert?

    Antworten
    • Sehr geehrte Frau Podszuck,

      Sie können der Abfrage Parameter mitgeben. In Power Query können Sie neue Parameter mit Name, Typ und vorgeschlagenen Werten anlegen. Sie finden Sie Schaltfläche auf der Registerkarte Home in der Gruppe Parameter.

      Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Pin It on Pinterest

Share This