Excel

Artikel
02/07/2024

Zusammenfassung

Element	Beschreibung
Freigabestatus	Allgemeine Verfügbarkeit
Produkte	Excel Power BI (Semantikmodelle) Power BI (Dataflows) Fabric (Dataflow Gen2) Power Apps (Dataflows) Dynamics 365 Customer Insights Analysis Services
Unterstützte Authentifizierungsarten	Anonym (online) Basic (online) Organisatorisches Konto (online)
Funktionsreferenz-Dokumentation	Excel.Workbook Excel.CurrentWorkbook

Hinweis

Einige Funktionen können in einem Produkt vorhanden sein, in anderen jedoch nicht, was an den Bereitstellungsplänen und hostspezifischen Funktionen liegt.

Voraussetzungen

Um eine Verbindung zu einer Legacy-Arbeitsmappe (z.B. .xls oder .xlsb) herzustellen, ist der Access Database Engine OLEDB (oder ACE) Provider erforderlich. Um diesen Anbieter zu installieren, gehen Sie auf die Download-Seite und installieren Sie die entsprechende Version (32 Bit oder 64 Bit). Wenn Sie es nicht installiert haben, erhalten Sie folgende Fehlermeldung, wenn Sie eine Verbindung zu Legacy-Arbeitsmappen herstellen:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE kann nicht in Cloud-Service-Umgebungen installiert werden. Wenn dieser Fehler in einem Cloud-Host (z. B. Power Query Online) auftritt, müssen Sie also ein Gateway verwenden, auf dem ACE installiert ist, um eine Verbindung zu den alten Excel-Dateien herzustellen.

Unterstützte Funktionen

Importieren

Verbindung zu einer Excel-Arbeitsmappe von Power Query Desktop aus

So stellen Sie die Verbindung von Power Query Desktop aus her:

Wählen Sie in der Benutzeroberfläche zum Abrufen von Daten Excel-Arbeitsmappe aus. Die Benutzeroberfläche zum Abrufen von Daten in Power Query Desktop ist je nach App unterschiedlich. Weitere Informationen zur Benutzeroberfläche zum Abrufen von Daten in Power Query Desktop für Ihre App finden Sie unter Wo man Daten abruft.
Suchen Sie die Excel-Arbeitsmappe, die Sie laden möchten, und wählen Sie sie aus. Wählen Sie anschließend Öffnen aus.

Wenn die Excel-Arbeitsmappe online ist, verwenden Sie den Webconnector, um eine Verbindung zur Arbeitsmappe herzustellen.
Wählen Sie in Navigatordie gewünschten Arbeitsmappeninformationen aus und wählen Sie dann entweder Laden, um die Daten zu laden, oder Daten transformieren, um die Daten im Power Query Editor weiter zu transformieren.

Verbindung zu einer Excel-Arbeitsmappe aus Power Query Online

So stellen Sie die Verbindung von Power Query Online aus her:

Wählen Sie in der Benutzeroberfläche zum Abrufen von Daten die Option Excel-Arbeitsmappe aus. In verschiedenen Apps gibt es unterschiedliche Wege, um zur Benutzeroberfläche zum Abrufen von Daten in Power Query Online zu gelangen. Weitere Informationen dazu, wie Sie aus Ihrer App zur Benutzeroberfläche zum Abrufen von Daten in Power Query Online gelangen, finden Sie unter Wo man Daten abruft.
Geben Sie im daraufhin angezeigten Excel-Dialogfeld den Pfad zur Excel-Arbeitsmappe an.
Wählen Sie bei Bedarf ein lokales Daten-Gateway für den Zugriff auf die Excel-Arbeitsmappe.
Wenn Sie zum ersten Mal auf diese Excel-Arbeitsmappe zugreifen, wählen Sie die Art der Authentifizierung und melden Sie sich bei Ihrem Konto an (falls erforderlich).
Wählen Sie in Navigatordie gewünschten Arbeitsmappeninformationen aus, und fahren Sie dann mit Transform Data fort, um die Daten im Power Query Editor zu transformieren.

Vorgeschlagene Tabellen

Wenn Sie eine Verbindung zu einer Excel-Arbeitsmappe herstellen, die nicht ausdrücklich eine einzelne Tabelle enthält, versucht der Power Query Navigator, eine Vorschlagsliste von Tabellen zu erstellen, aus der Sie wählen können. Betrachten Sie zum Beispiel die folgende Arbeitsmappe, die Daten von A1 bis C5, weitere Daten von D8 bis E10 und weitere von C13 bis F16 enthält.

