Befinden sich im Importbereich einer Excel Fehlermeldungen, dann führt das zu Komplikationen beim Import mit Power Query. Die Fehlermeldungen können nicht einfach als Text übernommen werden, da Power Query die Fehlermeldungen als solche erkennt und zwingend als Fehler interpretiert. Daher müssen die Fehler behandelt werden, um einen sauberen Import aus der Excel Datenquelle aufsetzen zu können.

Ausgangssituation

Das folgende Excel Import File enthält zwei Datensätze mit Fehlermeldungen in der Value Spalte:

Die Herausforderung in der Praxis liegt meistens darin, daß die Fehlermeldungen in langen Datenlisten versteckt und mit freiem Auge gar nicht zu sehen sind.

Auftretende Probleme beim Import mit Power Query

Wird dieses Excel Import File mit Power Query bspw. in Power BI importiert, dann kommt es jedenfalls zu Komplikationen. Diese Komplikationen sind unterschiedlich, je nachdem Transformationen auf die Spalte mit den Fehlern ausgeführt werden oder nicht.

Fall 1: keine Transformationen auf die Spalte mit den Fehlern

Die beiden Fehlermeldungen aus dem Excel Import File werden in Power Query von vornherein als Error erkannt und angezeigt, selbst wenn der Datentyp noch unbestimmt ("Any") ist:

Eine Datentypänderung auf "Text" ändert daran auch nichts, es ist daher nicht möglich, in Power Query die Excel Fehlermeldungen einfach als Text zu übernehmen und darauf die weitere Aufbereitung aufzubauen:

Der Import kann in diesem Fall aber ausgeführt werden, es gibt 2 auftretende Fehler ...

... die in der Zieltabelle im Datenmodell auch gut gehandhabt wurden, die Zellen mit den Fehlern wurden in BLANK() umgewandelt:

Auch wenn das Ergebnis in diesem Fall in Ordnung ist, ist ein Datenimport mit auftretenden Fehlern in der Praxis ein Problem, da jedes Mal überprüft werden müsste, ob die Fehler tatsächlich nur technische Fehler ohne inhaltliche Konsequenzen sind.

Fall 2: Transformationen auf die Spalte mit den Fehlern

Wir starten wieder mit der gleichen Ausgangssituation, die Fehler aus Excel werden in Power Query von vornherein als Error interpretiert (Datentyp "Any"):

Wird jetzt bspw. ein 0-Zeilen-Filter auf die Value Spalte angelegt, dann hat dies schlimme Konsequenzen. Alle Datensätze nach dem ersten Error werden weggefiltert und der gesamte Datensatz wird zum Error:

Jetzt kann auch der Import nicht mehr ausgeführt werden, dieser bricht mit folgender Fehlermeldung ab:

Lösung

Die Lösung ist glücklicherweise sehr einfach. Auf die Wertspalte mit den Errors wird zum frühestmöglichen Zeitpunkt die Funktion Replace Errors angewendet:

Die Fehlermeldungen aus Excel können jetzt beispielsweise mit "0" oder "null" ersetzt werden ...

... und damit sind sämtliche Fehler im Datenbestand beseitigt:

Jetzt können - wie gewohnt - alle Transformationen risikolos angewendet werden, beispielsweise ein 0-Zeilen-Filter:

Der Import läuft jetzt natürlich fehlerfrei durch und die Tabelle im Datenmodell enthält sämtliche relevanten Datensätze aus dem Excel Import File:

Best Practice für den Import aus Excel

In der Praxis liegt die Problematik idR darin, daß die fehlerhaften Datensätze häufig gar nicht im Vorschaubereich (erste 1000 Zeilen) zu sehen sind und die Komplikationen erst bei der Ausführung des Imports auftreten. Die Beschädigung des Datenbestands durch angewendete Transformationen auf die Spalte mit den Fehlern wird gar nicht gleich bemerkt, weil die Beschädigung erst unterhalb der Vorschauzeilen eintritt.

Daher kann beim Excel Import als Best Practice der Befehl Replace Errors vorsorglich auf alle Wertspalten angewendet werden, bevor die Datentypen und weitere Transformationen ausgeführt werden.