PQ: Liste gegen Kreuztabelle abgleichen

  Wis­sens­stand: Lev­el 2 ⇒ Solides Basiswis­sen in Excel, etwas Erfahrung in PQ   

Die (bere­its for­matierte) Orig­i­nal-Tabelle

In einem Excel-Forum wurde die Anfrage gestellt wie es möglich sei, die Liste in den Spal­ten A:D mit den Dat­en der Kreuzta­belle in den Spal­ten F:I abzu­gle­ichen. In der Spalte D (check) soll später ein Hin­weis ste­hen, ob die Rech­nungs­dat­en in (A:C) iden­tisch sind mit den Men­gen, die aus dem Lager aus­ge­bucht wor­den sind (F:I). Da ein solch­er Abgle­ich werk­täglich stat­tfind­en soll, bietet sich Pow­er Query als Werkzeug für die wiederkehrende Auswer­tung an.

Und die bei­den Daten­bere­iche sind bere­its (in dieser Datei) jew­eils als Intel­li­gente Tabelle mit dem Namen Tabelle1 und Tabelle2 for­matiert. Klick­en Sie zuerst in die Liste (links) und importieren Sie diese in den Pow­er Query-Edi­tor. Der Name der Abfrage ist naturgemäß Tabelle1. Erweit­ern 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 Kreuzta­belle, welche dann im Edi­tor natür­lich den Namen Tabelle2 bekommt. Aus Grün­den der Trans­parenz soll­ten Sie die bei­den Abfra­gen umbe­nen­nen in Rech­nung (die Liste) und Lager (die Kreuzta­belle). Spe­ich­ern Sie diese Abfrage auf die gle­iche Weise wie die erste Query.

Öff­nen Sie wieder die Abfrage Rech­nung. Ihnen wird sich­er auf­fall­en, dass in der Spalte Datum jed­er einzelne Datum­swert mit der Uhrzeit 00:00:00 einge­tra­gen ist. Der Daten­typ dieser Spalte ist Datum/Uhrzeit. Und da in dieser Spalte nur das reine Datum gebraucht wird, ändern Sie den Daten­typ auf (nur) Datum. Die Spalte check wird zwar prinzip­iell gebraucht aber es ist wesentlich effizien­ter, diese nur durch Pow­er Query erstellen und berech­nen zu lassen. Und da es nicht möglich ist, in ein­er (1) Abfrage zwei Spal­ten mit dem gle­ichen Namen zu haben, löschen Sie die existierende, zurzeit leere Spalte. Damit ist das Prob­lem behoben. 😉 

Erweit­ern Sie das linke Seit­en­fen­ster durch einen Klick auf den Text Abfra­gen. Klick­en Sie auf die Abfrage Lager, um diese zur aktuellen Query zu machen. Im ersten Schritt wer­den sie in PQ aus der Kreuzta­belle eine Tabelle in Form ein­er Liste erstellen. Dazu markieren Sie die Spalte Kennnum­mern und führen einen Recht­sklick in der Über­schrift durch. Im Kon­textmenü wählen Sie den Punkt Andere spal­ten ent­piv­otieren. Dadurch entste­ht aus den markierten Dat­en eine drei Spal­ten umfassende Abfrage:

Die Abfrage nach dem ent­piv­otieren

Ein Klick in die Werte der Spalte Attrib­ut wird Ihnen zeigen, dass die Dat­en zwar wie ein Datum ausse­hen jedoch vom Daten­typ Text sind. Wan­deln Sie diese Dat­en in ein “echt­es” Datum um. Der Klarheit wegen kön­nen Sie die Über­schrift dieser Spalte auch in Datum ändern, auch wenn es nicht wirk­lich erforder­lich ist. (Sie wer­den etwas später erken­nen, warum…)

Markieren Sie (in dieser Rei­hen­folge) Kennnum­mer und Datum. Sym­bol Grup­pieren nach und sor­gen Sie dafür, dass in den unteren Textfeldern diese Werte ste­hen:

• Neuer Spal­tenname: Summe,
• Vor­gang: Summe,
• Spalte: Wert.

Diese Werte auswählen bzw. ein­tra­gen

• Schließen Sie das Fen­ster per OK.
• Wech­seln Sie anschließend wieder zur Abfrage Rech­nung.
• Aktivieren sie erforder­lichen­falls und das Reg­is­ter Home/Start. Kom­binieren und erweit­ern Sie die Auswahl Abfra­gen zusam­men­führen.
• Klick­en sie nun auf Abfra­gen als neue Abfrage zusam­men­führen.
• Wählen Sie im Dia­log als zweite Abfrage Lager.
• Markieren Sie in bei­den Abfra­gen in genau dieser Rei­hen­folge: Datum und Kennnum­mer.

Und das Ergeb­nis ist eine neue Abfrage mit dem Namen Merge1, wo in der neuen, let­zten Spalte (Lager) der Inhalt jed­er einzel­nen Zeile nur Table ist.

Erweit­ern Sie die Über­schrift Lager durch einen Klick in den Dop­pelpfeil . Im Dia­log ent­fer­nen Sie alle Häkchen (ein­schließlich ganz unten Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den) jedoch nicht das Häkchen bei Summe:

Nur diese Häkchen sollen übrig bleiben

Nach einem OK ist diese Spalte zu Summe umbe­nan­nt wor­den und enthält jene Werte, die in der Grup­pierung aggregiert wor­den sind. An dieser Stelle benenne ich die Abfrage um, sie bekommt den Namen Abgle­ich.

Nun Spalte hinzufü­gen | Bed­ingte Spalte und tra­gen Sie im Dia­log fol­gende Werte ein:

Diese Werte soll­ten vor einem OK dort ste­hen

• Neuer Spal­tenname: Abgle­ich,
• Spal­tenname: Menge,
• Oper­a­tor: ist gle­ich,
• Wert: Hier zuerst auf das Sym­bol Klick­en und Spalte auswählen markieren, dann Summe,
• Aus­gabe: OK und
• Son­st: Fehler!.

Nach dem Schließen des Fen­sters hat Pow­er Query die Spalte Abgle­ich erstellt und dort bei Übere­in­stim­mung der bei­den vorheri­gen Spal­ten den Wert OK einge­tra­gen, bei Dif­feren­zen den Text Fehler!. Sin­nvoller­weise wer­den sie nun die let­zte Spalte so fil­tern, dass nur die Zeilen mit dem Fehler ste­hen bleiben.

Im let­zten Akt soll­ten Sie die Über­schriften Summe zu Rech­nung und Abgle­ich zu Lager umbe­nen­nen damit jedem, der die Dat­en erhält, sofort klar ist, worum es in der anschließend erstell­ten Tabelle im End­ef­fekt geht. 😎 

Sie bleiben in der Abfrage Abgle­ich. Datei | Schließen & laden und Pow­er Query schreibt in ein neu erstelltes Tabel­len­blatt eine Intel­li­gente Tabelle mit den eben gefilterten Dat­en. Natür­lich hät­ten sie auch Schließen und Laden in… ver­wen­den und den Zielort für die zu schreibende Datei sel­ber bes­tim­men kön­nen. Damit ist die Auf­gabe endgültig erledigt.

▲ nach oben …