Office-Blog

Beim Berechnen in Excel sind Formelfehler wie #DIV/0 nicht auszuschließen. Allein schon beim Eingeben können durch Tippfehler Zahlen zu Texten und damit unbrauchbar für die Datenanalyse werden. Welche Folgen hat das dann beim Import der Excel-Daten in Power Query? Wie lässt sich sicherstellen, dass dort nur valide Daten ausgewertet werden?
Eine spezielle Abfrage zur Fehlererkennung und -typisierung kann solche Fehlerwerte aufspüren und Hinweise zu deren Ursache geben.

Eine zusätzliche Abfrage für Kontrollzwecke zeigt mögliche Fehlerquellen und deren Ursache

Das sind die Schritte zum Anlegen der Kontrollabfrage.

So wirken sich Formelfehler in Power Query aus

Ich lese die oben links gezeigte Excel-Tabelle [1] in Power Query ein und entpivotiere sie dort.

• Das erledige ich über die Befehlsfolge Daten > Daten abrufen > Aus anderen Quellen > Aus Tabelle/Bereich.
• Im Power Query-Editor markiere ich die Spalte Region und wähle per Rechtsklick im Spaltenkopf Andere Spalten entpivotieren.
• Die resultierende Spalte Attribut benenne ich um in Monat.

So sieht das Ergebnis aus:

• In der Vorschau von Power Query werden die Zellen mit Formelfehlern als Error gekennzeichnet.
  

  Formelfehler aus Excel erscheinen in der Power Query-Vorschau als Error

• Lade ich die Daten später ins Tabellenblatt, verschwindet die Error-Anzeige und die Zellen bleiben leer [2].
• Ebenso verhält es sich beim Erstellen einer Pivot-Tabelle. Die fehlerbehafteten Zellen werden in der Auswertung einfach ignoriert.

So entsteht eine Kontrollabfrage für Fehlerwerte

Zum Anlegen einer Abfrage für Fehlerwerte erstelle ich einen Verweis per Rechtsklick auf die vorhandene Abfrage und füge weitere Schritte hinzu.

• Ich markiere die Spalte Wert und wähle – wie unten gezeigt – die Befehlsfolge Start > Zeilen beibehalten [2] > Fehler beibehalten [3].
  

  Zeilen mit Fehlern sollen beibehalten werden

• Zur genaueren Fehlertypermittlung erstelle ich eine benutzerdefinierte Spalte. Ich wähle Spalte hinzufügen > Benutzerdefinierte Spalte, gebe bei Formel =try ein und doppelklicke auf die Spalte Wert.
  

  Mit Hilfe von try die Fehlerinformationen aus der Spalte Wert abrufen

• Try erzeugt eine neue Spalte Benutzerdefiniert mit Record-Einträgen, die ich per Klick auf den Doppelpfeil [1] und Setzen des Häkchens bei Error [2] auspacke.
  

  Die Spalte Benutzerdefiniert entpacken, um an die Fehlerinformationen zu kommen

• Dadurch wird die Spalte Benutzerdefiniert zu Error umbenannt und besitzt weitere Record-Einträge. Diese Spalte entpacke ich wieder per Klick auf den Doppelpfeil [1] und wähle aus der Liste der Fehlerinformationen Message [2] aus.
  

  Die Spalte Error entpacken und die Fehlernachricht abrufen

• Schließlich entferne ich noch die Spalte Wert per Rechtsklick im Spaltenkopf.

So sieht nun das Ergebnis meiner Fehlerabfrage aus:

Das Ergebnis der Fehlerabfrage mit Fehlerlokalisierung und Hinweis auf die Ursache

Sind Fehler vorhanden, kann ich sie mit einem Blick erkennen und anhand der Fehlermeldung in der Originaldatei beheben. Die anschließende Datenanalyse kann ich nun mit gutem Gefühl durchführen :-).

HINWEIS
In der Abfrageliste werden Fehler – falls vorhanden – immer rechts neben der Angabe der geladenen Zeilen angezeigt. Hier kann im Fehlerfall per Hyperlink eine Abfrage generiert werden. Allerdings weist diese Abfrage nur die fehlerhaften Zeilen aus, nicht aber die Art des Fehlers.

Wer Interesse an weiteren Abfrage-Techniken hat, …

… ist genau richtig in meinem Online-Kurs Power Query für Fortgeschrittene: Mit eigenen Abfrage-Tools die Datenaufbereitung optimieren am 25. Oktober.

Hier geht’s zu den Details und zur Anmeldung.

Flyer als PDF herunterladen

TIPP: Wer hier im Blog ALLE Rezepte zu Power Query finden möchte, gibt einfach oben rechts in das Suchfeld Power-Query-Rezept ein.