Excel

Zusammenfassung

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 nicht installiert ist, wird beim Herstellen einer Verbindung mit älteren Arbeitsmappen der folgende Fehler angezeigt:

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 Cloudhost (z. B. Power Query Online) angezeigt wird, müssen Sie ein Gateway verwenden, das ACE installiert hat, um eine Verbindung mit den älteren 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:

1. 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.

2. 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.

3. 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:

1. 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.

   

2. Geben Sie im daraufhin angezeigten Excel-Dialogfeld den Pfad zur Excel-Arbeitsmappe an.

   

3. Wählen Sie bei Bedarf ein lokales Daten-Gateway für den Zugriff auf die Excel-Arbeitsmappe.

4. Wenn Sie zum ersten Mal auf diese Excel-Arbeitsmappe zugreifen, wählen Sie die Authentifizierungsart aus, und melden Sie sich bei Ihrem Konto an (falls erforderlich).

5. 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 mit einer Excel-Arbeitsmappe herstellen, die nicht speziell eine einzelne Tabelle enthält, versucht der Power Query-Navigator, eine vorgeschlagene Liste von Tabellen zu erstellen, aus denen Sie auswä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.

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.

Problembehandlung

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

Beim Importieren von Excel-Daten stellen Sie möglicherweise fest, dass sich bestimmte Zahlenwerte beim Importieren in Power Query leicht ä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 den tatsächlich gespeicherten Wert betrachten, beachten Sie, dass 0,049 in der .xlsx Datei tatsächlich als 0.0490000000002 gespeichert ist . Diese Zahl ist der Wert, den Power Query aus dem .xlsxliest und somit den Wert, der angezeigt wird, 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:

1. Öffnen Sie das Dokument in Excel Desktop.
2. Öffnen Sie das Menü Datei, wählen Sie die Registerkarte Info und dann Pfad kopieren.
3. Kopieren Sie die Adresse in das Feld "Dateipfad" oder "URL ", und entfernen Sie "?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). Aufgrund dieser Abhängigkeit kann es zu unerwarteten Verhaltensweisen kommen, wenn Ältere Arbeitsmappen importiert werden, die beim Importieren 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. Angenommen, Ihre Excel-Datei enthält die Zahl 1024.231, die Sie 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. Dieses Verhalten tritt auf, da ACE in diesem Fall nur den Wert sichtbar macht, wie er von Excel in Power Query erscheint, und nicht die zugrunde liegende Zahl.

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 Nullen für jeden Wert zurück, der nicht mit dem Typ übereinstimmt. 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:

1. Benennen Sie die xlsx-Datei mit einer .zip-Erweiterung um.
2. Öffnen Sie die Datei im Datei-Explorer.
3. Navigieren Sie zu xl\worksheets.
4. Kopieren Sie die xml-Datei für das problematische Blatt (z. B. Blatt1.xml) aus der Zip-Datei an einen anderen Ort.
5. Ü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.
6. 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. Diese Aktion überschreibt die falschen Dimensionen, die in der Datei gespeichert sind, mit dem richtigen Wert.

• 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 true, bewirkt diese Option, dass die Funktion die in der Arbeitsmappe gespeicherten Dimensionen ignoriert und stattdessen durch Prüfen der Daten ermittelt.

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

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

Schwerfällige oder langsame Leistung beim Laden von Excel-Daten

Falsche Dimensionen können auch zu einem langsamen Laden von Excel-Daten führen. In diesem Fall führen die viel größeren Dimensionen zur Langsamkeit, anstatt dass sie zu klein sind. Übermäßig große Dimensionen führen dazu, dass Power Query eine viel größere Datenmenge aus der Arbeitsmappe liest, als erforderlich ist.

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

Schlechte Leistung beim Laden von Daten aus SharePoint

Berücksichtigen Sie beim Abrufen von Daten von Excel auf Ihrem Computer oder aus SharePoint sowohl das Volumen der beteiligten Daten als auch die Komplexität der Arbeitsmappe.

Möglicherweise bemerken Sie beim Abrufen sehr großer Dateien aus SharePoint Leistungseinbußen. Die Dateigröße ist jedoch nur ein Teil des Problems. Wenn Sie erhebliche Geschäftslogik in einer Excel-Datei haben, die aus SharePoint abgerufen wird, muss diese Geschäftslogik möglicherweise ausgeführt werden, wenn Sie Ihre Daten aktualisieren, was zu komplizierten Berechnungen führen kann. Erwägen Sie, Daten zu aggregieren und vorzurechnen oder mehr der Geschäftslogik aus der Excel-Ebene und in die Power Query-Ebene zu verschieben.

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. Nur ACE kann Arbeitsmappen lesen, die im Format "Strict Open XML Spreadsheet" gespeichert sind. 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 in einem Clouddienst auftritt, müssen Sie ein Gateway verwenden, das auf einem Computer ausgeführt wird, 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.

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

Manchmal kann dieser Fehler jedoch auftreten, wenn es sich bei einer Datei um eine Open XML-Datei (z. B. .xlsx) handelt, der ACE-Treiber ist jedoch erforderlich, 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.
• Die Excel.Workbook-OptionuseHeaders konvertiert Zahlen und Datumsangaben mithilfe der aktuellen Kultur in Text und verhält sich daher anders, wenn sie in Umgebungen mit unterschiedlichen Betriebssystemkulturen ausgeführt werden. Wir empfehlen stattdessen die Verwendung von Table.PromoteHeaders .