PQ-Projekt/Workshop „Kleinbrot” (2)

Im ersten Teil dieses Work­shops haben Sie mit dem ent­piv­otieren der Dat­en eines der mächtig­sten Tools des Pow­er Query ken­nen­gel­ernt und damit die Basis für eine Piv­ot­Ta­belle gelegt und nach einem etwas verunglück­ten Ver­such auch eine anwend­bare Piv­ot­Table erstellt. 😉 Nutzen Sie Ihre fer­tig erstellte Datei, um direkt an den Stand der Dinge anzuschließen. Alter­na­tiv biete ich Ihnen mein bis hier­her erar­beitest Ergeb­nis hier zum Down­load an. Passen Sie bei Bedarf den Pfad der Daten­quelle an.¿ Diese PQ-Dat­en (das Abfrage-Ergeb­nis) hat­ten noch ein kleines Manko: In der Piv­ot­Ta­belle waren die Monate rein­er Text also keine kalen­darischen Dat­en, darum ist im Piv­ot­Table auch keine „echte” Grup­pierung nach Quar­tal­en möglich gewe­sen. Die Schlussfol­gerung: Die Spalte mit den Monat­sna­men muss vom Daten­typ Datum sein, damit das Vorhaben gelingt.

Hin­weis: Der hier beschriebene Weg wird für „ges­tandene” PQ-User fürchter­lich umständlich anmuten und gewiss den Gedanken ini­ti­ieren, dass es mit Ein­satz von M-Funk­tio­nen schneller, bess­er, … geht. Stimmt! Aber ich spreche hier Ein­steiger in Sachen Pow­er Query an und ich möchte so viel wie möglich per Mausklick abar­beit­en. Das erhöht mein­er Erfahrung nach die Bere­itschaft, sich mit der The­matik PQ weit­er­hin und auch tiefer auseinan­der zu set­zen.

Also erst ein­mal ein Wech­sel zurück zum Pow­er Query-Edi­tor. Das gelingt gut, wenn Sie im recht­en Seit­en­fen­ster auf das hier markierte Sym­bol Klick­en (Hin­weis: obere und untere Posi­tion kön­nen auch umgekehrt sein):

Wech­sel zwis­chen Piv­ot­Ta­bele und Pow­er Query-Edi­tor

Soll­ten Sie nur das Sym­bol für die Piv­ot­Ta­belle sehen, dann bitte im Menü Dat­en | (Gruppe Abfra­gen und Verbindun­gen) Abfra­gen und Verbindun­gen anklick­en. Nach dem öff­nen der Abfrage sind Sie wieder im PQ-Edi­tor; markieren Sie als erstes die Spalte Monat¿. Wech­seln Sie zum Menü Trans­formieren | (Gruppe Textspalte) For­mat | Prä­fix hinzufü­gen. Geben Sie in das Textfeld (Wert) ein: 1.  (also die Zif­fer 1, ein Punkt . und ein Leerze­ichen) und dann OK.

The­o­retisch kön­nten Sie aus diesen Werten bere­its ein echt­es Datum gener­ieren. Aber das würde das Gesamt­bild ver­fälschen, denn Pow­er Query reagiert hier genau wie Excel und würde automa­tisch das aktuelle Jahr für das gener­ierte Datum ver­wen­den. Und das kann es doch wohl nicht sein… Darum noch ein­mal For­mat | Suf­fix hinzufü­gen und geben Sie in das Textfeld erst ein Leerze­ichen und dann (für das Jahr) 2018 ein.

Das ganze sieht jet­zt zwar wie ein Datum aus, aber an der links­bündi­gen Aus­rich­tung erken­nen Sie, dass es sich immer noch um einen Text han­delt. Bei weit­er­hin markiert­er Spalte in der Gruppe Datum & Uhrzeit ein Klick auf Datum | Analysieren. Und jet­zt ist es tat­säch­lich ein Datum, was Sie auch am Sym­bol links in der Über­schrift erken­nen; es soll einen Kalen­der darstellen. Ein Klick auf Start | Schließen & laden und Sie gelan­gen umge­hend in die Piv­ot­Ta­belle, wo die Text-Monat­sna­men durch das Datum erset­zt wor­den sind.

Trotz allem denke ich „naja”, denn am 1. Jan­u­ar 2018 ist ja garantiert noch nicht der gesamte Monat­sum­satz erre­icht wor­den. 😉 Um Irri­ta­tio­nen vorzubeu­gen wäre beispiel­sweise Jan­u­ar 2018 oder die Anzeige des let­zten Tag des Monats ein­fach bess­er.

▲ nach oben …

Monatsname und Jahr

Im Piv­ot-Feld Monat ste­ht ja schon ein echt­es, wenn auch nicht zutr­e­f­fend­es Datum. Wenn es aber nur um die Optik, die Darstel­lung in der Piv­ot­Table geht, dann kön­nen Sie das Ausse­hen mit geringem Aufwand anpassen. Falls erforder­lich, wech­seln Sie im recht­en Seit­en­fen­ster zurAn­sicht für die Piv­ot-Bear­beit­ng (die bei­den Schalt­flächen oben rechts). Klick­en Sie im recht­en Seit­en­fen­ster (Piv­ot­Table-Felder) im unteren Bere­ich im Kas­ten Zeilen auf das Feld Monat. Im Drop­down wählen Sie dann Felde­in­stel­lun­gen. In diesem Fen­ster und dort ein Klick auf die Schalt­fläche Zahlen­for­mat:

Hier ändern Sie das Zahlen­for­mat für das Datum

Ide­al­er­weise wer­den Sie das Benutzerdefiniert-Zahlen­for­mat auswählen und als For­mat dann MMMM JJJJ wählen bzw. anpassen. Wenn Sie dann noch die Über­schriften abän­dern und die Umsatz-Spalte als Währung for­matieren, sieht das ganze so aus:

Die Piv­ot­Ta­belle ist schon „sauber” for­matiert

Das sieht doch schon ein­mal richtig gut aus; der Monat ist voll aus­geschrieben und das Jahr vier­stel­lig. Bedenken Sie aber, dass hin­ter den kalen­darischen Dat­en immer noch das Datum des Monat­ser­sten ste­ht. Wenn andere, uner­fahrene User daran „herum­spie­len”, kann es rasch zu Irri­ta­tio­nen bzw. unan­genehmen Über­raschun­gen kom­men. 🙁

In diesem Sta­di­um sind die Piv­ot-Dat­en noch nicht grup­piert. In dem Moment, wo sie eine Piv­ot-Grup­pierung nach Monat­en und/oder Quar­tal­en vornehmen, wird der Monat­sname unab­d­ing­bar in 3‑stelliger Kurz­schreib­weise dargestellt. Schade… (Stand Anfang 2023)

Klassisches Datum, Monatsende

Derzeit basieren die Piv­ot-Dat­en ja jew­eils auf dem Monat­ser­sten. Und wenn Sie beispiel­sweise wegen ein­er inter­na­tion­al ver­wend­baren Darstel­lung ein klas­sis­ches Datum im For­mat tt.MM.jjjj in der Spalte mit den Monat­en und den Pro­duk­ten anzeigen lassen wollen, sollte es der Klarheit wegen der let­zte Tag des entsprechen­den Monats sein. Wech­seln Sie dazu wieder in den Pow­er Query-Edi­tor. Natür­lich wäre es möglich, jedem Monat die Anzahl der Tage per Hand zuzuweisen. Aber erstens wäre das mit einigem Aufwand ver­bun­den und zweit­ens ist dann auch noch das Prob­lem „Feb­ru­ar”, der bekan­ntlich alle vier Jahre ein Schalt­jahr ist und nicht 28 son­dern 29 Tage hat.

Pow­er Query bietet Ihnen hier eine sehr gute, aus mein­er Sicht ele­gante Möglichkeit an, das jew­eilige Monat­sende zu berech­nen. Markieren Sie die Spalte Datum, wech­seln Sie erforder­lichen­falls in das Menü Trans­formieren und Datum | Monat | Monat­sende. Und Voila, es wird für jeden Monat (auch in Schalt­jahren) das kor­rek­te Monat­sende als kalen­darischen Datum dargestellt. Schließen & laden und in der Piv­ot­Ta­belle wird das kor­rek­te Datum des Ulti­mo angezeigt. Wenn Sie vorher das Zeilen­for­mat auf die Lang­form angepasst haben, ändern Sie es auf das gewün­schte Stan­dard-Datum z.B. TT.MM.JJJJ.

Und wie gewohnt hier mein erar­beit­etes Ergeb­nis (allerd­ings noch mit der Lang­form der Monate, die Änderung wer­den Sie gewiss sel­ber schaf­fen 😉 ).

▲ nach oben …

Zurück zum ersten Beitrag des Work­shops.

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

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

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,50 € freuen … (← Klick mich!)