Hartcodieren in PowerQuery?

Wenn Sie PowerQuery einsetzen, dann werden die Arbeitsschritte automatisch aufgezeichnet, so dass diese in Zukunft per „Aktualisieren“ immer wieder ausgeführt werden können. Die Arbeitsschritte werden genau in der Reihenfolge ausgeführt, wie Sie diese beim initialen Aufsetzen aufzeichnen. Die Spaltenbezeichnungen werden hartcodiert aufgezeichnet. Nachfolgend erklären wir zuerst, zu welchen Problemen dies führen kann und anschliessend zeigen wir die Lösungsansätze auf.

Problemstellung

Beispiel: Wenn Sie in Power Query die Funktion wählen, um andere Spalten zu entfernen, dann wird der Befehl wie folgt aufgezeichnet:

Auch wenn Sie andere Befehle aufzeichnen, werden die Spalten fix im Code aufgeführt. Dies führt dann zu einem Problem, wenn sich bspw. die Spaltennamen ändern oder wenn bspw. Sie die Sortierung ändern möchten. In so einem Fall müssen Sie im herkömmlichen Weg den PowerQuery Editor öffnen und die Anpassungen direkt im Code vornehmen.

Um zu vermeiden, dass Sie immer wieder im Code Anpassungen vornehmen müssen, schlagen wir hier einen alternativen Ansatz vor.

Lösungsansatz

Schritt 1: Datensatzbeispiel

Sie haben den folgenden Datensatz mit dem Namen „2023 Car Dataset“ aus einer Ihrer Abfragen:

Schritt 2: Implementieren der Spaltenauswahl, Umbenennung und Sortierung mit einer Liste

Angenommen, Sie möchten nur eine Teilmenge der Spalten auswählen und diese dann sortieren und umbenennen. Anstatt bei jeder Änderung wiederholt in den Power Query-Editor zu wechseln, können Sie eine Zuordnungstabelle in einem anderen Blatt implementieren.

1. Erstellen Sie eine Zuordnungstabelle, die aus drei Spalten besteht:

• „Spaltenname original“
• „Spaltenname neu“
• „Sortierreihenfolge“

Hinweis: Sie können diese Spalten nach Bedarf umbenennen.

2. Im folgenden Beispiel haben wir als ersten Schritt alle Spalten des Datensatzes in die erste Spalte kopiert.

3. Wir möchten nun die „Automarke“ und „Automodell“ in „Marke“ bzw. „Modell“ umbenennen.

4. Anschließend entfernen wir Spalten, die wir in unserem Datensatz nicht benötigen, aus der Zuordnungstabelle. In diesem Beispiel haben wir nur 12 von 20 Spalten beibehalten.

5. Für die restlichen Spalten, die wir nicht umbenennen müssen, geben wir die gleichen Namen in „Spaltenname neu“ ein.

6. Anschließend geben wir in „Sortierreihenfolge“ an, welche Spalte zuerst im Datensatz erscheint (von links nach rechts).

In diesem Fall möchten wir die Preise und Verkaufszahlen früher sehen als die anderen Informationen.

7. Jetzt müssen wir diese Zuordnungstabelle als Datenquelle von Power Query angeben. Dies kann erreicht werden, indem Sie die Tabelle auswählen und auf der Registerkarte „Daten“ auf „Aus Tabelle/Bereich“ klicken.

Schritt 3: Transformieren des Datensatzes mit Power Query

Der nächste Schritt besteht darin, die neu generierte Hilfstabelle als Grundlage für die Spaltenauswahl, Sortierung und Umbenennung zu verwenden.

1.Öffnen Sie den Power Query-Editor, indem Sie zu Daten -> Abfragen und Verbindungen gehen, im rechten Bereich mit der rechten Maustaste auf die neue Tabelle klicken und Bearbeiten auswählen.

Hinweis: Der Name der Zuordnungsliste hat normalerweise das Format Abfrage_[Tabellenname]. Daher wird es in unserem Beispiel so benannt, weil der Name der Zuordnungstabelle „tblMap“ war.

2. Das Ziel besteht hier darin, zwei Listen zu erstellen, die als Argumente für die Arbeitsschritte im eigentlichen Datensatz dienen.

i. listColNames – besteht aus den sortierten Spaltennamen
ii. listRenames – besteht aus einer Liste von Listen; enthält jeweils die alten und neuen Spaltennamen

3. Erstellen der Liste listColNames

i. Sortieren Sie die Liste über „Sortierreihenfolge“, indem Sie mit der rechten Maustaste auf die oben genannte Spalte klicken und „Aufsteigend sortieren“ auswählen

ii. Erstellen Sie einen Verweis (Reference) von Query_tblMap, indem Sie mit der rechten Maustaste auf die Abfrage klicken und auf Verweis (Reference) klicken.

