Power Query, Datums-Funktionalität hinzufügen und nutzen
Hinweis:
Die Arbeitsschritte dieses Beitrages sind in einem kleinen Video dokumentiert. Das unterstützende Begleit-Video finden Sie auf YouTube an dieser Stelle.
In diesem Beitrag ist mit dem Begriff „Datum” bzw. „Daten” ausschließlich das kalendarische Datum und nicht die Singular-Form von dem allgemein gültigen Begriff „Daten” gemeint. Im Ausnahmefall wird gesondert darauf hingewiesen, dass nicht das kalendarischen Datum gemeint ist.
Datum ist Datum ist Datum
Der 4. Januar ist in der gesamten Welt (sofern die übliche Zeitrechnung verwendet wird) der 4. Januar. Was immer sehr unterschiedlich sein kann und auch normalerweise der Fall ist: Die Schreibweise des Datums. Aber nicht nur das, je nach Datenquelle kann der Datentyp auch Zahl oder Text sein. Wenn das gegebene Datum eine (serielle) Zahl ist, stellt das für Excel als auch für Power Query absolut kein Problem dar. Egal auf welchem Kontinent oder in welchem Staat die (kalendarischen) Daten erfasst worden sind, der 4. Januar 2017 wird durch die Zahl 42739 repräsentiert. (Gilt natürlich nur für die Windows-Version, aber in der Mac-Version gibt es (noch) kein Power Query). Die Darstellung in Excel selbst ist je nach Landeseinstellung verschieden.
Interessant wird es aber, wenn beispielsweise eine Datenbank oder das Internet das Datum als Text liefert, die Landeseinstellungen aber grundsätzlich andere sind als bei uns hier in Deutschland. Beispiel USA: Da ist der 4. Januar dann im besten Fall January 4. 2017, im Normalfall aber 1/4/2017 oder 1–4‑2017, jedoch nie 1.4.2017; alle Daten gegebenenfalls auch mit 2‑stelliger Jahreszahl. – Laden Sie einmal diese Mappe und öffnen das erste Blatt US-Datum. Die Spalte A ist bewusst als Text formatiert, damit Excel die eingegebenen Werte nicht automatisch in ein deutsches Datum ‑welches dann auch noch falsch umgerechnet wird- wandelt.
Zugegeben, im Normalfall wird solch ein „Kuddelmuddel” nicht in einer abgeschlossenen Tabelle stehen, es werden gewiss immer die gleichen Schreibweisen verwendet. Und in einem solchen Normal-Fall ist es auch durchaus mit relativ wenigen Aufwand möglich, diese Daten über beispielsweise Text in Spalten in das gewünschte Format um zu wandeln. Hier geht es aber darum, mit Power Query auch solch einen Mischmasch in das deutsche bzw. europäische Format umzuwandeln. Dazu öffnen Sie die Daten-Tabelle in Power Query und erkunden erst einmal, was davon zu gebrauchen ist.
Die erste Feststellung: PQ hat jede der 3 Zeilen in ein Datum umgewandelt und automatisch die deutsche Schreibweise verwendet. Der Datentyp ist auch Datum. Aber ausschließlich die 1. Zeile enthält das korrekte Datum, die beiden Folgezeilen sind offensichtlich ein Aprilscherz. 🙁 – Dieses Beispiel zeigt Ihnen auf, dass sie bei derartigen Importen immer stichprobenartig kontrollieren müssen, ob sich da nicht Fehler dieser Art eingeschlichen haben.
Bevor Sie sich aber eine Korrektur vornehmen, schließen Sie diese Abfrage ohne sie zu speichern; Sie nutzen also beispielsweise im Menü Datei den Punkt Verwerfen und schließen oder Klicken auf das Schließen-Symbol des Fensters und entscheiden dann nach Sachlage. Wieder in der Tabelle schreiben Sie nun in Zeile 5 dieses US Datum: 1/18/2017. Also den 18. Januar 2017 in US-Schreibweise. Und jetzt erstellen Sie daraus wiederum eine Abfrage. Obwohl die ersten 3 Zeilen ja gleich geblieben sind, stellt sie das ganze doch komplett anders dar:
Daten komplett als Text
Da Power Query wegen der neue hinzufügen Zeile 4 keinen durchgängigen Datentyp erkennen konnte, weil die neu hinzugefügte Zeile nicht einfach so in ein Datum umgewandelt werden kann, ist die komplette Spalte erst einmal auch als Datentyp: Text formatiert worden. – Die Vorgehensweise zur Änderung in ein korrektes deutsches Datum ist typisch für einen solchen Fall. Gehen Sie dazu so vor:
- Rechtsklick in die Überschrift Datum
- Im Kontextmenü wählen Sie Typ ändern | Mit Gebietsschema…
- Im erscheinenden Dialogfenster wählen Sie bei Datentyp: natürlich das Datum, bei Gebietsschema: Englisch (USA)
- Bestätigen Sie mit OK und sie erkennen, dass alles seine Richtigkeit hat.
Wenn Sie diese Abfrage nun schließen und sich das Ergebnis in der Tabelle ansehen, bleibt es (natürlich) beim korrekten deutschen Datum, wie schon in der Abfrage dargestellt. – Problem gelöst. 💡
ISO 8601 (ANSI)
Manche ERP-Systeme, Datenbanken, Maschinen oder auch Scanner geben ein Datum in der Form JJJJMMTT oder JJJJ-MM-TT aus (Jahr teilweise auch 2‑stellig). Beides entspricht der ANSI-Norm. Mit Sicherheit wird kein / oder . als trennendes Element verwendet. Im zweiten Arbeitsblatt der Muster-Mappe (ISO 8601) erkennen Sie in einer Liste vier typische Werte. Wenn Sie diese Tabelle nun mit Power Query öffnen, stellt sie das Ganze so dar:
Eine andere Daten-Anordnung
Wenn Sie nun versuchen, den gleichen Weg wie zuvor zu gehen, werden sie enttäuscht: Sie haben nicht die Möglichkeit, ein Gebietsschema zu wählen. Also muss ein anderer Weg beschritten werden. Zu Beginn ist es erst einmal wichtig, dass der Typ der einzelnen Zeilen identisch ist. Denn wenn sie genau hinsehen werden Sie erkennen, dass bei Datentyp: der Eintrag Beliebig steht. Das zeigt sich auch daran, dass der Wert in Zeile 1 rechtsbündig dargestellt wird, also offensichtlich eine Zahl ist. Dir restlichen Zeilen sind linksbündig, dem Anschein nach Text.
Die Zeile 2 könnte der „Ausrutscher” sein, denn dort sind ja im Gegensatz zu den anderen Daten Jahr, Monat und Tag mit einem Bindestrich getrennt. Um diesen zu entfernen, markieren Sie die Spalte und in der Gruppe Transformieren gibt es den Punkt Werte ersetzen. Im Dialogfenster dann das Zeichen - als zu suchenden Wert eingeben und bei Ersetzen durch lassen Sie das Feld einfach leer. Nach einem Klick auf OK werden sie sehen, dass sie nichts sehen. 😉 Es hat sich offensichtlich nichts geändert. Um den Ablauf bei Angewendete Schritte „sauber” zu halten, löschen Sie dort im rechten Seitenfenster den letzten Eintrag (Ersetzter Wert).
Zugegeben, das ist etwas irritierend. Meine Sichtweise: Da PQ wohl „merkt”, dass die 2. Zeile ein Datum sein könnte, wird am - wegen des Datentyps Beliebig nicht gerüttelt. Der Ausweg ist entsprechend logisch: Wechseln Sie den Datentyp dieser Spalte zu Text und führen Sie den Ersetzungsvorgang wie gerade beschrieben noch einmal durch. Und dann klappt es auch wie gewünscht. Sie haben nun einige Möglichkeiten, dieses ANSI-Datum in ein deutsches Datum umzuwandeln. Zwei davon werde ich Ihnen vorstellen.
Umwandlung (1)
Die in den meisten Fällen einfachste Möglichkeit: Ähnlich wie gehabt werden sie den Datentyp verändern und dabei die Möglichkeit nutzen, das Gebietsschema als Werkzeug einzusetzen. Sie werden wiederum den Datentyp: Datum auswählen und bei Gebietsschema bietet sich Englisch (Welt) an, da dieses am universellen ist. Und das klappt auch sehr gut. (Englisch USA klappt meistens auch.) Dass in Zeile 4 eine Fehlermeldung erscheint, hat durchaus seine Berechtigung, denn es gibt zwar im Jahr 2016 aber nicht anno 2017 den 29. Februar. Power Query ist da erheblich konsequenter (und sensibler) als (mitunter) Excel und zählt nicht einfach einen Tag dazu. Bei solchen Fehlern bleibt ihnen eigentlich nur die Möglichkeit, die Quelldaten entsprechend anzupassen, es bei der Fehlermeldung in der Zelle zu belassen oder aber über das Symbol Zeilen verringern | Zeilen entfernen | Fehler entfernen diese Zeile zu löschen. Schließen & laden, um die gesamten Daten in ein neues Arbeitsblatt und dort wiederum in eine neue Tabelle zu schreiben.
Umwandlung (2)
Diese zweite Möglichkeit ist etwas aufwendiger, sie soll ihn aber als beispielhafte Methode für andere Gelegenheiten zeigen, wie sie zu einem gewünschten Ziel gelangen. Erst einmal werden Sie wiederum von der ANSI-Tabelle eine neue Abfrage erstellen. Auch hier werden sie auf die gleiche Weise wie eben die Bindestriche entfernen, damit ein einheitliches Datenformat vorliegt. Hier erst einmal STOP! Nun geht es nämlich darum, die drei Elemente des Textes (Datums) in jeweils eine einzelne Spalte aufzuteilen.
Sie markieren dazu erforderlichenfalls Spalte Datum, dann Gruppe Transformieren | Spalte teilen | Nach Anzahl von Zeichen. Zuerst soll er das 4‑stellige Jahr extrahiert werden, darum geben Sie im Dialogfenster die 4 ein und bei Teilen Klicken Sie auf Einmal, so weit links wie möglich. OK und das Jahr steht in einer eigenen Spalte. Die 2. Spalte jedoch ist leicht irritierend:
Das Jahr ist nun abgeteilt, aber …
Power Query hat selbstständig die 4 Ziffern (Text) in eine Zahl umgewandelt und dabei bleiben wegen der führenden Null nur 3 Ziffern übrig, was ja nicht unbedingt dem Sinn der Sache entspricht. Markieren Sie darum die Spalte Datum.2 und konvertieren Sie zum Datentyp: Text. Jetzt sind es wieder 4 Ziffern mit führender Null vor dem Monat. Diese Spalte teilen sie nun nach der 2. Stelle, sie können es hier bei Wiederholt belassen.
Auch wenn es ab diesem Punkt wiederum mehrere Möglichkeiten gibt, zeige ich Ihnen die aus meiner Sicht pragmatischste auf: Menü Spalte hinzufügen, markieren Sie nun alle 3 Spalten und wählen Sie in der Gruppe Aus Text den Punkt Spalten zusammenführen. Im Dialogfenster wählen Sie im Dropdown beim Trennzeichen den Punkt –Benutzerdefiniert–und geben in die Zeile darunter beispielsweise den Schrägstrich / oder ein - ein. Im Textfeld Neuer Spaltenname sollten Sie das Wort Zusammengeführt durch beispielsweise Datum ersetzen. Die ersten 3 Spalten sind immer noch markiert; Rechtsklick in eine der drei Überschriften und Spalten entfernen. Es bleibt die Spalte Datum über. Jetzt wiederum den Typ ändern, es reicht aber auch in diesem Fall aus, gleich den Typ Datum zu verwenden. Der Error in der Zeile 4 ist natürlich nach wie vor vorhanden und kann gelöscht werden. Ein Klick auf Schließen & laden, und auch diese Abfrage wird in einem neuen Tabellenblatt in eine Liste geschrieben.
Deutsches Datum, verschiedene Separierungen
Navigieren Sie in der Mappe zum Blatt DeutschDat, wo 100 unterschiedliche Daten zwischen dem 1. Januar 2000 und dem 31. Januar 2019 erfasst sind. Die Aufgabe ist nun: Mit Power Query (also nicht mit Excel) sollen unterschiedliche Werte aus dem jeweiligen Datum in einer getrennten Spalte erfasst werden. Dieses sind:
- Das Jahr
- Der Monat (numerisch und als Text)
- Der Tag
- Das Quartal des Jahres
- Der Wochentag (numerisch ‑EU-Norm- und als Text)
In Excel ist erfahrenen Nutzern das Erreichen dieses Ziels problemlos möglich, wer sich ein wenig in VBA auskennt, wird auch hier wenige Schwierigkeiten haben, das Wunschergebnis zu verwirklichen. In Power Query ist da schon ein wenig mehr Aufwand erforderlich. Sie müssen in die Sprache „M” einsteigen, um das Ganze zu verwirklichen. Wenn Sie also gar keine Affinität zu Programmiersprachen haben, sollten Sie sich vielleicht entscheiden, solche Aufgaben entweder in „Plain Excel” zu lösen oder das Ganze einem (externen) Fachmann zu überlassen. Aber bedenken Sie: Die Entscheidung pro Power Query hatte ja einen bestimmten Grund, und darum kann der zusätzliche Aufwand durchaus gerechtfertigt sein.
Erklärungen werden sie im folgenden Text kaum finden, auch sonst ist vieles recht kurz gehalten. Nach dem etwas näher beschriebenen Einstieg ist im Grunde genommen jeder Schritt gleich, nur die Vokabeln ändern sich ein wenig. In diesem Rahmen ein wichtiger Hinweis: Den Code, den sie eingeben, müssen Sie unbedingt in der exakten vorgegebenen Groß- Kleinschreibung verwenden. Fachlich ausgedrückt: die Syntax ist Case-sensitiv.
Wie sonst auch werden sie die Tabelle erst einmal im Query Editor öffnen. Im nächsten Schritt werden Sie der Optik wegen den Datentyp auf Datum (ohne Uhrzeit) ändern. Für jede der geforderten Auswertungspunkte werden sie nun nacheinander eine neue Spalte hinzufügen. Also ein Klick auf eben diesen Menüpunkt (Spalte hinzufügen). Klicken Sie anschließend in der Gruppe Allgemein auf die Auswahl Benutzerdefinierte Spalte. Es erscheint solch ein Dialogfenster:
Benutzerdefinierte Spalte hinzufügen
In das Textfeld Neuer Spaltenname schreiben Sie statt der Vorgabe Benutzerdefiniert eine „sprechende” Überschrift des jeweiligen berechneten Ergebnisses ein; für die 1. neue Spalte wäre dieses Jahr. Bei den folgenden Spalten entsprechend natürlich den passenden Text.
Jahr
Im großen Text-Eingabebereich Benutzerdefinierte Spaltenformel ist ja bereits das = vorgegeben und kann auch nicht gelöscht oder verändert werden. Geben Sie danach bitte folgende Formel ein:
Date.Year([Datum])
Sie können den gesamten Text dieser Formel von Hand eingeben, sie können sich es aber auch etwas leichter machen, indem sie nach der öffnenden runden Klammer auf die im rechten Teil des Fensters stehende Auflistung der Verfügbaren Spalten einen Doppelklick auf Datum durchführen. Dann wird der Feldname automatisch mit den umschließenden eckigen Klammern an der Stelle eingefügt. Bleibt nur noch, dass sie die Formel mit der schließenden unten Klammer versehen. OK und die Spalte mit der entsprechenden Jahreszahl und der korrekten Überschrift wird in die Abfrage eingefügt.
Monat
Für die numerische Ausgabe des Monats geben Sie beispielsweise diese Überschrift ein: Monat (1,2,3) und als Formel schreiben Sie dann:
Date.Month([Datum])
Wenn Sie jetzt denken „Ja, das ist doch so auch logisch” dann gebe ich Ihnen Recht. Und wenn dennoch irgend ein Fehler auftaucht, dann wird es wahrscheinlich daran liegen, dass sie nicht die exakte Schreibweise bezüglich der Groß-bzw. Kleinschreibung beachtet haben.
Vom Prinzip her sieht es bei der Textform des Monats genauso aus. Sie werden als Überschrift Monat (A,B,C) verwenden und diese Formel:
Date.MonthName([Datum])
Und der geschriebene Monatsname ist natürlich … Deutsch, sofern sie auch hier an die exakte Schreibweise gedacht haben. In einer französischen Umgebung wird es Französisch sein, bei anderen Sprachversionen entsprechend.
Tag
Das sollte Ihnen eigentlich alleine von der Hand gehen. Der englische Ausdruck für den Tag ist „Day”.
Quartal des Jahres
Diese Forderung ist schon etwas komplexer: Es soll nicht nur das Quartal als sondern auch durch einen / verbunden das Jahr mit angegeben werden. Aber auch das ist problemlos lösbar, wenn sie die Logik dieser Sprache aber auch des Power Query verstanden haben. Hier die Formel, erster Teil:
Date.QuarterOfYear([Datum])
OK, jetzt steht erst einmal das korrekte Quartal in der Spalte. Das Jahr hatten Sie schon mal berechnet, es steht in der 2. Spalte. Markieren Sie diese erst einmal durch einen einfachen Klick in die Überschrift. Immer noch im Menü Spalte hinzufügen Gruppe Allgemein finden Sie den Punkt Doppelte Spalte. „Klick” und automatisch wird als neue letzte Spalte eine Kopie mit dem Namen Jahr – Kopieren erstellt. Auch wenn Sie der Name stören sollte, belassen Sie es dabei. Das regelt sich von alleine… 😎 – Nun gilt es nur noch, diese beiden Spalten zu einer einzigen zusammenzufügen.
Markieren Sie zuerst die Spalte Quartal und danach die neu erstellte Spalte mit dem Jahr. Diese Reihenfolge ist wichtig! Rechtsklick in eine der beiden Überschriften und im Kontextmenü Spalten zusammenführen wählen. Im Dialogfenster geben Sie als Trennzeichen den / ein (geht nur über –Benutzerdefiniert–) und als Neuer Spaltenname nehmen Sie beispielsweise Qrt/Jahr. Die beiden eben noch vorhandenen einzelnen Spalten werden gelöscht und in der neuen Spalte in gewünschter Form dargestellt.
Wochentag
Jetzt ist ihre Kreativität gefordert. Vergessen Sie erst einmal die Forderung, dass die deutsche Norm verwendet werden soll. Im Excel heißt die Funktion ja WOCHENTAG(), in PQ ist natürlich alles anders, nämlich in Englisch. Und wenn sie sich die Logik beispielsweise beim Monatsnamen ansehen, kommen Sie vielleicht von alleine auf die Formel. Als Tipp: Tag Der Woche. Wenn Sie die Funktion richtig übersetzt haben und auch der Schreibweise im obigen Beispiel gefolgt sind wird beispielsweise in der Zeile 1 die Zahl 6 erscheinen, der 6. Tag der Woche. Und wenn sie sich Zeile 3 ansehen, werden Sie erkennen: Tag 0.
Tag 0, den gibt es doch gar nicht. Was steckt also dahinter? Zugegeben, ich war zu Beginn auch ziemlich verwirrt. Ein Blick in die Dokumentation der Funktionen hat mir dann aber gezeigt, dass das Ergebnis dieser Funktion immer Null-basiert ist. Also werden die Ergebnisse 0..6 zurück gegeben, der definierte erste Tag der Woche ist der Tag Null. Und das ist nicht änderbar. Zumindest nicht direkt. Aber es gibt natürlich einen Ausweg, der eigentlich ganz simpel ist:
Date.DayOfWeek([Datum])+1
Und das entspricht genau den Ergebnissen, die sie in Excel mit der Formel WOCHENTAG(Datum; 2) erhalten, also der expliziten Anweisung, die EU Norm (1. Tag der Woche ist Montag) zu verwenden. Hier erkennt Power Quer offensichtlich anhand der Ländereinstellungen, welches die korrekten Werte sind. Nett, wirklich nett.
Bleibt noch die ausgeschriebene Form des Wochentages. Hier will ich Ihnen nicht die Formel unterschlagen, denn vielleicht sind sie eben nicht zum Ziel gekommen; dann wird Ihnen diese Formel gewiss eine Hilfe sein, denn die gekürzte Form passt oben zu 100 %. Hier schreiben Sie:
=Date.DayOfWeekName([Datum])
und das Ergebnis passt. – Wenn Sie jetzt auf den Geschmack gekommen sind und denken: „nun ja, einer geht noch” und dabei an die Kalenderwoche denken, dann will ich sie nicht enttäuschen und auch hier das entsprechende Beispiel bringen:
Date.WeekOfYear([Datum])
… und es wir eine Kalenderwoche in die Spalte geschrieben. – Wie immer, wenn sie neue Wege beschreiten, werden sie das Ergebnis jeder Spalte kontrollieren. Dazu bieten sich die entsprechenden Excel Funktionen an. Wenn Sie also die Abfrage gespeichert und geschlossen haben und sich anschließend der erstellten Tabelle befinden, können Sie mit entsprechenden Vergleichsformeln in den Spalten J (und folgenden) ja eine entsprechende Formel hineinschreiben beispielsweise in J2:
=JAHR(A2)=B2
Das Ergebnis wird automatisch bis zur letzten Datenzeile ausgefüllt; wenn Sie diese Formel nach rechts rüber ziehen, werden die Spalten entsprechend auch angepasst. Das Ergebnis sollte stets WAHR sein.
In Sachen Datum (und genauso in Sachen Zeit) und vielen, vielen anderen Bereichen gibt es ausgesprochen viele Formeln der Sprache „M”. Ich selbst muss auch noch recht oft nachsehen, ob es für meinen Wunsch eine Funktion gibt aber auch um die genaue Syntax zu erkennen. Wenn Sie das selber einmal machen, geben Sie am besten in der Suchmaschine ihrer Wahl folgende Stichworte ein:
Power Query Sprache M
und sie werden vermutlich zu diesem Link geleitet. Im Bereich Date werden sie dann vielleicht auch erkennen, dass es für die Kalenderwoche keine Möglichkeit gibt, die europäische Norm als Argument zu verwenden. Insofern können Sie diese Funktion nur für die US-Kalenderwochen nicht jedoch für den europäischen Raum verwenden. Es ist aber durchaus denkbar, dass in den regelmäßigen Updates der Power Query Funktionalität dieser Punkt noch nachgebessert wird.
Ich räume ein: So ganz einfach ist das nicht mit den Funktionen der Sprache „M”. Aber wenn sie wegen großer Datenmengen (ohne die 1‑Mio-Grenze) oder wegen der doch erheblich besseren Geschwindigkeit bei Abfragen Power Query verwenden möchten oder müssen, dann lohnt sich auf Dauer gesehen auch die Beschäftigung mit diesem Thema.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen …