Moderne Datenanalyse in Excel

Kapitel 4. Spalten in Power Query umwandeln

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

In Kapitel 3 ging es darum, sich mit der Bearbeitung von Zeilen vertraut zu machen; in diesem Kapitel liegt der Schwerpunkt nun auf Spalten. Dieses Kapitel enthält verschiedene Techniken wie die Umwandlung von Groß- und Kleinschreibung, die Neuformatierung von Spalten, die Erstellung von berechneten Feldern und vieles mehr. Um die Demonstrationen in diesem Kapitel zu verfolgen, schau dir die Datei ch_04.xlsx im Ordner ch_04 des Buches an. Lade nun die Tabelle rentals in Power Query.

Wechselndes Säulengehäuse

Power Query vereinfacht die Umwandlung von Textspalten zwischen Kleinbuchstaben, Großbuchstaben und "richtiger" Großschreibung (bei der jedes Wort groß geschrieben wird). Um diese Funktion zu testen, drücke die Strg-Taste und markiere gleichzeitig die Spalten Title und Artist Name. Klicke dann mit der rechten Maustaste auf eine der Spalten und navigiere zu Transformieren → Jedes Wort großschreiben, wie in Abbildung 4-1 gezeigt.

Abbildung 4-1. Ändern der Groß- und Kleinschreibung in Power Query

Beachte, dass bei Title und Artist Name die Leerzeichen nach Doppelpunkten und Kommas fehlen. Um dies zu beheben, klickst du mit der rechten Maustaste auf eine der beiden Spalten und wählst "Werte ersetzen". Suche in im Dialogfeld Werte ersetzen nach ":" und ersetze es durch einen Doppelpunkt gefolgt von einem Leerzeichen, wie in Abbildung 4-2 gezeigt.

Abbildung 4-2. Ersetzen von Werten in Power Query

Als Nächstes wendest du das gleiche Verfahren auf Kommas an: Ersetze sie durch ein Komma, gefolgt von einem Leerzeichen.

Wie in Kapitel 3 gezeigt, erfasst Power Query jeden Schritt, den du an den Daten in der Liste "Angewandte Schritte" durchführst. Diese Funktion erleichtert die Überprüfung von Textänderungen im Vergleich zum herkömmlichen Suchen und Ersetzen erheblich.

Abgrenzung nach Spalten

In Kapitel 3 hast du gelernt, wie man Text mit Kommas in Zeilen aufteilt. Jetzt ist es an der Zeit, dasselbe mit Spalten zu tun. Klicke mit der rechten Maustaste auf die Spalte Item # und teile sie in zwei Spalten, indem du Spalte teilen → Nach Trennzeichen wählst. Wähle im Dialogfeld die Option Leerzeichen aus der Dropdown-Liste und klicke auf OK. Auch hier bietet dieses Verfahren eine bessere Benutzerfreundlichkeit und eine breitere Palette an Funktionen als die herkömmliche Funktion Text in Spalten.

Die abgegrenzten Spalten sind zunächst mit Item #.1 und Item #.2 beschriftet. Um sie umzubenennen, doppelklickst du einfach auf die Spaltenüberschriften im Editor. Wie bei allen Änderungen in Power Query werden auch diese Änderungen über Angewandte Schritte aufgezeichnet, sodass sie bei Bedarf mühelos rückgängig gemacht oder angepasst werden können.

Ändern von Datentypen

In Power Query wird jeder Spalte ein bestimmter Datentyp zugewiesen, der die Operationen definiert, die mit ihr durchgeführt werden können. Beim Importieren eines Datensatzes versucht Power Query automatisch, den am besten geeigneten Datentyp für jede Spalte zu ermitteln. Es gibt jedoch Situationen, in denen diese automatische Erkennung verbessert oder angepasst werden kann.

Nehmen wir zum Beispiel die Spalte UPC. Standardmäßig ist ihr der Datentyp Ganze Zahl zugewiesen. Da wir jedoch nicht davon ausgehen, dass wir mit dieser Spalte bedeutende mathematische Operationen durchführen, ist es besser, sie als Text zu speichern. Dazu klickst du auf das Zahlensymbol neben der Spalte UPC und änderst ihren Datentyp in Text, wie in Abbildung 4-3 zu sehen ist.

Fahre mit den folgenden Datentypänderungen fort:

• Konvertiere die Spalte ISBN 13 in Text.

• Konvertiere die Spalte Retail in eine Währung.

Abbildung 4-3. Ändern von Spaltendatentypen in Power Query

Spalten löschen

Wenn du unnötige Spalten aus einem Datensatz entfernst, kannst du ihn leichter verarbeiten und analysieren. Wähle die Spalte BTkey aus und drücke die Löschtaste, um sie aus deiner Abfrage zu entfernen. Wenn du dich entscheidest, diese Spalte später wieder aufzunehmen, kannst du sie ganz einfach über die Liste der angewandten Schritte abrufen, wie in Kapitel 2 erklärt.

