Tutorial: Strukturieren und Kombinieren von Daten in Power BI Desktop

Mit Power BI Desktop können Sie verschiedenste Arten von Datenquellen verknüpfen und die Daten dann basierend auf Ihren Anforderungen strukturieren. So können Sie visuelle Berichte erstellen und mit anderen Benutzern gemeinsam nutzen. Das Strukturieren von Daten ist mit einem Transformieren der Daten gleichzusetzen. Sie können beispielsweise Spalten oder Tabellen umbenennen, Text in Zahlen umwandeln, Zeilen entfernen, die erste Zeile als Überschrift festlegen usw. Das Kombinieren von Daten bedeutet, eine Verbindung mit zwei oder mehr Datenquellen herzustellen, sie nach Bedarf zu strukturieren und dann in einer einzelnen Abfrage zu konsolidieren.

In diesem Tutorial lernen Sie Folgendes:

  • Strukturieren von Daten mit dem Power Query-Editor.
  • Verbindungsherstellung mit verschiedenen Datenquellen
  • Kombinieren dieser Datenquellen und Erstellen eines Datenmodells zur Verwendung in Berichten

Dieses Tutorial veranschaulicht, wie Sie eine Abfrage mit Power BI Desktop strukturieren können. Hierbei werden insbesondere die gängigsten Aufgaben hervorgehoben. Die hier verwendete Abfrage (und auch der gesamte Erstellungsvorgang) ist im Handbuch Erste Schritte mit Power BI-Desktop ausführlicher beschrieben.

Power Query-Editor in Power BI Desktop verwendet die Kontextmenüs und das Menüband Transformieren. Die meisten der Optionen, die im Menüband verfügbar sind, können auch durch einen Rechtsklick auf ein Element (z. B. eine Spalte) und Auswahl einer Option aus dem daraufhin angezeigten Menü aufgerufen werden.

Strukturieren von Daten

Um Daten in Power Query-Editor zu strukturieren, geben Sie schritt-für-Schritt-Anweisungen für Power Query-Editor an, um die Daten beim Laden und Darstellen der Daten anzupassen. Die ursprüngliche Datenquelle bleibt davon unberührt. Angepasst bzw. strukturiert wird nur diese spezifische Ansicht der Daten.

Die Schritte, die Sie angeben (z. B. Umbenennen einer Tabelle, Transformieren eines Datentyps oder Löschen einer Spalte), werden vom Power Query-Editor aufgezeichnet. Bei jeder Verbindung dieser Abfrage mit der Datenquelle führt Power Query-Editor diese Schritte aus, damit die Daten dann in der gewünschten strukturierten Ansicht angezeigt werden. Dieser Vorgang wird immer dann ausgeführt, wenn Sie den Power Query-Editor in Power BI Desktop verwenden, oder wenn ein Benutzer auf die freigegebene Abfrage zugreift (z. B. im Power BI-Dienst). Diese Schritte werden sequenziell im Bereich Abfrageeinstellungen unter ANGEWENDETE SCHRITTE erfasst. Wir werden die einzelnen Schritte in diesem Artikel durchgehen.

Screenshot: Power Query-Editor mit dem Bereich

  1. Importieren Sie die Daten aus einer Webquelle. Wählen Sie die Dropdownliste Daten abrufen und dann Web aus.

    Screenshot: Power Query-Editor mit ausgewähltem Menü

  2. Fügen Sie diese URL in das Dialogfeld Aus dem Web ein, und wählen Sie OK aus.

    https://www.fool.com/research/best-states-to-retire
    

    Screenshot des Dialogfelds

  3. Wählen Sie im Dialogfeld Navigator die Option aus Table 1, und wählen Sie dann Daten transformieren aus.

    Screenshot des Dialogfelds

  4. Das Fenster Power Query-Editor wird geöffnet. Die bisher angewendeten Standardschritte werden im Bereich Abfrageeinstellungen unter ANGEWENDETE SCHRITTE angezeigt.

    • Quelle: Herstellen einer Verbindung mit der Website.
    • Extrahierte Tabelle aus HTML: Wählen Sie die Tabelle aus.
    • Höher gestufte Header: Ändern der obersten Datenzeile in Spaltenüberschriften.
    • Geänderter Typ: Ändern der als Text importierten Spaltentypen in die abgeleiteten Typen.

    Screenshot des fensters

  5. Ändern Sie den Tabellennamen von der Standardeinstellung Table 1 in Retirement Data, und drücken Sie dann die EINGABETASTE.

    Screenshot: Power Query-Editor, der zeigt, wie ein Tabellenname in den Abfrageeinstellungen bearbeitet wird.

  6. Die vorhandenen Daten werden nach einer gewichteten Bewertung sortiert, wie auf der Quellwebseite unter Methodik beschrieben. Fügen Sie eine benutzerdefinierte Spalte hinzu, um eine andere Bewertung zu berechnen. Anschließend sortieren wir die Tabelle in dieser Spalte, um die Rangfolge der benutzerdefinierten Bewertung mit der vorhandenen Rangfolge zu vergleichen.

  7. Wählen Sie im Menüband Spalte hinzufügen die Option Benutzerdefinierte Spalte aus.

    Screenshot des Menübands

  8. Geben Sie im Dialogfeld Benutzerdefinierte Spalte unter Neuer Spaltennamedie Zeichenfolge Neue Bewertung ein. Geben Sie für die Benutzerdefinierte Spaltenformel die folgenden Daten ein:

    ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8
    
  9. Vergewissern Sie sich, dass die Statusmeldung Es wurden keine Syntaxfehler erkannt. angezeigt wird, und klicken Sie auf OK.

    Screenshot des Dialogfelds

  10. In den Abfrageeinstellungen wird in der Liste ANGEWENDETE SCHRITTE jetzt der neue schritt Add custom (Hinzugefügt) angezeigt, den wir gerade definiert haben.

    Screenshot des Bereichs

Anpassen der Daten

Bevor wir mit dieser Abfrage arbeiten, nehmen wir einige Änderungen vor, um die Daten anzupassen:

  • Anpassen der Rangfolgen durch Entfernen einer Spalte

    Angenommen, Wetter ist kein Faktor in unseren Ergebnissen. Das Entfernen dieser Spalte aus der Abfrage wirkt sich nicht auf die anderen Daten aus.

  • Beheben Sie alle Fehler.

    Da wir eine Spalte entfernt haben, müssen wir unsere Berechnungen in der Spalte Neue Bewertung anpassen, indem wir deren Formel ändern.

  • Sortieren der Daten

    Sortieren Sie die Daten basierend auf der Spalte Neue Bewertung , und vergleichen Sie sie mit der vorhandenen Spalte Rang .

  • Ersetzen der Daten

    Es wird hervorgehoben, wie ein bestimmter Wert ersetzt und wie ein angewendeter Schritt eingefügt wird.

