Wenn man das Müsli mit Eierlikör anrührt, sieht der Tag gleich viel freundlicher aus.
„Guten Morgen,
ich versuche gerade eine PowerQuery-Auswertung aus den Interviewfragebogen zu erstellen.
Ich erhalte allerdings die Fehlermeldung „Die Konvertierung in Number war nicht möglich.
Was mache ich da falsch?“
Was mache ich mit so einer Mail? Richtig: ich schlage vor, mir das Ganze über teams anzusehen. Und tatsächlich:
Okay. Langsam. Von vorne bitte. Können wir uns das Ganze mal bitte in Ruhe ansehen? Was machen Sie?
In einem Ordner befinden sich mehr als 50 Excelmappen:
Jede dieser Mappen hat folgenden Aufbau:
In Spalte A befindet sich in jedem Formular eine Nummer der Form 0., 1., 2., …
Aus einigen dieser Gruppen sollen Informationen ausgelesen werden. Diese Informationen befinden sich in Spalten rechts daneben. Soweit so gut – PowerQuery ist das richtige Werkzeug hierfür. Wir schauen uns das Ganze an – Schritt für Schritt:
- Schritt: Leere Arbeitsmappe. Daten / Daten abrufen und transformieren / Daten abrufen / Aus Datei / Aus Ordner
2. Schritt. Der Ordner wird ausgewählt; die Daten werden transformiert.
3. Schritt: Unterordner werden ausgeschlossen; andere Dateitypen ebenso:
4. Schritt: In der Spalte „Content“ befindet sich der Inhalt. Da die Spalten alle den gleichen Aufbau haben, kann man die anderen Spalten löschen und diese Spalte „entpacken“:
Da alle Dateien den gleichen Aufbau und das gleiche Tabellenblatt haben, stellt dies kein Problem dar:
Das Ergebnis:
Da Informationen aus bestimmten „Gruppen“ geholt werden, wird die erste Spalte über Transformieren / Ausfüllen „nach unten gezogen“:
Einige Spalten werden gelöscht. Aus der ersten Spalte werden einige der benötigten Spalten selektiert:
Das Ergebnis wird zurück nach Excel geschrieben (Start / Schließen & Laden / Schießen & Laden in). Obwohl die Daten in Powerquery korrekt angezeigt werden:
ist die Fehlermeldung die Folge:
Ich stutze. Zurück zu PowerQuery. Vielleicht ist „irgend etwas“ in der ersten Spalte?!? Es sieht nicht so aus:
Aber: „Die Liste kann unvollständig sein.“ Ich lasse mir über Ansicht die „Spaltenqualität“ anzeigen:
Kein Fehler in der ersten Spalte!?!
Wirklich nicht?
Wir wissen, dass PowerQuery zu Beginn nur 1.000 Zeilen auswertet. Bei 50 Formularen x zirka 150 Zeilen sind das 7.500 Zeilen. Okay – ich lasse ALLE Zeilen auswerten, indem ich auf der Statuszeile von 1.000 auf „alle“ wechsle:
Und tatsächlich: JETZT lautet die Beschriftung der Zeile „Spaltenqualität“
Unerwarteter Fehler.
Aha!
Ich gehe auf die Suche – Schritt für Schritt zurück. Schon bald ist klar, dass die Häufigkeit der Fehler unter 1% liegt:
Der Fehler tritt auf, als der Typ geändert wird. Moment – DAS habe ich doch gar nicht gemacht:
Richtig: in Datei / Optionen und Einstellungen / Abfrageoptionen lautet die Grundeinstellung:
Spaltentypen und -überschriften für unstrukturierte Quellen immer erkennen. Und richtig: Das produziert den Fehler:
[DataFormat.Error]
Aha – diese Einstellung bewirkt, dass aus 0., 1., 2., … die Zahlen 1, 2, 3, … werden. Das heißt: in einer der Dateien befindet sich wahrscheinlich in Spalte A eine andere Informationen.
Welche Datei? Zurück zum Anfang:
Ich entferne die erste und die zweite Spalte (den Dateinamen) nicht:
Bevor der Datentyp geändert wird, lasse ich mir alle Inhalte anzeigen:
Da ich die Dateinamen „sehe“, kann ich die Spalte in den Datentyp „Text“ konvertieren und den Übeltäter filtern:
Als Text erzeugt der Punkt kein Problem, allerdings bei der (automatischen) Umwandlung in Zahl.
Und dann funktioniert die Zusammenfassung problemlos:
Fazit: Vermeiden Sie – wenn möglich – die automatische Datenkonvertierung.
Verwenden Sie ALLE Daten bei der Fehlersuche.
Verwenden Sie die Werkzeuge der Registerkarte Ansicht, also: Spaltenqualität, Spaltenprofil und Spaltenverteilung.