Arbeiten mit Daten

Power Query bietet eine Vielzahl von ausgefeilten Methoden zur Verwaltung, Umwandlung und Formatierung von Datumsangaben. Es erleichtert die Änderung von Datumstypen, indem es den Benutzern ermöglicht, Komponenten wie die Monatsnummer und den Tagesnamen zu extrahieren und diese Komponenten dann in den am besten geeigneten Datentypen zu speichern.

Um diese Funktion zu testen, wenden wir sie auf die Spalte Release Date auf verschiedene Arten an. Beginne damit, Kopien dieser Spalte zu erstellen: Klicke mit der rechten Maustaste auf die Spalte und wähle "Spalte duplizieren". Führe diesen Vorgang zwei weitere Male durch, um insgesamt drei doppelte Datumsspalten zu erstellen.

Klicke mit der rechten Maustaste auf die erste duplizierte Spalte Release Date und navigiere zu Transformieren → Jahr → Jahr, wie in Abbildung 4-4. Die Spalte wird neu formatiert und ihr Typ so geändert, dass nur noch das Jahr statt des kompletten Datums angezeigt wird.

Abbildung 4-4. Umwandlung von Datumsspalten in Power Query

Entferne die Monats- und Tageszahlen aus den nächsten beiden Spalten. Doppelklicke auf die Spaltenüberschriften und benenne sie in Year, Month bzw. Day um, um die neu formatierten Daten wiederzugeben. Schließe deine Ergebnisse und lade sie in eine Excel-Tabelle.

Du hast eine Reihe von spaltenorientierten Datenmanipulationen in Power Query erfolgreich ausgeführt. Jetzt kannst du diese Abfrage in Excel laden.

Benutzerdefinierte Spalten erstellen

Das Hinzufügen von einer berechneten Spalte ist eine häufige Aufgabe bei der Datenbereinigung. Egal, ob es sich um eine Gewinnspanne, eine Datumsdauer oder etwas anderes handelt, Power Query erledigt diesen Prozess mit seiner Programmiersprache M.

Für die nächste Demonstration gehst du zum Arbeitsblatt teams von ch_04.xlsx. Dieser Datensatz enthält die Saisondaten aller Major League Baseball Teams seit 2000. Unser Ziel ist es, eine neue Spalte zu erstellen, die den Gewinnrekord für jedes Team während der Saison berechnet. Diese Berechnung erfolgt, indem die Anzahl der Siege durch die Gesamtzahl der Siege und Niederlagen geteilt wird.

Der erste Schritt besteht natürlich darin, die Daten in Power Query zu laden. Gehe dann im Menüband des Editors zu Spalte hinzufügen → Benutzerdefinierte Spalte. Benenne deine benutzerdefinierte Spalte Wpct und definiere sie mit der folgenden Formel:

[W] / ([W] + [L])

Die Programmiersprache M von Power Query folgt einer Syntax, die der von Excel-Tabellen ähnelt, wobei Spaltenverweise in einzelne eckige Klammern eingeschlossen werden. Nutze Microsofts IntelliSense, um die Tabulatortaste zu drücken und den Code automatisch zu vervollständigen, während du diese Verweise eingibst. Außerdem kannst du in der Liste "Verfügbare Spalten" unter auf die gewünschten Spalten doppelklicken und sie so in den Formelbereich einfügen.

Wenn alles korrekt ist, erscheint unten im Dialogfeld ein grünes Häkchen, das anzeigt, dass keine Syntaxfehler gefunden wurden (siehe Abbildung 4-5).

Abbildung 4-5. Erstellen einer Gewinnprozentberechnung

Sobald du diese Spalte erstellt hast, änderst du ihren Datentyp in Power Query in Prozent.

Laden und Prüfen der Daten

Unsere neue Spalte ist berechnet und bereit zum Arbeiten. Gehe im Menüband des Power Query Editors auf Start → Schließen & Laden → Schließen & Laden in, wähle dann PivotTable Report und OK. Von dort aus kannst du die Daten analysieren, z. B. indem du den Durchschnitt Wpct für jeden Teamnamen berechnest, wie in Abbildung 4-6 gezeigt.

Abbildung 4-6. Zusammenfassen der Ergebnisse in einer PivotTable

Power Query-Datentypen versus Excel-Zellenformatierung

Beachte , dass das Ändern des Datentyps einer Spalte in Power Query nur die Art und Weise ändert, wie die Daten in Power Query selbst gespeichert werden; es ändert nicht die Formatierung der Daten in Excel. Wenn du z. B. eine Spalte in Power Query als Prozentwert formatierst, wie Wpct im vorherigen Beispiel, musst du die Prozentwertformatierung in Excel selbst noch anwenden. In Teil III werden wir untersuchen, wie du die Darstellung der Daten durch Datenformatierung in Power Pivot konsistent ändern kannst.