Diese Änderungen werden in den folgenden Schritten beschrieben.

  1. Um die Spalte Wetter zu entfernen, wählen Sie die Spalte aus, wählen Sie im Menüband die Registerkarte Start und dann Spalten entfernen aus.

    Screenshot des Startmenüs von Power Query-Editor mit hervorgehobener Schaltfläche

    Hinweis

    Die Werte der neuen Bewertung haben sich aufgrund der Reihenfolge der Schritte nicht geändert. Power Query-Editor zeichnet die Schritte sequenziell, aber unabhängig voneinander auf. Um Aktionen in einer anderen Reihenfolge anzuwenden, können Sie jeden angewendeten Schritt nach oben oder unten verschieben.

  2. Klicken Sie mit der rechten Maustaste auf einen Schritt, um das zugehörige Kontextmenü anzuzeigen.

    Screenshot des Kontextmenüs

  3. Verschieben Sie den letzten Schritt Entfernte Spalten an die Stelle direkt über den Schritt Hinzugefügte benutzerdefiniert Spalte.

    Screenshot der Liste

  4. Wählen Sie den Schritt Hinzugefügte benutzerdefiniert Spalte aus.

    Beachten Sie, dass in der Spalte Neue Bewertung jetzt Fehler anstelle des berechneten Werts angezeigt wird.

    Screenshot: Power Query-Editor und die Spalte

    Es gibt mehrere Möglichkeiten, weitere Informationen zu jedem Fehler zu erhalten. Wenn Sie die Zelle auswählen, ohne auf das Wort Fehler zu klicken, zeigt der Power Query-Editor die Fehlerinformationen im unteren Fensterbereich an.

    Screenshot: Power Query-Editor mit der Spalte

    Wenn Sie das Wort Fehler direkt auswählen, erstellt der Power Query-Editor im Bereich Abfrageeinstellungen unter Angewendete Schritte einen weiteren Schritt und zeigt die Informationen zu dem Fehler an. Da wir an keiner anderen Stelle Fehlerinformationen anzeigen müssen, wählen Sie Abbrechen aus.

  5. Um die Fehler zu beheben, sind zwei Änderungen erforderlich: Der Name der Spalte Wetter wird entfernt und der Divisor von 8 in 7 geändert. Sie können diese Änderungen auf zwei Arten vornehmen:

    1. Klicken Sie mit der rechten Maustaste auf den Schritt Benutzerdefinierte Spalte , und wählen Sie Einstellungen bearbeiten aus. Dadurch wird das Dialogfeld Benutzerdefinierte Spalte angezeigt, das Sie zum Erstellen der Spalte Neue Bewertung verwendet haben. Bearbeiten Sie die Formel wie zuvor beschrieben, bis sie wie folgt aussieht:

      Screenshot des Dialogfelds

    2. Wählen Sie die Spalte Neue Bewertung aus, und zeigen Sie dann die Datenformel der Spalte an, indem Sie das Kontrollkästchen Bearbeitungsleiste auf der Registerkarte Ansicht aktivieren.

      Screenshot: Power Query-Editor mit der Spalte

      Bearbeiten Sie die Formel wie zuvor beschrieben, bis sie wie folgt aussieht, und drücken Sie dann die EINGABETASTE.

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] ) / 7)              
      

    Der Power Query-Editor ersetzt die Daten durch die überarbeiteten Werte, und der Schritt Hinzugefügte benutzerdefinierte Spalte wird ohne Fehler abgeschlossen.

    Hinweis

    Sie können auch die Option Fehler entfernen (im Menüband oder Kontextmenü) auswählen, mit der alle fehlerhafte Zeilen entfernt werden. In diesem Tutorial möchten wir jedoch alle Daten in der Tabelle beibehalten.

  6. Sortieren Sie die Daten basierend auf der Spalte Neue Bewertung . Wählen Sie zuerst den zuletzt angewendeten Schritt Add Custom (Benutzerdefiniert hinzugefügt ) aus, um die neuesten Daten anzuzeigen. Wählen Sie dann die Dropdownliste neben der Spaltenüberschrift Neue Bewertung aus, und wählen Sie Absteigend sortieren aus.

    Screenshot: Power Query-Editor mit der Spalte

    Die Daten werden nun nach neuer Bewertung sortiert. Sie können einen angewendeten Schritt an einer beliebigen Stelle in der Liste auswählen und die Daten an diesem Punkt in der Sequenz weiter strukturieren. Power Query-Editor fügt automatisch einen neuen Schritt direkt nach dem aktuell ausgewählten angewendeten Schritt ein.

  7. Wählen Sie unter ANGEWENDETE SCHRITTE den Schritt vor der benutzerdefinierten Spalte aus. Dabei handelt es sich um den Schritt Entfernte Spalten . Hier ersetzen wir den Wert der Wohnkostenbewertung in Oregon. Klicken Sie mit der rechten Maustaste auf die entsprechende Zelle, die oregons Wohnkostenwert enthält, und wählen Sie dann Werte ersetzen aus. Beachten Sie auch die aktuelle Auswahl in Angewendeter Schritt.

    Screenshot des Fensters

  8. Wählen Sie Einfügen aus.

    Da wir einen Schritt einfügen, erinnert uns Power Query-Editor daran, dass nachfolgende Schritte dazu führen können, dass die Abfrage unterbrochen wird.

    Screenshot des Dialogfelds

  9. Ändern Sie den Datenwert in 100,0.

    Power Query-Editor ersetzt die Daten für Oregon. Wenn Sie einen neuen angewendeten Schritt erstellen, Power Query-Editor diesen basierend auf der Aktion, in diesem Fall, ersetzter Wert, benennt. Wenn Ihre Abfrage mehrere Schritte mit demselben Namen enthält, fügt Power Query-Editor dem Namen jedes nachfolgenden angewendeten Schritts eine steigende Zahl an.

  10. Wählen Sie nun den letzten Schritt Sortierte Zeilen unter Angewendete Schritte aus.

    Beachten Sie, dass sich die Daten in Bezug auf die neue Rangfolge von Oregon geändert haben. Dies ist darauf zurückzuführen, dass wir den Schritt Ersetzter Wert an der richtigen Stelle eingefügt haben, nämlich vor dem Schritt Hinzugefügte benutzerdefiniert Spalte.

    Die Daten sind nun so strukturiert, dass sie unseren Anforderungen entsprechen. Jetzt stellen wir eine Verbindung zu einer anderen Datenquelle her und kombinieren die Daten.

