Excel power tutorial‌-abfrage (abrufen und transformieren)

Wenn Sie zu den Menschen gehören, die viel mit Daten arbeiten, können Sie jeder sein ( Buchhalter , HR, Datenanalyst usw.), PowerQuery kann Ihr leistungsstarkes Werkzeug sein.

Um es auf den Punkt zu bringen: Power Query ist eine der fortgeschrittenen Excel-Kenntnisse , die Sie erlernen müssen. In diesem Tutorial werden Sie PowerQuery im Detail erkunden und lernen, wie Sie damit Daten transformieren.

Lass uns anfangen.

Was ist Excel Power Query?

Power Query ist ein Excel-Add-In, das Sie für ETL verwenden können. Das bedeutet, dass Sie Daten aus verschiedenen Quellen extrahieren , transformieren und dann in die Tabelle laden können. Man kann sagen, dass POWER QUERY eine Datenbereinigungsmaschine ist, weil es alle Optionen zur Datentransformation bietet. Es ist in Echtzeit und zeichnet jeden Ihrer Schritte auf.

Warum sollten Sie Power Query verwenden (Vorteile)?

Wenn Sie diese Frage im Kopf haben, ist hier meine Antwort für Sie:

• Verschiedene Datenquellen : Sie können Daten aus verschiedenen Datenquellen wie CSV , TXT, JSON usw. in einen leistungsstarken Abfrageeditor laden.
• Daten einfach transformieren: Normalerweise verwenden Sie für Datentransformationen Formeln und Pivot-Tabellen, aber mit POWER QUERY können Sie viel mit nur wenigen Klicks erledigen.
• Es ist in Echtzeit: Schreiben Sie eine Abfrage einmal und Sie können sie jedes Mal aktualisieren, wenn sich die Daten ändern, und die neuen Daten, die Sie aktualisiert haben, werden umgewandelt.

Lassen Sie mich ein Beispiel nennen:

Stellen Sie sich vor, Sie haben 100 Excel-Dateien mit Daten aus 100 Städten und Ihr Chef möchte nun, dass Sie einen Bericht mit allen Daten aus diesen 100 Dateien erstellen. OKAY, wenn Sie sich dafür entscheiden, jede Datei manuell zu öffnen und die Daten aus diesen Dateien zu kopieren und einzufügen, und Sie dafür mindestens eine Stunde benötigen.

Aber mit Power Query können Sie das in wenigen Minuten erledigen. Sind Sie aufgeregt? GUT.

Später in diesem Tutorial erfahren Sie anhand vieler Beispiele, wie Sie Power Query verwenden. Zunächst müssen Sie jedoch das Konzept verstehen.

Das Konzept des Strombedarfs

Um die Leistungsabfrage zu erlernen, müssen Sie das Konzept verstehen, das in drei Schritten funktioniert:

1. Daten abrufen

Mit Power Query können Sie Daten aus verschiedenen Quellen wie dem Web, CSV, Textdateien, mehreren Arbeitsmappen aus einem Ordner und vielen anderen Quellen abrufen, in denen wir Daten speichern können.

2. Transformieren Sie die Daten

Nachdem Sie Daten in die Power-Abfrage eingegeben haben, stehen Ihnen eine ganze Reihe von Optionen zur Verfügung, mit denen Sie sie umwandeln und bereinigen können. Es erstellt Abfragen für alle Schritte, die Sie ausführen (in einer Reihenfolge, Schritt für Schritt).

3. Daten laden

Vom erweiterten Abfrageeditor aus können Sie die transformierten Daten in das Arbeitsblatt laden oder direkt eine PivotTable oder ein PivotChart erstellen oder eine reine Datenverbindung erstellen.

Wo ist Power Query (Installation)?

Unten sehen Sie, wie Sie Power Query Access in verschiedenen Versionen von Microsoft Excel installieren.

Excel 2007

