Die Basis dieser Auf­gabe habe ich als Fragestel­lung in einem Forum gese­hen. Um mit der (neuen) DSGVO in kein­er Weise in Kon­flikt zu ger­at­en, habe ich nur das Gerüst über­nom­men und prak­tisch alle Dat­en kom­plett verän­dert.

Die eigentliche Auf­gaben­stel­lung stellt sich so dar: In dieser Tabelle sind Kalen­der­wochen, Pro­jek­te und die einzel­nen Wochen­t­age als Spal­tenüber­schriften geschrieben und darunter die entsprechen­den Dat­en. Bei den Wochen­t­a­gen sind jew­eils die erfassten Zeit­en in Indus­trie-Stun­den erfasst wor­den, an Tagen, wo für das entsprechende Pro­jekt kein Arbeit­saufwand erfol­gte, blieb der Inhalt der jew­eili­gen Zelle leer. Für den ersten Überblick hier ein Screen­shot der unfor­matierten Dat­en:

Die Roh-Dat­en in unfor­matiert­er Form

Ich stelle ich Ihnen hier 2 grund­sät­zlich unter­schiedliche Vorge­hensweisen vor. Bei­de haben ihre Vor- aber auch Nachteile. Bei­den gemein­sam ist, dass ich Pow­er Query nutze aber die Abfrage­sprache M nicht voll aus­reize; das würde an dieser Stelle ein­fach nicht in den Rah­men der kosten­losen Infor­ma­tionsver­mit­tlung des Blogs passen. Ich möchte Ihnen damit sagen, dass die Berech­nung des Mon­tags der entsprechen­den Kalen­der­woche zwar auch mit Mit­teln des Pow­er Query durchge­führt wer­den kann, ich jedoch in die Quell­dat­en eine entsprechende Hil­f­ss­palte ein­füge. Hil­f­ss­pal­ten sind ja per se nicht böse und lassen sich ja auch prob­lem­los aus­blenden (und wer­den den­noch von Pow­er Query mit über­nom­men).

▲ nach oben …

Vorbereitung

Prinzip­iell sollte vor jedem Import von Dat­en aus einem Excel-Arbeits­blatt jen­er Bere­ich, der die zu importieren den Dat­en enthält, als Liste/Intelligente Tabelle for­matiert wer­den. Ich klicke dazu in die zu importieren den Dat­en und dann StrgL oder StrgT; Sie kön­nen aber auch gerne den Weg über das Menü (Reg­is­ter Start) gehen, um den gle­ichen Effekt zu erre­ichen. Anschließend markiere ich die Spalte C und füge dort eine neue, leere Spalte ein. Hier berechne ich das Datum des Mon­tags der entsprechen­den Kalen­der­woche. Dazu ver­wende ich in C2 diese angepasste Formel (Quelle: Excelformeln.de):
=7*KÜRZEN((2&-1&-(JAHR(A2)*1))/7+(LINKS(JAHR(A2);2)*1))-5

Dank der Funk­tion­al­ität der for­matierten Tabelle wird die Formel automa­tisch bis zur let­zten Zeile der Dat­en nach unten kopiert und der jew­eilige Mon­tag der dazuge­höri­gen Kalen­der­woche aus­gegeben. Nor­maler­weise wer­den die berech­neten Ergeb­nisse als serielle Zahl dargestellt, der Optik und der Über­prüf­barkeit wegen for­matieren Sie diese (erst ein­mal) als Datum; es reicht gewiss das Kurz­for­mat ohne den Wochen­tag. Ich selb­st vol­lziehe diesen Schritt recht sel­ten, weil die Dat­en in ein­er der kom­menden Schritte sowieso in das reine Zahlen­for­mat (die serielle Zahl) umge­wan­delt wer­den.

▲ nach oben …

Lösung 1 (auch für Einsteiger nachvollziehbar)

Ganz zu Beginn wer­den Sie die Roh­dat­en in den Pow­er Query Edi­tor importieren. Dazu muss eine beliebige Zelle im Bere­ich der Liste markiert sein. Je nach Excel-Ver­sion Klick­en Sie nun in Excel ²⁰¹⁰⁄₂₀₁₃ auf den Menüpunkt Pow­er Query, in ²⁰¹⁶⁄₃₆₅ wählen Sie das Menü Dat­en. Im Menüband anschließend auf Von Tabelle bzw. Aus Tabelle. Es öffnet sich der Edi­tor und das Ganze stellt sich so dar:

Die Dat­en direkt nach dem Import

Fast jede der Spal­ten hat einen „sprechen­den” Namen, ausgenom­men die Spalte mit dem Datum des Mon­tags der jew­eili­gen Kalen­der­woche. Nicht nur der Trans­parenz wegen ändere ich diese Über­schrift auf Datum. – Eine kurze Über­prü­fung, ob alles o. k. ist, was es stan­dard­mäßig auch sein sollte. Dass die Spalte Datum grund­sät­zlich als Datum/Zeit for­matiert ist gehört zu den kleinen Unan­nehm­lichkeit­en des Pow­er Query; ich würde mir wün­schen, dass ich einen Schal­ter set­zen kann, dass kalen­darischen Dat­en, die ohne Zei­tangabe in den Roh­dat­en ste­hen, dann auch (nur) als Datum ohne das ange­hängte 00:00:00 dargestellt wer­den. Aber das nur neben­bei …

Pow­er Query kann nicht so ohne weit­eres einen Tag zu einem gegebe­nen Datum addieren oder sub­trahieren. Also eine berech­nete Spalte mit der Formel =[Datum]+1 wird zu einem Fehler führen. Darum mache ich einen kleinen Umweg und for­matiere die Spalte Datum als Daten­typ: Ganze Zahl. Da ste­ht jet­zt beispiel­sweise in der Daten­zeile 1 der Wert 42.730 (in Pow­er Query ohne den Tausender­punkt, der­ar­tige For­matierung ken­nt PQ nicht).

Da es im Laufe der Arbeit noch einige Abfra­gen mehr wer­den, gebe ich dieser Query einen sin­nvollen Namen: Raw­Da­ta; auch wenn ich die deutsche Sprache im Nor­mal­fall vorziehe ist es mitunter sin­nvoll oder hil­fre­ich, den typ­is­chen inter­na­tionalen Namen zu ver­wen­den. Am ein­fach­sten ändern Sie den Namen dieser Abfrage im recht­en Seit­en­fen­ster, direkt unter Eigen­schaften | Name.

Damit ist jet­zt eine „gesunde” Basis geschaf­fen. Ich spe­ichere diese Abfrage jet­zt, aber nicht über einen Klick auf die Schalt­fläche Schließen & laden son­dern auf den Text darunter und wählen dann im Auswahlmenü die Posi­tion Schließen & laden in…, um anschließend im Dia­log die Auswahl Nur Verbindung zu tre­f­fen. Das hat den Vorteil, dass die zu erstel­len­den Tabellen nur im Arbeitsspe­ich­er des Com­put­ers ver­wal­tet wer­den und nicht in die Arbeitsmappe geschrieben wer­den. Das gilt übri­gens auch für die gle­ich zu erstel­len­den und anschließend bear­beit­eten Dup­likate dieser Abfrage.

Ich per­sön­lich ziehe es vor, solch eine min­i­mal angepasste Basis-Abfrage in exakt dieser Form und Aus­führung zu behal­ten. Ich weiß ja nie, wozu das gut sein kann; vielle­icht brauche ich diese Abfrage später noch ein­mal bei ein­er anderen Gele­gen­heit. Darum erstelle ich jet­zt von dieser Abfrage 5 Dup­likate. Als Vorge­hensweise bevorzuge ich, diese (bis­lang einzige) Abfrage zu öff­nen und dann im linken Seit­en­bere­ich des Edi­tors auf das senkrecht ste­hende Wort Abfra­gen zu Klick­en; das führt dazu, dass aus diesem schmalen Streifen ein richtiges Seit­en­fen­ster wird. Per Recht­sklick auf das Wort Raw­Da­ta in diesem Bere­ich wäh­le ich im Kon­textmenü Duplizieren und wieder­hole anschließend exakt diesen Vor­gang 4 mal. Danach benenne ich die Dup­likate um; aus Raw­Da­ta (2) wird Mon­tag, aus Raw­Da­ta (3) Dien­stag, etc.