Berechnete Spalten versus Maßnahmen

Es ist wichtig zu beachten, dass der in der PivotTabelle angezeigte Durchschnitt Wpct ein einfacher, ungewichteter Durchschnitt der Gewinnquoten der Saison ist. Das bedeutet, dass Saisons mit einer geringeren Anzahl von Spielen - wie die von der Pandemie betroffene Saison 2020 - einen überproportionalen Einfluss auf die Berechnung haben. Um dies zu überprüfen, vergleiche den Wert Average of Wpct in der PivotTable mit unserer eigenen Excel-Berechnung, wie in Abbildung 4-7 dargestellt.

Abbildung 4-7. Scheinbare PivotTable-Fehlberechnung

Ein Ansatz, um dieses Problem zu lösen, ist die Verwendung dynamischer Kennzahlen für die Aggregation in Echtzeit und für Berechnungen, die auf den Analysekontext zugeschnitten sind. Dies wird mit Tools wie dem Datenmodell von Power Pivot und der Sprache DAX erreicht, die in Teil II dieses Buches besprochen werden.

Das bedeutet nicht, dass berechnete Spalten in Power Query gänzlich vermieden werden sollten. Sie sind einfach zu erstellen und rechnerisch effizient. Wenn jedoch die Möglichkeit besteht, dass diese Spalten zu irreführenden Aggregationen führen könnten, ist es ratsam, stattdessen eine DAX-Maßnahme zu wählen.

Daten umgestalten

In Kapitel 1 hast du das Konzept der "aufgeräumten" Daten kennengelernt, bei denen jede Variable nur in einer einzigen Spalte gespeichert wird. Vielleicht erinnerst du dich an die Daten auf dem Arbeitsblatt sales als Beispiel für unordentliche Daten. Glücklicherweise löst Power Query dieses kritische Problem der Speicherung von Daten. Navigiere zunächst zu dem bekannten Arbeitsblatt sales der Arbeitsmappe ch_04.xlsx. Lade diese Tabelle in Power Query, um denDatenumwandlungsprozess zu starten.

Ziel ist es, alle Umsatzspalten in einer Spalte mit dem Namen sales zusammen mit den Bezeichnungen für diese Umsätze in einer Spalte mit dem Namen department zu "entflechten" oder zu "verschmelzen". Dazu hältst du die Strg-Taste gedrückt und wählst die ersten drei Variablen aus: customer_id, channel, und region. Klicke mit der rechten Maustaste und wähle Andere Spalten aufheben, wie in Abbildung 4-8 gezeigt.

Abbildung 4-8. Aufheben der Pivotierung eines Datensatzes in Power Query

Standardmäßig heißen die beiden resultierenden unpivotierten Spalten Attribute und Value. Benenne sie in department bzw. sales um. Du kannst die Abfrage nun in eine PivotTable laden und die Verkäufe nach Kanal und Region analysieren. Die Ergebnisse und Vorteile der Erstellung einer PivotTable auf der Grundlage dieser umgestalteten Daten sind in Abbildung 4-9 zu sehen.

Abbildung 4-9. Arbeiten mit PivotTables an einem unpivotierten Datensatz

Fazit

In diesem Kapitel wurden verschiedene Möglichkeiten zur Bearbeitung von Spalten in Power Query untersucht. Kapitel 5 geht noch einen Schritt weiter und arbeitet mit mehreren Datensätzen in einer einzigen Abfrage. Du lernst, wie du Datenquellen zusammenführen und anhängen kannst und wie du dich mit externen Quellen wie .csv-Dateien verbinden kannst.

Übungen

Übe das Transformieren von Spalten in Power Query mit der Datei ch_04_exercises.xlsx, die du im Ordner exercises\ch_04_exercises im Begleit-Repository des Buches findest. Führe die folgenden Transformationen an diesem Arbeitsauftragsdatensatz durch:

1. Ändere die Spalte date in ein Monatsformat, z. B. 1/1/2023 in Januar.

2. Wandle die Spalte owner in die richtige Großschreibung um.

3. Teile die Spalte location in zwei separate Spalten auf: zip und state.

4. Gestalte den Datensatz so um, dass subscription_cost, support_cost und services_cost zu zwei Spalten zusammengefasst werden: category und cost.

5. Führe eine neue Spalte namens tax ein, die 7% der Werte in der Spalte cost berechnet.

6. Konvertiere die Variable zip in den Datentyp Text und aktualisiere die beiden Spalten cost und tax auf Währung.

7. Lade die Ergebnisse in eine Tabelle.

Die Lösungen für diese Umformungen findest du in ch_04_solutions.xlsx im gleichen Ordner .