Wis­sens­stand: Lev­el 1 ⇒ Ein­steiger in PQ, keine/kaum Erfahrung

Prolog

Dieser Work­shop umfasst mehrere Kapi­tel und ist ins­beson­dere für Ein­steiger in Sachen Pow­er Query gedacht und geeignet. Und gle­ich an dieser Stelle eine kleine Erk­lärung, wie es im Titel zum Schlag­wort „Klein­brot” kommt: Vor vie­len Jahren habe ich für meine Schu­lun­gen ein­mal eine Muster-Tabelle erstellt, die (damals noch auf DM- und nicht auf €-Basis) die Umsätze der Bäck­erei Klein­brot divers­er Pro­duk­t­grup­pen in den ver­schiede­nen Monat­en aufge­führt hat und als Basis für grundle­gende Berech­nun­gen diente. Anfangs war es in der Tat exakt jene Tabelle, welche Sie für diese erste Übung ver­wen­den wer­den (allerd­ings Jahr 2014). Später habe ich dann für erweit­erte Übun­gen die Umsatz­zahlen auf die einzel­nen Arbeit­stage des Jahres 2014 extrapoliert. Diese Tabellen und Arbeits­blät­ter wer­den Sie im Laufe des Pro­jek­ts auch noch zum Down­load ange­boten bekom­men. 🙂

▲ nach oben …

Einstieg

Grundle­gen­der Hin­weis: Ich spe­ichere die hier ver­wen­de­ten Dat­en alle im Verze­ich­nis C:\Data\Kleinbrot_18-22\; wenn Sie ein­mal meine Pow­er Query-Ergeb­nisse dieses Pro­jek­ts herun­ter­laden wird dort als Daten­quelle stets dieser Pfad ver­wen­det; erforder­lichen­falls müssen Sie in Ihrem Pro­jekt eine entsprechende Änderung vornehmen. Und meine Anweisun­gen, die sich in diesem Pro­jekt auf zu importierende Dat­en beziehen, ver­wen­den naturgemäß stets diesen Pfad.

Ich empfinde es als unge­mein hil­fre­ich, wenn ich die Dat­en, welche ich bear­beit­en oder auswerten möchte, im Orig­i­nalzu­s­tand kenne. Erforder­lichen­falls kann ich an der Stelle bere­its entschei­den, ob die vor­liegen­den Dat­en über­haupt geeignet sind oder ich vielle­icht grundle­gende Änderun­gen vornehmen sollte bzw. muss.

Öff­nen Sie also erst ein­mal dieses File und lassen Sie es auf sich wirken. Beacht­en Sie auch, dass dort ver­bun­dene Zellen existieren, welche zwar der Optik sehr dien­lich sein kön­nen aber vielfach zu Prob­le­men bei Berech­nun­gen mit Formel und auch VBA führen kön­nen. – Wenn Sie sich einen Ein­druck ver­schafft haben, schließen Sie das File ohne Änderun­gen vorgenom­men zu haben und spe­ich­ern Sie die Arbeitsmappe beispiel­sweise in C:\Data\Kleinbrot_18-22\ unter dem gle­ichen Namen ab. Ide­al­er­weise schließen Sie Excel und rufen das Pro­gramm sofort im Anschluss mit ein­er leeren Arbeitsmappe wieder auf.

▲ nach oben …

Der Import

Bekan­ntlich führen sehr viele Wege nach Rom und auch hier gibt es einige unter­schiedliche Möglichkeit­en um zum Ziel zu gelan­gen, also die gewün­scht­en Dat­en zu importieren. Ich wäh­le hier einen Weg, der kom­fort­a­bel und sich­er exakt so nach Pow­er Query importiert, wie ich es möchte.

Sie befind­en sich in der leeren Arbeitsmappe. Gehen Sie über das Menü Dat­en | Dat­en abrufen | Aus Datei | Aus Excel Arbeitsmappe. Wech­seln Sie im Dia­log zu dem Verze­ich­nis, wo Sie das importierte File abgelegt haben. Markieren Sie im Dia­log den Ein­trag Kleinbrot_Kreuztabelle 2018.xlsx und Klick­en Sie anschließend auf Importieren. Direkt danach öffnet sich der Nav­i­ga­tor-Dia­log und Klick­en dann im linken Teil des Fen­sters auf die Zeile Tabelle1. Das stellt sich nun so dar:

Der Bere­ich­sname Tabelle1 wurde auti­ma­tisch durch Pow­er Query vergeben

Erweit­ern Sie die Schalt­fläche Laden   und wählen Laden in… | (und im Dia­log) Nur Verbindung erstellen um zu ver­hin­dern, dass die Dat­en jet­zt schon direkt in ein Excel Arbeits­blatt geschrieben wer­den. – Sie wer­den die kom­menden Schritte so gut wie auss­chließlich im Pow­er Query-Edi­tor durch­führen.

Das Excel-Arbeits­blatt bleibt leer, im recht­en Teil hat sich ein Seit­en­fen­ster aufge­tan, wo der Ein­trag ein­er Abfrage (Tabelle1) mit dem Zusatz Nur Verbindung aufgezeigt wird:

Tabelle1 als ‘Nur Verbindung’

Um diese Abfrage zu öff­nen und darin zu arbeit­en führen Sie entwed­er einen Recht­sklick (mit entsprechen­der Reak­tion im Kon­text) oder einen Dop­pelk­lick auf diesen Ein­trag durch; der Pow­er Query-Edi­tor stellt sich nun so dar:

Pow­er Query hat die kom­plet­ten Dat­en selb­st­ständig importiert

Sie erken­nen, dass Pow­er Query mit Aus­nahme der ersten Spalte automa­tis­che Über­schrift-Namen vergeben hat; Eine kor­rek­te Tabelle oder Liste muss eine ein­deutige Über­schrift in jed­er Spalte haben. Für die erste Spalte wurde der Wert aus der Zelle A1 der Quell­dat­en über­nom­men. Auch wenn es wegen der Zen­trierung über mehrere Zellen so scheint, der Text Bäck­erei Klein­brot ste­ht tat­säch­lich auss­chließlich in A1 der Quell­dat­en.

Spätestens jet­zt ist es an der Zeit sich zu über­legen, welche Dat­en für die Auswer­tung wirk­lich gebraucht wer­den und welche nicht. Was zur Folge hat, dass ver­schiedene Zeilen und auch Spal­ten gelöscht bzw. nicht über­nom­men wer­den. Da Berech­nun­gen in PQ deut­lich bess­er und per­for­man­ter durchge­führt wer­den als in Plain Excel brauchen Sie auss­chließlich die Zeilen für Jan­u­ar bis Dezem­ber und auch die Spal­ten Brot bis Son­stiges. Gehen Sie dazu so vor:

• Start | (Gruppe Spal­ten ver­wal­ten) Zeilen ver­ringern | Zeilen ent­fer­nen | Erste Zeilen ent­fer­nen und geben Sie bei Anzahl von Zeilen eine 2 ein, um die bei­den ersten Zeilen zu löschen. Hin­weis: Die Zeilen wer­den wirk­lich gelöscht und nicht (wie in Excel) nur ver­steckt. Das erken­nen Sie auch an der Zeilen­num­merierung.
• Anschließend im Menüband auf Erste Zeile als Über­schrift ver­wen­den Klick­en.
• Danach ändern Sie die Über­schrift Column1 (eben noch Bäck­erei Klein­brot) auf Monat.
• Im näch­sten Schritt markieren Sie die Über­schrift Gesamt, Shift und ein Klick auf Durch­schnitt; bei­de Spal­ten sind markiert. Nun entwed­er die Taste Entf oder ein Recht­sklick in eine der bei­den markierten Über­schriften und Spal­ten ent­fer­nen auswählen, um diese Spal­ten zu löschen.
• Da Sie nur die Dat­en der zwölf Monate brauchen, wiederum Zeilen ver­ringern | Zeilen beibehal­ten | erste Zeilen beibehal­ten und geben Sie im Dia­log 12 ein.

▲ nach oben …

Weiter geht’s

Nun sind nur noch jene Dat­en in der Abfrage exis­tent, die auch wirk­lich gebraucht wer­den. Das Ziel soll sein, daraus eine Piv­ot­Table zu erstellen. Ver­suchen Sie es gerne, indem sie auf Schließen & laden Klick­en, dann im recht­en Seit­en­fen­ster ein Recht­sklick auf das Feld Tabelle1  Nur Verbindung und wählen Sie im Kon­textmenü die Zeile Laden in… Im sich dann öff­nen­den Dia­log wählen Sie Piv­ot­Table-Bericht:

Die Query als Piv­ot-Tabelle spe­ich­ern

Und im recht­en Seit­en­fen­ster kön­nen Sie nach einem OK die Piv­ot­Table-Felder auswählen und in der Excel-Tabelle anord­nen. Allerd­ings wer­den Sie (falls Sie es nicht schon iim Vor­wege wussten) rasch fest­stellen, dass eine „vernün­ftige” Auswer­tung bei dieser Kon­stel­la­tion nicht möglich ist: 🙁

Kreuzta­bellen eignen sich nicht als Daten­ba­sis für eine Piv­ot­Table

Das wäre naturgemäß auch nicht anders, wenn sie im Orig­i­nal-File die gewün­scht­en Dat­en in Excel markiert und dann in eine Piv­ot-Tabelle exportiert hät­ten. Eine Kreuzta­belle ist zwar gut für die Bild­schirm- und Papi­er-Ansicht,  eignet sich nun ein­mal nicht dafür, als Piv­ot­Table aus­gew­ertet zu wer­den. Um das Ziel zu erre­ichen, müssen die Dat­en in ein­er Daten­satz-Liste (wie in ein­er Daten­bank) vor­liegen; 3 Spal­ten: Monat, Pro­dukt, Umsatz . Darum löschen Sie die Piv­ot­Table, indem Sie beispiel­sweise auf den Schnittpunkt zwis­chen Zeilen- und Spal­tenkopf Klick­en:

Hier ein Klick und Sie löschen das gesamte Arbeits­blatt

… wobei das gesamte Arbeits­blatt markiert wird; und dann Entf oder irgend­wo ein Recht­sklick und Zellen löschen. Nun ist das Arbeits­blatt wieder leer. Gle­ich wer­den Sie dort (in A1)wieder eine „vernün­ftige” 😉 Piv­ot Tabelle erstellen.

Öff­nen Sie nun wieder die Abfrage Tabelle1. Markieren Sie die erste Spalte (Monat), indem sie in die Über­schrift Klick­en; diese ist nun in einem dun­kleren Grün als die Daten­felder dargestellt. Nun ein Recht­sklick in die Über­schrift und im Kon­textmenü ein Klick auf Andere Spal­ten ent­piv­otieren. Und schon haben Sie eine ordentliche Daten­satz-Liste, die nicht mehr aus 12 son­dern nun aus 72 Zeilen beste­ht:

Die Daten­satz-Liste nach dem ent­piv­otieren

Jedes Pro­dukt ist in jedem Monat in ein­er eige­nen Zeile vertreten. Ide­al­er­weise geben Sie nun der Spalte Attrib­ut den Namen Pro­dukt ¿ und ändern Sie die Über­schrift Wert beispiel­sweise in Umsatz. Wenn Sie nun auf dieser Daten­ba­sis auf dem gle­ichen Wege wie vorher eine Piv­ot­Table erstellen wer­den sie erken­nen, dass die Aus­gangslage deut­lich bess­er ist als vorher. 😉 Zugegeben, eine Piv­ot-Grup­pierung nach beispiel­sweise Quar­tal­en ist hier (noch) nicht möglich, aber das lässt sich auch mit wenig Aufwand in Pow­er Query so abän­dern, dass sie diesen Wun­sch real­isieren kön­nen. Lesen Sie dazu ein­fach das näch­ste Kapi­tel.

„Spie­len” sie jet­zt in der Piv­ot-Tabelle gerne mit beispiel­sweise dem Daten­schnitt für die Monate und vielle­icht auch die Pro­duk­te, um eine ele­gante Fil­terung zu ermöglichen. Und natür­lich kön­nen Sie auf diesem Wege auch „zu Fuß” nach dem gewün­scht­en Quar­tal fil­tern. Hin­weis: Sollte Ihnen das The­ma Piv­ot­Table nicht geläu­fig sein, lesen Sie gerne an dieser Stelle eine aus­führliche Ein­führung zu diesem Future. Und mein Ergeb­nis dieser Auf­gabe kön­nen Sie zum ver­gle­ichen hier herun­ter­laden.