Die Speicherung von Excel-basierten Datenquellen an Online-Speicherorten wie SharePoint oder OneDrive Online ist in modernen Unternehmen eine gängige Praxis. Sie bietet deutliche Vorteile, insbesondere im Hinblick auf die Datenverwaltung und die Zusammenarbeit in großen Entwicklungsteams. Eine gemeinsame Dateilokation vereinfacht die Fehlerbehebung erheblich, da Fehler in der Quelldatei leichter lokalisiert oder verschobene Dateipfade schneller nachvollzogen werden können.
Trotz der Vorteile des Teilens von Excel-Dateien in Online-Quellen gibt es derzeit keinen standardisierten Power BI Desktop Datenkonnektor für Excel Online Quellen. Während in Dataflows unter Power Query Online eine URL als Quelle für Excel-Arbeitsmappen eingefügt werden kann, bietet Power BI Desktop nativ nur den „Excel-Arbeitsmappen“-Konnektor, der auf eine lokale Dateisystem-Position verweist. Dies stellt eine Herausforderung für Anwender dar, die ihre Online-Daten nahtlos integrieren möchten. wenn dann formel excel können in Power BI Desktop nicht direkt angewendet werden, wenn die Datenquelle online ist.
Diagramm: Datenfluss von BI-Quellen in Power BI
Power Query Life Hacks: Eine Einführung
In Power BI Desktop können wir einen der nützlichsten Power Query Life Hacks nutzen, um eine Verbindung zu Excel Online Quellen herzustellen. Ein Power Query Life Hack bezeichnet die Verwendung der Power Query Benutzeroberfläche als groben Rahmen für gängige Transformationsschritte, deren standardisierte Optionen oft nicht ausreichen, um Daten korrekt zu transformieren. Hinter den Kulissen übersetzt die Power Query Engine jeden Klick auf eine Option im Power Query Menüband oder die Auswahl eines neuen Datenquellen-Konnektors in ein Stück M-Code. Dieser automatisch generierte Code kann dann in der Formelleiste oder im Erweiterten Editor an die exakten Transformationsbedürfnisse angepasst werden.
Screenshot: Auswahl des Excel-Konnektors in Power BI Desktop
Das Schreiben benutzerdefinierter M-Anweisungen kann komplex sein, besonders angesichts der Vielschichtigkeit der M-Formelsprache. Der Anblick eines leeren Erweiterten Editors reicht oft aus, um erfahrenen Datenanalysten einen Schreck einzujagen. Durch die Nutzung der Power Query Benutzeroberfläche als Ausgangspunkt lässt sich wertvolle Zeit sparen, die sonst für das Durchforsten der M-Formel-Dokumentation für grundlegende Transformationsschritte benötigt würde. Stattdessen können Sie sich stärker den komplexeren Aspekten widmen. Dies ist vergleichbar mit dem Aufbau einer excel tabelle mit formeln, bei der man zuerst die Grundstruktur schafft, bevor man tiefergehende Berechnungen implementiert.
Screenshot: M-Code im erweiterten Editor von Power Query
Schritt-für-Schritt-Anleitung: Excel Online mit Power BI Desktop verbinden
Die Verbindung von Excel Online Quellen mit Power BI Desktop erfordert eine kleine, aber effektive Anpassung im Power Query Editor. Hier ist eine detaillierte Anleitung:
1. Lokale Datenquelle vorbereiten
Laden Sie zunächst die Excel-Quelldatei lokal auf Ihren Computer herunter. Dies dient dazu, einen ersten Eindruck von der Datenstruktur zu erhalten. Alternativ können Sie auch mit einer beliebigen leeren Excel-Arbeitsmappe beginnen.
2. Den Excel-Arbeitsmappen-Konnektor nutzen
Wählen Sie in Power BI Desktop den nativen Excel-Arbeitsmappen-Konnektor aus, indem Sie zu Neue Quelle > Excel-Arbeitsmappe navigieren. Wählen Sie die auf Ihrem lokalen Computer gespeicherte Excel-Datei aus und klicken Sie anschließend auf Transformieren.
3. M-Code im Formelbereich anpassen
Klicken Sie im Eigenschaftenbereich auf die Zeile für den Schritt Quelle. Erweitern Sie die Formelleiste, indem Sie auf das Pfeil-nach-unten-Symbol klicken.
Screenshot: Quelle-Schritt im Power Query Editor
Hier sehen Sie, was hinter den Kulissen der Power Query Benutzeroberfläche passiert. Durch das Klicken auf Neue Quelle > Excel-Arbeitsmappe übersetzte die Power Query Engine diese Aktion als den M-Code-Schnipsel „Excel.Workbook()“. Innerhalb des Excel.Workbook()-Blocks befindet sich ein verschachtelter File.Contents()-Block, der auf den Dateispeicherort auf Ihrem lokalen Computer verweist.
Screenshot: Pfeil zum Erweitern der Formelleiste
Screenshot: Formelleiste mit Excel.Workbook und File.Contents
Wir können diesen File.Contents()-Block löschen und ihn durch einen Web.Contents()-Block ersetzen, um auf die URL zu verweisen, unter der sich die Excel Online Quelle befindet.
4. Die URL bereinigen
Um die URL für SharePoint- oder OneDrive-Online-Quellen zu generieren, suchen Sie die Excel-Datei in der Dokumentbibliothek. Klicken Sie auf das Ellipsen-Symbol > Link kopieren neben dem Dateinamen. Klicken Sie anschließend auf Kopieren.
Im Power Query Editor löschen Sie den gesamten File.Contents()-Block – alles bis zum Teil , null, true) am Ende der Zeile.
Geben Sie Web.Contents(“ mit einem öffnenden doppelten Anführungszeichen ein. Fügen Sie die URL aus der Online-Quelle ein.
Wichtig: Sie müssen diese automatisch generierte URL aus der Option „Link kopieren“ bereinigen. Je nach Stil der SharePoint-Seite müssen Sie einen der beiden hervorgehobenen Abschnitte aus der URL löschen:
- Löschen Sie :x:/r/ oder :x/s/ nach dem sharepoint.com/-Abschnitt.
- Löschen Sie den gesamten Text vom Fragezeichen bis zum Ende.
Beispiele für bereinigte URLs:
Screenshot: Beispiele für bereinigte SharePoint- und OneDrive-URLs
Stellen Sie sicher, dass die URL mit einem abschließenden doppelten Anführungszeichen und einer schließenden Klammer versehen ist. ” ). Drücken Sie in Ihrer Formelleiste die Eingabetaste, um die Änderungen zu übernehmen. Die korrekte Anwendung von formeln für excel ist essenziell, auch wenn man den Weg über Power Query geht.
5. Anmeldeinformationen festlegen
Klicken Sie auf die Schaltfläche Anmeldeinformationen bearbeiten im gelben Band. Melden Sie sich mit Ihrem Organisationskonto an, das Zugriff auf die SharePoint-Seite hat.
Feinabstimmung und Datenschutz
Beim Umgang mit sensiblen organisatorischen Quellen ist es ratsam, die Datenschutzebene zu ändern, um Datenlecks zu verhindern und Compliance-Standards einzuhalten.
Screenshot: Datenschutzebenen-Einstellungen in Power BI
Standardmäßig versucht die Power Query Engine, alle Transformationsschritte in eine einzige M-Anweisung zu kombinieren und an die Quelle zurückzuschieben. Bei öffentlichen Quellen versucht die Power Query Engine, die Abfrageleistung zu verbessern, indem sie Abfragen aus mehreren Quellen offenlegt. Klicken Sie auf Datenquelleneinstellungen und wählen Sie die Excel-Quelle aus. Klicken Sie anschließend auf Berechtigungen bearbeiten und wählen Sie je nach Bedarf Organisatorisch oder Privat aus.
Führen Sie weitere erforderliche Power Query Transformationsschritte an der Quelldatei durch. Klicken Sie auf Schließen & Anwenden in der linken Ecke des Power Query Menübands, sobald Sie bereit sind, diese Tabelle mit dem Modell zu verbinden oder das Dataset zu veröffentlichen.
Veröffentlichung im Power BI Service
Nachdem die Verbindung in Power BI Desktop erfolgreich hergestellt wurde, ist der nächste Schritt die Veröffentlichung im Power BI Service.
Screenshot: Veröffentlichungsoptionen in Power BI Desktop
Klicken Sie auf Veröffentlichen und wählen Sie den gewünschten Arbeitsbereich aus.
Navigieren Sie im Power BI Service zum Ziel-Arbeitsbereich. Suchen Sie das veröffentlichte Dataset. Klicken Sie auf das Ellipsen-Symbol > Einstellungen.
Erweitern Sie den Abschnitt Anmeldeinformationen für Datenquellen.
- Wählen Sie die Excel Web-Quelle > Anmeldeinformationen bearbeiten.
- Ändern Sie die Authentifizierungsmethode auf OAuth2.
- Wählen Sie dieselbe Datenschutzebene-Einstellung, die Sie in Power BI Desktop festgelegt haben. Die Einstellungen der Datenschutzebene werden nicht automatisch in den Service übertragen, wenn Sie das Dataset oder den Bericht veröffentlichen.
- Klicken Sie auf Anmelden und melden Sie sich über das Popup-Fenster an.
Aktualisieren Sie das Dataset und richten Sie bei Bedarf einen Geplanten Aktualisierungszyklus ein.
Screenshot: Einstellungen für Datasets im Power BI Service
Wichtige Hinweise zur Datenpflege
Nachdem die Verbindung hergestellt und veröffentlicht wurde, werden Änderungen, die online an der Datei vorgenommen werden, nach der Aktualisierung des Berichts reflektiert. Beachten Sie jedoch, dass der Umgang mit Excel-Quellen langfristig herausfordernd sein kann. Bei der Verwendung von Excel Online Quellen ist es entscheidend, stets einen Prozess für die Eigentümer der Quelldateien bezüglich der ordnungsgemäßen Pflege der Quelle zu entwickeln. Die korrekte Berechnung von excel formel stunden berechnen oder die summe bei excel sollte durch klare Richtlinien für die Quelldatei gewährleistet sein.
Kommunizieren Sie mit den Eigentümern der Dateiquelle, dass jede Änderung in der Excel-Quelle nachgelagerte Power BI Inhalte beeinflussen wird. Treten Fehler in der Quelle auf oder ändert sich die Dateistruktur, können Power BI Inhalte nicht mehr aktualisiert werden.
Häufige Verhaltensweisen, die zu Aktualisierungsfehlern führen, sind:
- Zellen, die #N/A-Fehler enthalten
- Zellen, deren Werte mit den in Power BI festgelegten Datentypen kollidieren
- Power Query Schritte, die sich auf spezifische Spalten oder Blätter beziehen, die umbenannt oder aus dem Quellspeicherort entfernt wurden
Die Pflege von Excel Online Quellen kann die Transparenz im Team verbessern und die Zusammenarbeit erleichtern, vorausgesetzt, angemessene Governance-Prozesse werden von allen verstanden und befolgt.
Lassen Sie sich von Experten unterstützen, um Business Intelligence in Ihrer Organisation optimal einzusetzen, unabhängig davon, wo Sie sich auf Ihrer Analyse-Reise befinden.