Mir fällt es leichter, die logis­che bzw. gewohnte Rei­hen­folge einzuhal­ten. Darum beginne ich mit der fol­gen­den „Runde” wieder beim Mon­tag. Im linken Seit­en­fen­ster also ein Klick auf die Abfrage dieses Wochen­tags. Um die zu nutzen­den Werte des Mon­tags zu separi­eren, markiere ich die ersten 4 Spal­ten der Abfrage. Anschließend ein Recht­sklick in eine der Über­schriften und im Kon­textmenü wäh­le ich Andere Spal­ten ent­fer­nen. Als let­zten Schritt wer­den Sie die Über­schrift des jew­eili­gen Wochen­t­ages (hier: Mon­tag) an die Inhalte der Spalte anpassen; vergeben Sie-auch später in den anderen Spal­ten- einen neu­tralen „sprechen­den” Namen: Stun­den.

Der Mon­tag ist damit erst ein­mal abgeschlossen. Wech­seln Sie zum Dien­stag. Prinzip­iell ist hier das gle­iche Vorge­hen ange­sagt. Nur wer­den Sie hier die ersten 3 Spal­ten markieren und anschließend mit Strg die Spalte Dien­stag. Wie gehabt löschen Sie die nicht markiert Spal­ten per Recht­sklick in eine der markierten Über­schriften und dann im Kon­textmenü Andere Spal­ten ent­fer­nen. Da die jew­eilige serielle Zahl aber für den Mon­tag und nicht für den Dien­stag der entsprechen­den Kalen­der­woche ste­ht, muss die Zahl um den Wert 1 erhöht wer­den. Es gibt ver­schiedene Wege, das Ziel zu erre­ichen ich wech­se­le dazu in das Reg­is­ter Trans­formieren, markiere die Spalte Datum und im Menüband ein Klick auf Stan­dard | Addieren. Im Dia­log geben Sie (natür­lich) eine 1 in das Textfeld ein, denn sie wollen ja den Wert um 1 erhöhen. Im Prinzip gle­ich­es Vorge­hen bei Mittwoch bis zum Fre­itag, nur dass Sie die Zahl der zu addieren­den Tage entsprechend anpassen.

Jet­zt existieren 5 Abfra­gen mit den kor­rek­ten Werten für die einzel­nen Wochen­t­age. Und da die Spal­ten stets die gle­ichen Über­schriften haben und der Daten­typ auch immer iden­tisch ist, lassen sich diese Queries auch her­vor­ra­gend ein­er neuen Abfrage zusam­men­fassen. Meine Macke mit der logis­chen Rei­hen­folge treu bleibend beginne ich wieder mit dem Mon­tag. 😎 

Wech­sel zum Menüreg­is­ter Start, Schalt­fläche Kom­binieren und im Drop­down den Menüpunkt Abfra­gen anfü­gen durch einen Klick auf  erweit­ern, dann Abfra­gen als neu anfü­gen. Im Dia­log erst ein­mal die Auswahl 3 oder mehr Tabellen markieren. Das stellt sich nun so dar:

Um mehr als 1 Tabelle anzufü­gen …

Der Mon­tag ist im Bere­ich Anzufü­gende Tabellen bere­its einge­tra­gen. Markieren Sie im linken Bere­ich Ver­füg­bare Tabelle(n) die Ein­träge Dien­stag bis Fre­itag und Klick­en Sie dann auf Hinzuf… Umge­hend sind in der recht­en Box alle Wochen­t­age sicht­bar. Schließen Sie das Fen­ster mit einem Klick auf den OK But­ton. Die neue Abfrage stellt sich so dar:

Nach dem zusam­men­fü­gen aller Wochen­t­age-Tabellen

