Bei vielen meiner Kunden wird Excel entweder als primäre oder als ergänzende Datengrundlage für Power-BI-Reports genutzt. Die Szenarien sind oft sehr unterschiedlich. Entweder stammen die Dateien aus Exporten anderer Systeme oder bestehende Daten aus SQL, Dataverse oder SharePoint-Listen werden gezielt mit Informationen aus einer Excel-Datei angereichert.

In diesem Beispiel zeige ich dir, wie du eine Excel-Datei in Power BI einbindest und den Report anschließend im Power BI Service veröffentlichst, sodass du die Daten später auch aktualisieren kannst. Genau an dieser Stelle stoßen viele auf Probleme. Nach dem Veröffentlichen erscheint häufig die Meldung, dass kein Datengateway eingerichtet wurde oder keine Cloudverbindung vorhanden ist.

Wichtig ist dabei ein zentraler Punkt. Wenn du Excel als Datenquelle nutzt, brauchst du kein Datengateway, solange sich die Datei in der Cloud befindet, zum Beispiel in SharePoint oder OneDrive. Entscheidest du dich hingegen für eine lokale Datei, kann Power BI im Service nicht darauf zugreifen und genau dann bekommst du diese Fehlermeldung.

Inhalt

• Aufbau meiner Excel-Datei
• Aufbau der Power BI Datei
• Das Aktualisierungsproblem
• Bestehenden Excel Link ändern
• Upload der neuen Datei

Hinweis: Um dieses Beispiel nach zustellen, benötigst du mindestens eine Power BI-Pro Lizenz, OneDrive Business und entsprechende Berechtigungen in Power BI Web entsprechend Arbeitsbereich erstellen zu dürfen.

Aufbau meiner Excel-Datei

In meinem Beispiel ist die Excel-Datei bewusst einfach gehalten. Sie enthält eine Tabelle mit zwei Spalten, Lizenzname und Preis. Wichtig ist dabei, dass die Daten in Excel als Tabelle formatiert sind. Wenn du eine CSV-Datei importierst oder in Excel einfach nur einen Zellbereich ohne Tabelle nutzt, arbeitet Power BI eher starr mit der Struktur. Erweiterst du später die Datei um zusätzliche Spalten, werden diese Änderungen beim Aktualisieren in der Regel nicht automatisch übernommen.

Anders sieht es aus, wenn du den Bereich als Excel-Tabelle formatierst. In diesem Fall greift Power BI auf ein dynamisches Tabellenobjekt zu. Neue Spalten und Zeilen können dadurch grundsätzlich bei einer Aktualisierung berücksichtigt werden.

Wichtig ist aber ein Punkt, den viele übersehen. Sobald du in Power Query mit festen Spalten arbeitest, zum Beispiel durch „Spalten auswählen“ oder „Spalten entfernen“, schränkst du diese Dynamik wieder ein.

Aufbau der Power BI Datei

Leider musst du jetzt zwischen zwei Szenarien unterscheiden. Im ersten Szenario hast du bereits einen Report erstellt und möchtest diesen nachträglich so anpassen, dass die Daten auch im Power BI Service aktualisiert werden können. Im zweiten Szenario startest du komplett neu und baust den Report von Grund auf auf. Wenn bei dir das erste Szenario zutrifft, hast du die Excel-Datei wahrscheinlich lokal in Power BI eingebunden und darauf basierend deinen Bericht erstellt. Anschließend hast du den Report im Power BI Service in einen Arbeitsbereich veröffentlicht. Genau hier entsteht später das Problem mit der Aktualisierung.

Im zweiten Szenario kannst du es direkt richtig machen. Statt die Excel-Datei über „Excel-Arbeitsmappe“ oder als lokale Datei einzubinden, solltest du von Anfang an die Datenquelle „Web“ verwenden. Dafür benötigst du einen direkten Link auf die Datei. Wie du diesen Link erstellst, zeige ich dir im nächsten Kapitel „URL für Weblink erstellen“. Sobald du den Link hast, kannst du ihn einfach in Power BI einfügen und die Excel-Datei über die URL anbinden.

Das Aktualisierungsproblem

Den passenden Link aus OneDrive zu bekommen ist leider etwas trickreich. Du kannst ihn nicht einfach über die Dateieigenschaften übernehmen. Auch die Funktion „Link kopieren“ hilft dir hier nicht weiter, da dieser Link nicht in einem Format vorliegt, das Power BI als Webquelle verwenden kann. Du musst dir den Link daher einmal selbst zusammenbauen.