Kombinieren von Daten

Die Daten zu verschiedenen Zuständen sind interessant und werden für die Erstellung weiterer Analysebemühungen und Abfragen nützlich sein. Die meisten Daten zu Bundesstaaten verwenden jedoch eine zweistellige Abkürzung für Zustandscodes, nicht den vollständigen Namen des Staates. Wir müssen die Namen der Bundesstaaten in irgendeiner Weise mit den Kürzeln verknüpfen.

Es gibt eine weitere öffentliche Datenquelle, die diese Zuordnung bereitstellt, aber sie erfordert einen angemessenen Umfang an Gestaltung, bevor wir sie mit unserer Alterstabelle verbinden können. Führen Sie die folgenden Schritte aus, um die Daten zu strukturieren:

  1. Wählen Sie im Power Query-Editor über das Menüband Start den Befehl Neue Quelle > Web aus.

  2. Geben Sie die Adresse der Website für Abkürzungen von Bundesstaaten ein ( https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations ), und klicken Sie dann auf Verbinden.

    Der Navigator zeigt die Inhalte der Website an.

    Screenshot: Seite

  3. Wählen Sie Codes und Abkürzungen für US-Bundesstaaten, Bundesbezirke, Territorien und andere Regionen aus.

    Tipp

    Es wird ein wenig Strukturierung benötigen, um die Daten dieser Tabelle auf das gewünschte Maß zu analysieren. Gibt es eine schnellere oder einfachere Möglichkeit, die folgenden Schritte auszuführen? Ja, Sie könnten eine Beziehung zwischen den beiden Tabellen erstellen und die Daten basierend auf dieser Beziehung strukturieren. Die folgenden Beispielschritte sind hilfreich für das Arbeiten mit Tabellen. Beziehungen können Ihnen jedoch helfen, Daten aus mehreren Tabellen schnell zu verwenden.