Eigentlich ist damit die Abfrage kom­plett, das Ziel erre­icht. Wie gesagt, eigentlich. Denn was noch ziem­lich stört ist die Darstel­lung, die For­matierung der Spalte Datum. Darum vergeben sie hier den Daten­typ Datum und es ste­ht ein für uns Anwen­der bess­er les­bar­er Werte in jed­er Zeile der Spalte, das offen­sichtliche Datum. 😆 Als Kos­metik wer­den sie jet­zt die Abfrage zuerst nach Datum auf­steigend sortieren und anschließend eben­falls auf­steigend nach Pro­jekt. Als let­zten Schritt fil­tern Sie die Spalte Stun­den so, dass sie beim Wert (NULL) das Häkchen ent­fer­nen.

Ein Klick auf das Sym­bol Schließen & laden und in einem neuen Arbeits­blatt wird eine Tabelle mit 4 Spal­ten und 39 Zeilen plus Über­schriften erstellt. Und das ist nun wirk­lich das gewün­schte Ziel. Wenn Sie möcht­en kön­nen Sie die Dat­en nun noch in ein­er Piv­ot­Table weit­er auswerten. Und natür­lich kön­nen Sie auch mit Pow­er Query noch dieses oder jenes anstellen. 💡 

▲ nach oben …

Lösung 2 (Kurz, knapp, effizient)

Nach dieser vielle­icht etwas pro­vokan­ten Über­schrift wer­den sich vielle­icht fra­gen, warum eine Lösung mit diesen Attribut­en nicht für Ein­steiger geeignet sein soll. Nun ja, das hat etwas mit ein­er weit­eren Macke von mir zu tun: Ich lege bei der Wis­sensver­mit­tlung Wert darauf, dass die Anwen­der das Vorge­hen nicht nur abschreiben, son­dern die einzel­nen Schritte auch nachvol­lziehen, möglichst sog­ar ver­ste­hen kön­nen, um darauf später in Eigenini­tia­tive auf­bauen zu kön­nen. Diese Lösung ist zwar um einiges pro­fes­sioneller und vor allen Din­gen kürz­er als die erste, dafür aber auch längst nicht so trans­par­ent und sie set­zt schon etwas Erfahrung mit Pow­er Query voraus. Als Doku­men­ta­tion wie kom­plex diese Lösung ist hier ein Screen­shot der einzel­nen aber auch einzi­gen Schritte:

Über­sichtliche Zahl Arbeitss­chritte

Das ist wirk­lich alles. Sie kön­nen entwed­er wieder mit den unfor­matierten Quell­dat­en aus dem Down­load begin­nen (vorher bei Bedarf die eben erstellte Lösungs-Datei umbe­nen­nen) oder aber in der bish­eri­gen Arbeitsmappe weit­er­ar­beit­en und darin eine weit­ere Abfrage erstellen.

Wenn Sie wieder bei Null anfan­gen, dann for­matieren sie die Dat­en ide­al­er­weise erst ein­mal als Tabelle, fügen die Formel für die Berech­nung des Mon­tags der entsprechen­den Kalen­der­woche wie bere­its weit­er oben beschrieben ein, ändern für die neue Spalte die Über­schrift auf beispiel­sweise MoDa­tum (Mon­tag-Datum) und importieren Sie die Tabelle anschließend wie gewohnt in den Pow­er Query Edi­tor. – Falls Sie direkt auf dem bish­er erar­beit­eten auf­bauen ohne die Datei zu wech­seln, erstellen Sie eine Kopie, also ein Dup­likat der Abfrage Raw­Da­ta und ändern den Namen eventuell auf Raw­Da­ta (Kopie). Dadurch ist die Aus­gangslage für bei­de Vorge­hensweisen iden­tisch. Acht­en Sie aber bitte in jedem Fall darauf, dass die kalen­darischen Dat­en in der Spalte MoDa­tum als serielle Zahl vor­liegen; erforder­lichen­falls wan­deln Sie den Daten­typ in Ganze Zahl.

Markieren Sie nun die ersten 3 Spal­ten, also KW, Pro­jekt und MoDa­tum genau in dieser Rei­hen­folge; das geht übri­gens auch mith­il­fe der Taste Shift. Nun ein Recht­sklick in eine der markierten Über­schriften und Andere Spal­ten ent­piv­otieren. Dadurch entste­ht eine 5‑spaltigen Liste mit ins­ge­samt 39 Zeilen:

Dem Ziel sehr nahe: Nach dem ent­piv­otieren

