Power BI – Tage zwischen einem Zeitraum in einer Tabelle ausgeben

Bekanntlich ist Microsoft Power BI eines der besten Werkszeuge, um Daten unterschiedlichster Herkunft zu analysieren. Dadurch eignet sich das Tool auch optimal gewünschte Projektberichte oder Auswertungen im Bereich Projektmanagement zu erstellen. Aber nicht nur die gewünschten Reports können durch Power BI erstellt werden, dass Produkt eignet sich auch hervorragend dazu, eventuelle fehlende Ansichten für zusätzliche notwendige Auswertung die für ein Projekt notwendig sind zu erstellen . Zum Beispiel einen Ressourcenbericht über die Abwesenheit meiner Mitarbeiter. Dieser Report soll mir dann anzeigen, wer mir für ein Projekt zur Verfügung steht – oder eventuell nicht. Das angedachte Szenario ist eigentlich relativ einfach. Ich erstelle eine SharePoint Liste in einer Teamwebsite in SharePoint Online, um die Abwesenheiten dort einzutragen. In der Liste wird dann wesentlich der Name der entsprechenden Ressource und der gewünschte Zeitraum der Abwesenheit mit einem Anfangs und Enddatum eingetragen. Danach wird diese Liste in Power BI weiterverarbeitet und in meinem Report visualisiert dargestellt. In der folgenden Grafik zeige ich euch ein Beispiel von meiner Liste.

Kommen wir nun zu der Problemstellung für viele Power BI Coder. Wenn ich eine Abwesenheit in der Liste eintrage, habe ich leider nur das Anfangs- und das Enddatum der Abwesenheit. Was ich allerdings benötige, sind in meiner Tabelle nicht nur das Anfangs- und das Enddatum, sondern auch die Tage dazwischen. Für das oben angezeigte Beispiel bedeutet das, dass ich in meiner Tabelle ebenfalls die Tage 01.03, 02.03, 03.03, 04.03 und 05.03 benötige. Nur so ist es mir möglich, die direkten Beziehungen zu den Abwesenheiten zu erstellen. Im Internet findet ihr hierzu viele komplexer Anleitungen. Ich möchte euch in diesem Blog zeigen, wie ihr eine einfach Lowcode-Lösung erstellen könnt.

Hinweis: Um dieses Beispiel nachzustellen benötigt Ihr eine SharePoint-Online Liste mit einen Anfangs- und Enddatum, sowie einen Namen für den Antragssteller. In meinem Beispiel hole ich den Benutzer-Account in die Tabelle. Ebenfalls benötigt Ihr Microsoft Power Bi Desktop auf eurem Computer. Die aktuelle Version könnt ihr hier herunterladen.

Einbinden der SharePoint-Liste

Öffnet Microsoft Power Bi Desktop. Klickt dann im Ribbon-Menü auf die Option „Daten abrufen“ und wählt im Kontextmenü die Option „Weitere…“.

Gebt dann im oberen Suchfeld „SharePoint“ ein. Euch werden jetzt auf der rechten Seite mehrere Optionen angeboten. Klickt auf die Option „SharePoint Online-Liste“ und dann im unteren Bereich auf den Button „Verbinden“, um den Assistenten zu starten. Gebt in dem Feld „Website-URL“ den Link zu eurer Teamwebsite ein und klickt dann im unteren Bereich auf den Button „OK“.

Sucht jetzt eure SharePoint Liste. Markiert die Liste und klickt dann auf „Laden“.

Hinweis: Damit die Zeit für die Aktualisierung optimiert werden, sollten alle ungenutzten Spalten gelöscht werden. Aus diesem Grund solltet Ihr jetzt alle unbenutzten Datenfelder löschen.

UTC-Datum anpassen

Das erste Problem was wir haben, ist das eingetragene Datum in den Spalten Anfang und Ende aus der SharePoint Liste. Wie ihr schnell feststellt, stimmt das importierte Datum, trotz angepasster Zeitzone in der SharePoint-Liste, nicht mit dem eingetragenem Datum überein. Das liegt daran, dass Microsoft das Datum nicht in der erfassten UTC-Zone abspeichert, sondern immer in UTC +0000. Das Datum kann man aber relativ einfach an die eigene Zeitzone anpassen. Hierzu öffnet Ihr den PowerQuery-Editor. Klickt hierzu im Ribbon-Menü auf die Option „Start“ und dann auf den Button „Daten transformieren“.

