Power BI – Datumstabelle mit Filter in Power Query M erstellen

Für ein Projekt musste ich eine Datums-Tabelle in Power Query erstellen, welche den Zeitraum zwischen den frühesten- und dem spätesten Datum einer anderen Tabelle aus Project for the Web berechnet und dann den Zeitverlauf in dieser Tabelle als Zeitstrahl hinzufügt bzw. anpasst. Da ich die Erstellung sehr kniffelig fand, möchte ich dir in diesem Blog-Artikel eine kurze Anleitung geben, wie du dieses Problem lösen kannst!

Hinweis: In meinem Beispiel habe ich die Datenbank „msdyn_resourceassignment“ eingebunden und die JSON-Daten expandiert. Solltest Du das Beispiel 1:1 nachbauen wollen, so findest Du die Anleitung in diesem Blogbeitrag. Dies ist nicht zwingend nötig, solange Du schon eine Tabelle mit einer Spalte mit einem Datumswert hast, die Du für den Filter verwenden möchtest.

Öffne Power BI Desktop auf deinem Rechner. Öffne den Power Query Editor. Klicke hierzu auf „Tabellentools (1)“ und wähle im Menü die „Option Daten eingeben (2)“ aus.

Füge im nächsten Schritt eine neue Abfrage hinzu. Klicke auf „Neue Quellen (1)“ und öffne im Kontextmenü die Option „Leere Abfrage (2)“ aus.

Eine leere Abfrage wird geöffnet.

Als nächstes benötigst du drei Variablen. In der ersten Variable speicherst du das früheste Anfangsdatum und in der zweiten speicherst Du dann das späteste Datum der entsprechenden Spalte aus deiner anderen Tabelle. Das späteste Datum benötigst Du aber nur, um die Tage zwischen dem Anfangs- und dem Enddatum zu berechnen. Hier bei handelt es sich dann um die dritte Variable die wir benötigen. Die Variablen trägst Du im „Erweiterten Editor“ ein. Öffne diesen in dem du unter dem Ribbon-Menü auf „Start (1)“ und dann auf den Button „Erweiterter Editor (2)“ klickst.

Die Variablen werden unterhalb des „let-Bereiches“ eingetragen. Die Option „Quelle“ kannst du entfernen. Jetzt benötigen wir das Datumsfeld aus der zweiten Tabelle. In meinem Fall lautet das „ZeitAlsTag“. Die Variable nenne ich „DatumMin“ und lese in dieser das kleinste Datum aus der entsprechenden Tabellenspalte „msdyn_resourceassignment[ZeitAlsTag]“ ein. Dies können wir mit der folgenden Abfrage „Date.From“ realisieren. Gebe jetzt die folgende Formel ein.

StartDatum = List.Min(msdyn_resourceassignment[ZeitAlsTag])

Passe die Formel bei Bedarf an und ersetze meinen Datenbank- und den Spaltennamen mit deinem. Gebe unter dem „in-Bereich“ noch den Variabel Namen „StartDatum“ ein.

Schließe jetzt die Abfrage und schaue Dir das Ergebnis an. Wie du erkennst, wird dir das kleinste Datum in der Spalte angezeigt. Auf der rechten Seite unter der Rubrik „Angewendete Schritte“ findest Du auch die gespeicherte Variable „StartDatum“.

Öffne erneut den „Erweiterten Editor wie oben beschrieben. Füge unter der geraden angelegten Variabel eine weitere hinzu. Jetzt benötigen wir das größte Datum. Dies kannst Du mit der Formel:

EndDatum = List.Max(msdyn_resourceassignment[ZeitAlsTag])

Ändere einfach den Variablennamen von „StartDatum“ auf „EndDatum“ und in der Formel das „Min“ gegen ein „Max“ in der Formel. Ergänze die vorherige Formel mit einem Komma „,“. Ändere im „in-Bereich“ ebenfalls die Ausgabe auf „EndDatum“. Die Abfrage sollte wie folgt aussehen.

