Meistens können Sie Daten aus Excel-Arbeitsmappen problemlos in Power Query importieren. Fehlerwerte in der Excel-Tabelle verursachen Probleme. Fehlerhafte Zellen können jetzt oder später durch Aktualisierungen der Daten auftreten. Identifizieren Sie diese Zellen vorausschauend. In diesem Tipp erfahren Sie, wie Sie Zellen mit Fehlerhinweisen über eine separate Abfrage in Power Query ermitteln. Diese Abfrage hilft Ihnen, die Fehler später in der Excel-Arbeitsmappe zu korrigieren.

Ausgangsbeispiel

Als Ausgangsbeispiel dient eine Tabelle mit Daten zu Kundenbestellungen. Die dynamische Tabelle hat den Namen Bestellungen. In der Tabelle sind einige Fehler eingebaut, wie Sie in Excel-Arbeitsmappen vorkommen können. Die Tabelle sieht so aus:

Die Daten aus der Tabelle sollen in Power Query importiert werden. In der ersten Abfrage sollen nur die Kunden übernommen werden, bei denen keine Fehlerhinweise existieren. In der zweiten Abfrage sollen fehlerhafte Datensätze angezeigt werden. Neben der Datensatznummer sollen in der Abfrage die Fehlerart und die Fehlermeldung erscheinen. Diese Vorgehensweise ist sinnvoll, wenn Sie fehlerhafte Datensätze zunächst korrigieren möchten und Sie nicht grundsätzlich von der späteren Datenanalyse ausschließen wollen.

Import der Daten in Power Query

Um die Daten aus der Excel-Arbeitsmappe in Power Query zu importieren, können Sie folgendermaßen vorgehen:

1. Erstellen Sie eine neue Arbeitsmappe.
2. Klicken Sie auf der Registerkarte Daten in der Gruppe Daten abrufen und transformieren auf die Schaltfläche Daten abrufen.
3. Klicken Sie auf Aus Datei und dann auf Excel-Arbeitsmappe.
4. Wählen Sie die Excel-Arbeitsmappe mit den zu importierenden Bestelldaten aus. Das Dialogbild Navigator erscheint:

5. Wählen Sie Tabelle Bestellungen aus und klicken Sie danach auf Daten transformieren. Der Editor von Power Query erscheint.
6. Wählen Sie für die einzelnen Spalten die passenden Datentypen aus. Die Abfrage im Editor von Power Query Abfrage sieht danach so aus:

In Power Query erkennen Sie die Zellen mit den Fehlern an den grünen Hinweistext Error. Um den genauen Fehler zu identifizieren, klicken Sie in die Zelle mit dem Fehler rechts neben dem Hinweistext. Am unteren Bildschirmrand wird der genaue Fehlerhinweis eingeblendet. Für den Fehler in Zeile 6 wird folgendes angezeigt:

Neue Abfrage mit den Datensätzen ohne Fehler

Aus der Abfrage Bestellungen sollen zunächst nur die Datensätze übernommen werden, in denen keine Fehler auftreten. Gehen Sie folgendermaßen vor:

1. Markieren Sie in der Navigation die Abfrage Bestellungen.
2. Rufen Sie mit der rechten Maustaste das Kontextmenü auf und klicken Sie auf den Menüpunkt Verweis. Es wird eine neue Abfrage erstellt mit dem Namen Bestellungen (2).
3. Ändern Sie den Namen der Abfrage auf Bestellungen_OK.
4. Klicken Sie in auf den nach unten zeigendem Pfeil in der linken oberen Zelle der Tabelle.
5. Wählen Sie den Menüpunkt Fehler entfernen. Danach werden alle Zeilen mit Fehlern aus der Tabelle gelöscht.

Die Abfrage Bestellungen_OK sieht danach so aus:

Neue Abfrage mit den fehlerhaften Datensätzen

Im nächsten Schritt wird eine zweite Abfrage auf Basis der Abfrage Bestellungen erstellt. In dieser Abfrage erscheinen nur die fehlerhaften Datensätze. Folgende Schritte sind notwendig:

