Excel Power Query: Ein Leitfaden für Anfänger
Warum wir Power Query benötigen
Power Query ist ein leistungsstarkes Tool zur Datenvorbereitung, das nahtlos in ETL-Prozesse (Extraktion, Transformation, Laden) integriert werden kann. Es ist benutzerfreundlich, bietet viele Datenverbindungen und erleichtert die Bearbeitung großer Datenmengen. Besonders für komplexe Aufgaben wie das Bereinigen, Kombinieren und Aktualisieren von Daten aus verschiedenen Quellen ist Power Query unersetzlich. An dieser Stelle kann man sehr viel AI generierten Content einsetzen, aber einen Anfänger dürfte das nicht interessieren, deshalb komme ich gleich zum Hauptteil.
Beispiel: Breitbandatlas
Damit es nicht langweilig wird, nutzen wir ein Beispiel. Die Bundesnetzagentur veröffentlicht gemeinsam mit dem Bundesministerium für Digitales und Verkehr den Breitbandatlas. Wir laden uns die Daten für Ende des Jahres 2022 herunter. Dabei handelt es sich um eine XLSX-Datei, die wir im Folgenden nutzen werden. Die Datei muss irgendwo auf dem Computer gespeichert werden.
Daten laden
Der erste Schritt besteht darin, die Daten in den Power Query Editor zu laden. Dafür klicken wir im Ribbon auf Daten und wählen Daten abrufen.
Hier sehen wir die zahlreichen Optionen, man kann Daten aus TXT-, CSV-, XLSX-Dateien, SQL-Datenbanken, PDF-Dateien, SharePoint-Quellen usw. abrufen. Ich habe anfänglich am häufigsten die Verbindung zu Tabellen innerhalb einer Excel-Tabelle genutzt ("Aus Tabelle/Bereich"), später dann überwiegend aus externen Excel-Tabellen ("Aus Excel-Arbeitsmappe") oder vielen Excel-Dateien ("Aus Ordner").
In diesem Fall nutzen wir Aus Excel-Arbeitsmappe, um die zuvor heruntergeladene XLSX-Datei zu öffnen.
Anschließend öffnet sich ein Fenster, wo man eine Datei auswählen kann. Danach folgt der Navigator:
Im Navigator kann ich alle Arbeitsblätter sehen, ich würde auch Tabellen sehen, wenn es in dieser Datei welche gäbe (aber anscheinend hatte die BNA keine Lust die Datei richtig zu formatieren). Ich kann auswählen, zu welchen Teilen ich eine Verbindung herstellen möchte. Ich wähle für dieses Beispiel das Arbeitsblatt "Breitband-Infrastruktur" aus und drücke auf "Daten transformieren".
Power Query Editor
Nun öffnet sich der Power Query Editor mit den geladenen Daten. Schauen wir uns die wichtigsten Bereiche des Editors an:
Abfragebereich (links)
Im Abfragebereich auf der linken Seite können wir alle Abfragen sehen, die in PowerQuery geladen werden. In unserem Fall befindet sich dort eine einzige Abfrage. Hat man sehr viele Abfragen lohnt es sich dort mit rechtsklick Ordner anzulegen und/oder die Abfragen sinnvoll zu benennen. Beispielsweise kann ich dort auch eine ETL-Struktur anlegen.
Abfrageeinstellungen (rechts)
Rechts sehen wir die Abfrageeinstellungen, uns interessiert besonders der Teil mit den angewendeten Schritten. Wir können die Schritte anpassen (über das Zahnrad), löschen (über das Kreuz) und die Reihenfolge bei Bedarf verändern (über Drag and Drop).
Lädt man neue Daten in Power Query werden automatisch ein paar Schritte angewendet wie zum Beispiel eine automatische Typ Erkennung. Das ist in vielen Fällen völlig unnötig und führt nur zu Fehlern. Wir löschen über die Kreuze die letzten beiden Schritte, weil sie falsch sind.
Globale Abfrageoptionen anpassen
Bevor wir mit der Datenbereinigung beginnen, ändern wir zwei globale Einstellungen. Dazu unter Datei (oben links) die Abfrageoptionen auswählen.
Hier schalten wir die Typerkennung aus und schalten das automatische Laden von Abfragen in ein Arbeitsblatt aus:
Datenreinigung
Jetzt beginnt die eigentliche Datenbereinigung. Dazu können wir in der Multifunktionszeile (oben) verschiedene Funktionen auswählen.
Oberste Zeilen entfernen
Im Vorschaufenster (mittlerer Bereich) sehen wir, dass die relevanten Daten erst ab Zeile 4 beginnen. Also entfernen wir die ersten drei Zeilen über Zeilen entfernen > Erste Zeilen entfernen.
Rechts sehen wir den neuen Schritt, nachdem wir bestätigen, dass die ersten drei Zeilen entfernt werden sollen.
Erste Zeile als Überschrift
Die erste Zeile enthält die Spaltennamen, daher stufen wir sie als Überschrift hoch.
Spalten auswählen
Um die Daten übersichtlicher zu gestalten, wählen wir die Spalten „Name“, „Glasfaserleitung“ und „Straßenlaterne“ aus (Strg gedrückt halten für Mehrauswahl) und entfernen den Rest über Spalten entfernen > Andere entfernen.
Datentypen ändern
Die Spalten haben verschiedene Datentypen (z.B. Text, Zahl). Diese passen wir über dieses ABC123 Symbol neben den Spaltennamen an oder versuchen zunächst den automatischen Weg indem man alle Spalten auswählt und im Menü Transformieren. "Datentyp erkennen" drückt.
In diesem Fall wurden sie richtig erkannt, ansonsten muss man ab und zu manuell nachhelfen.
Sollten internationale Formate (z. B. 1,000.00 statt 1.000,00 oder 01/31/2024 statt 31.01.2024) auftreten, können wir über Mit Gebietsschema... die passenden Einstellungen vornehmen.
Fehlerkorrektur
In diesem Fall zwar nicht notwendig, man könnte aber auf Fehler stoßen, insbesondere durch die Typenkonvertierung. Unter Ansicht ist es sinnvoll die Spaltenqualität anzuschalten
Power Query zeigt uns, dass 11 % der Einträge in der Spalte „Straßenlaternen“ leer sind. Falls hier Fehler wären könnten wir uns überlegen was wir damit machen wollen. Im besten Fall kann man dafür sorgen, dass die Fehler überhaupt nicht auftreten, aber wenn man nur bedingt Einfluss auf die Datengrundlage hat, ist das nicht immer möglich. In diesem Fall kann man die Fehler entfernen.
Oder man ersetzt sie durch andere Werte oder null
Duplikate
Zudem lassen sich Duplikate durch Rechtsklick auf die relevanten Spalten und Duplikate entfernen leicht eliminieren.
Es gibt noch viel mehr Möglichkeiten zur Datenbereinigung, aber das sollte für's Erste reichen.
Datentransformation
Wo genau der Übergang zwischen Datenreinigung und Datentransformation liegt, weiß ich nicht. Jedenfalls erzeuge ich mir an dieser Stelle einen Verweis auf meine Abfrage, indem ich rechts da drauf drücke und Verweis auswähle.
Es erscheint eine neue Abfrage, die ich in die nächste Gruppe schiebe.
Spalten teilen
In diesem Fall zwar nicht nötig, aber grundsätzlich ist das Teilen einer Spalte eine der wichtigsten Transformationen. Wir teilen jetzt die Spalte mit dem Namen nach Trennzeichen.
Im nächsten Menü wähle ich aus, dass ich beim äußersten rechten Leerzeichen die Trennung vornehmen möchte. Ich hätte aber auch deutlich mehr Optionen.
Dadurch erhalte ich zwei Spalten.
Daten filtern
Wir können die Daten filtern indem wir auf den Pfeil rechts eines Spaltennamens drücken. Ich habe ein paar Städte gefiltert (Berlin, Bonn, Düsseldorf, Hamburg, Jena, München) und merke, dass einige doppelt vorkommen.
Bei München gibt es einmal die Stadt und einmal das Landkreis München, dementsprechend haben wir hier sogar unterschiedliche Werte. Jetzt können wir in Name.1 nach Kreisfreie Stadt filtern und die Spalte Name.1 löschen sowie Name.2 in Kreisfreie Stadt umbenennen.
Im Nachhinein denke ich mir, dass ich doch lieber alle Städte berücksichtigen würde. Das Tolle ist, dass ich nun rechts den Schritt gefilterte Zeilen auswählen kann und ich wähle alle Zeilen von Name.2 aus. Wenn ich nun wieder zum letzten Schritt springe, habe ich alle Kreisfreien Städte, zu denen Daten vorliegen.
Nun möchte ich wissen welche Stadt die meisten Glasfasern hat und welche die meisten Laternen. Dafür verweise ich zwei Mal auf diese Abfrage. Bei den Glasfasern sortiere ich nach Glasfaserleistung absteigend und behalte nur die erste Zeile.
Das Gleiche mache ich analog bei den Straßenlaternen in der anderen Abfrage.
Daten laden
Als Nächstes möchte ich die Daten laden. Ich erstelle mir zwei weitere verweisende Abfragen und drücke auf das Schließen & laden Symbol.
Wenn man diesen Text befolgt und die Standardoptionen geändert hat, lädt die Abfrage nicht in der Excel-Datei. Ansonsten hat man jetzt 6 neue Arbeitsblätter.
Unter Abfragen und Verbindungen wählen wir mit Rechtsklick die "load" Abfrage und in diesem Menü "Laden in..."
Wir wollen die Daten als Tabelle importieren.
Und nun habe ich das Ergebnis in meiner Datei geladen.
Hätte man das mit gewöhnlichen Formeln auch hinbekommen? Ja.
Hatte Berlin Ende 2022 wirklich nur 59 Laternen? Ich glaube nicht.
Die Vorteile in dieser Vorgehensweise liegen darin, dass man nicht alle Daten in die Arbeitsmappe lädt, dadurch bleiben die Dateien kleiner. Man kann Daten aus unterschiedlichen Quellen extrahieren und kombinieren. Außerdem kann man nun die Daten sehr einfach aktualisieren.
Aktualisierung der Daten
Ein großer Vorteil von Power Query ist die einfache Datenaktualisierung. Wir laden den neusten Datensatz herunter. Wir öffnen den Power Query Editor indem wir auf eine Abfrage in "Abfragen und Verbunden" mit linksklick doppelt drauf drücken. Wir navigieren zum ersten Schritt mit der Quelle.
Hier wählen wir über das Zahnrad eine neue Quelle aus. Dann speichern wir und schließen den Editor wieder. Noch hat sich nichts an den Daten geändert. Unter Daten drücken wir auf "Alle aktualisieren".
Und im nächsten Moment haben wir die aktuellsten Daten geladen:
Berlin hat angeblich in diesem Zeitraum keine einzige neue Straßenlaterne gebaut, in Bremen sind dafür über 30.000 Stück verschwunden. Wo sie nun hin sind kann uns Power Query leider nicht beantworten.
Fazit
Mit wenig Aufwand konnten wir in diesem Beispiel Daten aus einer Excel-Datei importieren, bereinigen und transformieren. Der größte Vorteil liegt in der Möglichkeit, Datenquellen zu kombinieren und diese bei Bedarf einfach zu aktualisieren – ohne manuell alle Schritte neu durchführen zu müssen. Während einfache Aufgaben auch mit Formeln machbar wären, bietet Power Query für komplexere Datenverarbeitungsszenarien eine deutlich effizientere Lösung.