iii. Es wird eine neue Abfrage mit dem Namen Query_tblMap (2) erstellt, die sich auf Query_tblMap bezieht. Klicken Sie mit der rechten Maustaste auf die Spalte „Spaltenname original“ und wählen Sie Drilldown aus. Dieser Schritt ruft die ausgewählte Spalte ab und wandelt sie in eine Liste um.

iv. Benennen Sie die Abfrage im rechten Bereich in „listColNames“ um. Diese Liste ist nun zur Verwendung bereit.

4. Erstellen der Liste listRenames.

i. Erstellen Sie einen weiteren Verweis (Reference) von Query_tblMap, indem Sie mit der rechten Maustaste auf die Abfrage klicken und auf Verweis (Reference) klicken.

ii. Erstellen Sie in dieser Referenzabfrage Query_tblMap (2) eine benutzerdefinierte Spalte, indem Sie zur Registerkarte „Spalte hinzufügen“ wechseln.

iii. Geben Sie im Formelfeld „Benutzerdefinierte Spalte“ die Formel wie folgt ein:
{[Spaltenname ursprünglich], [Spaltenname neu]}
Dadurch wird eine Liste mit 2 Elementen pro Datensatz erstellt. Eine mit dem alten und eine mit dem neuen Namen.
Geben Sie den Spaltennamen an. In diesem Beispiel verwenden wir listRenames.

iv. Klicken Sie mit der rechten Maustaste auf die benutzerdefinierte Spalte „listRenames“ und wählen Sie „Drilldown“ aus.

v. Benennen Sie die Abfrage im rechten Bereich in listColNames um. Diese Liste ist nun zur Verwendung bereit.

5. Verwendung der Liste bei der Transformation des tatsächlichen Datensatzes. In diesem Beispiel wird der Autodatensatz 2023 mithilfe der Einträge in listColNames und listRenames manipuliert.

i. Um nur die relevanten Spalten basierend auf der Liste listColNames auszuwählen, fügen Sie einen „Dummy“-Arbeitsschritt ein, indem Sie mit der rechten Maustaste auf eine Spalte (oder mehrere Spalten) klicken und „Andere Spalten entfernen“ auswählen.

ii. Dadurch wird ein Table.SelectColumns()-Schritt erstellt. Zunächst ist die Liste der auszuwählenden Spalten fest codiert, wie in der folgenden Abbildung hervorgehoben.

iii. Ersetzen Sie die hervorgehobene Liste durch listColNames. Die Tabelle enthält jetzt nur noch die relevanten Spalten aus der Zuordnungstabelle mit angewendeter Sortierreihenfolge.

iv. Um alle Spalten basierend auf der Liste „listRenames“ umzubenennen, fügen Sie einen „Dummy“-Arbeitsschritt ein, indem Sie mit der rechten Maustaste auf eine Spalte klicken, „Umbenennen“ auswählen und dann einen Dummy-Spaltennamen eingeben.

v. Dadurch wird ein Table.RenameColumns()-Schritt erstellt. Wie in Table.SelectColumns() ist auch die Liste der umzubenennenden Spalten zunächst fest codiert, wie in der folgenden Abbildung hervorgehoben.

vi. Ersetzen Sie die hervorgehobene Liste durch listRenames. Dadurch werden alle relevanten Spalten entsprechend umbenannt. In unserem Beispiel werden die ersten beiden Spalten in „Make“ bzw. „Model“ umbenannt.

6. Der Datensatz kann nun in Excel aktualisiert werden. Gehen Sie zu Schließen und Laden -> Schließen und Laden in.

7. Stellen Sie sicher, dass „Nur Verbindung erstellen“ markiert ist, bevor Sie auf „OK“ klicken.

Schritt 4: Endgültige Datensatzergebnisse

Der endgültige Datensatz in Excel wird nun basierend auf der Zuordnungstabelle aktualisiert. Wenn Sie eine Spalte umbenennen, umsortieren oder neu auswählen müssen, müssen Sie jetzt einfach die Zuordnungstabelle ändern und dann den Datensatz aktualisieren.

Zusammenfassung

Um Excel-Daten effektiv zu verwalten, stellt dieser Artikel eine leistungsstarke Power Query-Lösung vor. Ohne den Editor zu verwenden, können Benutzer mithilfe einer Zuordnungstabelle Spalten schnell auswählen, umbenennen und sortieren. Tabellenkalkulationen bleiben strukturiert und aktuell, während der Prozess optimiert wird, was Zeit und Aufwand spart. Verabschieden Sie sich von der manuellen Datenmanipulation und nutzen Sie die Automatisierung, um Ihre Produktivität zu optimieren.

Benötigen Sie einen VBA Programmierer?

Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).
Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular.