In den letzten Jahren hat sich Power Query zu einem meiner Lieblingswerkzeuge entwickelt. Dieses Tool ist einerseits relativ einfach zu erlernen. Und es ist andererseits so mächtig, dass man sich damit viele der komplizierten Formelmonster aus der Vergangenheit sparen kann. Sehr häufig wird sogar VBA-Programmierung damit überflüssig.
Wenn du diesen Blog schon länger liest, kennst du vermutlich meine Excel-Quickies. Und genau nach diesem Muster werde ich in unregelmäßigen Abständen auch kleine Tipps für Power Query vorstellen: Die Power Query Quickies, oder etwas kürzer, die Power Quickies!
Los geht es heute damit:
- Power-Quickie 1: Den Überblick über seine Abfragen behalten
- Power-Quickie 2: Eine Funktionsreferenz erstellen
- Power-Quickie 3: Abfragen automatisch aktualisieren
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 Einführungsartikel:
Einführung in Power-Query – Teil 1
Einführung in Power-Query – Teil 2
Power Quickie 1: Den Überblick behalten
Wenn man Power Query etwas intensiver nutzt und mit mehreren Abfragen arbeitet, die auch noch miteinander verbunden sind, kann man schnell mal den Überblick über die Zusammenhänge verlieren.
Dazu bietet Power Query eine sehr einfache Lösung, nämlich eine Ansicht der Abfrageabhängigkeiten. Du findest sie hier: Menü „Ansicht | Abfrageabhängigkeiten“
Abfrageabhängigkeiten anzeigen
Damit öffnet sich ein neues Fenster, in dem dein Meisterwerk in Form eines Baumdiagramms dargestellt wird.
Alle Abfragen im Baumdiagramm
Ziehe das Fenster auf die benötigte Größe (oder auf gleich auf Vollbild) und passe die Darstellung über die Optionen in der rechten unteren Ecke an.
Größe und Layout anpassen
Mit dem Symbol 1 im Bild wird das Diagramm automatisch auf die aktuelle Fenstergröße gezoomt. Über das Layout-Menü (Nr. 2 im Bild) kannst Du noch die Ausrichtung des Baums beeinflussen.
Besonders praktisch ist es, wenn man eine einzelne Abfrage innerhalb des Diagramms anklickt: Dann werden nämlich alle Abhängigkeiten für diese eine Abfrage hervorgehoben:
Einzelne Abfrageabhängigkeiten hervorheben
So wird sehr schnell deutlich, wie alles zusammenhängt, wodurch sich eine etwaige Fehlersuche sehr vereinfacht.
Power Quickie 2: Eine Funktionsreferenz erstellen
Wer mit Excel arbeitet und den Namen einer bestimmten Tabellenfunktion nicht mehr so genau weiß, kann sich einfach in der Funktionsbibliothek umsehen:
Die Excel-Funktionsbibliothek
In Power Query gibt es mit der Abfragesprache M eine Vielzahl an eigenen Funktionen. Irgendwann kommt man an den Punkt, wo die einfachen Transformationen, die man über das Menü auswählen kann, nicht mehr ausreichen. Und dann kommen die M-Funktionen ins Spiel. Eine integrierte Bibliothek zum Nachschlagen wird man aber vergeblich suchen. Mit diesem Tipp kannst du dir aber trotzdem ganz leicht ein Funktionsverzeichnis anzeigen lassen.
Erstelle einfach eine neue, leere Abfrage. Wenn du noch in Excel bis, geht das Menü das „Daten | Daten abrufen | Aus anderen Quellen | Leere Abfrage“
Eine leere Abfrage aus Excel heraus erstellen
Und falls du schon den Power Query-Editor geöffnet hast, wählst du stattdessen das Menü „Start | Neue Quelle | Andere Quellen | Leere Abfrage“
Eine leere Abfrage in Power Query erstellen
Nun klickst du einfach in die Bearbeitungszeile und tippst dort Folgendes ein:
= #shared
Nach der Bestätigung mit der Eingabetaste erzeugt Power Query eine Liste mit allen M-Funktionen:
Eine Funktionsreferenz erstellen
Nun kann man entweder direkt durch die lange Liste blättern, bis man fündig wurde. Oder man wandelt diese Liste über das Menü „Datensatztools | Konvertieren | In Tabelle“ in eine Tabelle um und kann diese dann beispielsweise über eine normale Transformation alphabetisch sortieren:
DIe Liste in eine Tabelle konvertieren
Die sortierte Funktionsreferenz
Ein Klick auf einen Eintrag in der Value-Spalte liefert dann eine Hilfe zu der gewählten Funktion, hier im Beispiel für Text.Starts:
Interaktive Funktionsbeschreibung
Das ist zwar immer noch nicht so komfortabel, wie die Funktionsbibliothek in Excel, aber immerhin ein Anfang.
Power Quickie 3: Abfragen automatisch aktualisieren
Bei manchen Auswertungen kann es wünschenswert sein, dass sofort beim Öffnen der Excel-Datei die enthaltenen Abfragen automatisch aktualisiert werden. Das kannst du für jede Abfrage individuell festlegen. Lass dir dazu zunächst über das Menü „Daten | Abfragen und Verbindungen“ den Arbeitsbereich mit allen enthaltenen Abfragen einblenden:
Den Bereich Abfragen und Verbindungen einblenden
Führe jetzt auf die gewünschte Abfrage einen Rechtsklick aus und wähle aus dem Menü den Punkt „Eigenschaften…“
Abfrageeigenschaften aufrufen
Jetzt kannst du auswählen, ob die Abfrage alle paar Minuten aktualisiert werden soll (würde ich persönlich eher davon absehen). Oder ob die Aktualisierung zumindest beim Öffnen der Datei stattfinden soll. Damit stellt man sicher, dass man zu Beginn seiner Arbeit immer die aktuellsten Daten zu Gesicht bekommt:
Die gewünschte Aktualisierung einstellen
Zusammen mit der zweiten Option (beim Öffnen der Datei) kann man noch ein weiteres Häkchen setzen „Daten vor dem Speichern des Arbeitsblatts aus dem externen Datenbereich entfernen“. Was hat es damit auf sich?
Diese Option könnte interessant werden, wenn die Datei ohnehin schon sehr groß ist und man Speicher sparen möchte. Und beim nächsten Öffnen der Datei werden dann alle Daten ohnehin wieder neu eingelesen.
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.
Sehr schön, dass Du das Thema aufgreifst. Danke. Freue mich auf die Fortsetzung.
Hallo Gerhard,
freut mich, wenn das Thema ankommt. Da wird es in Zukunft sicherlich noch einige Artikel dazu geben.
Schöne Grüße,
Martin
Sehr geehrter Herr Weiß,
wie immer ein toller Beitrag und ich bin gespannt was noch so alles zu Query kommen wird, vielen Dank schon jetzt dafür 🙂
Zwar ein anderes Thema, kann aber jedem passieren der Daten aus dem Internet per Query importiert, Lösungen gibt es viel, doch nur wenige werden in der Sprache M für Query angeboten. Zwei hatte ich schon ausprobiert, die erst hat nicht funktioniert vllt. habe ich das dalsche Excel 2019, die zweite Lösung hat funktioniert, aber nur wenn beim Import der Wert als Zahl 8 Stellen hatte und nicht 7 Stellen.
Auf der Webseite wird die Spalte als Datum angezeigt, diese enthält 5.08.2021 aber auch 15.08.2021, bei der Übernahme mach Query dieses 5082021 und 15082021. Setze ich dieses auf Datum kommt Error heraus.
Das war die Formel die der User maniweb am 20.08.2019 im Forum „clever-excel“ veröffentlich hatte
=Date.FromText(Text.Start([Datum],Number.IntegerDivide(Text.Length([Datum]),6,0)+1)&“.“&Text.Range([Datum],Number.IntegerDivide(Text.Length([Datum]),6,0)+1,2)&“.“&Text.End([Datum],2))
Leider wandelt die Formel nur Werte mit 8 Stellen um in ein Datum, aber bei den Werten mit 7 Stellen kommt Error heraus.
Kann man nun die Formel bei benutzerdefinierter Spalte doppelt hinterlerlegen und die zweite dann von 6 auf 5 ändern? Oder welche Formel muss man anwenden damit aus 5082021 und 15082021 nun ein echtes Datum wird?
Vielleicht in einem neune Beitrag zu Power Query?
Dennoch vielen Dank für eine Antwort, gerne auch an meine Mailadresse, schau aber auch hier in den Beitrag gelegenlich rein
Bleiben Sie gesund
Liebe Grüße aus Hessen vom EuroCafe
Hallo EuroCafe,
eigentlich sollte das auch komplett ohne Formeln funktionieren und hat bei meinen Tests auch geklappt: Dazu erstmal den etwaigen automatisch eingefügten Schritt „Geänderter Typ“ entfernen. Und danach die Spalte auf den Datentyp „Datum“ ändern.
Sieht dann vereinfacht so aus:
Schöne Grüße,
Martin
Danke Herr Weiß,
habe den Typ beim Impotieren im Ersten Schritt auf Text geändert und dann hat es geklappt, vergesse immer mal wieder das Query beim ersten Import versucht den Typ automatisch zu erkennen und festzulegen 🙁
Liebe Grüße vom EuroCafe
Hallo EuroCafe,
bei Bedarf lässt sich die automatische Typerkennung in PowerQuery auch ganz abstellen:
Menü „Daten | Daten abrufen | Abfrageoptionen | Global | Daten laden“ -> Spaltentypen und -überschriften für unstrukturierte Quellen niemals erkennen
Schöne Grüße,
Martin
Hallo Herr Weiß,
das sind sehr hilfreiche Tipps, vielen Dank!
Ich habe Excel-Dateien von meinem Vorgänger übernommen, der mit Power Query gearbeitet und ich finde mich gerade in das Thema ein.
Ich habe momentan das Problem, dass von meinem Vorgänger an einige ausgegebene Abfragen Spalten mit sverweis-Formeln angefügt wurden.
Diese passe ich an (z.b. auf aktuellen Monat oder aktuelle Datei). Wenn jedoch die Dateien aktualisiert werden und sich die Power Query Abfrage aktualisiert, steht auf einmal wieder die „alte“ sverweis-Formel in der Spalte, als ob ich niemals etwas an der Formel verändert habe.
Haben Sie hier einen Tipp, wie ich meine Anpassungen beibehalten kann?
Besten Dank und viele Grüße
Ina E.
Guten Abend Frau Ina, ich wollte eigentlich nach meinem Problem schauen und habe dann ihres gelesen.
Dieses Problem tritt manchmal auf, wenn man die Queryabfrage importiert, dann eine Spalte zur Tabelle hinzufügt und dort dann eine Formel ein gibt. Öffnet man nun die Datei erneut und verändert man die Formel und speichert diese, wird manchmal bei nächsten öffnen die alte Formel aus der Historie wieder eingefügt, nach dem man Query aktualisiert hat und den Import gestartet hatte.
Bei mir war die Lösung folgende: Ich habe die Tabellengröße auf die Queryabfrage reduziert, in der angrenzenden Spalte nun die neue Formel eingefügt und bis ans Ende kopiert. Datei gespeichert und geschlossen und dann wieder geöfnet und den Tabellenbereich nun auf die Nachbarspalte erweitert. Wieder gespeichert, geschlossen und erneut geöffnet, nach der Aktualisierung blieb die neue Formel erhalten.
Was ich aber nicht erklären kann, warum Excel das macht, mein EDV-Leiter hatte nur mal mitgeteilt, das in einer Exceldatei teilweise informationen mitgespeichert werden, die dann eben nicht neu geladen werden, wenn man die Datei erneut öffnet. So kann man sogar über eine Formel auf einen Verzeichnisbereich zugreifen bei den man die Berechtigung hat, der Personenkreis der mit dr Auswertung aber arbeitet keine Berechtigung benötigt, weil eben die Daten in der Datei gespeichert werden und nicht erneut geladen bzw. aktualisiert werden. Liebe Grüße und einfach mal ausprobieren 🙂 vom EuroCafe
Guten Tag EuroCafe,
herzlichen Dank für den Ratschlag, es hat bestens funktioniert! Man lernt nie aus 🙂
Ich bedanke mich nochmal und wünsche schon mal ein sonniges Wochenende!
Hallo Ina,
der Leser EuroCafe hat ja schon auf Ihre Frage geantwortet, ich möchte nur folgende Ergänzung dazu anbringen:
Die Daten einer PowerQuery-Abfrage werden ja immer in eine formatierte Tabelle geladen, welche eine gewisse „Intelligenz“ mitbringt. Dazu gehört zum Beispiel, dass einmal eingegeben Formeln im Hintergrund gespeichert werden, wie auch EuroCafe schon geschrieben hat. Wenn Sie nun Ihre neue Formel eingeben bzw. eine vorhandene ändern und mit der Eingabetaste bestätigen, erscheint normalerweise ein kleines Symbol neben der rechten unteren Zellenecke. Dort bekommen Sie dann die Möglichkeit angeboten, die Formel in alle Zellen zu übernehmen und damit auch in die interne Historie.
Vielleicht hilft das ein wenig weiter.
Schöne Grüße,
Martin
Guten Tag Herr Weiß,
der Tipp von EuroCafe hat das Problem gelöst, aber der Ratschlag mit dem Symbol behalte ich ebenfalls im Hinterkopf.
Vielen Dank und ein schönes Wochenende! VG Ina