Screenshot der Excel-Arbeitsmappe mit drei Datensätzen.

Wenn Sie eine Verbindung zu den Daten in Power Query herstellen, erstellt der Power Query-Navigator zwei Listen. Die erste Liste enthält das gesamte Arbeitsmappenblatt, die zweite Liste enthält drei vorgeschlagene Tabellen.

Wenn Sie das gesamte Blatt im Navigator auswählen, wird die Arbeitsmappe so angezeigt, wie sie in Excel erscheint, wobei alle leeren Zellen mit null gefüllt sind.

Wenn Sie eine der vorgeschlagenen Tabellen auswählen, wird jede einzelne Tabelle, die Power Query aus dem Layout der Arbeitsmappe ermitteln konnte, im Navigator angezeigt. Wenn Sie zum Beispiel Tabelle 3 wählen, werden die Daten angezeigt, die ursprünglich in den Zellen C13 bis F16 standen.

Hinweis

Wenn sich das Blatt stark ändert, wird die Tabelle möglicherweise nicht richtig aktualisiert. Möglicherweise können Sie die Aktualisierung beheben, indem Sie die Daten erneut importieren und eine neue vorgeschlagene Tabelle auswählen.

Problembehandlung

Numerische Genauigkeit (oder „Warum haben sich meine Zahlen geändert?“)

Beim Importieren von Excel-Daten fällt Ihnen vielleicht auf, dass sich bestimmte Zahlenwerte beim Importieren in Power Query geringfügig zu verändern scheinen. Wenn Sie zum Beispiel in Excel eine Zelle mit dem Wert 0,049 auswählen, wird diese Zahl in der Formelleiste als 0,049 angezeigt. Wenn Sie jedoch dieselbe Zelle in Power Query importieren und auswählen, wird sie in den Vorschaudetails als 0,049000000000000002 angezeigt (auch wenn sie in der Vorschautabelle als 0,049 formatiert ist). Was geht da vor?

Die Antwort ist etwas kompliziert und hat damit zu tun, dass Excel Zahlen in der sogenannten binären Gleitkommadarstellungspeichert. Unterm Strich gibt es bestimmte Zahlen, die Excel nicht mit 100%iger Genauigkeit darstellen kann. Wenn Sie die .xlsx-Datei öffnen und sich den tatsächlich gespeicherten Wert ansehen, werden Sie feststellen, dass in der .xlsx-Datei 0,049 tatsächlich als 0,049000000000000002 gespeichert ist. Dies ist der Wert, den Power Query aus der .xlsx-Datei liest, und somit der Wert, der erscheint, wenn Sie die Zelle in Power Query auswählen. (Weitere Informationen zur numerischen Genauigkeit in Power Query finden Sie in den Abschnitten „Dezimalzahl“ und „Feste Dezimalzahl“ auf Datentypen in Power Query)

Verbinden mit einer Online-Excel-Arbeitsmappe

Wenn Sie eine Verbindung zu einem in Sharepoint gehosteten Excel-Dokument herstellen möchten, können Sie dies über den Web Connector in Power BI Desktop, Excel und Dataflows sowie über den Excel Connector in Dataflows tun. Um den Link zur Datei zu erhalten:

Öffnen Sie das Dokument in Excel Desktop.
Öffnen Sie das Menü Datei, wählen Sie die Registerkarte Info und dann Pfad kopieren.
Kopieren Sie die Adresse in das Feld Dateipfad oder URL, und entfernen Sie das ?web=1 vom Ende der Adresse.

Älterer ACE-Connector

Power Query liest Legacy-Arbeitsmappen (z. B. .xls oder .xlsb) mit dem OLEDB-Anbieter Access Database Engine (oder ACE). Aus diesem Grund kann es beim Import von Legacy-Arbeitsmappen zu unerwarteten Verhaltensweisen kommen, die beim Import von OpenXML-Arbeitsmappen (z. B. .xlsx) nicht auftreten. Hier sind einige allgemeine Beispiele aufgeführt.

Unerwartete Werteformatierung