Jetzt müssen wir als erstes die Spalte Anfang und Ende in unser Zeitzonenformat umwandeln. Klickt im Ribbon-Menü auf die Registerkarte „Transformieren“ und klickt auf den Button „Datentyp“. Wählt im Kontextmenü die Option „Datum/Uhrzeit/Zeitzone“, um die markierte Spalte mit der Zeitzone zu formatieren. Wiederholt diesen Schritt mit der Spalte „Ende“.

Markiert erneut den Spaltenkopf „Anfang“. Klickt unter dem Menüpunkt „Spalte hinzufügen“ auf die Option „Zeit“ und wählt dann im Kontextmenü die Option „Lokale Uhrzeit“. Wiederholt den Schritt erneut mit der Spalte „Ende“.

Es wurden jetzt zwei weitere Spalten hinzugefügt. Im nächste Schritt benennen wir diese um. Die Spalten sollen den Namen „AnfangAbw“ und „EndeAbw“ erhalten. Klickt hierzu auf den Spaltenkopf mit der rechten Maustaste und wählt im Kontextmenü die Option „Umbenennen“. Gebt euren gewünschten Namen für die entsprechende Spalte ein. Wiederholt den Vorgang mit der Spalte Ende. Sobald ihr die Umbenennung abgeschlossen habt, können die Spalten besser in dem Report angesprochen werden.

Tabelle transformieren

Im letzten Schritt werden wir jetzt die Tabelle transformieren, damit wir unsere Datumseinträge erhalten. Wir benötigen hierzu als erstes eine neue Spalte, in der wir eine Liste erstellen in der die benötigten Tage zwischen dem Anfangs- und dem Enddatum gespeichert werden. Klickt hierzu im Ribbon-Menü unter der Registerkarte „Spalte hinzufügen“ auf den Button „Benutzerdefinierte Spalte“.

Als Namen für den Spaltenkopf nehme ich „AbwAlsTag“. Gebt hierzu die dargestellte Formel ein und klickt dann auf „OK“.

{Number.From([AnfangAbw])..Number.From([EndeAbw])}

{Number.From(Date.From[AnfangAbw]))..Number.From(Date.From([EndeAbw]))}

Hinweis: Bei der Formel kam es zu dem Fehler: Expression.Error: „Die Zahl befindet sich außerhalb des gültigen Bereichs eines 32-Bit-Ganzzahlwerts.Details: 44986,04167“ Aus dem Grund empfehle ich die folgende Formel zu verwenden!

Hinweis: Da wir nur Nummernformate in der Liste speichern können, müssen wir das Datum aus den Spalten in eine entsprechende Nummer umwandeln. Das machen wir mit der Anweisung „Number.Form“. Aber keine Sorge, das Zahlenformat werden wir später wieder umwandeln in ein Datumsformat.

Als nächstes werden wir jetzt die Zeile ausweiten. Klickt hierzu rechts neben den Spaltennamen auf das „Transformationsmenü“ und klickt im Kontextmenü auf die Option „Auf neue Zeilen ausweiten“.

Die Tage zwischen dem End- und Anfangsdatum werden euch jetzt im Nummernformat angezeigt. Diese wollen wir jetzt im nächsten Schritt in das entsprechende Datumsformat umwandeln. Klickt hierzu wieder auf den Spaltenkopf. Klickt dann in der Ribbon auf die Registerkarte „Transformieren“ und wählt erneut die Option „Dateityp“ aus. Wählt im Kontextmenü das gewünschte Datumsformat aus.

Euch wird jetzt das Zahlenformat wieder als Datum angezeigt. Jetzt könnt Ihr die gewünschten Reports erstellen. In meinem Beispiel seht Ihr einen Urlaubsbericht der euch anzeigt, wann eine Ressource abwesend ist und wann nicht.