Wie auch in der ersten Übung muss dem Wochen­tag ja das kor­rek­te Datum zuge­ord­net wer­den. Das geschieht ‑ähn­lich wie in Excel- mit ein­er WENN-Formel, die hier aber die eine oder andere Beson­der­heit hat. Auf der einen Seite ist die Sprache der Weisun­gen, Funk­tio­nen, Entschei­dun­gen, … stets Englisch. Und auf der anderen Seite ist es wichtig, die Schlüs­sel­be­griffe wie beispiel­sweise if und else in exakt dieser Groß- Klein­schrei­bung zu ver­wen­den. Um das Vorhaben zu real­isieren, erstellen Sie eine neue Spalte. Im Menü-Reg­is­ter aktivieren Sie Spalte hinzufü­gen, im Menüband dann Benutzerdefinierte Spalte. Im Dia­log geben Sie bei Neuer Spal­tenname Datum ein, im Bere­ich Benutzerdefinierte Spal­tenformel diese Formel:

if [Attribut]="Dienstag" then [MoDatum]+1 else
   if [Attribut]="Mittwoch" then [MoDatum]+2 else
   if [Attribut]="Donnerstag" then [MoDatum]+3 else
   if [Attribut]="Freitag" then [MoDatum]+4
   else [MoDatum]

(Sie kön­nen übri­gens­diesen Formel-Text hierüber mit der Maus markieren, in die Zwis­chen­ablage kopieren und dann in Ihrem Dialogfen­ster ein­fü­gen.)

Die fer­tig eingegebene Formel im Dia­log

Nach einem Klick auf OK wird eine neue Spalte mit den ganz­zahli­gen, seriellen Werten der berech­neten kalen­darischen Dat­en des jew­eili­gen Wochen­t­ages erstellt. Hier begin­nend mit 42730, 42731, … Im näch­sten Schritt löschen Sie die Spal­ten MoDa­tum und Attrib­ut. Geben Sie der Spalte Wert eine neue Über­schrift: Stun­den. Ver­schieben sie diese Spalte nun ans Ende.

Jet­zt haben Sie es fast geschafft. Bleibt nur noch, die Spalte Datum auch den Daten­typ: Datum zuzuweisen, um ein „richtiges” Datum 😉 zu sehen. Nun die Spalte Datum auf­steigend sortieren und anschließend auch die Spalte Pro­jekt auf­steigend sortieren. Die let­zte „Amt­shand­lung” beste­ht darin, dass sie jet­zt Schließen & laden in… wählen um die Abfrage in einem neuen Arbeits­blatt abzule­gen oder diese Abfrage an gewün­schter Stelle zu posi­tion­ieren. Der Weg über Schließen & laden in… ist erforder­lich, da Sie son­st (wahrschein­lich) nur eine Verbindung erstellen und das Ergeb­nis nicht als Tabelle in der Arbeitsmappe sehen. – Und wenn Sie jet­zt schneller waren, als ich schreiben kon­nte dann schauen Sie ein­mal hier nach, um das ele­gant wieder ger­ade zu biegen.

Wenn Sie die bei­den Vorge­hensweisen vom Ergeb­nis her ver­gle­ichen wer­den Sie fest­stellen, dass es keinen Unter­schied gibt, obwohl die Vorge­hensweise bei­der Übun­gen recht unter­schiedlich ist. – Da das Ergeb­nis ein­er ganz nor­male for­matierte Tabelle ist, kön­nen Sie diese natür­lich auch auf beliebige Weise weit­er­ver­ar­beit­en und auswerten. Vielfach bietet sich da eine Piv­ot­Table an.

Soll­ten Sie das Bedürf­nis haben, in Pow­er Query weit­ere Auswer­tun­gen vorzunehmen (beispiel­sweise die zu berech­nende Stun­den­zahl für jeden Kun­den im einzel­nen Monat per Grup­pierung), dann empfehle ich Ihnen, von der let­zten erar­beit­eten Ver­sion wiederum ein Dup­likat oder einen Ver­weis zu erstellen und diese zusät­zliche Abfrage als Basis  zu nehmen.

▲ nach oben …