Aufgrund von ACE werden Werte aus einer alten Excel-Arbeitsmappe möglicherweise mit weniger Präzision oder Genauigkeit importiert, als Sie erwarten. Stellen Sie sich zum Beispiel vor, Ihre Excel-Datei enthält die Zahl 1024.231, die Sie für die Anzeige als „1.024.23“ formatiert haben. Beim Import in Power Query wird dieser Wert als Textwert „1.024,23“ anstelle der zugrunde liegenden Full-Fidelity-Zahl (1024,231) dargestellt. Das liegt daran, dass ACE in diesem Fall nicht den zugrunde liegenden Wert an Power Query weitergibt, sondern nur den Wert, wie er in Excel angezeigt wird.

Unerwartete Nullwerte

Wenn ACE ein Blatt lädt, schaut es sich die ersten acht Zeilen an, um die Datentypen der Spalten zu bestimmen. Wenn die ersten acht Zeilen nicht repräsentativ für die späteren Zeilen sind, wendet ACE möglicherweise einen falschen Typ auf diese Spalte an und gibt für jeden Wert, der nicht dem Typ entspricht, Nullen zurück. Wenn beispielsweise eine Spalte in den ersten acht Zeilen Zahlen enthält (z. B. 1000, 1001 usw.), in den späteren Zeilen jedoch nichtnumerische Daten (z. B. „100Y“ und „100Z“), schließt ACE daraus, dass die Spalte Zahlen enthält, und alle nichtnumerischen Werte werden als Null zurückgegeben.

Inkonsistente Werteformatierung

In einigen Fällen liefert ACE bei verschiedenen Aktualisierungen völlig unterschiedliche Ergebnisse. Anhand des Beispiels, das im Abschnitt über die Formatierung von unter beschrieben wird, könnten Sie plötzlich den Wert 1024.231 anstelle von „1.024.23“ sehen. Dieser Unterschied kann dadurch verursacht werden, dass die alte Arbeitsmappe in Excel geöffnet ist, während sie in Power Query importiert wird. Um dieses Problem zu beheben, schließen Sie die Arbeitsmappe.

Fehlende oder unvollständige Excel-Daten

Manchmal gelingt es Power Query nicht, alle Daten aus einem Excel-Arbeitsblatt zu extrahieren. Dieser Fehler wird häufig dadurch verursacht, dass das Arbeitsblatt falsche Abmessungen hat (z. B. die Abmessungen A1:C200, wenn die tatsächlichen Daten mehr als drei Spalten oder 200 Zeilen belegen).

Wie man falsche Abmessungen diagnostiziert

So zeigen Sie die Abmessungen eines Arbeitsblatts an:

Benennen Sie die xlsx-Datei mit einer .zip-Erweiterung um.
Öffnen Sie die Datei im Datei-Explorer.
Navigieren Sie zu xl\worksheets.
Kopieren Sie die xml-Datei für das problematische Blatt (z. B. Blatt1.xml) aus der Zip-Datei an einen anderen Ort.
Überprüfen Sie die ersten Zeilen der Datei. Wenn die Datei klein genug ist, öffnen Sie sie in einem Texteditor. Wenn die Datei zu groß ist, um in einem Texteditor geöffnet zu werden, führen Sie den folgenden Befehl in einer Eingabeaufforderung aus: more Sheet1.xml.
Suchen Sie nach einem <dimension .../> Tag (z.B. <dimension ref="A1:C200" />).

Wenn Ihre Datei ein Dimensionsattribut hat, das auf eine einzelne Zelle verweist (z. B. <dimension ref="A1" />), verwendet Power Query dieses Attribut, um die Anfangszeile und -spalte der Daten auf dem Blatt zu finden.

Wenn Ihre Datei jedoch ein Dimensionsattribut hat, das auf mehrere Zellen verweist (z. B. <dimension ref="A1:AJ45000"/>), verwendet Power Query diesen Bereich, um die Startzeile und -spalte sowie die Endzeile und -spalte zu finden. Wenn dieser Bereich nicht alle Daten auf dem Blatt enthält, werden einige der Daten nicht geladen.

Wie man falsche Abmessungen korrigiert

Sie können Probleme, die durch falsche Abmessungen verursacht werden, mit einer der folgenden Aktionen beheben:

Öffnen Sie das Dokument in Excel und speichern Sie es erneut. Durch diese Aktion werden die in der Datei gespeicherten falschen Abmessungen mit dem richtigen Wert überschrieben.
Vergewissern Sie sich, dass das Tool, mit dem die Excel-Datei erstellt wurde, so eingestellt ist, dass die Abmessungen korrekt ausgegeben werden.
Aktualisieren Sie Ihre M-Abfrage, um die falschen Abmessungen zu ignorieren. Ab der Version von Power Query vom Dezember 2020 unterstützt Excel.Workbook nun eine Option InferSheetDimensions. Wenn diese Option auf „true“ gesetzt wird, ignoriert die Funktion die in der Arbeitsmappe gespeicherten Dimensionen und bestimmt sie stattdessen durch die Inspektion der Daten.

