Blog

Suche
Rainer Pollmann

Rainer Pollmann

Power Query (#009): Daten zusammenführen – SVERWEIS 3.0

Mit Hilfe von Power Query lassen sich Stammdaten und Bewegungsdaten zu einer Datenliste vereinigen. Eine Aufgabenstellung, bei der viele USER die Funktionen SVERWEIS und / oder INDEX & VERGLEICH einsetzen.

Im aktuellen Beispiel gibt es vier Tabellenblätter in der Beispieldatei. Um eine gemeinsame Datenbasis zu erzeugen, müssen Bewegungsdaten (TB_Absatz) mit den Stammdaten verbunden werden. Dies geschieht normalerweise mit SVERWEIS & Co, hier im Beispiel mit Power Query:

Datei(n) in das Power Query-Modell laden

  1. Öffnen Sie eine neue, leere Excel-Datei.
  2. Klicken Sie im Menü Daten auf den Arbeitsbereich Abrufen und Transformieren ● Neue Abfrage ● Aus Datei ● Aus Excel.
  3. Wählen Sie die Datei „DBIS.XLSX“.
  4. Im Navigations-Fenster aktivieren Sie das Kontrollkästchen „Mehrere Elemente auswählen“ (Abbildung 4)
  5. Wählen Sie im Bereich darunter alle Tabellen aus (siehe rechte Marginalie).
  6. Mit einem Klick auf <Bearbeiten> werden diese Tabellen in das Modell geladen und in einem Abfragefenster sowie den Abfrageeinstellungen rechts angezeigt (Abbildung 5).
Bild1

Tabellen verbinden (SVERWEIS 3.0) => Joins

  1. Wählen Sie in dem Abfragefenster die Tabelle „TB_Absatz“ aus.
  2. Klicken Sie im Menü Start auf den Arbeitsbereich Kombinieren ● Abfragen Zusammenführen. Das Fenster „Kombinieren“ öffnet sich.
  3. Klicken Sie bei der Tabelle „TB_Absatz“ die Spalte „KundenID“ an.
  4. Wählen Sie im unteren Teil des Fensters aus der Drop-Down-Liste die Tabelle „TS_Kunden“ aus und klicken dort die Spalte „KundenID“ an.
  5. Bestätigen Sie mit einem Klick auf <OK> die Verbindungen (Abbildung 6).
  6. Klicken Sie bei der Tabelle „TB_Absatz“ die Spalte „ProduktID“ an.
  7. Wählen Sie im unteren Teil des Fensters aus der Drop-Down-Liste die Tabelle „TS_Produkte“ aus und klicken dort die Spalte „ProduktID“ an.
  8. Bestätigen Sie mit einem Klick auf <OK> die Verbindungen.
  9. Klicken Sie bei der Tabelle „TB_Absatz“ die Spalte „RegionID“ an.
  10. Wählen Sie im unteren Teil des Fensters aus der Drop-Down-Liste die Tabelle „TS_Regionen“ aus und klicken dort die Spalte „RegionID“ an.
  11. Bestätigen Sie mit einem Klick auf <OK> die Verbindungen.
  12. Die Abfragetabelle hat nun drei neue Spalten <NewColumn> erhalten.
Bild2

Nun ist die Verbindung zwischen den vier Tabellen grundsätzlich erstellt, aber die Daten für die eine Datenquelle müssen noch ausgewählt werden.

Daten selektieren

In der Tabelle „TB_Absatz“ sind nun drei Verbindungen zu anderen Tabellen angelegt. Erkennbar ist dies an den hinzugekommenen Spalten „NewColumn“, „NewColumn1“ und „NewColumn2“. Nun soll das Datenmodell auf die Daten reduziert werden, die letztlich im Reporting-Tool benötigt werden.

  1. Klicken Sie in der Tabelle TB_Absatz auf das Doppelpfeilsymbol der Spalte „NewColumn“. Ein Fenster erscheint.
  2. Deaktivieren Sie die Spalte „KundenID“ und bestätigen Sie mit <OK> (Abbildung 7).
  3. Nun wird die Tabelle um drei Spalten erweitert
  4. Klicken Sie in der Tabelle TB_Absatz auf das Doppelpfeilsymbol der Spalte „NewColumn1“. Ein Fenster erscheint.
  5. Deaktivieren Sie die Spalten „ProduktID“, „Bezeichnung“ und bestätigen Sie mit <OK> (Abbildung 9).
  6. Klicken Sie in der Tabelle TB_Absatz auf das Doppelpfeilsymbol der Spalte „NewColumn2“. Ein Fenster erscheint.
  7. Deaktivieren Sie die Spalten „RegionID“, „Ressort“, „Vertriebsgebiet“ und bestätigen Sie mit <OK> (Abbildung 10).
Bild3

Nun müssen noch in der Tabelle „TB_Absatz“ die Spalten entfernt werden, die nachher in der Ziel-Detail nicht benötigt werden.

Spalten entfernen

  1. Markieren Sie in der Tabelle TS_Absatz die Spalten, die entfernt werden sollen. Dazu können Sie die <STRG>-Taste einsetzen:
  • ID
  • RegionID
  • ProduktID
  • Dimension
  1. Drücken Sie die <ENTF>-Taste, um die Spalten zu löschen

Spalten umbenennen

  1. Klicken Sie in der Tabelle TB_Absatz den Spaltenkopf der Spalte „NewColumn.Firma“ doppelt an.
  2. Ändern Sie die Bezeichnung in „Firma“.
  3. Wieder holen Sie diesen Vorgang für alle Spalten, die mit der Zeichenfolge „NewColumn“ beginnen.

Spalten Verschieben

  1. Markieren Sie in der Tabelle TB_Absatz den Spaltenkopf der Spalte „Menge“.
  2. Ziehen Sie mit gedrückter linker Maustaste diese Spalte rechts neben die Spalte „Preis“.
  3. Wieder holen Sie diesen Vorgang für alle Spalten, die mit der Zeichenfolge „NewColumn“ beginnen.

Verbindung erstellen

  1. Klicken Sie im Menü Start auf den Arbeitsbereich Schließen ● Schließen & Laden in. Das Fenster „Laden in“ öffnet sich.
  2. Wählen Sie die Option „Nur Verbindung erstellen“ aus. Danach finden Sie eine leere Tabelle vor. Das Modell existiert aber als Verbindung

Sie haben nun eine Datenliste vorliegen, die aus 4 verschiedenen Datenbereichen erstellt wurde. Davon enthielten drei Tabellen Stammdaten und eine Bewegungsdaten. Das Ergebnis liegt nach dem Import in die Excel-Datei als sogenannte Tabelle, als ein Datenbankähnlicher Bereich vor. Dieser Bereich kann mit einer Pivot-Tabelle oder Power-Pivot ausgewertet werden.

Bild4

Tabelle importieren

  1. Wenn Sie ein Power Query-Modell nur als Verbindung laden, erhalten Sie zunächst eine leere Excel-Tabelle.
  2. Klicken Sie die Tabelle(n), die Sie laden wollen im Bereich „Arbeitsmappenabfragen“ mit der rechten Maustaste an. Das hier die Tabelle „TS_Absatz“. Ein Kontextmenü öffnet sich.
  3. Wählen den Befehl Laden in… aus. Das Fenster „Laden in“ öffnet sich.
  4. Wählen Sie die Option „Tabelle“ aus.
  5. Nur die ausgewählte Tabelle wird geladen.

Print Friendly, PDF & Email

Beitrag teilen:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Unsere Seminarempfehlungen

Power Query 1
Sie lernen mit Power Query schnell und ohne Makros Daten aus verschiedenen Datenquellen zu importieren, zu bereinigen, miteinander zu verbinden und sie für Ihre Analysen und Reports passend aufzubereiten.

Up to date bleiben

Melden Sie sich für unseren Newsletter an!