Öffne dazu OneDrive im Browser und gehe in deinen Bereich „Eigene Dateien“. Navigiere zu dem Ordner, in dem deine Excel-Datei liegt. Kopiere dir zunächst die URL dieses OneDrive-Ordners und füge sie in einen Texteditor ein.

Jetzt ergänzt du den persönlichen Pfad. OneDrive verwendet in der URL den Bereich „/personal“ und arbeitet intern mit URL-Encoding. Dabei steht %2F für einen Slash. Direkt danach folgt dein Benutzer- oder Ordnername, wie er auch in der URL angezeigt wird. Diesen Teil übernimmst du ebenfalls und ergänzt ihn hinter „/personal“.

Im nächsten Schritt fügst du „/Documents“ hinzu, da alle Dateien in OneDrive in diesem Bereich liegen. Danach ergänzt du den restlichen Pfad zu deiner Datei. Wenn deine Datei in Unterordnern liegt, übernimmst du die Ordnerstruktur genau so, wie sie dir im Browser angezeigt wird, und trennst die einzelnen Ebenen mit einem Slash. Zum Schluss ergänzt du den Dateinamen deiner Excel-Datei.

Wenn du fertig bist, solltest du den Link testen. Öffne einen neuen Tab im Browser und füge die URL ein. Wenn alles korrekt ist, wird dir die Excel-Datei direkt zum Download angeboten.

Genau diesen Link verwendest du später in Power BI. Wenn du ihn über die Datenquelle „Web“ einbindest, greift Power BI direkt auf die Datei in der Cloud zu und kann sie auch entsprechend aktualisieren.

Direkte URL zum OneDrive Ordner

Korrigieren wir nun den lokalen Link und ersetzen ihn durch den Web-Link. Öffne dazu Power BI Desktop und lade den Report, in dem du die Verbindung anpassen möchtest. Wechsel anschließend in den Power Query Editor. Klicke dazu unter der Registerkarte „Start“ auf „Daten transformieren“. Der Power Query Editor öffnet sich in einem neuen Fenster.

Öffne dort den „Erweiterten Editor“. Hier siehst du sehr gut, dass aktuell noch die lokale Datei eingebunden ist.

Genau das müssen wir jetzt ändern. Statt der lokalen Quelle verwendest du den Web-Link, den du im vorherigen Schritt erstellt hast. Im Prinzip ersetzt du also die bestehende Quelle durch den neuen Link. Damit du den kompletten Code nicht selbst schreiben musst, habe ich dir ein Beispiel bei GitHub bereitgestellt [Download]. Du kannst den Code einfach kopieren und anschließend anpassen, indem du deine eigene URL und falls nötig den Namen deines Arbeitsblattes einsetzt. Deine Abfrage sollte danach entsprechend angepasst aussehen. Bestätige die Änderung mit „Fertig“.

Nachdem sich das Fenster geschlossen hat, wirst du aufgefordert, dich erneut anzumelden. Klicke dazu auf „Anmeldeinformationen bearbeiten“ und gib deine Zugangsdaten ein.

Sobald die Anmeldung erfolgreich war, wird die Tabelle neu geladen – jetzt allerdings aus der Cloud und nicht mehr von deinem lokalen Rechner.

Hinweis: Es kann sein, dass du dennoch einige Felder erneut den Visuals hinzufügen musst!

Upload der neuen Datei

Es ist soweit wir können nun testen, ob sich das Dataset aktualisieren lässt. Veröffentliche den Report erneut in Power BI. Wechsel anschließend in Power BI Web und öffne den entsprechenden Arbeitsbereich. Klicke beim Dataset auf die drei Punkte („Weitere Optionen“) und wähle im Kontextmenü den Eintrag „Einstellungen“ aus.

Unter Datenquelle-Anmeldeinformationen klickst du auf „Anmeldeinformationen bearbeiten“. Gib hier erneut deine Anmeldedaten ein. Wähle als Authentifizierungsmethode OAuth2 und als Datenschutzebene Organisation aus. Klicke anschließend auf „Anmelden“.

Der gelbe Hinweis sollte jetzt verschwunden sein. Gehe zurück in den Arbeitsbereich und fahre mit der Maus über das Dataset. Klicke dort auf „Aktualisieren“. Die Daten werden nun wie gewünscht aktualisiert.