Hier ein Beispiel, wie Sie diese Option anbieten können:

Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Das langsame Laden von Excel-Daten kann auch durch falsche Dimensionen verursacht werden. In diesem Fall wird die Langsamkeit jedoch dadurch verursacht, dass die Dimensionen viel größer sind, als sie sein müssten, und nicht, weil sie zu klein sind. Zu große Dimensionen führen dazu, dass Power Query eine viel größere Menge an Daten aus der Arbeitsmappe liest, als tatsächlich benötigt wird.

Ausführliche Anweisungen zur Behebung dieses Problems finden Sie unter Suchen und Zurücksetzen der letzten Zelle in einem Arbeitsblatt .

Wenn Sie Daten aus Excel auf Ihrem Rechner oder aus SharePoint abrufen, müssen Sie sowohl das Volumen der betroffenen Daten als auch die Komplexität der Arbeitsmappe berücksichtigen.

Beim Abrufen sehr großer Dateien aus SharePoint werden Sie Leistungseinbußen feststellen. Dies ist jedoch nur ein Teil des Problems. Wenn Sie in einer Excel-Datei, die von SharePoint abgerufen wird, eine umfangreiche Geschäftslogik haben, muss diese Geschäftslogik möglicherweise ausgeführt werden, wenn Sie Ihre Daten aktualisieren, was zu komplizierten Berechnungen führen kann. Ziehen Sie in Erwägung, Daten zu aggregieren und vorzuberechnen oder einen größeren Teil der Geschäftslogik aus der Excel-Ebene in die Power Query-Ebene zu verlagern.

Fehler bei der Verwendung des Excel-Connectors zum Importieren von CSV-Dateien

Auch wenn CSV-Dateien in Excel geöffnet werden können, sind sie keine Excel-Dateien. Verwenden Sie stattdessen den Text/CSV-Connector .

Fehler beim Importieren von „Strict Open XML Spreadsheet“ Arbeitsmappen

Beim Importieren von Arbeitsmappen, die im Excel-Format „Strict Open XML Spreadsheet“ gespeichert sind, kann der folgende Fehler auftreten:

DataFormat.Error: The specified package is invalid. The main part is missing.

Dieser Fehler tritt auf, wenn der ACE-Treiber nicht auf dem Host-Computer installiert ist. Arbeitsmappen, die im Format „Strict Open XML Spreadsheet“ gespeichert sind, können nur von ACE gelesen werden. Da solche Arbeitsmappen jedoch dieselbe Dateierweiterung wie normale Open XML-Arbeitsmappen (.xlsx) verwenden, können wir die Erweiterung nicht verwenden, um die übliche Fehlermeldung the Access Database Engine OLEDB provider may be required to read this type of file anzuzeigen.

Um den Fehler zu beheben, installieren Sie den ACE-Treiber. Wenn der Fehler bei einem Cloud-Dienst auftritt, müssen Sie ein Gateway verwenden, das auf einem Computer läuft, auf dem der ACE-Treiber installiert ist.

„Datei enthält beschädigte Daten“ Fehler

Beim Importieren bestimmter Excel-Arbeitsmappen wird möglicherweise der folgende Fehler angezeigt.

DataFormat.Error: File contains corrupted data.

Normalerweise weist dieser Fehler auf ein Problem mit dem Format der Datei hin.

Manchmal kann dieser Fehler jedoch auftreten, wenn eine Datei als Open XML-Datei (z. B. .xlsx) erscheint, aber der ACE-Treiber tatsächlich benötigt wird, um die Datei zu verarbeiten. Weitere Informationen zur Verarbeitung von Dateien, für die der ACE-Treiber erforderlich ist, finden Sie im Abschnitt Legacy ACE connector.

Bekannte Probleme und Einschränkungen

Power Query Online kann nicht auf verschlüsselte Excel-Dateien zugreifen. Da mit anderen Vertraulichkeitstypen als „Öffentlich“ oder „Nicht geschäftlich“ gekennzeichnete Excel-Dateien verschlüsselt sind, sind sie über Power Query Online nicht zugänglich.
Power Query Online unterstützt keine kennwortgeschützten Excel-Dateien.