In meinem letzten Power Query-Seminar fragten mich die Teilnehmenden, wie sie Spaltennamen dynamisch auf Basis der eingestellten Sprache umbenennen könnten. Ich versprach nach einer Lösung zu suchen. In diesem Beitrag zeige ich nun mehrere Möglichkeiten auf.
Hinweis: Dieser Beitrag behandelt Power Query in Excel. Sie können alle gezeigten Möglichkeiten aber auch (mit geringen Abwandlungen) in Power BI Desktop anwenden.
Die Beispieldaten
Als Beispiel soll uns diese kleine Tabelle mit verschiedenen Bestellungen dienen.
Sie können die Tabelle mit den Beispieldaten jetzt kostenfrei herunterladen:
Ich werde nun nacheinander drei Möglichkeiten vorstellen, mit denen Sie Spalten in Power Query dynamisch umbenennen können:
- mit der Power Query-Funktion Culture.Current
- mit einem Power Query-Parameter
- mit einer Tabelle im Excel-Arbeitsblatt
Möglichkeit 1: Die Funktion Culture.Current
Nach kurzer Suche bin ich auf die Power Query-Funktion Culture.Current gestoßen. Sie gibt den Namen der aktuellen Kultur der Anwendung zurück. Diese etwas sperrige Beschreibung meint die eingestellte Sprache für das Gebietsschema. (Keine Sorge, ich zeige gleich noch, wo Sie diese finden!)
Die Funktion gibt jeweils den Sprachcode zurück. Hier ein paar Beispiele:
Sprachcode | Sprache |
de-DE | Deutsch (Deutschland) |
en-US | Englisch (USA) |
Schauen wir uns also einmal an, wo wir in Power Query die Sprache für das Gebietsschema einstellen können.
Sprache für das Gebietsschema in Power Query einstellen
Wir öffnen zuerst die Abfrageoptionen von Power Query:
Unter Regionale Einstellungen im Abschnitt AKTUELLE ARBEITSMAPPE finden wir die eingestellte Sprache für das Gebietsschema. Wenn wir die Maus auf das kleine Fragezeichen bewegen, dann sagt uns Power Query: "Das Gebietsschema legt die regionalen Einstellungen zur Darstellung von Zahlen, Datumsangaben und der Uhrzeit in importierten Texten für diese Datei fest."
Die hier eingestellte Sprache liest Culture.Current also aus!
Übrigens: der Menüpunkt Regionale Einstellungen im Abschnitt GLOBAL regelt nur die Sprache für die automatisch generierten Schrittnamen. Die Einstellung dort ändert nichts am Gebietsschema und wirkt sich daher auch nicht auf unsere Funktion aus.
Schritt einfügen, der die Spalten dynamisch umbenennt
Aktuell sieht unsere Abfrage in Power Query so aus:
Wir fügen nun einen weiteren Schritt hin zu. Dort werden wir mit einer if-Funktion prüfen, ob Culture.Current den Wert "en-US" zurückgibt. Wenn das der Fall ist, dann vergeben wir englische Spaltennamen. Ist nicht "en-US" eingestellt, soll einfach nichts passieren.
Wir klicken auf das fx-Zeichen vor der Bearbeitungsleiste. Dadurch fügen wir einen neuen Abfrageschritt ein. Dieser Schritt erhält den Namen "Benutzerdefiniert1". Diesen Namen können (und sollten) wir später ändern.
Wir löschen den Text in der Bearbeitungsleiste. Dieser verweist einfach nur auf den vorherigen Schritt „Geänderter Typ“. Übrigens: Schrittnamen, die Leerzeichen enthalten, setzt Power Query in doppelte Anführungszeichen und setzt eine Raute (#) davor. Daher steht dort #“Geänderter Typ“.
Nun geben wir folgende Formel ein:
= if Culture.Current = "en-US" then
Table.RenameColumns(#"Geänderter Typ", {{"Bestell-Nr.", "Order No."}, {"Bestelldatum", "Order date"}, {"Artikel", "Article"}})
else #"Geänderter Typ"
Beachten Sie bitte, dass Power Query ganz genau auf Groß- und Kleinschreibung achtet! Sie müssen die Formel also wirklich genauso eintippen, wie hier abgebildet.
Das folgende Schaubild soll diese if-Funktion einmal deutlicher erklären:
Die Funktion Table.RenameColumns weist jeder Spalte einen neuen Namen zu. Im ersten Argument geben wir die Tabelle mit den umzubenennen Spalten an. Dies ist einfach der vorherige Schritt „Geänderter Typ“.
Falls Ihnen das seltsam vorkommt: der Schritt „Geänderter Typ“ speichert einfach gesagt die Tabelle zum Stand dieses Schrittes (also die Tabelle mit den eingestellten Datentypen). Auch die anderen Schritte speichern immer einen bestimmten Stand der Tabelle ab.
Im zweiten Argument geben wir jeweils in Listenform den alten und den neuen Spaltennamen an. Eine Liste steht in Power Query immer in geschweiften Klammern {}. Zusätzlich müssen alle 3 Listen auch wieder in einer Liste stehen. Daher die doppelten geschweiften Klammern am Anfang und am Ende.
Selbstverständlich können Sie auch auf weitere Sprachcodes (Spanisch, Französisch etc.) prüfen. Dazu verschachteln Sie weitere if-Funktionen in der Formel. Um das Beispiel nicht zu kompliziert zu machen, verzichte ich jedoch darauf.
Wichtiger Hinweis: Dieser eben eingefügte Schritt sollte der letzte Abfrageschritt sein! Da sich die Namen der Spalten ja nun ändern können, sollten keine darauffolgenden Schritte mehr auf diese zugreifen. Dies würde sonst mit ziemlicher Sicherheit zu Fehlern führen!
Zur besseren Dokumentation sollten wir den Schritt jetzt noch umbenennen. Wir klicken dazu mit der rechten Maustaste auf den Schritt "Benutzerdefiniert1" und wählen den Befehl Umbenennen im Kontextmenü. Wir nennen den Schritt um in "Dynamische Spaltennamen".
Test unserer dynamischen Spaltenumbenennung
Wir können nun testen, ob unsere Abfrage richtig funktioniert. Wir stellen in den Abfrageoptionen das Gebietsschema um auf Englisch (USA). Dann aktualisieren wir die Vorschau in Power Query. Die Spaltennamen sind jetzt in Englisch.
Möglichkeit 2: Spaltennamen mit einem Parameter umbenennen
Über einen Parameter können wir Informationen an Power Query übergeben. Der Nachteil ist allerdings, dass wir den Power Query-Editor öffnen müssen, um den Parameterwert zu ändern. Trotzdem möchte ich diese Möglichkeit einmal vorstellen.
Parameter erzeugen
Über die Registerkarte Start können wir einen neuen Parameter anlegen.
Wir nennen den Parameter "Sprache". Die optionale Beschreibung hilft den Nutzerinnen und Nutzern dabei, den Sinn des Parameters zu erfassen. Wir definieren unseren Parameter außerdem als Pflichtfeld. Das bedeutet, dass ein Sprachcode im Parameter eingegeben werden muss.
Darüber hinaus müssen wir einen aktuellen Wert festlegen. Wir schreiben daher "en-US" in das Feld.
Der Parameter taucht nun links im Power Query-Fenster unter den Abfragen auf. Wenn wir ihn anklicken, können wir den aktuellen Wert sehen und ihn bei Bedarf auch ändern.
Parameter in if-Funktion verwenden
Die Formel im Abfrageschritt „Dynamische Spaltennamen“ können wir nun folgendermaßen abändern:
= if Sprache = "en-US" then
Table.RenameColumns(#"Geänderter Typ", {{"Bestell-Nr.", "Order No."}, {"Bestelldatum", "Order date"}, {"Artikel", "Article"}})
else #"Geänderter Typ"
In unserer if-Funktion prüfen wir, ob der Parameter "Sprache" den Wert "en-US" hat. Wir können einfach Culture.Current aus der alten Formel löschen und durch den Namen des Parameters ersetzen.
Möglichkeit 3: Spalten dynamisch über eine Angabe im Excel-Arbeitsblatt ändern
Diese Option gibt uns die größte Flexibilität, denn hier können wir die Sprache direkt im Excel-Arbeitsblatt einstellen. Den Power Query-Editor brauchen wir dann nicht mehr zu öffnen.
Wir legen in unserer Excel-Datei (und zwar in der Datei, in welcher sich die Power Query-Abfragen befinden und nicht in der Beispieldatei!) einfach eine kleine Tabelle an, welche den aktuellen Sprachcode beinhaltet.
Liste als Tabelle formatieren und in Power Query laden
Wir formatieren die kleine Liste dann als Tabelle und vergeben den Namen "tblSprache".
Achten Sie darauf, dass Sie die Option Tabelle hat Überschriften anhaken. Auf der Registerkarte Tabellenentwurf können Sie dann vorne links der Tabelle einen Namen geben. Die Registerkarte sehen Sie übrigens nur, wenn Sie eine Zelle der Tabelle ausgewählt haben.
Wir laden unsere Tabelle dann über den Befehl Aus Tabelle/Bereich in Power Query. Den Befehl finden Sie auf der Registerkarte Daten. Klicken Sie in eine Zelle unsere Tabelle, bevor Sie den Befehl anwählen, damit Excel weiß, dass Sie diese Tabelle meinen.
Wir finden unsere Tabelle dann in der Abfrageliste von Power Query.
Drilldown auf den Sprachcode ausführen
Wir müssen nun den Sprachcode aus der Tabelle extrahieren. Das bedeutet, dass die Abfrage als Ergebnis keine Tabelle mehr liefern darf, sondern nur noch den Sprachcode in Textform. Dies erreichen wir mit einem sogenannten Drilldown.
Wir klicken mit der rechten Maustaste auf den Sprachcode und wählen im Kontextmenü den Befehl Drilldown ausführen. Das Abfrageergebnis ist jetzt keine Tabelle mehr, sondern nur noch der Sprachcode in Textform.
Abfrageergebnis des Drilldowns in if-Funktion verwenden
Wir können im Abfrageschritt „Dynamische Spaltennamen“ die Formel nun folgendermaßen anpassen:
= if tblSprache = "en-US" then
Table.RenameColumns(#"Geänderter Typ", {{"Bestell-Nr.", "Order No."}, {"Bestelldatum", "Order date"}, {"Artikel", "Article"}})
else #"Geänderter Typ"
In der if-Funktion prüfen wir nun, ob die Abfrage "tblSprache" den Text "en-US" enthält. Auch hier brauchen wir lediglich den Parameter "Sprache" durch die Abfrage "tblSprache" zu ersetzen.
Nun können wir einfach im Arbeitsblatt den Sprachcode ändern und dann die Tabelle aktualisieren. Diese erhält dann umgehend die neuen Spaltennamen. Den Power Query-Editor müssen wir dazu nicht öffnen.
Datei mit allen Lösungen
Sie könne nun die fertige Excel-Datei mit den Power Query-Abfragen kostenfrei herunterladen. Im Power Query-Editor finden Sie für jede der drei vorgestellten Möglichkeiten eine eigene Abfrage.
Ändern Sie die Datenquelleneinstellungen auf die oben heruntergeladene Beispieldatei, damit die Abfragen auch bei Ihnen funktionieren.
Fazit
In diesem Artikel habe ich 3 Möglichkeiten vorgestellt, um Spaltennamen in Power Query dynamisch umzubenennen.
Im 1. Fall habe ich die Funktion Culture.Current benutzt, um die Sprache des Gebietsschemas auszulesen. Diese Sprache können Sie in den Abfrageoptionen von Power Query ändern.
Im 2. Fall habe ich einen Parameter verwendet, um den Sprachcode an die Abfrage zu übergeben. Dies hat jedoch den Nachteil, dass Sie den Power Query-Editor öffnen müssen, um den Parameter zu aktualisieren.
Daher habe ich Ihnen im 3. Fall eine Möglichkeit mit noch mehr Flexibilität aufgezeigt. Sie legen einfach eine kleine Tabelle in Excel an, in welcher Sie den aktuellen Sprachcode erfassen. Diese Tabelle laden Sie dann in Power Query und extrahieren den Sprachcode über einen Drilldown. Der große Vorteil ist, dass Sie den Sprachcode jetzt direkt in Excel ändern können, ohne den Power Query-Editor öffnen zu müssen.
Alle 3 Möglichkeiten verwenden eine if-Funktion und stellen je nach Sprachcode mithilfe der Funktion Table.RenameColumns die gewünschten Spaltennamen ein.
Ich wünsche Ihnen viel Spaß beim Ausprobieren und Testen dieser Lösungsmöglichkeiten in Ihren Power Query-Dateien.