Führen Sie zum Strukturieren der Daten diese Schritte aus:

  1. Entfernen Sie die oberste Zeile. Diese ist ein Ergebnis der Tabellenerstellung aus der Webseite und wird nicht gebraucht. Wählen Sie im Menüband auf der Registerkarte Start die Optionen Zeilen entfernen > Erste Zeilen entfernen aus.

    Screenshot: Power Query-Editor mit hervorgehobener Dropdownliste

    Das Dialogfeld Oberste Zeilen entfernen wird angezeigt. Geben Sie 1 zu entfernende Zeile an.

  2. Höherstufen Sie die neue oberste Zeile auf Kopfzeilen mit Erste Zeile als Kopfzeile verwenden auf der Registerkarte Start oder auf der Registerkarte Transformieren im Menüband.

  3. Da die Tabelle "Retirement Data " keine Informationen zu Washington DC oder Territorien enthält, müssen wir sie aus unserer Liste filtern. Wählen Sie den Namen und Den Status der Dropdownliste region_1 Spalte aus, und deaktivieren Sie dann alle Kontrollkästchen außer Status.

    Screenshot: Power Query-Editor mit einem Spaltenfilter, in dem nur der Statuswert ausgewählt ist.

  4. Entfernen Sie alle nicht benötigten Spalten. Da wir nur die Zuordnung jedes Bundesstaats zu seiner offiziellen zweistelligen Abkürzung (Name und Status der Region und ANSI-Spalten ) benötigen, können wir die anderen Spalten entfernen. Wählen Sie zunächst die Spalte Name und Status des Bereichs aus, halten Sie dann die STRG-TASTE gedrückt, und wählen Sie die Spalte ANSI aus. Wählen Sie im Menüband auf der Registerkarte Start die Option Spalten > entfernen Andere Spalten entfernen aus.

    Screenshot: Power Query-Editor, in der die Dropdownliste

    Hinweis

    Die Abfolge der angewendeten Schritte in Power Query-Editor ist wichtig und wirkt sich auf die Form der Daten aus. Es ist auch wichtig zu berücksichtigen, wie sich ein Schritt auf einen anderen nachfolgenden Schritt auswirken kann. Wenn Sie beispielsweise einen Schritt aus den angewendeten Schritten entfernen, verhalten sich nachfolgende Schritte möglicherweise nicht wie ursprünglich vorgesehen.

    Hinweis

    Wenn Sie die Breite des Power Query-Editorfensters verringern, werden einige Elemente des Menübands gestaucht, um den angezeigten Raum optimal zu nutzen. Wenn Sie das Fenster des Power Query-Editors verbreitern, werden die Elemente des Menübands gedehnt, um die größere Fläche des Menübands zu nutzen.

  5. Benennen Sie die Spalten und die Tabelle um. Es gibt mehrere Möglichkeiten, eine Spalte umzubenennen: Wählen Sie zunächst die Spalte aus, und wählen Sie dann auf der Registerkarte Transformieren im Menüband die Option Umbenennen aus, oder klicken Sie mit der rechten Maustaste, und wählen Sie Umbenennen aus. Die folgende Abbildung zeigt beide Optionen, aber Sie müssen nur eine auswählen.

    Screenshot: Power Query-Editor mit hervorgehobener Schaltfläche

  6. Benennen Sie die Spalten in State Name und State Code um. Um die Tabelle umzubenennen, geben Sie im Bereich Abfrageeinstellungen die Namenstatuscodes ein.

    Screenshot des Fensters Power Query-Editor mit den Ergebnissen der Strukturierung von Quelldaten von Zustandscodes in einer Tabelle.

Kombinieren von Abfragen

Nachdem wir nun die Tabelle "Statuscodes " wie gewünscht gestaltet haben, kombinieren wir diese beiden Tabellen oder Abfragen in einer Tabelle. Da die jetzt vorliegenden Tabellen das Ergebnis der Abfragen sind, die wir auf die Daten angewendet haben, werden sie häufig als Abfragen bezeichnet.

Zum Kombinieren von Abfragen gibt zwei Möglichkeiten: Zusammenführen und Anfügen.

  • Für eine oder mehrere Spalten , die Sie einer anderen Abfrage hinzufügen möchten, führen Sie die Abfragen zusammen .
  • Für eine oder mehrere Datenzeilen , die Sie einer vorhandenen Abfrage hinzufügen möchten, fügen Sie die Abfrage an .

