In der modernen Geschäftswelt ist heute jeder Job ein Teil des Datenanalysten . Egal, ob Sie in der Personalabteilung, im Vertrieb oder im Marketing arbeiten, Sie benötigen Datenkenntnisse. Jeder, der schon einmal mit Daten gearbeitet hat, weiß, dass sie selten im benötigten Format vorliegen. Normalerweise ist zusätzliche Arbeit erforderlich, um sie in einen verwertbaren Zustand zu bringen.
Power Query in Excel ist die Lösung für all Ihre Datenchaos-Probleme. Es kann viele nützliche Datentransformationen durchführen, die Ihnen dabei helfen, Ihre Daten zu bereinigen, sodass sie für weitere Analysen bereit sind.
In diesem Beitrag gehen wir einige äußerst nützliche Power Query-Tipps durch, die Ihnen dabei helfen, die besten in der Software verfügbaren Datentransformationsfunktionen zu verwenden.
Wenn Sie noch nie von Power Query gehört haben und sich mit seinen Funktionen vertraut machen möchten, sehen Sie sich dieses kurze Einführungsvideo an:
Wenn Sie einen tieferen Einblick in die Software gewinnen möchten, können Sie den GoSkills Power Query-Kurs belegen oder sich diesen Einführungsartikel zu Power Query ansehen .
Okay, lasst uns direkt eintauchen!
1. Zellen nach Trennzeichen aufteilen
Hier ist das Szenario:
Die Daten enthalten eine Liste von Namen und sie sind alle gleich formatiert. Nachname gefolgt von einem Komma und einem Leerzeichen und dann der Vorname.
Sie müssen jedoch Vor- und Nachnamen in eigenen Spalten trennen. Dies können Sie mit dem Power Query-Befehl „Spalten teilen“ tun.
Wählen Sie im Abfrage-Editor die Spalte aus, die Sie teilen möchten, und wechseln Sie zur Registerkarte „Transformieren“ . Wählen Sie in der Multifunktionsleiste den Befehl „ Spalte teilen“ und wählen Sie „ nach Trennzeichen“ .
Sie können auch darauf zugreifen, indem Sie mit der rechten Maustaste auf die Spaltenüberschrift klicken und im Menü „Spalte teilen“ auswählen.
Wählen Sie ein benutzerdefiniertes Trennzeichen und geben Sie ein Komma gefolgt von einem Leerzeichen ein. Drücken Sie nun die OK -Taste. Jetzt werden alle Namen in eigene Spalten aufgeteilt. Ganz einfach!
2. Länderspezifische Formatierung korrigieren
Okay, hier ist der Deal:
Verschiedene Länder verwenden unterschiedliche Formatierungskonventionen für Dinge wie Daten und Zahlen.
In Deutschland und vielen anderen kontinentaleuropäischen Ländern würden 10.346,36 $ als 10,346,36 $ geschrieben. Bei dieser Zahlenformatierung spielen Komma und Punkt die umgekehrte Rolle und Excel behandelt dies als Text statt als Zahl.
Auch bei Datumsangaben kann es knifflig werden. In den USA wird das Format MM/TT/JJJJ verwendet, während im Rest der Welt das Format TT/MM/JJJJ oder JJJJ-MM-TT verwendet wird.
Dies kann äußerst frustrierend sein, wenn Sie versuchen, mit Daten zu arbeiten, die von verschiedenen Standorten auf der ganzen Welt empfangen wurden.
Mithilfe der Gebietsschemafunktion von Power Query lässt sich dieses Problem im Handumdrehen beheben.
Klicken Sie mit der linken Maustaste auf das Datentypsymbol links neben der Spaltenüberschrift. Wählen Sie im Menü „ Gebietsschema verwenden“ aus.
Sie können auch darauf zugreifen, indem Sie mit der rechten Maustaste auf die Spaltenüberschrift klicken und im Menü „Typ ändern“ auswählen.
Wählen Sie den Datentyp aus , den Sie transformieren möchten. In diesem Fall ist es eine Währung.
Wählen Sie nun das Gebietsschema aus , aus dem die Daten stammen. In diesem Fall wurden sie mit dem deutschen Gebietsschema formatiert. Klicken Sie auf die Schaltfläche „OK“ und Excel erkennt die Daten nun als Zahlen und nicht als Text!
3. Fehlende Daten ergänzen
Dies ist einer der Power Query-Tipps auf dieser Liste, den Sie möglicherweise fast täglich verwenden.
Möglicherweise fehlen in einigen Zeilen Daten und Sie müssen die Daten aus der Zeile darüber ergänzen.
Dies passiert häufig, wenn Daten aus einer Pivot-Tabelle kopiert und eingefügt werden .
In diesem Fall ist in der äußersten linken Spalte nur die erste Zeile ausgefüllt und darunter befinden sich leere Zeilen, die dasselbe Produkt darstellen. Glücklicherweise ist das Ausfüllen der Spalte mit diesen Daten mit Power Query ganz einfach.
Wählen Sie die Spalte aus, die Sie mit Daten nach unten füllen möchten, und wechseln Sie zur Registerkarte „Transformieren“ im Menüband des Power Query-Editors. Drücken Sie dann den Befehl „Füllen“ und wählen Sie „Nach unten“ aus dem Menü.
Dies kann auch verwendet werden, indem Sie mit der rechten Maustaste auf die Spalte klicken und im Menü „Füllen und nach unten“ auswählen.
Dadurch werden alle leeren Zellen mit der letzten oben gefundenen nicht leeren Zelle gefüllt. Voila, keine fehlenden Daten mehr!
4. Organisieren Sie Ihre Daten neu, indem Sie Spalten verschieben oder entfernen
Ihre Daten entsprechen möglicherweise genau Ihren Wünschen, bis auf ein kleines Detail, das fehlt. Sie enthalten Spalten, die Sie nicht wirklich benötigen, oder die Spalten sind nicht in der gewünschten Reihenfolge.
Es ist eine einfache Sache, aber durch das Entrümpeln und Organisieren Ihrer Daten können Sie sich auf das Wesentliche konzentrieren.
Dies kann beim Datenimport mit Power Query leicht behoben werden.
Wählen Sie eine beliebige Spalte aus, die Sie aus den Daten entfernen möchten, wechseln Sie zur Registerkarte „Start“ im Menüband des Abfrage-Editors, wählen Sie den Befehl „Spalten entfernen“ und wählen Sie anschließend im Menü die Option „Spalten entfernen“ aus.
Dies ist auch durch einen Rechtsklick auf die Spalte und Auswahl von Entfernen möglich .
Tipp : Ändern sich die Spaltenüberschriften, die Sie entfernen möchten, manchmal? Wählen Sie dann die Spalten aus, die Sie behalten möchten, und verwenden Sie stattdessen den Befehl „Andere Spalten entfernen“ . Die Abfrage verweist auf die Spalten, die Sie behalten möchten, und verursacht keine Fehler, wenn sich beim nächsten Datenimport die Namen der entfernten Spalten ändern.
Auch das Verschieben von Spalten ist ganz einfach! Klicken Sie einfach mit der linken Maustaste auf die Spaltenüberschrift und ziehen Sie sie an die neue Position, entweder nach links oder nach rechts.
5. Entfernen Sie doppelte Daten
Das Entfernen doppelter Daten ist ein wichtiger Trick, den Sie in Ihrem Datenarsenal benötigen.
Doppelte Daten können sehr gefährlich sein. Eine versehentliche Verdoppelung der gemeldeten Umsätze in Ihrem Unternehmen könnte katastrophale Folgen haben.
Es ist vielleicht nicht so schwerwiegend, dass Sie doppelte Daten entfernen müssen. Möglicherweise müssen Sie nur eine Liste eindeutiger Werte aus einer Spalte mit Wiederholungen extrahieren.
Ganz gleich, aus welchem Grund Sie Duplikate entfernen müssen: Mit Power Query ist es kinderleicht.
Wählen Sie die Spalte(n) aus, die die doppelten Daten enthalten, wechseln Sie dann zur Registerkarte „Start“ im Menüband des Power Query-Editors und klicken Sie auf die Schaltfläche „Zeilen entfernen“ . Wählen Sie im Menü „Duplikate entfernen“ aus.
Sie können auch darauf zugreifen, indem Sie mit der rechten Maustaste auf die Spaltenüberschrift klicken und im Menü „Duplikate entfernen“ auswählen.
Keine Duplikate mehr!
Wenn Sie eine Aufzeichnung der duplizierten Daten aufbewahren möchten, gibt es dafür sogar einen Befehl. Verwenden Sie auf der Registerkarte „Start “ den Befehl „Zeilen behalten“ und wählen Sie im Menü „Duplikate behalten“ aus.
6. Gruppendaten
Mit Power Query können Sie Ihre Daten mit dem Befehl „Gruppieren nach “ zusammenfassen .
Das Zusammenfassen von Daten ist der Schlüssel zum Gewinnen von Erkenntnissen. Wenn Sie Ihre Daten jedoch analysieren möchten, sollten Sie diesen Befehl besser vermeiden und die Zusammenfassung später in einer Pivot-Tabelle durchführen. Die Verwendung einer Pivot-Tabelle ist ein viel dynamischerer Ansatz.
Die Gruppierung kann weiterhin ein nützlicher oder notwendiger Schritt in Ihrem Datentransformationsprozess sein.
Um Ihre Daten zu gruppieren, gehen Sie zur Registerkarte „Transformieren“ im Menüband des Power Query-Editors und drücken Sie den Befehl „Gruppieren nach“ .
Konfigurieren Sie die Gruppierungsoptionen.
- Wählen Sie entweder die Option „Einfach“ oder „Erweitert“ . Mit den Basisoptionen können Sie Ihre Daten nach einem einzelnen Feld gruppieren, während mit den erweiterten Optionen mehrere Felder möglich sind.
- Wählen Sie die Felder aus, nach denen Sie gruppieren möchten. Dies ist normalerweise ein Text- oder Datumsfeld.
- Wählen Sie die Spalten aus, die Sie zusammenfassen möchten, und die Operation, mit der Sie sie zusammenfassen möchten. Dabei handelt es sich normalerweise um numerische Felder, die Sie summieren, zählen oder deren Durchschnitt Sie berechnen möchten.
7. Transponieren Sie Ihre Daten
Ihre Daten sehen gut aus, aber …
Sie möchten es einfach umdrehen.
Was Sie tun müssen, ist es zu transponieren. Das heißt, die Zeilen werden zu Spalten und die Spalten zu Zeilen.
Das Transponieren der Daten ist ein dreistufiger Prozess. Ein Teil der zu transponierenden Daten befindet sich in den Spaltenüberschriften, daher müssen Sie diese zuerst in eine Datenzeile umwandeln.
- Gehen Sie zur Registerkarte „Transformieren“ und klicken Sie auf den Befehl „ Erste Zeile als Überschrift verwenden“ . Wählen Sie dann im Menü „Überschriften als erste Zeile verwenden“ aus . Dadurch werden die Spaltenüberschriften in die erste Datenzeile eingefügt.
- Klicken Sie auf der Registerkarte „Transformieren“ auf den Befehl „Transponieren“ .
- Jetzt können Sie die oberste Zeile wieder in Spaltenüberschriften umwandeln. Klicken Sie auf der Registerkarte „Transformieren “ auf den Befehl „Erste Zeile als Überschrift verwenden“ und wählen Sie im Menü „ Erste Zeile als Überschrift verwenden“ aus.
8. Alter berechnen
Die Berechnung des Alters einer Person oder Sache ist bei jeder Art von Analyse ein häufiger Bedarf, und Power Query verfügt über eine integrierte Berechnung.
Wählen Sie die Spalte mit dem Datum aus, auf dessen Grundlage Sie das Alter berechnen möchten.
- Gehen Sie im Power Query-Editor zur Registerkarte „Spalte hinzufügen“ , drücken Sie den Befehl „Datum“ und wählen Sie im Menü „Alter“ . Dadurch wird das Alter in Tagen angegeben, Sie müssen es also in Jahre umrechnen.
- Gehen Sie im Power Query-Editor zur Registerkarte „Transformieren “, klicken Sie auf die Schaltfläche „Dauer“ und wählen Sie im Menü „Gesamtjahre“ aus.
Jetzt haben Sie das Alter in Jahren! Wenn Sie die Abfrage aktualisieren, wird die Berechnung basierend auf dem heutigen Datum aktualisiert.
9. Daten entpivotieren
Ihre Daten sind möglicherweise bereits pivotiert.
Alle Werte in den Spalten „Produkt A“ und „Produkt B“ stellen dieselbe Verkaufsmetrik dar. Sie sollten eigentlich in einer Spalte mit einer anderen Spalte stehen, die angibt, von welchem Produkt der Verkaufsbetrag stammt.
Im aktuellen Format können Sie die Daten nicht weiter analysieren. In diesem Fall müssen Sie die Pivotierung der Daten aufheben.
Wählen Sie alle Spalten aus, deren Pivotierung Sie aufheben möchten, wechseln Sie dann zur Registerkarte „Transformieren“ im Menüband des Power Query-Editors und wählen Sie im Menüband des Power Query-Editors die Option „Spalten aufheben“ aus.
Jetzt wird eine neue Spalte mit entweder Produkt A oder B sowie alle Verkaufszahlen in einer weiteren Spalte angezeigt.
Sie müssen diese Spalten umbenennen, da sie standardmäßig „Attribut“ und „Wert“ heißen. Benennen Sie sie in etwas wie „Produkt“ bzw. „Umsatz“ um.
Ihre Daten können jetzt zur weiteren Analyse in eine Pivot-Tabelle geladen werden!
Mehr lesen: Wo und wie Sie The Wire 2024 online sehen können: Rückkehr nach Baltimore mit Streaming
10. Erstellen Sie eine Spalte basierend auf Beispielen
Ein häufiges Szenario ist das Erstellen neuer Spalten basierend auf vorhandenen Spalten in Ihren Daten.
In diesem Beispiel haben Sie eine Liste mit E-Mail-Adressen und möchten für jede Zeile eine neue Spalte mit dem Text „Ardeen arbeitet bei Uber“ usw. erstellen.
Sie müssen den Vornamen und das Unternehmen aus der E-Mail-Adresse extrahieren und diese aneinanderreihen, wobei Sie den Namen und das Unternehmen groß schreiben müssen.
Dazu müssten in Power Query einige unterschiedliche Transformationsschritte angewendet werden, um das Ergebnis zu erhalten.
Das heißt, Sie müssen herausfinden, welche Abfolge von Schritten zum gewünschten Ergebnis führt.
Sie können Power Query tatsächlich dazu bringen, anhand einiger von Ihnen bereitgestellter Beispiele herauszufinden, wie die Daten transformiert werden.
Wechseln Sie zur Registerkarte „Spalte hinzufügen“ im Menüband des Power Query-Editors und wählen Sie den Befehl „Spalte aus Beispiel“ aus .
Nun müssen Sie Power Query einige Beispiele für das gewünschte Ergebnis in der Spalte geben.
- Geben Sie 2 oder mehr Beispielzeilenergebnisse für Power Query an, um die gewünschte Transformation zu erlernen und zu erstellen.
- Die von Power Query vorgeschlagene Transformation wird in den verbleibenden Zeilen hellgrau angezeigt. Überprüfen Sie die Ergebnisse und fügen Sie weitere Beispiele hinzu, wenn sie nicht ganz korrekt sind.
- Drücken Sie die OK -Taste, wenn Sie mit den Ergebnissen zufrieden sind.
Power Query erstellt und fügt Ihrer Abfrage die erforderlichen Transformationsschritte hinzu!
Du hast die Macht
In Power Query sind zahlreiche Befehle zur Datentransformation integriert.
Wenn Sie Ihre Daten irgendwie transformieren müssen, hat Power Query wahrscheinlich einen Befehl dafür!
Wenn Sie weitere Power Query-Tipps suchen, sehen Sie sich diese Liste an .
Jetzt ist es ganz einfach, saubere Daten genau nach Ihren Wünschen zu formatieren. Keine komplexe Codierung oder Formeln erforderlich.
Sind Sie bereit, die „Power“ von Power Query freizusetzen? Nehmen Sie am GoSkills Power Query-Kurs teil und erhalten Sie nach Abschluss ein Zertifikat, das Ihren Lebenslauf aufwertet.