Daten mit Power Query 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 2.0)

  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.

Weitere Details….

erfahren Sie in unseren Seminar Power Query 1:  – Daten laden, verdichten.

Dieser Eintrag wurde veröffentlicht in Controlling, EXCEL, PowerQuery und getaggt mit am von .

Über Rainer Pollmann

Rainer Pollmann ist Geschäftsführender Partner von Pollmann & Rühm Training in Augsburg. Er studierte Wirtschaftsprüfung, Controlling und Organisationslehre (BWL) an der Universität Augsburg und ist seit 1989 aktiv als Trainer und Berater für Controller aktiv. Seit 2006 entwickelt er aktiv Branchenstandards im Kommunikations-Management als stellvertretender Leiter des Fachkreis Kommunikations-Controlling im Internationalen Controller Verein (ICV) mit.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s