PQ: Liste gegen Kreuztabelle abgleichen

  Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ   

Die (bereits formatierte) Original-Tabelle

In einem Excel-Forum wurde die Anfrage gestellt wie es möglich sei, die Liste in den Spalten A:D mit den Daten der Kreuztabelle in den Spalten F:I abzugleichen. In der Spalte D (check) soll später ein Hinweis stehen, ob die Rechnungsdaten in (A:C) identisch sind mit den Mengen, die aus dem Lager ausgebucht worden sind (F:I). Da ein solcher Abgleich werktäglich stattfinden soll, bietet sich Power Query als Werkzeug für die wiederkehrende Auswertung an.

Und die beiden Datenbereiche sind bereits (in dieser Datei) jeweils als Intelligente Tabelle mit dem Namen Tabelle1 und Tabelle2 formatiert. Klicken Sie zuerst in die Liste (links) und importieren Sie diese in den Power Query-Editor. Der Name der Abfrage ist naturgemäß Tabelle1. Erweitern sie im Menüband den Text Schließen & laden und wählen Sie Schließen & Laden in… | Nur Verbindung erstellen.

Importieren Sie anschließend auch die Kreuztabelle, welche dann im Editor natürlich den Namen Tabelle2 bekommt. Aus Gründen der Transparenz sollten Sie die beiden Abfragen umbenennen in Rechnung (die Liste) und Lager (die Kreuztabelle). Speichern Sie diese Abfrage auf die gleiche Weise wie die erste Query.

Öffnen Sie wieder die Abfrage Rechnung. Ihnen wird sicher auffallen, dass in der Spalte Datum jeder einzelne Datumswert mit der Uhrzeit 00:00:00 eingetragen ist. Der Datentyp dieser Spalte ist Datum/Uhrzeit. Und da in dieser Spalte nur das reine Datum gebraucht wird, ändern Sie den Datentyp auf (nur) Datum. Die Spalte check wird zwar prinzipiell gebraucht aber es ist wesentlich effizienter, diese nur durch Power Query erstellen und berechnen zu lassen. Und da es nicht möglich ist, in einer (1) Abfrage zwei Spalten mit dem gleichen Namen zu haben, löschen Sie die existierende, zurzeit leere Spalte. Damit ist das Problem behoben. 😉 

Erweitern Sie das linke Seitenfenster durch einen Klick auf den Text Abfragen. Klicken Sie auf die Abfrage Lager, um diese zur aktuellen Query zu machen. Im ersten Schritt werden sie in PQ aus der Kreuztabelle eine Tabelle in Form einer Liste erstellen. Dazu markieren Sie die Spalte Kennnummern und führen einen Rechtsklick in der Überschrift durch. Im Kontextmenü wählen Sie den Punkt Andere spalten entpivotieren. Dadurch entsteht aus den markierten Daten eine drei Spalten umfassende Abfrage:

Die Abfrage nach dem entpivotieren

Ein Klick in die Werte der Spalte Attribut wird Ihnen zeigen, dass die Daten zwar wie ein Datum aussehen jedoch vom Datentyp Text sind. Wandeln Sie diese Daten in ein “echtes“ Datum um. Der Klarheit wegen können Sie die Überschrift dieser Spalte auch in Datum ändern, auch wenn es nicht wirklich erforderlich ist. (Sie werden etwas später erkennen, warum…)

Markieren Sie (in dieser Reihenfolge) Kennnummer und Datum. Symbol Gruppieren nach und sorgen Sie dafür, dass in den unteren Textfeldern diese Werte stehen:

  • Neuer Spaltenname: Summe,
  • Vorgang: Summe,
  • Spalte: Wert.

Diese Werte auswählen bzw. eintragen

  • Schließen Sie das Fenster per OK.
  • Wechseln Sie anschließend wieder zur Abfrage Rechnung.
  • Aktivieren sie erforderlichenfalls und das Register Home/Start. Kombinieren und erweitern Sie die Auswahl Abfragen zusammenführen.
  • Klicken sie nun auf Abfragen als neue Abfrage zusammenführen.
  • Wählen Sie im Dialog als zweite Abfrage Lager.
  • Markieren Sie in beiden Abfragen in genau dieser Reihenfolge: Datum und Kennnummer.

Und das Ergebnis ist eine neue Abfrage mit dem Namen Merge1, wo in der neuen, letzten Spalte (Lager) der Inhalt jeder einzelnen Zeile nur Table ist.

Erweitern Sie die Überschrift Lager durch einen Klick in den Doppelpfeil Doppelpfeil. Im Dialog entfernen Sie alle Häkchen (einschließlich ganz unten Ursprünglichen Spaltennamen als Präfix verwenden) jedoch nicht das Häkchen bei Summe:

Nur diese Häkchen sollen übrig bleiben

Nach einem OK ist diese Spalte zu Summe umbenannt worden und enthält jene Werte, die in der Gruppierung aggregiert worden sind. An dieser Stelle benenne ich die Abfrage um, sie bekommt den Namen Abgleich.

Nun Spalte hinzufügen | Bedingte Spalte und tragen Sie im Dialog folgende Werte ein:

Diese Werte sollten vor einem OK dort stehen

  • Neuer Spaltenname: Abgleich,
  • Spaltenname: Menge,
  • Operator: ist gleich,
  • Wert: Hier zuerst auf das Symbol klicken und Spalte auswählen markieren, dann Summe,
  • Ausgabe: OK und
  • Sonst: Fehler!.

Nach dem Schließen des Fensters hat Power Query die Spalte Abgleich erstellt und dort bei Übereinstimmung der beiden vorherigen Spalten den Wert OK eingetragen, bei Differenzen den Text Fehler!. Sinnvollerweise werden sie nun die letzte Spalte so filtern, dass nur die Zeilen mit dem Fehler stehen bleiben.

Im letzten Akt sollten Sie die Überschriften Summe zu Rechnung und Abgleich zu Lager umbenennen damit jedem, der die Daten erhält, sofort klar ist, worum es in der anschließend erstellten Tabelle im Endeffekt geht. 😎 

Sie bleiben in der Abfrage Abgleich. Datei | Schließen & laden und Power Query schreibt in ein neu erstelltes Tabellenblatt eine Intelligente Tabelle mit den eben gefilterten Daten. Natürlich hätten sie auch Schließen und Laden in… verwenden und den Zielort für die zu schreibende Datei selber bestimmen können. Damit ist die Aufgabe endgültig erledigt.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag (z.B. 1,50€) Ihrerseits freuen …

Dieser Beitrag wurde unter Allgemein veröffentlicht. Setze ein Lesezeichen auf den Permalink.