1. Markieren Sie in der Navigation die Abfrage Bestellungen.
2. Rufen Sie nochmals mit der rechten Maustaste das Kontextmenü auf und klicken Sie auf den Menüpunkt Verweis. Es wird wieder eine neue Abfrage erstellt mit dem Namen Bestellungen (2).
3. Ändern Sie den Namen der Abfrage dieses Mal auf Bestellungen_Fehler.
4. Fügen Sie der Abfrage eine neue Indexspalte hinzu. Diese Spalte dient der Identifizierung der Datensatznummern. Klicken Sie im Menüband auf die Registerkarte Spalte hinzufügen auf den nach unten zeigendem Pfeil rechts neben der Schaltfläche Indexspalte. Wählen Sie den Menüpunkt Von 1 aus.
5. Verschieben Sie die neue Spalte Index links vor die Spalte Kunde.
6. Markieren Sie die Spalte Index und rufen Sie mit der rechten Maustaste das Kontextmenü auf. Klicken Sie auf den Menüpunkt Andere Spalten entpivotieren. Die Abfrage sieht danach so aus:

7. Markieren Sie die Spalte Wert.
8. Klicken Sie im Menüband auf der Registerkarte Start auf die Schaltfläche Zeilen beibehalten.
9. Klicken Sie auf den Untermenüpunkt Fehler beibehalten. Danach sieht die Abfrage so aus:

10. Fügen Sie eine neue benutzerdefinierte Spalte hinzu, in dem Sie im Menüband auf der Registerkarte Spalte hinzufügen auf die Schaltfläche Benutzerdefinierte Spalte klicken. Das Dialogbild Benutzerdefinierte Spalte erscheint.
11. Geben Sie im Eingabefeld Neuer Spaltenname den Ausdruck Fehlerhinweis und im Eingabefeld Benutzerdefinierte Spaltenformel den Ausdruck try [Wert] ein. Mit der Formel wird eine neue Spalte mit Fehlerwerten provoziert, um genauere Fehlerhinweise zu erhalten. Die Abfrage sieht danach so aus:

12. Klicken Sie im Spaltenkopf der Spalte Fehlerhinweis auf die Schaltfläche mit den beiden Pfeilen. Deaktivieren Sie im erscheinenden Dialogbild die Option Ursprünglichen Spaltennamen als Präfix verwenden und klicken Sie auf OK.
13. Klicken Sie im Spaltenkopf der neu hinzugefügten Spalte Error auf die Schaltfläche mit den beiden Pfeilen. Deaktivieren Sie im erscheinenden Dialogbild alle Spalten bis auf Reason und Message und zusätzlich die Option Ursprünglichen Spaltennamen als Präfix verwenden. KlickenSie auf OK. Die Abfrage sieht danach so aus:

14. Löschen Sie die Spalten Wert und HasError.
15. Benennen Sie die Spalten folgendermaßen um: Index zu Datensatz-Nr., Attribut zu Spaltenname, Reason zu Grund und Message zu Meldung.

Die Navigationsleiste sowie die Abfrage Bestellungen_Fehler sehen am Ende so aus:

In der Tabelle finden Sie die Nummern sowie nähere Hinweise zu den fehlerhaften Datensätzen.

Da Sie in der Regel die Abfrage Bestellungen, die sowohl die fehlerfreien als auch die fehlerhaften Sätze enthält, nicht in ein Tabellenblatt oder in ein Datenmodell übernehmen möchten, können Sie es von der Übernahme ausschließen, in dem Sie nach dem Import die Eigenschaften der Abfrage aufrufen und unter dem Menüpunkt Laden in die Option Nur Verbindung erstellen wählen.

Fazit

In diesem Tipp haben Sie eine Möglichkeit kennengelernt, wie Sie in Power Query beim Import von Daten aus einer Excel-Arbeitsmappe Zellen mit Fehlerhinweisen in eine separate Abfrage zusammenfassen können.