Power Query an sich erleichtert das Excel-Leben schon ungemein, aber mit ein paar kleinen Kniffen kann es manchmal noch einfacher werden. Und genau das ist es, worum es bei den Power Quickies geht.
Und das sind die heutigen Tipps:
- Power-Quickie 13: Die Editor-Ansicht vergrößern oder verkleinern
- Power-Quickie 14: Mehrere Tabellen aus der aktuellen Arbeitsmappe zusammenführen
- Power-Quickie 15: Zellen nach unten ausfüllen
Und so geht’s:
Solltest du dich bisher noch nicht an Power Query herangewagt oder vielleicht noch nie etwas davon gehört haben, empfehle ich dir auf jeden Fall diese beiden Blogartikel:
Einführung in Power-Query – Teil 1
Einführung in Power-Query – Teil 2
Jetzt aber zu den heutigen Power Quickies.
Power Quickie 13: Die Editor-Ansicht vergrößern oder verkleinern
Wenn man den Power Query-Editor geöffnet hat, dann lässt sich zwar wie in allen Excel-Fenstern die Fenstergröße zum Beispiel auf Vollbild anpassen. Aber manchmal reicht das noch nicht aus, um sich einen Überblick über die Tabelle zu verschaffen.
Mit der Tastenkombination Strg+Umschalt+Minuszeichen bzw. Strg+Umschalt+Pluszeichen lässt sich das leicht ändern. Damit zoomst du die Ansicht größer oder kleiner, je nachdem was halt gerade benötigt wird:
Editor-Ansicht verkleinern (Strg+Umschalt+Minuszeichen)
Editor-Ansicht vergrößern (Strg+Umschalt+Pluszeichen)
Um ganz schnell wieder zur Normalgröße zurückzukommen, muss man nur kurz die Fenstergröße verändern (z.B. auf Vollbild oder Fenster) oder das Fenster ein wenig auf dem Bildschirm verschieben.
Power Quickie 14: Mehrere Tabellen aus der aktuellen Arbeitsmappe zusammenführen
Dass man mit Power Query ganz einfach und in einem Rutsch mehrere Dateien aus einem Verzeichnis importieren kann, hat sich vermutlich schon herumgesprochen (falls nicht, guckst du hier).
Wie schafft man es jedoch, mehrere Tabellen gleichzeitig aus der aktuell geöffneten Arbeitsmappe in Power Query zu laden? Um zum Beispiel alle Monatstabellen, die in je einem eigenen Arbeitsblatt gespeichert sind, zu einer einzigen großen Liste zusammenzufassen.
Das Menü bietet hierfür leider keine Option, man kann hier nur einzelne Tabellen laden:
Keine Möglichkeit für den Import mehrerer Tabellen
Mit einem kleinen Trick geht es aber doch. Du erstellst eine neue leere Abfrage:
Daten | Daten abrufen | Aus anderen Quellen | Leere Abfrage
Eine neue leere Abfrage erstellen
In dem leeren Editorfenster tippst du oben in der Bearbeitungszeile folgende Funktion ein:
= Excel.CurrentWorkbook()
Die benötigte M-Funktion in der Bearbeitungsleiste eingeben
Es handelt sich dabei um eine sogenannte M-Funktion, das ist die Formelsprache innerhalb von Power Query.
Sollte die Bearbeitungsleiste bei dir nicht sichtbar sein, musst du nur im Ansicht-Menü die entsprechende Option aktivieren:
Bearbeitungsleiste bei Bedarf einblenden
Nachdem du die Funktion mit der Eingabetaste bestätigt hast, werden dir alle formatierten Tabellen angezeigt:
Alle Tabelle der aktuellen Arbeitsmappe
Über einen Klick auf das doppelte Pfeilsymbol neben der Spaltenbezeichnung „Content“ werden dir alle Feldnamen angeboten:
Tabelleninhalt erweitern
Entferne noch das Häkchen „Ursprüngliche Spaltennamen als Präfix verwenden“ und bestätige die Auswahl mit OK
Alle Spalten werden übernommen
Und schön werden dir alle Tabellen zusammengefasst in einer einzigen Abfrage dargestellt. Die Namen der ursprünglichen formatierten Tabellen werden in einem eigenen Feld „Name“ aufgeführt:
Die zusammengeführte Gesamttabelle
Power Quickie 15: Zeilen nach unten ausfüllen
Du kennst das vielleicht: Irgendjemand hat eine Excel-Tabelle aufbereitet, die wie im folgenden Bild aussieht und die du mit einer Pivot-Tabelle auswerten möchtest:
Ein ungeeignetes Tabellenlayout
Das Problem dabei ist, dass für die Region und den Verkäufer leider nur immer die jeweils ersten Einträge aufgeführt sind. Danach folgen lauter leere Zellen. Für eine Pivot-Tabelle also denkbar ungeeignet. Wenn es sich nur um ein paar Datensätze handelt, dann kannst du die Einträge sicherlich von Hand nach unten kopieren, so dass in jeder Zeile sowohl die Region als auch der Verkäufer steht.
Irgendwann wird es aber lästig, vor allem dann, wenn du die Daten regelmäßig in diesem Format geliefert bekommst. Für Power Query ist das jedoch ein Klacks. Laden wir die Liste also über das Menü „Daten | Aus dem Blatt“ (in älteren Versionen heißt es hier noch „Aus Tabelle/Bereich“) in den Power Query-Editor:
Laden in den Power Query-Editor
Alles was du jetzt machen musst, ist die beiden Spalten „Region“ und „Verkäufer“ zu markieren (gedrückte Strg-Taste). Anschließend wählst du über einen Rechtsklick auf einen der beiden Spaltenüberschriften aus dem Kontextmenü die Option „Ausfüllen | Nach unten“ aus:
Beide Spalten nach unten ausfüllen
Und schon sieht unsere Tabelle so aus, wie sie soll:
Die Abfrage nach der Transformation
Einfacher geht’s ja wohl nicht mehr, oder? Jetzt kannst du die Tabelle zurück nach Excel laden und mit einer Pivot-Tabelle auswerten.
Der Vorher-Nachher-Effekt
Das war’s für die heutigen Power Query-Tipps. Ich hoffe, da war auch für dich etwas dabei!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin, vielen Dank für die heutigen Power-Quickies! Das „unten ausfüllen“ konnte ich super gebrauchen!
LG Anette
Hallo Anette,
freut mich zu hören!
Schöne Grüße,
Martin
Hallo Martin,
wie immer nützliche und praxisnahe Tipps. Vielen Dank!
Eine Ergänzung vielleicht zum Power Quickie 15 – alternativ läßt sich das auch ohne Power Query lösen mit
F5 (Gehe zu) – Inhalte – Leerzellen auswählen – es werden alle Leerzellen markiert – dann in oberster Zelle Eingabe der Referenzzelle z.B. „=A2“ – dann Strg+Enter.
Führt zum gleichen Ergebnis.
Gruß
Thorsten
Hallo Thorsten,
danke für diese clevere Alternative!
Schöne Grüße,
Martin
Hallo Martín
Danke für sehr guten Tipps.
Ich habe da leider ein Problem mit nach unten füllen.
Wenn ich dies ausführe, werden die Leere Felder nicht befüllt.
Ich kann mir aber nicht erklären, warum dies nicht funktioniert.
Hast du da einen Tipp, was ich machen könnte?
Lg Michael
Hallo Michael,
das dürfte daran liegen, dass die vermeintlich leeren Felder nicht wirklich leer sind. Nur wenn die Zellen „null“ anzeigen, sind sie wirklich leer. Du könntest also mit verschiedenen Bereinigungen in Power Query die Zellen wirklich leeren:
Rechtsklick | Transformieren | Kürzen
oder
Rechtsklick | Transformieren | Bereinigen
oder
Menü Transformieren | Werte ersetzen. Das Feld „zu suchender Wert“ lässt du leer, ins Feld „Ersetzen durch“ schreibst du null.
Schöne Grüße,
Martin
Hallo Martin,
Danke für die Hilfe. Jetzt geht es.
Wirklich tolle Arbeit.
Schöne Grüße Michael