Wenn Sie Excel 2007 verwenden, ist PQ für diese Version leider nicht verfügbar . Sie müssen daher auf die latest version von Excel aktualisieren (Excel für Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

Excel 2010 und Excel 2013

Für 2010 und 2013 müssen Sie ein Add-in separat installieren , das Sie über diesen Link herunterladen können. Nach der Installation erhalten Sie eine neue Registerkarte im Excel-Menüband wie unten:

• Laden Sie zunächst das Add-In hier herunter (offizielle Microsoft-Website).
• Sobald die Datei heruntergeladen ist, öffnen Sie sie und befolgen Sie die Anweisungen.
• Danach erhalten Sie automatisch die Registerkarte „Power Query“ in Ihrem Excel-Menüband.

Wenn diese Registerkarte „POWER QUERY“ aus irgendeinem Grund nicht angezeigt wird, besteht kein Grund zur Sorge. Sie können es mit der Option COM-Add-Ins hinzufügen.

• Gehen Sie zur Registerkarte Datei ➜ Optionen ➜ Add-Ins.

• Wählen Sie in den „Add-In“-Optionen „COM-Add-Ins“ und klicken Sie auf „GO“.
• Aktivieren Sie anschließend das Kontrollkästchen „Microsoft Power Query für Excel“.

• Klicken Sie am Ende auf OK.

Excel 2016, 2019, Office 365

Wenn Sie Excel 2016 oder Excel 2019 verwenden oder ein OFFICE 365-Abonnement haben, ist es bereits auf der Registerkarte „Daten“ als Gruppe mit dem Namen „GET & TRANSFORM“ vorhanden (der Name gefällt Ihnen, oder?).

Excel-Mac

Wenn Sie Excel auf einem Mac verwenden, gibt es dafür leider kein Power-Query-Add-In und Sie können nur eine vorhandene Abfrage aktualisieren , aber keine neue erstellen oder gar eine Abfrage bearbeiten ( LINK ).

Power Query-Editor

Power Query verfügt über einen eigenen Editor, in dem Sie die Daten abrufen, alle Schritte zum Erstellen von Abfragen ausführen und diese dann in die Tabelle laden können. Um den Power Query-Editor zu öffnen, müssen Sie zur Registerkarte „Daten“ und unter „Abrufen und transformieren“ ➜ „Daten abrufen“ ➜ „Power Query-Editor starten“ gehen.

Unten sehen Sie eine erste Vorschau des Editors, die Sie erhalten, wenn Sie ihn öffnen.

Sehen wir uns nun jeden Abschnitt im Detail an:

1. Band

Schauen wir uns alle verfügbaren Registerkarten an:

• Datei: Auf der Registerkarte „Datei“ können Sie die Daten laden, den Editor entfernen und die Abfrageparameter öffnen.
• Home : Auf der Registerkarte HOME haben Sie Optionen zum Verwalten geladener Daten, z. B. zum Löschen und Verschieben von Spalten und Zeilen.
• Transformieren : Diese Registerkarte enthält alle Optionen, die Sie zum Transformieren und Bereinigen von Daten benötigen, z. B. Spalten zusammenführen, transponieren usw.
• Spalte hinzufügen : Hier haben Sie die Möglichkeit, neue Spalten zu den Daten hinzuzufügen, die Sie im erweiterten Editor haben.
• Ansicht : Auf dieser Registerkarte können Sie Änderungen an der Ansicht des erweiterten Abfrageeditors und den geladenen Daten vornehmen.

2. Schritte angewendet

Auf der rechten Seite des Editors befindet sich ein Bereich mit Abfrageeinstellungen, der den Abfragenamen und alle in einer Sequenz angewendeten Schritte enthält.

Wenn Sie mit der rechten Maustaste auf einen Schritt klicken, wird eine Liste mit Optionen angezeigt, die Sie ausführen können, z. B. umbenennen, löschen, bearbeiten, nach oben oder unten verschieben usw. und wenn Sie auf einen Schritt klicken, führt Sie der Editor zu der in diesem Schritt durchgeführten Transformation.

Schauen Sie unten, wo Sie alle fünf Schritte insgesamt angewendet haben. Wenn ich auf den vierten Schritt klicke, komme ich zur Transformation in Schritt vier, wo sich die Spaltennamen nicht geändert haben.

3. Fragen

Im Abfragebereich auf der linken Seite werden alle Abfragen aufgelistet, die Sie derzeit in der Arbeitsmappe haben. Im Grunde ist es ein Ort, an dem Sie alle Abfragen verwalten können.

Wenn Sie mit der rechten Maustaste auf einen Abfragenamen klicken, werden alle verfügbaren Optionen angezeigt (Kopieren, Löschen, Duplizieren usw.).

Sie können eine neue Abfrage auch erstellen, indem Sie einfach mit der rechten Maustaste auf den leeren Bereich im Abfragebereich klicken und dann die Option für die Datenquelle auswählen.

4. Formelleiste

Wie gesagt, jedes Mal, wenn Sie einen Schritt im Editor anwenden, wird M-Code für diesen Schritt generiert, und Sie können diesen Code in der Bearbeitungsleiste sehen. Sie können einfach auf die Bearbeitungsleiste klicken, um den Code zu bearbeiten.

Sobald Sie gelernt haben, wie man M-Code verwendet, können Sie auch einen Schritt erstellen, indem Sie den Code schreiben und einfach auf die Schaltfläche „FX“ klicken, um einen benutzerdefinierten Schritt einzugeben.

5. Datenübersicht

Der Datenvorschaubereich sieht aus wie eine Excel-Tabelle, unterscheidet sich jedoch geringfügig von einer normalen Tabellenkalkulation, in der Sie eine Zelle oder Daten direkt bearbeiten können. Wenn Sie Daten in den Editor laden (das machen wir gleich), werden alle Spalten mit den Überschriften mit den Spaltennamen und dann die Zeilen mit den Daten angezeigt.

Oben in jeder Spalte können Sie den Datentyp der Daten der Spalte sehen. Wenn Sie Daten in den Editor laden, wendet Advanced Query (fast immer) automatisch den richtigen Datentyp auf jede Spalte an.

Sie können auf die Schaltfläche oben links in der Spaltenüberschrift klicken, um den auf die Spalte angewendeten Datentyp zu ändern. Es enthält eine Liste aller Datentypen, aus denen Sie schöpfen können.

Und auf der linken Seite der Spaltenüberschrift befindet sich die Filterschaltfläche, mit der Sie die Spaltenwerte filtern können. Hinweis: Wenn Sie die Werte einer Spalte filtern, betrachtet die erweiterte Abfrage dies als einen einzelnen Schritt und listet ihn unter „Angewandte Schritte“ auf.

Wenn Sie mit der rechten Maustaste auf die Spaltenüberschrift klicken, sehen Sie, dass es ein Menü mit einer Liste von Optionen gibt, mit denen Sie die Daten transformieren und eine der darin gespeicherten Optionen und PQ als Schritt im angewendeten Prozess verwenden können. nicht.

Datenquellen für Power Query

Das Beste an Power Query ist, dass Sie die Möglichkeit haben, Daten aus mehreren Quellen abzurufen, diese Daten umzuwandeln und sie dann in die Tabelle zu laden. Wenn Sie in GET & TRANSFORM auf Daten abrufen klicken, wird die vollständige Liste der Datenquellen angezeigt, die Sie in den Editor laden können.

Schauen wir uns nun einige der Datenquellen an:

• Aus Tabelle/Bereich : Mit dieser Option können Sie Daten direkt aus dem aktiven Arbeitsblatt in den erweiterten Abfrageeditor laden.
• Aus Arbeitsmappe : Aus einer anderen Arbeitsmappe, die Sie auf Ihrem Computer haben. Sie müssen diese Datei nur über ein geöffnetes Dialogfeld finden und die Daten werden automatisch aus dieser Datei abgerufen.
• Aus Text/CSV : Holen Sie sich Daten aus einer Textdatei oder einer durch Kommas getrennten Datei und laden Sie sie dann in die Tabelle.
• Aus Ordner : Es übernimmt alle Dateien aus dem Ordner und unterstützt Daten in einem leistungsstarken Abfrageeditor. (Siehe Folgendes: Excel-Dateien aus einem Ordner kombinieren).
• Aus dem Internet : Mit dieser Option erhalten Sie Daten von einer Webadresse. Stellen Sie sich vor, Sie haben eine Datei, die im Internet gespeichert ist, oder Sie haben eine Webseite, von der Sie die Daten abrufen müssen.

So laden Sie Daten in den Power Query Editor

Lassen Sie uns nun lernen, wie Sie Daten in den leistungsstarken Abfrageeditor laden. Hier finden Sie eine Liste der Schülernamen und ihrer Ergebnisse ( LINK ).

Sie laden die Daten direkt aus der Tabelle, daher müssen Sie zuerst die Datei öffnen und dann die folgenden Schritte ausführen:

• Wenden Sie zunächst eine Excel-Tabelle auf die Daten an (selbst wenn Sie dies nicht tun, erledigt Excel dies für Sie, bevor die Daten in den PQ-Editor geladen werden).
• Wählen Sie nun eine Zelle in der Tabelle aus und klicken Sie auf „Aus Tabelle/Bereich“ (Registerkarte „Daten abrufen und transformieren“).

• Sobald Sie auf die Schaltfläche klicken, bestätigt Excel den Datenbereich, um eine Excel-Tabelle darauf anzuwenden.

• Zu diesem Zeitpunkt befinden sich die Daten im Power Query-Editor und sie sehen in etwa wie unten aus.

• Hier sieht man:
  • In der Bearbeitungsleiste hat PQ den M-Code für die Tabelle generiert, die Sie gerade in den Editor geladen haben.
  • Auf der linken Seite des Editors befindet sich der Abfragebereich, in dem sich die Liste der Abfragen befindet.
  • Auf der rechten Seite finden Sie in den Abfrageeinstellungen den Abschnitt „Angewandte Schritte“, in dem alle Schritte aufgelistet sind. Hinweis: Sie müssen davon ausgehen, dass Sie keinen „Modified Type“ ausgeführt haben, aber es gibt dort einen Schritt namens „Modified Type“. Lassen Sie mich Ihnen die INTELLIGENZ von POWER QUERY erklären. Wenn Sie Daten in den Editor laden, prüft dieser automatisch die richtigen Datentypen für alle Spalten und wendet sie an.

Power Query-Beispiele (Tipps und Tricks)

Sie können lernen, wie Sie einige der grundlegenden Aufgaben erledigen, die Sie normalerweise mit Funktionsformeln in Excel erledigen, aber mit PowerQuery können Sie dies mit nur wenigen Klicks erledigen:

1. Werte ersetzen

Sie haben eine Werteliste und möchten einen oder mehrere Werte durch etwas anderes ersetzen. Nun, mit Hilfe von Power Query können Sie im Handumdrehen eine Abfrage erstellen und diese Werte ersetzen.

In der Liste unten müssen Sie meinen Namen „Puneet“ durch „Punit“ ersetzen.

• Bearbeiten Sie zunächst die Liste im erweiterten Abfrageeditor.
• Gehen Sie anschließend im Power Query-Editor zur Registerkarte „Transformieren“ und klicken Sie auf „Werte ersetzen“.

• Geben Sie nun unter „Zu suchender Wert“ „Puneet“ und unter „Ersetzen durch“ „Punit“ ein und klicken Sie anschließend auf „OK“.

• Sobald Sie auf „OK“ klicken, werden alle Werte durch die neuen Werte ersetzt. Klicken Sie nun auf „Schließen und laden“, um die Daten in die Tabelle zu laden.

2. Daten sortieren

Genau wie beim normalen Sortieren können Sie Daten mit PowerQuery sortieren und ich verwende dieselbe Namensliste, die Sie im obigen Beispiel verwendet haben.

• Laden Sie zunächst die Daten in den leistungsstarken Abfrageeditor.
• Auf der Registerkarte „Startseite“ stehen Ihnen zwei Sortierschaltflächen zur Verfügung (Aufsteigend und Absteigend).
• Klicken Sie zum Sortieren auf eine dieser Schaltflächen.

3. Spalten löschen

Nehmen wir an, Sie haben irgendwo Daten und müssen einige Spalten daraus entfernen. Der Punkt ist, dass Sie diese Spalten jedes Mal löschen müssen, wenn Sie neue Daten hinzufügen, oder? Aber Power Query kann dafür sorgen.

• Wählen Sie die Spalte oder mehrere Spalten aus, die Sie löschen möchten.

• Klicken Sie nun mit der rechten Maustaste und wählen Sie „Löschen“.

Kurzer Tipp: Es gibt auch die Option „Andere Spalten entfernen“, mit der Sie alle nicht ausgewählten Spalten löschen können.

4. Spalten teilen

Genau wie bei der Option „Text in Spalte“ gibt es in der Power-Abfrage „Spalte teilen“. Lassen Sie mich Ihnen sagen, wie es funktioniert.

• Wählen Sie die Spalte aus und gehen Sie zur Registerkarte „Startseite“ ➜ „Transformieren“ ➜ „Spalte teilen“ ➜ „Nach Trennzeichen“.

• Wählen Sie den Benutzernamen aus der Dropdown-Liste aus und geben Sie „–“ ein.
• Hier haben Sie nun drei verschiedene Möglichkeiten, eine Spalte aufzuteilen.
  • Trennzeichen ganz links
  • Trennzeichen ganz rechts
  • Jedes Vorkommen des Trennzeichens

Wenn Sie nur ein Trennzeichen in einer Zelle haben, funktionieren alle drei gleich, wenn Sie jedoch mehr als ein Trennzeichen haben, sollten Sie entsprechend wählen.

5. Benennen Sie eine Spalte um

Sie können eine Spalte einfach umbenennen, indem Sie mit der rechten Maustaste klicken und dann auf „Umbenennen“ klicken.

Kurzer Tipp : Nehmen wir an, Sie möchten eine Spalte umbenennen und jemand anderes benennt sie versehentlich um. Sie können diesen Namen mit einem einzigen Klick wiederherstellen.

6. Spalte duplizieren

In Power Query gibt es eine einfache Option zum Erstellen einer doppelten Spalte. Klicken Sie einfach mit der rechten Maustaste auf die Spalte, für die Sie eine doppelte Spalte erstellen möchten, und klicken Sie dann auf „Spalte duplizieren“.

7. Spalte oder Zeile vertauschen

Bei der Potenzabfrage ist die Umsetzung ein Kinderspiel. Ja, nur ein Klick.

• Nachdem Sie die Daten in den Advanced Query Editor geladen haben, müssen Sie nur noch die Spalte(n) oder Zeile(n) auswählen.
• Gehen Sie zur Registerkarte „Transformieren“ ➜ Tabelle ➜ Transponieren.

8. Fehler ersetzen/beseitigen

Normalerweise können Sie zum Ersetzen oder Entfernen von Fehlern in Excel die Option „Suchen und Ersetzen“ oder VBA-Code verwenden. Aber in PowerQuery ist es viel einfacher. Sehen Sie sich die Spalte unten an, in der Sie Fehler finden und diese entfernen und ersetzen können.

Wenn Sie mit der rechten Maustaste auf die Spalte klicken, stehen Ihnen beide Optionen zur Verfügung.

• Fehler ersetzen
• Fehler beseitigen

9. Datentyp ändern

Sie haben Daten in einer Spalte, diese haben jedoch nicht das richtige Format. Sie müssen also jedes Mal das Format ändern.

• Bearbeiten Sie zunächst die Daten im leistungsstarken Abfrageeditor.
• Wählen Sie anschließend die Spalte aus und wechseln Sie zur Registerkarte „Transformieren“.
• Wählen Sie nun unter Datentyp „Datum“ als Typ aus.

10. Spalte aus Beispielen hinzufügen

In der erweiterten Abfrage besteht die Möglichkeit, eine Beispielspalte hinzuzufügen, die eigentlich kein Beispiel ist, das sich auf die aktuelle Spalte bezieht.

Lassen Sie mich Ihnen ein Beispiel geben:

Stellen Sie sich vor, Sie benötigen Tagesnamen aus einer Datumsspalte. Anstatt eine Formel oder eine andere mögliche Option zu verwenden, können Sie „Spalte aus Beispielen hinzufügen“ verwenden.

So geht’s:

• Klicken Sie mit der rechten Maustaste auf eine Spalte und klicken Sie auf „Spalte aus Beispielen hinzufügen“.

• Hier erhalten Sie eine leere Spalte. Klicken Sie auf die erste Zelle in der Spalte, um eine Liste der Werte zu erhalten, die Sie einfügen können.

• Wählen Sie „Name des Wochentags ab Datum“ und klicken Sie auf „OK“.

Boom! Ihre neue Kolumne ist da.

11. Boxen wechseln

Sie haben die folgenden Möglichkeiten, die Groß-/Kleinschreibung von Texten in PowerQuery zu ändern.

• Winzig
• Großbuchstaben
• Schreiben Sie jedes Wort groß

Sie können dies tun, indem Sie mit der rechten Maustaste auf eine Spalte klicken und eine der drei oben genannten Optionen auswählen. Oder gehen Sie zur Registerkarte „Transformieren“ ➜ Textspalte ➜ Format.

12. Trimmen und reinigen

Um Daten zu löschen oder unerwünschte Leerzeichen zu entfernen , können Sie in PowerQuery die Optionen TRIM und CLEAN verwenden. Die Schritte sind einfach:

• Klicken Sie mit der rechten Maustaste auf eine Spalte oder wählen Sie alle Spalten aus, wenn Sie mehrere Spalten haben.
• Gehen Sie zur Registerkarte „Transformieren“ ➜ Textspalte ➜ Format.
  1. TRIM: um Leerzeichen am Ende und Anfang einer Zelle zu entfernen.
  2. CLEAN: um nicht druckbare Zeichen aus einer Zelle zu entfernen.

13. Fügen Sie ein Präfix/Suffix hinzu

Sie haben also eine Werteliste und möchten aus dieser Liste in jede Zelle ein Präfix/Suffix hinzufügen. In Excel können Sie die Verkettungsmethode verwenden, in PowerQuery gibt es jedoch eine einfach zu verwendende Option für beide.

• Wählen Sie zunächst die Spalte aus, in der Sie ein Präfix/Suffix hinzufügen möchten.
• Gehen Sie dann zur Registerkarte „Transformieren“ ➜ Textspalte ➜ Format ➜ Präfix hinzufügen/Suffix hinzufügen.

• Sobald Sie auf eine der Optionen klicken, wird ein Dialogfeld zur Texteingabe angezeigt.

• Klicken Sie nach der Eingabe des Textes auf OK.

14. Werte extrahieren

Wenn Sie ein Formelfreak sind, stimmen Sie mir sicher zu, dass das Extrahieren von Text oder Zahlen aus einer Zelle die Kombination verschiedener Funktionen erfordert. Aber PowerQuery hat viele dieser Probleme gelöst. Sie haben sieben Möglichkeiten, Werte aus einer Zelle zu extrahieren.

15. Nur das Datum oder die Uhrzeit

Es kommt oft vor, dass Sie Datum und Uhrzeit beide in einer Zelle haben, aber eines davon benötigen.

• Wählen Sie die Spalte aus, in der Sie das kombinierte Datum und die Uhrzeit haben.
• Falls Sie es wollen:
  • Datum : Rechtsklick ➜ Transformieren ➜ Nur Datum.
  • Zeit : Rechtsklick ➜ Transformieren ➜ Nur Zeit.

16. Kombinieren Sie Datum und Uhrzeit

Jetzt wissen Sie, wie Sie Datum und Uhrzeit trennen. Aber als nächstes müssen Sie wissen, wie man sie kombiniert.

• Wählen Sie zunächst die Datumsspalte aus und klicken Sie auf die Option „Nur Datum“.
• Wählen Sie anschließend beide Spalten (Datum und Uhrzeit) aus und gehen Sie zur Registerkarte „Transformieren“. Gehen Sie in der Gruppe „Datums- und Uhrzeitspalte“ zu „Datum“ und klicken Sie auf „Datum und Uhrzeit kombinieren“.

17. Gerundete Zahlen

Hier sind die folgenden Optionen, die Ihnen zum Runden von Zahlen zur Verfügung stehen.

• Abrunden: Eine Zahl abrunden.
• Runden: Eine Zahl runden.
• Rundung: Sie können wählen, wie weit Sie meine Dezimalstellen runden möchten.

Hier sind die Schritte:

• Wählen Sie die Spalte aus und klicken Sie mit der rechten Maustaste auf ➜ Transformieren ➜ Runden .
  1. Abrunden: Eine Zahl abrunden.
  2. Runden: Eine Zahl runden.
  3. Rundung: Sie können wählen, wie weit Sie meine Dezimalstellen runden möchten.

Hinweis: Wenn Sie die Option „#3 Runde“ auswählen, müssen Sie die Anzahl der zu rundenden Dezimalstellen eingeben.

18. Berechnungen

Es gibt Optionen, mit denen Sie Berechnungen durchführen können (viele davon). Sie finden alle diese Optionen auf der Registerkarte „Transformieren“ (in der Gruppe „Numerische Spalte“).

• Basic
• Statistiken
• Wissenschaftler
• Trigonometrie
• Runden
• Information

Um eine dieser Berechnungen durchzuführen, müssen Sie die Spalte und dann die Option auswählen.

19. Gruppieren nach

Nehmen wir an, Sie haben einen großen Datensatz und möchten eine Übersichtstabelle erstellen. Folgendes müssen Sie tun:

• Klicken Sie auf der Registerkarte „Transformieren“ auf die Schaltfläche „Gruppieren nach“ und Sie erhalten ein Dialogfeld.

• Wählen Sie nun in diesem Dialogfeld die Spalte aus, mit der Sie gruppieren möchten. Fügen Sie anschließend einen Namen hinzu, wählen Sie die Operation und die Spalte aus, in der Sie Werte haben.

• Klicken Sie am Ende auf OK.

Hinweis: In der Option „Gruppieren nach“ gibt es auch erweiterte Optionen, mit denen Sie eine mehrstufige Gruppentabelle erstellen können.

20. Negative Werte entfernen

In einem meiner Blog-Beiträge habe ich sieben Methoden zum Entfernen negativer Vorzeichen aufgelistet und Power Query ist eine davon. Klicken Sie mit der rechten Maustaste auf eine Spalte, gehen Sie zur Option „Transformieren“ und klicken Sie dann auf „Absoluter Wert“.

Dadurch werden sofort alle negativen Vorzeichen aus den Werten entfernt.

So laden Sie Daten in die Tabelle

Sobald Sie Ihre Daten transformiert haben, können Sie sie in die Tabelle laden und für weitere Analysen verwenden. Auf der Registerkarte „Startseite“ gibt es eine Schaltfläche namens „Schließen und laden“. Wenn Sie darauf klicken, wird eine Dropdown-Liste angezeigt, die zusätzliche Optionen bietet:

• Schließen und laden
• Schließen und einfüllen

• Sobald Sie auf die Schaltfläche klicken, werden die folgenden Optionen angezeigt:

• Wählen Sie aus, wie Sie diese Daten in Ihrer Tabelle anzeigen möchten.
  • Malerei
  • Pivot-Tabellenbericht:
  • Tischdrehpunkt
  • Erstellen Sie nur eine Verbindung
  • Wo möchten Sie die Daten ablegen?
    • Vorhandenes Arbeitsblatt
    • Neues Arbeitsblatt.
  • Fügen Sie diese Daten dem Datenmodell hinzu.
• Wählen Sie einfach die Tabellenoption und das neue Arbeitsblatt aus, lassen Sie das Datenmodell deaktiviert und klicken Sie auf OK.

• Sobald Sie auf „OK“ klicken, wird ein neues Arbeitsblatt mit den Daten hinzugefügt.

Weitere Beispiele zum Lernen

Eine Abfrage automatisch aktualisieren

Von allen Beispielen, die ich hier angeführt habe, ist dies das wichtigste. Wenn Sie eine Abfrage erstellen, können Sie diese automatisch aktualisieren (Sie können einen Timer festlegen).

Und hier sind die Schritte:

• Klicken Sie auf der Registerkarte „Daten“ auf „Abfragen und Verbindungen“ und Sie erhalten den Bereich „Abfragen und Verbindungen“ auf der rechten Seite des Fensters.

• Klicken Sie nun mit der rechten Maustaste auf die Abfrage, aktivieren Sie „Alle aktualisieren“ und geben Sie die Minuten ein.

So verwenden Sie eine Formel und Funktion in Power Query

Genauso wie Sie Funktionen und Formeln in einer Excel-Tabelle verwenden können, verfügt Advanced Query über eine eigene Liste mit Funktionen, die Sie verwenden können. Die Grundlagen von Funktionen und Formeln in PowerQuery sind dieselben wie die Tabellenkalkulationsfunktionen von Excel.

In PQ müssen Sie eine neue benutzerdefinierte Spalte hinzufügen, um eine Funktion oder Formel hinzuzufügen.

Nehmen wir ein Beispiel: In den Daten unten (bereits im PQ-Editor) haben Sie den Vor- und Nachnamen ( DOWNLOAD-LINK ) .

Stellen Sie sich vor, Sie müssten die beiden Namen zusammenführen und eine Spalte für den vollständigen Namen erstellen. In diesem Fall können Sie eine einfache Formel eingeben, um die Namen der beiden Spalten zu verketten.

• Gehen Sie zunächst zur Registerkarte „Spalte hinzufügen“ und klicken Sie auf „Benutzerdefinierte Spalte“.

• Geben Sie nun im Dialogfeld „Benutzerdefinierte Spalte“ den Namen der neuen Spalte „Vollständiger Name“ oder einen beliebigen Namen für die neue Spalte ein.

• • In der benutzerdefinierten Spaltenformel müssen Sie die Formel eingeben. Geben Sie also die folgende Formel ein :

 [First Name]&" "&[Last Name] 

• Wenn Sie eine Formel in die „Benutzerdefinierte Spaltenformel“ eingeben, überprüft PQ die von Ihnen eingegebene Formel und zeigt die Meldung „Es wurden keine Syntaxfehler erkannt“ an. Wenn ein Fehler vorliegt, wird basierend auf der Art des Fehlers eine Fehlermeldung angezeigt.
• Sobald Sie die Formel eingegeben haben und keine Fehler vorliegen, klicken Sie einfach auf OK.
• Sie haben nun am Ende der Daten eine neue Spalte, die Werte aus zwei Spalten (Vorname und Nachname) enthält.

So verwenden Sie eine Funktion in Power Query

Ebenso können Sie beim Hinzufügen einer benutzerdefinierten Spalte auch eine Funktion verwenden, und Power Query verfügt über eine riesige Liste an Funktionen, die Sie verwenden können.

Lassen Sie uns anhand eines einfachen Beispiels verstehen, wie eine Funktion verwendet wird. Ich setze das obige Beispiel fort, in dem wir eine neue Spalte mit Vornamen und Nachnamen hinzugefügt haben.

Jetzt müssen Sie jedoch den vollständigen Namenstext in dieser Spalte in Großbuchstaben umwandeln. Die Funktion, die Sie verwenden können, ist „ Text.Upper “ . Wie der Name schon sagt, wandelt es Text in Großbuchstaben um.

• Gehen Sie zunächst zur Registerkarte „Spalte hinzufügen“ und klicken Sie auf die benutzerdefinierte Spalte.

• • Geben Sie nun im Dialogfeld „Benutzerdefinierte Spalte“ den Spaltennamen und die Formel unten in das Feld „Benutzerdefinierte Spaltenformel“ ein:

 Text.Upper([Full Name]) 

• Und wenn Sie auf „OK“ klicken, wird eine neue Spalte mit allen Namen in Großbuchstaben erstellt.
• Als nächstes löschen Sie die alte Spalte und benennen die neue Spalte um. Klicken Sie also mit der rechten Maustaste auf die erste Spalte und wählen Sie Löschen.

• Benennen Sie am Ende die neue Spalte in „Vollständiger Name“ um.

Es gibt insgesamt 700 Funktionen, die Sie in Power Query beim Hinzufügen einer neuen Spalte verwenden können. Hier finden Sie die vollständige Liste dieser Funktionen von Microsoft . Sehen Sie sich diese an.

So ändern Sie eine Abfrage in PQ

Wenn Sie Änderungen an der Abfrage vornehmen möchten, die sich bereits in Ihrer Arbeitsmappe befindet, können Sie sie einfach bearbeiten und dann diese Änderungen vornehmen. Auf der Registerkarte „Daten“ gibt es eine Schaltfläche mit dem Namen „Abfragen und Verbindungen“.

Wenn Sie auf diese Schaltfläche klicken, wird auf der rechten Seite ein Bereich geöffnet, in dem alle Abfragen aufgelistet sind, die Sie in der aktuellen Arbeitsmappe haben.

Sie können mit der rechten Maustaste auf den Abfragenamen klicken und „Bearbeiten“ auswählen. Sie gelangen dann in den leistungsstarken Abfrageeditor, um ihn zu bearbeiten.

Wenn Sie eine Abfrage bearbeiten, können Sie sehen, dass alle Schritte, die Sie zuvor ausgeführt haben, unter „Angewandte Schritte“ aufgelistet sind, die Sie auch bearbeiten oder neue Schritte ausführen können.

Und wenn Sie mit Ihren Änderungen fertig sind, können Sie einfach auf die Schaltfläche „Schließen und Laden“ klicken.

Verbindungen exportieren und importieren

Wenn Sie über eine Verbindung verfügen, die Sie für eine Abfrage verwendet haben, und diese Verbindung nun mit jemand anderem teilen möchten, können Sie diese Verbindung als ODC-Datei exportieren.

In der Abfragetabelle gibt es eine Schaltfläche namens „Verbindung exportieren“ . Wenn Sie darauf klicken, können Sie die Verbindung dieser Abfrage in Ihrem System speichern.

Und wenn Sie eine Verbindung importieren möchten, die von jemand anderem geteilt wird, können Sie einfach zur Registerkarte „Daten“ gehen und im Bereich „Get & Transform“ auf „Existing Connections“ klicken .

Klicken Sie dann auf die Schaltfläche „Nach mehr suchen“, um die für Sie freigegebene Verbindungsdatei zu finden und in Ihre Arbeitsmappe zu importieren.

PowerQuery-Sprache (M-Code)

Wie ich bereits erwähnt habe, generiert PowerQuery für jeden Schritt, den Sie ausführen, einen Code (im Backend), der M-Code genannt wird. Auf der Registerkarte „Startseite“ gibt es eine Schaltfläche namens „ Erweiterter Editor“ , mit der Sie den Code anzeigen können.

Und wenn Sie auf den erweiterten Editor klicken, wird Ihnen der Code-Editor angezeigt und dieser Code sieht so aus:

M unterscheidet zwischen Groß- und Kleinschreibung und verwendet wie alle anderen Sprachen Variablen und Ausdrücke. Die Grundstruktur des Codes sieht wie folgt aus, wobei der Code mit dem LET-Ausdruck beginnt.

In diesem Code haben wir zwei Variablen und die darauf eingestellten Werte. Um den Wert zu erhalten, wurde schließlich der IN-Ausdruck verwendet. Wenn Sie nun auf „OK“ klicken, wird im Ergebnis der der Variablen „Variablenname“ zugewiesene Wert zurückgegeben.

Sehen Sie sich diese Ressource an, um mehr über Power Query Language zu erfahren.

Am Ende

Sie können es sich nicht leisten, POWER QUERY zu vermeiden. Wenn Sie so denken, können viele Dinge, die wir mit Excel-Funktionen oder VBA-Codes machen, damit automatisiert werden, und ich bin sicher, dass dieses Tutorial Sie dazu inspirieren wird, es immer häufiger zu verwenden.