In diesem Fall möchten wir die Abfragen zusammenführen:

  1. Wählen Sie im linken Bereich des Power Query-Editors die Abfrage aus, in der beide Abfragen zusammengeführt werden sollen. In diesem Fall handelt es sich um Ausmusterungsdaten.

  2. Wählen Sie im Menüband auf der Registerkarte Start die Option Abfragen zusammenführen > Abfragen zusammenführen aus.

    Screenshot der Dropdownliste

    Möglicherweise werden Sie aufgefordert, die Datenschutzebenen festzulegen, um sicherzustellen, dass die Daten kombiniert werden, ohne Daten aufzunehmen oder zu übertragen, die Sie nicht übertragen möchten.

    Das Fenster Zusammenführen wird geöffnet. Sie werden aufgefordert, die Tabelle auszuwählen, die mit der ausgewählten Tabelle zusammengeführt werden soll. Anschließend müssen Sie die entsprechenden Spalten angeben, die für die Zusammenführung verwendet werden sollen.

  3. Wählen Sie in der Tabelle Ausmusterungsdaten die Option Status und dann die Abfrage Statuscodes aus.

    Wenn Sie eine übereinstimmende Spalte auswählen, wird die Schaltfläche OK aktiviert.

    Screenshot des Dialogfelds

  4. Klicken Sie auf OK.

    Der Power Query-Editor erstellt am Ende der Abfrage eine neue Spalte, die den Inhalt der Tabelle (Abfrage) umfasst, die mit der vorhandenen Abfrage zusammengeführt wurde. Alle Spalten aus der zusammengeführten Abfrage werden auf die neue Spalte zusammengefasst. Sie können jedoch die Tabelle Erweitern und gewünschte Spalten einbeziehen.

  5. Um die zusammengeführte Tabelle zu erweitern und auszuwählen, welche Spalten eingeschlossen werden sollen, wählen Sie das Erweiterungssymbol ( ) aus.

    Das Fenster Erweitern wird geöffnet.

    Screenshot des Dialogfelds

  6. In unserem Fall wird nur die Spalte State Code benötigt. Wählen Sie diese Spalte aus, deaktivieren Sie Ursprünglichen Spaltennamen als Präfix verwenden, und klicken Sie dann auf OK.

    Wenn wir das Kontrollkästchen für Originalspaltennamen als Präfix verwenden aktiviert haben, würde die zusammengeführte Spalte State Codes.State Code heißen.

    Hinweis

    Wenn Sie untersuchen möchten, wie Sie die Tabelle Zustandscodes einfügen, können Sie ein wenig experimentieren. Wenn Ihnen die Ergebnisse nicht gefallen, löschen Sie diesen Schritt einfach aus der Liste ANGEWENDETE SCHRITTE im Bereich Abfrageeinstellungen , und Ihre Abfrage kehrt in den Zustand zurück, bevor Sie diesen Erweitern-Schritt anwenden. Sie können beliebig oft und solange experimentieren, bis die Erweiterung Ihren Vorstellungen entspricht.

    Wir verfügen jetzt über eine einzelne Abfrage (Tabelle), die zwei Datenquellen kombiniert, von denen jede entsprechend unseren Anforderungen gestaltet wurde. Diese Abfrage kann eine Grundlage für interessante Datenverbindungen sein, z. B. Statistiken über Wohnkosten, Lebensqualität oder Kriminalitätsrate in einem beliebigen Bundesland.

  7. Um Ihre Änderungen zu übernehmen und den Power Query-Editor zu schließen, wählen Sie auf der Registerkarte Start des Menübands die Option Schließen & Anwenden aus.

    Das transformierte Dataset wird in Power BI Desktop angezeigt und kann zum Erstellen von Berichten verwendet werden.

    Screenshot der Schaltfläche

Nächste Schritte

Weitere Informationen zu Power BI Desktop und seinen Funktionen finden Sie in den folgenden Ressourcen: