Wer mit Excel oder Power BI Desktop Daten via Power Query aus anderen Systemen importiert, stößt schnell auf ein Problem: Im Zeitverlauf ändern sich die Spaltenbeschriftungen der Quelldaten hin und wieder. Dies kann zu Problemen führen, da Power Query Spalten über deren Namen referenziert. In diesem Beitrag zeige ich Dir, wie Du Power Query bei Daten mit sich ändernden Spaltenüberschriften korrekt nutzt.
Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!
Die Quelldaten
- Spalte 1: Abteilungsbezeichnung
- Spalte 2: Anzahl Mitarbeiter
Datenverarbeitung mit Power Query
In der Excel-Version 2016 wird eine Tabelle innerhalb von Excel wie folgt in Power Query geladen:
- Die aktive Zelle muss sich in der »intelligenten« Tabelle befinden
- Über den Reiter Daten (2) auf die Schaltfläche Aus Tabelle/ Bereich (3) klicken
In Power Query sieht die importierte Tabelle dann wie folgt aus:
Beim Import der Tabellen fügt Power Query automatisch den Schritt geänderter Typ hinzu. Wichtig hierbei ist, dass der Spaltenname 07.07.2017 fest im M-Code hinterlegt wird. Der M-Code verweist also nicht auf die zweite Spalte in der Tabelle, sondern direkt auf die Spalte 07.07.2017, die bei der nächsten Datenlieferung nicht mehr existieren wird.
Exkurs: Wer die automatische Typenerkennung lieber ausschalten möchte, tut dies in Excel 2016 wie folgt:
Nachdem die Tabelle nun in Power Query vorhanden ist, versehe ich die Überschrift mit etwas mehr Bedeutung: Aus 07.07.2017 wird Anzahl Mitarbeiter.
Dazu klicke ich doppelt auf die Überschrift der zweiten Spalte und benennen diese um. Das Ergebnis sieht wie folgt aus:
Bis hierhin reicht mir die Datenverarbeitung erst einmal und ich lade das Ergebnis der Power Query-Abfrage nun in eine Exceltabelle:
Nachdem die Daten in ein neues Excel-Tabellenblatt importiert wurden, sieht die entstandene Tabelle wie folgt aus.
Die schlichte Umbenennung der zweiten Spalte in Anzahl Mitarbeiter dient hierbei natürlich nur als sehr simples Beispiel für alle denkbaren Transformationen, die mit Power Query möglich sind. Was passiert jetzt bei der nächsten Datenzulieferung mit veränderter Spaltenüberschrift?
Neue Daten, neue Spaltenüberschrift
Eine Woche vergeht und ich erhalte neue Daten in meiner Exceltabelle. Da die zweite Spalte das Tagesdatum der Datenzulieferung enthält, hat sich also die zweite Spaltenüberschrift meiner Importtabelle geändert.
Versuche ich diesen Import zu aktualisieren, folgt umgehend folgende Fehlermeldung:
Power Query kann die gesuchte Spalte 07.07.2017 nicht finden. Ich gehe zurück in Power Query, um zu sehen, was hier passiert ist.
Erinnerst Du Dich daran, dass Power Query automatisch die Datentypenerkennung übernimmt und dabei (und auch bei vielen anderen Schritten) fest auf Spaltennamen verweist?! Genau das ist die Ursache des Problems. Power Query sucht weiterhin nach der Spalte mit der Bezeichnung 07.07.2017, obwohl diese bereits 14.07.2017 heißt.
Im Schritt Umbenannte Spalten stoße ich auf dasselbe Problem, weil auch hier eine fest hinterlegte Spaltenreferenz besteht.
Lösung über die grafischen Nutzeroberfläche (GUI)
Lösungen für das Problem veränderter Spaltenüberschriften gibt es viele. Für das aktuelle Problem zeige ich Euch die meiner Meinung nach einfachste Lösung, die ohne jegliche Kenntnisse von M erstellt werden kann.
1. Ich lade die Tabelle erneut in Power Query
2. Anschließend entferne ich den Schritt mit der automatische Erkennung der Datentypen
Dieser Schritt beinhaltet den ersten fixen Spaltenbezug (07.07.2017) und verursacht daher bei der nächsten Datenzulieferung Probleme. Daher entferne ich diesen Schritt zunächst und füge ihn später wieder hinzu.
3. Verwendung der Überschriften als erste Zeile
Ich kann die Überschriften zurücksetzen und als erste Zeile benutzen. Da dies der zweite Schritt in der Power Query-Abfrage ist (gleich nach der Datenquelle), stelle ich hierüber sicher, dass die beiden Spalten zu Beginn dieser Abfrage immer Column1 und Column2 heißen. Dies macht die Abfrage robust.
4. Erzeugung einer robusten, d. h. sich nicht mehr ändernden Überschrift
Nun vergebe ich sprechende Spaltennamen, die sich danach nicht mehr ändern werden.
5. Löschen der ersten Zeile (in der die ursprüngliche Überschrift steht)
Die erste Zeile, in denen sich die alte Spaltenüberschrift befindet, kann ich nun entfernen.
6. Anpassen der Datentypen
Abschließend passe ich die Datentypen wieder an.
Schlussfolgerung
Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne
Viele Grüße aus Hamburg,
Lars
Neueste Kommentare