Schließe erneut den Editor und kontrolliere das Ergebnis. Beide Variablen werden Dir auf der rechten Seite angezeigt. Mit einem Klick auf den entsprechenden Variablennamen kannst Du dir die unterschiedlichen Werte anschauen.

Jetzt benötigen wir die Tage, zwischen dem frühesten und dem spätesten Datum. Eigentlich könnten wir einfach die Datums von einander subtrahieren. Allerdings erhalten wir so die Formatierung des Rückgabewerts als Datum. Diesen benötigen wir aber in der Formatierung Zahl. Öffne erneut den „Erweiterten Editor“ und gebe eine weitere Variable „TageGesamt“ ein. Du kannst jetzt schon mit den vorherigen Variablen arbeiten. Trage zur Berechnung die folgende Formel ein:

TageGesamt = Number.From(EndeDatum – StartDatum)

Durch den Vorsatz Number.From erhalten wir das Ergebnis als Nummernwert. Ergänze wieder hinter der vorherigen Formel ein Komma „,“ und im „in-Bereich“ die Variable auf „TageGesamt“. Die Abfrage sollte wie folgt aussehen. Schließe das Fenster wieder und kontrolliere das Ergebnis.

Dir werden jetzt die Tage in der Formatierung Zahl angezeigt.

Jetzt können wir die Formel für die Erstellung der Tabellenspalte für den Zeitstrahl eingeben. Die Syntax ist folgende:

List.Dates(start as date, count as number, step as duration) as list

Gehen wir kurz die Formel durch. „Start“ ist unsere Variable „StartDatum“. “Count“ hierbei handelt es sich um die Tage für die Zeitspanne. Hier tragen wir unsere Variable „TageGesamt“ ein. „Step“ müssen wir in der Formel ergänzen. Hierbei handelt es sich um den gewünschten Intervall. In meinem Fall möchte ich Tageweise die Berechnung durchführen. Öffne erneut den „Erweiterten Editor“. Gebe nun die Folgende Formel ein. Denke wieder daran, hinter der vorherigen Formel ein Komma „,“ zusetzen und die „Zeitstrahl-Variable“ in den „in-Bereich“ zu setzen.

Zeitstrahl = List.Dates(StartDatum,TageGesamt + 1, #duration(1, 0, 0, 0))

Hinweis: Damit Du den letzten Tag dazu addierst, musst du noch einen Tag dazu rechnen.

Die Eingabe sollte nun wie folgt aussehen. Schließe den Editor.

Wie du siehst, wurde die Tabelle mit einem fortlaufenden Datum vom “MinDatum” bis “MaxDatum” angelegt. Sollte sich jetzt das Max-Datum oder das Min-Datum ändern, so wird diese automatisch angepasst.

Leider sind die Werte in einem Text-Format gespeichert und eignen sich nicht für die Auswertung. Aus dem Grund musst Du die Abfrage in eine Tabelle umwandeln und dann die Formatierung ändern. Vergebe als erstes der Abfrage1 einen gewünschten Namen. Hierzu trägst Du einfach auf der rechten Seite in der Rubrik „Eigenschaften“ den gewünschten Namen ein. In meinem Beispiel nenne ich die Tabelle „Zeitstrahl“. Klicke anschließend die „Enter-Taste“. Der Name wird dann entsprechend angepasst.

17

Jetzt wählst Du die Registerkarte „Transformation (1)“ und klickst auf den Button „Zur Tabelle konvertieren (2)“.

Bestätige die Abfrage mit „OK“.

Jetzt kannst Du die Formatierung der Spalte von Text auf Datum ändern und den Namen der Spalte anpassen. Klicke hierzu auf das Zahlensymbol an dem Spaltennamen und wähle das gewünschte Formatierungsformat aus.

Klicke abschließend mit der rechten Maustaste auf den Namen „Column1“ und wähle im Kontextmenü die Option „Umbenennen“ aus. Gebe einen gewünschten Namen ein.