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
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
Olga Merdinger meint
Kann man in die Spaltenbezeichnung auch ein Parameter einbauen? Ich brauche Spalten, die das Datum beinhalten, diese Datums werden vom User on einem Tabellenbereich im Excel eingegeben. Wie z.B. von: 01.01.20 steht in der Zeile A5 ,Bis : 31.12.20 steht in der Zeile A 10 und dann brauche ich eine Spalte Namens Kosten zum [A5]-1, d.h. Kosten zum Datum [Von]-1Tag .
Geht das?
Danke. Olga
Mandy Arent meint
Guten Tag Lars,
ich bin mir nicht sicher, ob ich bei folgender Fehlermeldung ähnlich vorgehen sollte, wie du es für umbenannte Spalten vorschlägst oder funktioniert das hier nicht?
Nach einer Spaltentrennung mit Zeichenwechsel (Buchstabe von Ziffern) erhalte ich die Fehlermeldung „Splitter.SplitTextByCharacterTransition“ (= Table.SplitColumn(#“Ersetzter Wert2″, „Schicht“, Splitter.SplitTextByCharacterTransition((c) => not List.Contains({„0“..“9″}, c), {„0“..“9″}), {„Schicht.1“, „Schicht.2“}) nach dem Aktualisieren.
Gruß Marent
Florian meint
Hi Lars,
Danke erstmal für den informativen Artikel!
gibt es eine Möglichkeit in den Exceleinstellungen grundsätzlich das „geänderter Typ“ zu deaktivieren ohne den Extra Schritt über PowerQuery?
Ich habe das Problem das Excel aus CSV Dateien die Prozentwerte nicht korrekt übernimmt. Interessanterweise werden die Werte die als Prozent erkannt werden falsch dargestellt und die Prozentwerte die Excel als „Standard“ lässt sind korrekt dargestellt…
Danke, sehr spannend Blog! LG Florian
Lars Schreiber meint
Hi Florian,
Du kannst in Excel in Power Query in den „Abfrageoptionen“ unter „Daten laden“ sowohl für die „Aktuelle Arbeitsmappe“ als auch „Global“ (also für alle zukünftigen Arbeitsmappen“) die Erkennung von Spaltentypen und -überschriften untersagen.
Viele Grüße,
Lars
Ralf Hennings meint
Hallo Lars,
ich habe ein ähnliches Problem, aber mit dem Dateiname, hoffe du kannst hier weiterhelfen.
Ich lade Excel-Daten direkt aus einem Ordner. Da sich die Namen der Ordner in der Exceldatei ändern, liegt der Bezug auf den Dateien.
Mein Problem:
Die Datei hat z.B. den Name: ACD_Report_12022021.xls, beim Monatswechsel soll er nun auch eine Datei mit dem Namen ACD_Report_01032021.xls einlesen.
Ich erhalte aber nun den Fehler:
Expression.Error: Die Spalte „ACD_Report_12022021.xlsx“ der Tabelle wurde nicht gefunden.
Details: ACD_Report_12022021.xlsx
Benenne ich die Datei in z.B. ACD_Report_29022021.xls um, lest er die Datei ein.
Kannst du mir ein Tipp geben, an was das liegt. Habe leider bisher keine
Lösung im Netz dafür gefunden.
Vielen Dank
Ralf
Udo meint
Danke für diese Anleitung.
Das hilft weiter, wenn sich die Spaltennamen ändern.
Risiko bei dieser Variante ist nun, wenn sich auf dem Quellblatt die Spaltenanordnung ändert. Wenn also Spalte 5 und 6 miteinander getauscht werden, passen die Ergebnisse nicht mehr zueinander.
Lars Schreiber meint
Hallo Udo,
danke für Dein Feedback. Wie der Name des Artikels schon sagt, handelt es sich bei dieser Lösung um die Problemstellung sich ändernder Spaltenüberschriften bei konstanter Tabellenstruktur. Wenn sich der Inhalt der Spalten ändert, oder sich der Aufbau der Tabelle wandelt, handelt es sich um ein völlig anderes Szenario und dieses Bedarf natürlich auch einer vollkommen anderen Lösung. Insofern sehe ich dies weniger als ‚Risiko‘ der aktuellen Lösung, sondern eher als eine andere/ zusätzliche Anforderung.
Danke und viele Grüße,
Lars
Hewal meint
Danke für die Anleitung. Ich war schon am verzweifeln und entsprechend froh, dass es dafür eine Lösung gibt.
Was jedoch komisch ist, wenn ich über das Menüband „Alle Aktualisieren“ drücke, kommt die Fehlermeldung: „Mindestens ein im Bericht verwendeter Feldname ist nicht mehr gültig. Wenn Sie den Namen eines Felds in der Datenquelle ändern, geben Sie im Feld einen neuen Namen ein.“.
Aktualisiere ich meine Abfragen über das rechte „Abfragen und Verbindungen“ – Fenster manuell, klappt das ohne Fehler? Ist das ein Bug? Oder kommt die böse Überraschung, sobald ich tatsächlich mit neuen Daten aktualisieren möchte?
Kevin meint
Hallo, vielen Dank für die Anleitung. Das Hilft tatsächlich bei einigen Schwierigkeiten.
Ich habe eine weitere Frage. Ich habe einen mtl. Report der bis Jan. 2020 genau 41 Spalten hatte. Jetzt ist eine neue Spalte in 202 hinzugekommen. Gibt es einen Trick das er weiterhin alle korrekt liest auch von die Dateiein aus 2020 eine Spalte mehr haben.
Daten werden aus einem Ordner jeden Monat aktualisiert. In diesen Ordner liegen alle Monatsdaten ab.
Grüße
Kevin meint
— Jetzt ist eine neue Spalte in 2020 hinzugekommen. Gibt es einen Trick das er weiterhin alle Spalten aus 2019 korrekt liest und die neuen Daten plus eine Spalte mehr in den Dateien ab 2020 einfach hinzufügt. Die Namen haben sich sonst nicht geändert. Es ist nur eine neue Spalte hinzugekommen. —