Tabelle mit Power Query in Excel umstellen

Um die Daten in einer Excel-Tabelle schnell und flexibel mit Hilfe von PivotTables und PivotCharts analysieren zu können, ist der Aufbau der Tabelle und die Anordnung der Spalten von entscheidender Bedeutung. Falls der Tabellenaufbau für Analysen nicht gut geeignet sein sollte, können Sie in vielen Fällen mit dem Add-In Power Query für Excel 2010 und Excel 2013 die Tabelle für die Analyse passend umstellen, ohne dass Sie zahlreiche Formeln eingeben oder sogar VBA-Makros erstellen müssen.

In unserem Beispiel soll eine Tabelle mit den geplanten monatlichen Absatzmengen für 3 Artikel so umgestellt werden, dass die Daten problemlos mit einer PivotTable analysiert werden können. Die Tabelle sieht folgendermaßen aus:

Excel Planwerte

Die flexible Analyse der Daten mit Hilfe von PivotTables wird dadurch erschwert, dass die Planmengen für die einzelnen Monate sich in verschiedenen Spalten befinden. Da das Transponieren der Zeilen und Spalten in diesem Fall nicht weiterhilft, soll mit Hilfe des Add-Ins Power Query die Tabelle umgestellt werden.

Falls das Add-In Power Query auf Ihrem Rechner installiert und aktiviert ist, finden Sie im Menüband von Excel die Registerkarte Power Query.

Gehen Sie folgendermaßen vor, um die Tabelle umzustellen:

  1. Aktivieren Sie eine Zelle in der Tabelle mit den geplanten Absatzmengen.
  2. Klicken Sie im Menüband auf der Registerkarte Power Query in der Gruppe Excel-Daten auf die Schaltfläche Von Tabelle. Es erscheint das folgende Dialogbild.

    Power Query Von Tabelle

  3. Aktivieren Sie das Kontrollkästchen Meine Tabelle hat Überschriften und klicken Sie anschließend auf die Schaltfläche OK. In Power Query erscheint das folgende Dialogbild

    Power Query Abfrageeditor

  4. Markieren Sie die Spalte Artikel, in dem Sie mit der Maus in den Spaltenkopf klicken.
  5. Klicken Sie auf im Menüband auf der Registerkarte Transformieren in der Gruppe Beliebige Spalte auf die Schaltfläche Andere Spalten entpivotieren, d.h. es sollen alle Spalten bis auf die Spalte Artikel entpivotiert werden. Im Abfrageeditor sieht die Tabelle danach folgendermaßen aus (Ausschnitt):

    Power Query Entpivotieren

    Beim Entpivotieren werden die betroffenen Spalten Periode 1 bis Periode 12 gelöscht. Es werden zwei neue Spalten mit den Namen Attribut und Wert erzeugt. In unserem Fall werden die neuen Spalten hinter der verbleibenden Spalte Artikel eingefügt. Für die Planmengen der 3 Artikel in den 12 Perioden werden Attribut-Wert-Paare gebildet. Dadurch hat die Tabelle nach dem Entpivotieren statt 3 Zeilen nun 36 Zeilen (3*12=36).

  6. Klicken Sie doppelt in den Spaltenkopf der Spalte Attribut und benennen Sie die Spalte in Periode um.
  7. Im nächsten Schritt soll der Ausdruck Periode aus den Zeilen der Spalte Periode entfernt werden, damit nur die die Zahlen übrig bleiben. Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Transformieren auf die Schaltfläche Werte ersetzen. Das folgende Dialogbild erscheint:

    Power Query Werte ersetzen

  8. Geben Sie im Eingabefeld Zu suchender Wert den Ausdruck Periode ein und klicken Sie dann auf die Schaltfläche OK. Das Eingabefeld Ersetzen durch bleibt leer, weil in jeder Zeile der Spalte nur die Zahl erhalten bleiben soll.
  9. Im nächsten Schritt soll aus den Werten in der Spalte Periode Datumswerte erzeugt werden. Markieren Sie die Spalte Periode und klicken Sie im Menüband auf der Registerkarte Spalte hinzufügen in der Gruppe Allgemein auf die Schaltfläche Benutzerdefinierte Spalte einfügen. Es erscheint das folgende Dialogbild:

    Power Query Benutzedefinierte Spalte einfügen

  10. Löschen Sie im Eingabefeld Neuer Spaltenname den vorbelegten Namen Custom und tragen Sie den Namen Datum ein.
  11. Geben Sie die folgende benutzerdefinierte Spaltenformel ein, um z.B. die Zahl 1 umzuwandeln in den Datumswert 01.01.2015.

    =Date.FromText([Periode]&"/2015/01")

  12. Prüfen Sie, ob nach der Eingabe der Formel der folgende Hinweis erscheint: Es wurden kein Syntaxfehler gefunden. Klicken Sie auf die Schaltfläche OK. Die Tabelle hat folgendes Aussehen (Ausschnitt):

    Power Query Benutzedefinierte Spalte einfügen

  13. Verschieben Sie die Spalte Datum an den Anfang der Tabelle.
  14. Löschen Sie Spalte Periode aus der Tabelle, in dem Sie auf der Registerkarte Start in der Gruppe Spalten verwalten auf die Schaltfläche Spalten entfernen klicken. Die Spalte wird nicht mehr benötigt.
  15. Markieren Sie die Spalte Datum und klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Transformieren auf den Pfeil im Kombinationsfeld Datentyp. Klicken Sie auf den Eintrag Datum. Die Tabelle ist nun für die Übertragung in ein Excel-Tabellenblatt vorbereitet.
  16. Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Schließen auf die Schaltfläche Schließen und Laden. In der Excel-Arbeitsmappe wird ein neues Tabellenblatt angelegt mit den Daten aus der mit Power Query umgewandelten Tabelle. Das Tabellenblatt sieht folgendermaßen aus:

    Power Query Tabellenblatt laden

    Am rechten Rand des neu angelegten Tabellenblatts erscheint der folgende Auswahlbereich:

    Power Query Arbeitsmappenabfrage

  17. Aus dieser Tabelle können Sie mit wenigen Mausklicks z. B. die folgende PivotTable erstellen.

    PivotTable Planwerte

Hinweise

  • Wenn Sie die Planwerte nachträglich ändern, können Sie Power Query veranlassen, die Daten erneut aus dem Tabellenblatt zu lesen, umzuwandeln und in das dafür vorgesehene Tabellenblatt zurückzuschreiben. Klicken Sie im Auswahlbereich Arbeitsmappenabfragen auf die Schaltfläche mit dem Aktualisierungssymbol am rechten oberen Rand der Schaltfläche Planzahlen.
  • Bei der Übernahme der Plandaten aus dem Excel-Tabellenblatt nach Power Query hat das Add-In den Bereich mit den Ursprungsdaten in eine Excel-interne Tabelle umgewandelt. Diese Umwandelung hat den Vorteil, dass Sie in diese Tabelle sogar Planwerte für zusätzliche Artikel eingeben können. Starten Sie danach die Arbeitsmappenabfrage erneut, um die Daten der neuen Artikel ebenfalls in das vorgesehene Tabellenblatt zurückzuschreiben.

Seminartipp

Weitere interessante Tipps zu Power Query erfahren Sie in unserem Seminar Power Query. In dem Seminar Unternehmensdaten analysieren lernen Sie, wie Sie schnell und flexibel Daten mit PivotTables und PivotCharts analysieren.

Hat Ihnen dieser Beitrag weitergeholfen?

ja nein Andere Erwartung

Geben Sie uns Ihr Feedback. Möchten Sie eine Antwort von uns erhalten, tragen Sie zusammen mit Ihrem Feedback auch Ihre E-Mail-Adresse ein. Ihre E-Mail-Adresse wird ausschließlich für die Kommunikation mit Ihnen verwendet. Sie wird nicht veröffentlicht oder weitergegeben.

Benötigen Sie professionelle Unterstützung beim Einsatz von Word, Excel, Outlook, PowerPoint oder Access, so sind wir der richtige Ansprechpartner für Sie. Senden Sie uns eine E-Mail mit einer kurzen Problembeschreibung. Jemand aus unserem Team wird sich bei Ihnen melden, um weitere Details abzuklären und Ihnen ein Angebot für unsere Dienstleistung zu erstellen.