In einem Forum (Her­ber, Link-Zugriff lei­der nicht mehr möglich) wurde unter dem Titel „Tabellen zusam­men­fassen” eine schein­bar recht ein­fache Frage gestellt. Zumin­d­est auf den ersten Blick. Beim zweit­en Hin­se­hen zeigte sich, dass doch etwas mehr „Gehirn­schmalz” erforder­lich ist, um eine einiger­maßen „schlanke” und auch nachvol­lziehbare Lösung zu find­en.

Damit Sie gle­ich jene Datei zur Ver­fü­gung haben, welche ich für dieses für diese Muster­lö­sung ver­wende, laden Sie ide­al­er­weise dieses File von unserem Serv­er herunter. Wenn sie nun im Forum den ersten Beitrag zum The­ma durch­le­sen wer­den sie erken­nen, dass in den Zeilen 2:12 die auszuw­er­tenden Dat­en ste­hen, in Zeile 1 (zumin­d­est teil­weise) die Über­schriften. In den Zeilen 14:21 ist das Wun­schergeb­nis des Fragestellers dargestellt.

Da es ein „ungeschriebenes Gesetz” (grund­sät­zlich eine Voraus­set­zung) ist, dass in ein­er Liste jede Spalte eine ein­deutige Über­schrift haben muss, habe ich in den ersten drei Spal­ten ein­fach eine eigene, hof­fentlich sin­nvolle Über­schrift einge­fügt: Spalte A: Song, Spalte_B: Band/Interpret und Spalte C ein­fach ein Frageze­ichen ?, da ich die Bedeu­tung nicht kenne. Das stellt sich nun so dar:

Die Roh-Dat­en vor dem Import

… Und da ja bekan­ntlich viele Wege nach Rom führen, stelle ich Ihnen hier diesen oder jenen Weg zum Erproben vor.

Vorarbeit für jede der Möglichkeiten

Im ersten Schritt wer­den Sie eine beliebige Zelle der Roh­dat­en markieren und StrgT oder StrgL Klick­en, um daraus eine so genan­nte Intel­li­gente Tabelle zu machen. Den gle­ichen Effekt erre­ichen Sie, wenn sie im Reg­is­ter Start Gruppe For­matvor­la­gen auf das Sym­bol Als Tabelle for­matieren Klick­en. Und diese Tabelle wer­den Sie jet­zt in den Pow­er Query-Edi­tor importieren. Das geht am besten, wenn Sie entwed­er das Reg­is­ter Pow­er Query (Excel ²⁰¹⁰⁄₁₃) bzw. in neueren Ver­sio­nen das Reg­is­ter Dat­en aktivieren und dort auf Von Tabelle bzw. Aus Tabelle Klick­en. Die Dat­en wer­den in den Edi­tor importiert und es stellt sich nun so dar:

Die Dat­en sind in den Pow­er Query-Edi­tor importiert

Die Forderung des Fragestellers das war ja, dass zuerst die Spalte 1 (Song) und anschließend die Spalte 2 (Band/Interpret) jew­eils auf­steigend sortiert wer­den soll. Darum Klick­en Sie zuerst in die Spalte Song, erweit­ern die Über­schrift durch einen Klick auf  und sortieren die Dat­en auf­steigend. Anschließend gle­icher­maßen die Spalte Band/Interpret auf­steigend sortieren. Sie erken­nen, dass diese Vorge­hensweise anders ist als in Excel, in Pow­er Query wird stets die logis­che Rei­hen­folge des Sortieren angewen­det.  😎 

Falls Sie sich wun­dern, dass ich trotz offen­sichtlich beste­hen­der kor­rek­ter Sortierung diesen Vor­gang noch ein­mal durch­führe, dann hat es dur­chaus seinen Grund: Zu oft erlebe ich, dass irgend­wann neue Dat­en hinzuge­fügt wer­den und die Sortierung dann nicht mehr so ist, wie sie sein soll. Auf diese Weise erledigt Pow­er Query für Sie die Arbeit ganz alleine bei jedem Aktu­al­isieren der Datei.

▲ nach oben …

(1) Sicher ist Sicher

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

Sie ken­nen das vielle­icht von Flughäfen oder anderen sicher­heit­srel­e­van­ten Insti­tu­tio­nen, die Sicher­heit­skon­trollen sind für alle Beteiligten immer mit etwas mehr Aufwand ver­bun­den aber dafür gilt der Satz „safe­ty first” (Sicher­heit geht vor). Und so ist es auch hier in Pow­er Query. Etwas mehr Aufwand, dafür aber die Sicher­heit, dass nicht irgend­wo ein Mausklick vergessen wird und es dadurch zu ver­fälschen Ergeb­nis­sen kommt. Und im End­ef­fekt bietet solch eine Lösung fast immer mehr Flex­i­bil­ität für kün­ftige Wün­sche, welche mit diesen Basis­dat­en ver­wirk­licht wer­den sollen.

Der aus mein­er Sicht sich­er­ste Weg geht über den Vor­gang des Ent­piv­otieren und anschließend (nach ein­er Bear­beitung) wieder Piv­otieren der Dat­en. Und das große „Aber” dabei ist, dass sie ide­al­er­weise einige Ken­nt­nisse in Pow­er Query haben soll­ten oder wenn sie Ein­steiger in Sachen PQ sind, ein gerüt­telt Maß an Lern­bere­itschaft mit­brin­gen. Aber wie gesagt, dieser Weg ist sich­er, wenn Sie die fol­gende Anweisung haarklein nachvol­lziehen. – Und im kom­menden Abschnitt ist auch ein Weg für Ein­steiger beschrieben.

Um mir die fol­gen­den Schritte etwas leichter zu gestal­ten, führe ich die Spal­ten Song und Band/Interpret der­art zusam­men, dass sie in ein­er einzi­gen Spalte ste­hen und mit Tab­stopp als Trennze­ichen verse­hen sind. Den automa­tisch gener­ierten Namen Zusam­menge­führt belasse ich der Klarheit wegen. In ein­er der let­zten Schritte bekom­men die wieder getren­nten Spal­ten dann die ursprünglichen Beze­ich­nun­gen zurück.

Nun markieren Sie die Spalte Zusam­menge­führt, Recht­sklick in die Über­schrift und anschließend Andere Spal­ten ent­piv­otieren. Im Anschluss stellt sich das so dar:

Die Dat­en direkt nach dem ent­piv­otieren

Diesen Stand in der Abfrage wer­den sie später noch ein­mal brauchen. Von dieser Query stellen Sie vor weit­eren Schrit­ten ein Dup­likat auf der Basis des Ver­weis­es. Klick­en Sie zu Beginn in den Streifen links der Abfrage (in den Text Abfra­gen) und es öffnet sich ein Seit­en­fen­ster. Recht­sklick in den Namen des einzi­gen Ein­trags Tabelle1, Ver­weis und es wird automa­tisch eine neue Tabelle mit dem Namen Tabelle1 (2) erstellt. An der Markierung sowie im recht­en Seit­en­fen­ster erken­nen Sie, dass die neue Tabelle auch die aktuelle ist. Fil­tern Sie nun die Spalte Attrib­ut so, dass nur die Zeilen mit dem Ein­trag ? Sicht­bar sind.

Markieren Sie die Spalte Zusam­menge­führt, Grup­pieren nach und im Dia­log geben Sie als Neuer Spal­tenname beispiel­sweise Summe ein, bei Vor­gang wählen Sie eben­falls Summe. Als Spalte wählen Sie naturgemäß die Spalte Wert. Schließen Sie den Dia­log mit OK. Sie erken­nen, dass die iden­tis­chen Zeilen aus Zusam­menge­führt zu 1 Zeile zusam­menge­fasst wor­den sind und die in der Spalte Summe entsprechend addiert wor­den sind:

Grup­piert und addiert, die Werte zweier Spal­ten

Nach diesem ersten Schritt ist es an der Zeit, die Abfra­gen zu sich­ern. Markieren Sie dazu Abfrage1, Datei, Schließen & laden in… | Nur Verbindung. Öff­nen Sie anschließend wieder Abfrage1 auf beliebige Weise. 

Erstellen Sie erneut ein Ver­weis-Dup­likat der Tabelle1, welche jet­zt automa­tisch den Namen Tabelle1 (3) erhält. Fil­tern Sie hier die Spalte Attrib­ut so, dass die Zeilen mit dem Inhalt ? nicht mehr angezeigt wer­den (und auch nicht mehr in der Abfrage enthal­ten sind). An dieser Stelle ist es beson­ders deut­lich sicht­bar, dass beim Ent­piv­otieren jene Spal­ten nicht über­nom­men wor­den sind, wo auss­chließlich null enthal­ten war, die also kom­plett leer waren bzw. sind. Durch Ihr Tun sind jet­zt nur noch die Spal­ten 1977 und 1978 dargestellt, erfasst wor­den.

Markieren Sie die Spal­ten Zusam­menge­führt und Attrib­ut, dann ein Klick auf Grup­pieren nach. Bei Neuer Spal­tenname geben Sie beispiel­sweise Summe ein, unter Vor­gang wählen Sie eben­falls Summe. Im Feld Spalte wählen Sie wie beim vorheri­gen Beispiel Wert und schließen anschließend den Dia­log. Und das Ergeb­nis stellt sich nun so dar:

Grup­pieren, dieses Mal mit einem anderen Ergeb­nis

Sie wer­den jet­zt schon bei ein­er kleinen Stich­probe fest­stellen, dass die Werte dem Wun­sch-Ergeb­nis des Fragesteller es entsprechen. Im näch­sten Schritt wer­den die Dat­en wieder piv­otiert, also in eine Kreuzta­belle über­tra­gen. Markieren Sie darum jene Spalte, welche die Über­schriften enthält: Attrib­ut. Anschließend ein Wech­sel zum Reg­is­ter Trans­formieren. Wählen Sie hier in der Gruppe Beliebige Spalte das Sym­bol für Piv­otieren . Im Dia­log ändern Sie die Werte-Spalte auf Summe und anschließend OK. Wenn sie nun in Gedanken die dritte Spalte der Roh­dat­en (?) aus­blenden, dann stimmt das Ergeb­nis weitest­ge­hend mit dem Wun­sch-Ergeb­nis übere­in. (Ja, die erste Spalte muss natür­lich noch geteilt wer­den, klar…)

Nun wer­den Sie die fehlende Spalte in genau diese Abfrage ein­fü­gen. Zu Beginn acht­en Sie darauf, dass die Abfrage1 (3) aktiviert ist. Start, Kom­binieren | Abfra­gen zusam­men­führen | Abfra­gen als neue Abfrage zusam­men­führen. Im Dia­log Zusam­men­führen wählen Sie im mit­tleren, kleinen Kom­bi­na­tions­feld den Wert Tabelle1 (2). Verknüpfen Sie nun im oberen als auch unteren Block die bei­den Felder Zusam­menge­führt, indem sie jew­eils in die Über­schrift oder ein beliebiges Feld in der Spalte Klick­en. Das stellt sich nun so dar:

Der erste Schritt beim Zusam­men­führen der bei­den Abfra­gen

Der vorgeschla­gene Wert bei Join-Art kann so bleiben. Nach dem Schließen des Dialogs sieht die Abfrage so aus:

Vielle­icht nicht Ihren Erwartun­gen entsprechend, aber kor­rekt

Sie erken­nen, dass im Anschluss an die bish­erige let­zte Spalte eine neue Spalte mit dem Namen der Abfrage Tabelle1 (2) hinzu gekom­men ist. In jed­er Zeile der Spalte ste­ht Table (Tabelle) als Inhalt. Erweit­ern Sie nun die Über­schrift in dieser Spalte durch einen Klick auf den Dop­pelpfeil . Ent­fer­nen Sie im Dialogfen­ster das Häkchen bei Zusam­menge­führt und unter­halb des Kas­tens bei Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den. Der einzig markierte Wert Anzahl wird sofort der Inhalt der aktuellen Spalte sein. Ziehen Sie diese Spalte Anzahl an die 2. Posi­tion von links (also vor 1977).

Bleibt noch die Tren­nung der zuvor zusam­menge­führten zwei Spal­ten. Markieren Sie dazu die Spalte Zusam­menge­führt, Start und Spalte teilen | Nach Trennze­ichen. Die Vor­gabe ist der Tab­stopp und Sie wer­den sich erin­nern, dass das genau das Trennze­ichen beim Zusam­men­fü­gen der Spal­ten war. Also akzep­tieren Sie das und schließen den Dia­log.

Bin­go! So soll es sein, das Ergeb­nis ist abso­lut dem Wun­sch entsprechend. Jet­zt bleibt nur noch, die Query via Schließen & laden in ein neues Sheet zu schreiben oder per Schließen & laden in… an beliebiger Posi­tion zu platzieren. – Mir ist bewusst, dass auf diese Weise nur die Jahre dargestellt wer­den, wo auch Dat­en enthal­ten sind. Es wäre dur­chaus möglich, auch die „leeren” Jahre im Ergeb­nis mit darzustellen, aber ein­er­seits ist das nicht unbe­d­ingt aus­sagekräftig und ander­er­seits war es nicht gefordert (wobei ich davon aus­ge­he, dass in der Orig­i­nal-Datei alle Spal­ten Dat­en enthal­ten).

▲ nach oben …

(2) Gruppieren, Gruppieren

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

Dieser Abschnitt ist eigentlich nur ent­standen, weil im Forum jemand schrieb, dass die Lösung „nur” mit der Funk­tion­al­ität des Grup­pierens, also ohne (ent)pivotieren möglich sei. Es gab einiges an Wider­spruch ander­er Helfer und das hat meinen Ehrgeiz anges­tachelt …  😎 Ich räume aber auch ein, dass ich mir das etwas ein­fach­er vorgestellt hat­te, ohne die erforder­lichen Umwege. Aber schauen Sie selb­st. Und im End­ef­fekt sieht die Lösung ganz ein­fach aus. Wie so oft im Leben, „man” muss nur wis­sen, wie es geht…  💡 

Wie Sie die Basis-Dat­en in den Pow­er Query-Edi­tor importieren, ist bere­its ganz zu Beginn dieses Beitrages detail­liert erk­lärt. Was den ersten Schritt, das Zusam­men­führen der bei­den ersten Spal­ten bet­rifft: Den Punkt bzw. Schritt kön­nen und soll­ten Sie hier auch gerne anwen­den, obwohl es auch einen etwas aufwändi­geren Weg ohne das zusam­men­führen und späterem entkop­peln der bei­den Spal­ten gibt. Da wür­den Sie dann in der Grup­pierung stets bei­de Spal­ten markieren. – Zu Beginn stellt sich die Abfrage so dar:

Um die bei­den Spal­ten Song und Band/Interpret zusam­men­zuführen, markieren Sie erforder­lichen­falls erst die Spalte Song und danach Shift und ein Klick in die Über­schrift der Spalte Band/Interpret. Wech­seln Sie nun zum Reg­is­ter Trans­formieren und wählen dort in der Gruppe Textspalte den Ein­trag Spal­ten zusam­men­führen. Im Dia­log wählen Sie im Kom­bi­na­tions­feld Trennze­ichen den Ein­trag Tab­stopp. Bei Neue Spal­tenname (option­al) ist der Name Zusam­menge­führt vorgeschla­gen, dabei kön­nen, soll­ten Sie es belassen. Das Ergeb­nis stellt sich zu Beginn so dar:

Die bei­den ersten Über­schriften sind zu 1 Spalte zusam­menge­fasst

Markieren Sie nun auss­chließlich die erste Spalte Zusam­menge­führt. Wählen Sie im Menüband des Sym­bol Grup­pieren nach, unten tra­gen in das Feld Neuer Spal­tenname beispiel­sweise Summe ein. Sie kön­nten auch ein ? schreiben, denn hier wird genau diese Spalte sum­miert. Ich bleibe aber im Script beim von mir vorge­se­henen Namen. Bei Vor­gang wählen Sie im Kom­bi­na­tions­feld naturgemäß auch Summe und bei Spalte wählen Sie ?, denn diese Spalte soll für jeden der Begriffe der Spalte Zusam­menge­führt die Addi­tion der einzel­nen Werte repräsen­tieren. Oder anders aus­ge­drückt: Gle­iche Inhalte in der ersten Spalte wer­den zusam­menge­fasst und sum­miert aus­gegeben, einzelne Werte bleiben naturgemäß so, wie sie derzeit sind.

Wenn Sie jet­zt das Dia­log-Fen­ster mit OK jet­zt schließen wür­den, dann sähe das Ergeb­nis so aus:

Das Ergeb­nis stimmt, aber …

Die Zahlen stim­men. Aber es fehlen natür­lich ganz entschei­dende Spal­ten, die Jahres-Zahlen. Auf jeden Fall sind es die Werte jen­er Jahre, die nicht auss­chließlich leere Felder enthal­ten. Darum ist noch ein weit­er­er Schritt im Grup­pieren-Dia­log erforder­lich. – Soweit Sie den Dia­log nicht geschlossen und den Text hierüber auss­chließlich zur Ken­nt­nis genom­men haben, gehen Sie direkt zum fol­gen­den Absatz und führen die dargestell­ten Schritte aus. Wenn Sie ein wenig exper­i­men­tiert haben, um das Ergeb­nis nachvol­lziehen zu kön­nen, dann Klick­en Sie in der let­zten Zeile des Bere­ichs Angewen­dete Schritte im recht­en Seit­en­fen­ster auf das Zah­n­rad-Sym­bol rechts und der Dia­log wird sich im vorheri­gen Zus­tand wieder öff­nen:

„Darf es etwas mehr sein?” 😉

Auf den ersten Blick scheint es so zu sein, dass sich dort nicht viel ändern lässt. Aber wenn sie genau hin­se­hen wer­den sie erken­nen, dass direkt unter der Über­schrift Grup­pieren nach das Options­feld Stan­dard markiert ist; daneben gibt es aber auch noch das Feld Weit­ere. Genau dieses wer­den sie nun per Klick aktivieren. Das Fen­ster ver­größert sich und zusät­zlich zur Schalt­fläche Grup­pierung hinzufü­gen gibt es im unteren Bere­ich eine weit­ere, neue Schalt­fläche: Aggre­ga­tion hinzufü­gen. Ein Klick darauf wird unter­halb der eben aus­ge­füll­ten Zeile mit den drei Feldern eine neue Zeile mit der gle­ichen Anzahl von Feldern Feldern darstellen.

Wie zuvor bere­its ein­mal ange­merkt, muss jedes Feld in ein­er Liste (und natür­lich auch auch in Pow­er Query) einen ein­deuti­gen Namen haben. Darum geben Sie in diese neue Zeile unter Neuer Spal­tenname eine beliebige Beze­ich­nung ein. Ich ver­wende dazu meis­tens nur ein -, weil ich diesem Feld­na­men Prinzip­iell nicht mehr ver­wen­den werde (Pow­er Query aber sehr wohl). Bei Vor­gang wählen Sie in die zweite Zeile den Begriff Alle Zeilen, das Feld Spalte ist nicht auswählbar. Klick­en Sie nun auf OK und das Ergeb­nis wird sich so darstellen:

„Table” ste­ht in der neuen Spalte für „Tabelle”

Klick­en Sie in der Über­schrift der neuen Spalte - auf das Sym­bol mit dem Dop­pelpfeil , um den Inhalt der Spalte zu erweit­ern. Das stellt sich anschließend so dar:

Erst ein­mal etwas unge­wohnt, aber gar nicht so schlimm … 😉

Aktivieren Sie im ersten Schritt das Options­feld Aggregieren (ganz oben) und ent­fer­nen Sie dann das Häkchen bei Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den (ganz unten). Sie wer­den beim Durch­scrollen der Liste erken­nen, dass es zwei unter­schiedliche führende Zeichen gibt: die Raute # sowie das Sum­men­ze­ichen ∑. Set­zen Sie das Häkchen bei den einzi­gen Jahren mit Dat­en, also bei 1977 und 1978, alle anderen Felder bleiben leer:

Die Jahres-Spal­ten aggregieren

Das Ergeb­nis wird auch Sie überzeu­gen. Der Zusatz „Summe von ” in der Über­schrift wurde durch Pow­er Query automa­tisch hinzuge­fügt, kann aber aus mein­er Sicht beste­hen bleiben. Oder aber Sie ändern die Über­schriften so, wie Sie diese gerne hät­ten; entwed­er per Dop­pelk­lick oder markieren und dann F2. Es ist aber auch eine Über­legung wert, eine solche Änderung erst in der erstell­ten Excel-Tabelle per Erset­zen durchzuführen.

Diese Lösung kann sich gewiss sehen lassen

Jet­zt bleibt prak­tisch nur noch, die Spalte Zusam­menge­führt wieder in ihre eigentlichen Ursprünge zurück zu „ver­wan­deln”. Dazu markieren Sie diese Spalte durch einen Klick in die Über­schrift, Start, Spalte teilen | Nach Trennze­ichen | Tab­stopp | Bei jedem Vorkom­men des Trennze­ichens und dann OK. Jet­zt noch die Über­schriften der bei­den ersten Spal­ten wieder auf Song und Band/Interpret ändern und jet­zt bedarf es gewiss kein­er weit­eren Änderung der Abfrage.

Start oder Datei, Schließen & laden führt dazu, dass die Abfrage in ein Excel-Arbeits­blatt geschrieben wird. Wenn Sie gerne eine andere Posi­tion hät­ten, kön­nen Sie die erzeugte Tabelle prob­lem­los auss­chnei­den und an gewün­schter Posi­tion wieder ein­fü­gen.

Sie werde erken­nen, dass sich dieser Aufwand wirk­lich lohnt, wenn Sie die Dat­en und der Ursprungsta­belle ändern oder ergänzen. Nach einem Öff­nen der *.xlsx wird sich zu Beginn keine Änderung zeigen. Das ist gewollt so, denn es kann ja sein, dass Sie Ihre Arbeit am Vor­abend unter­brochen haben und nun am fol­gen­den Mor­gen genau an der Stelle weit­er­ma­chen wollen. Wenn Sie bei entsprechen­der Gele­gen­heit aber einen Klick auf Aktu­al­isieren durch­führen, dann haben Sie sofort alle neu berech­neten und aktu­al­isierten Dat­en auf dem Bild­schirm.

Mein Tipp an dieser Stelle: Arbeit­en Sie sich mit der Zeit in die fort­geschrit­tene Lösung des ersten Abschnitts ein. Es lohnt sich gewiss, denn Sie wer­den auch in vie­len anderen Fällen von den erlern­ten Funk­tion­al­itäten des Pow­er Query prof­i­tieren.

Hin­weis: Im Forum wurde dieses The­ma heiß disku­tiert. Während ich dieses Skript ver­fasste, kamen diverse Beiträge und Lösungsvorschläge dazu. Ich habe bewusst während der Schreib-Phase nicht im Forum nachgeschaut, was sich dort „getan hat”. Allerd­ings wurde mir mit schön­er Regelmäßigkeit angezeigt, dass dort ein neuer Beitrag (mit einem Auszug des Titels) eingestellt wor­den ist. Ich werde mich im neuen Jahr (2019) dort ein­mal „durch­wühlen”. Vielle­icht gibt es ja ähn­liche oder auch bessere Lösun­gen. Wobei ich generell mit der Qual­i­fizierung „bess­er” meine Prob­leme habe, denn wenn das Ergeb­nis auch in Gren­zfällen stimmt, dann ist jed­er Weg gut. Ob nun kürz­er oder länger, ver­schlun­gener oder ger­ad­er, das ist eher eine Frage des Geschmacks. Ich vertrete die Überzeu­gung, dass ein Weg, eine Funk­tion oder Formel bzw. ein Code trans­par­ent sein soll. Über­sicht kostet mitunter die eine oder andere Zeile oder auch manch einen Schritt mehr. Nutzer, die nur das Ergeb­nis wollen, ohne den Anspruch zu haben, selb­st am Pro­jekt weit­er zu arbeit­en, wer­den das nicht unbe­d­ingt zu schätzen wis­sen und wahrschein­lich den kürz­eren, ger­aden Weg wählen. Diesem Anspruch will und werde ich nie gerecht wer­den, denn ich habe an meine Leser den Anspruch, dass sie aus meine Beiträ­gen Wis­sen schöpfen, um auf diesem Fun­da­ment ihr eigenes Schaf­fens­ge­bäude aufzubauen. (Das Datum der Veröf­fentlichung ist nicht ohne Grund Sil­vester 2018… 💡 )

▲ nach oben …

(3) Übung mit realistischen Daten, Mut zur Lücke  😉 

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

Da wir heute am let­zten Tag des Jahres 2018 ste­hen ist schon am Hor­i­zont sicht­bar, dass eine weit­ere Spalte für das kom­mende Jahr einge­fügt wer­den wird. Gle­icher­maßen für das Jahr 2020, und, und, und… Darum ist es hil­fre­ich, wenn der Auf­bau der Abfrage so ist, dass automa­tisch alle Spal­ten, also alle Jahre für die Berech­nung berück­sichtigt wer­den. Selb­st dann, wenn da ein­mal ein Jahr keine Dat­en enthält. Für den Wis­sens­stand des ersten Abschnitts (etwas Erfahrung) wäre das prinzip­iell kein Prob­lem, für Ein­steiger ist vielle­icht der eine oder andere kleine Umweg ganz hil­fre­ich. 🙂 

Ich habe Ihnen im Muster-File eine tabel­lar­ische Auf­stel­lung zusam­mengestellt, wo zwar durch die Funk­tion ZUFALLSBEREICH() Fan­tasie-Zahlen gener­iert wor­den sind, die aber den­noch denkbar sein kön­nen. In den Jahren 2012, 2016 und 2019 sind (noch) keine Dat­en erfasst, die Spal­ten sollen aber in der endgülti­gen Darstel­lung im Daten­bere­ich auch leer angezeigt wer­den.  Gehen Sie dazu so vor:

• Laden Sie dieses File von unserem Serv­er und importieren Sie die Dat­en auf dem bekan­nten Wege in den Pow­er Query-Edi­tor.
• Sortieren Sie die erste und auch zweite Spalte auf­steigend.
• Markieren Sie alle Spal­ten ab der drit­ten Spalte (?) bis zur let­zten Spalte (derzeit 2019).
• Auch wenn bei Daten­typ: Ganze Zahl angezeigt wird, expliz­it noch ein­mal Ganze Zahl wählen.
• Wenn Sie ein neues Jahr in den Roh­dat­en erstellt haben und dort noch keine Dat­en enthal­ten sind, muss für diese Spalte (beispiel­sweise 2020) auch ein­ma­lig dieser Daten­typ geset­zt wer­den. 
• Bestäti­gen Sie mit OK.

Im näch­sten Schritt erstellen Sie aus dieser Abfrage ein (1) Dup­likat als Ver­weis: Reg­is­ter Start, Gruppe Abfrage | Ver­wal­ten | Ver­weis. Wech­seln Sie erforder­lichen­falls zur neu erstell­ten Abfrage mit der Beze­ich­nung Tabelle 1 (2) durch einen Klick im linken Seit­en­fen­ster auf diesen Ein­trag. Falls links der Abfrage nur ein schmaler Streifen mit dem Text Abfrage sicht­bar ist, ein­fach darauf Klick­en. – Hin­weis: Auch wenn die der Erstel­lung der Ver­weis-Abfrage nicht in jedem Fall erforder­lich ist so halte ich das für eine gute Vor­sichts­maß­nahme. „Man” weiß ja nie, ob die Orig­i­nal-Dat­en noch ein­mal gebraucht wer­den oder irgend­wie im Kern angepasst wer­den sollen.

Markieren Sie hier in Tabelle1 (2) die Spal­ten Song und Band/Interpret und in der Gruppe Trans­formieren wählen Sie Grup­pieren nach (das geht übri­gens auch über einen Recht­sklick). Den Inhalt des Feldes Neuer Spal­tenname (Anzahl) kön­nen Sie so belassen. Bei Vor­gang wählen Sie den Ein­trag Alle Zeilen. Spalte ist zwar nicht aus­ge­graut, Sie kön­nen aber dort den­noch nichts ein­tra­gen.

Klick­en Sie nun auf Aggre­ga­tion hinzufü­gen und tra­gen Sie in das leere Feld unter­halb  Neuer Spal­tenname beispiel­sweise Summe ein. Vor­gang ist hier naturgemäß Summe und bei Spalte wählen Sie in der gle­ichen Zeile die Spalte ?. Das Ganze zeigt sich nun so:

Eine etwas aufwändi­gere Grup­pierung der Dat­en

Das Ergeb­nis wird Sie vielle­icht etwas über­raschen:

Das Ergeb­nis ist vielle­icht irri­tierend, aber kor­rekt

Ver­schieben Sie nun die Spalte Summe an die 3. Posi­tion, also nach Band/Interpret. Entwed­er Klick­en Sie dazu in die Über­schrift der Spalte Summe und ziehen Sie diese per Hand dort hin, alter­na­tiv geht das auch über das Kon­textmenü, wenn Sie die Über­schrift markiert haben: Ver­schieben | Links.

Die nun let­zte Spalte hat ja die vielle­icht irri­tierende Über­schrift Anzahl, aber das ist in diesem Fall nicht wirk­lich rel­e­vant. Aber falls es Ihnen lieber ist, ändern Sie die Über­schrift auf Jahre. Ich habe das auch ein­fach ein­mal der Trans­parenz wegen getan. Was aber gewiss befremdlich sein kann ist, dass jede einzelne Zeile den Inhalt Table hat. Klick­en Sie dort zum Erweit­ern auf den Dop­pelpfeil . Und der Dia­log, welch­er sich nun öffnet, ist für sie wahrschein­lich auch nicht viel aus­sagekräftiger. 😉 Aber in diesem Fall sieht es vielle­icht schlim­mer aus, als es ist; Sie wer­den gar nicht so über­mäßig viel tun müssen. 😎 

Einige Häkchen set­zen und ent­fer­nen, und das klappt dann auch … 😉

Zuerst acht­en Sie darauf, dass die Options-Schalt­fläche Aggregieren aktiviert ist. Anschließend markieren Sie die ober­ste Auswahl (Alle Spal­ten auswählen). Ent­fer­nen Sie dann alle Häkchen, wo keine Jahreszahl ste­ht (also bei den ersten 3 Spal­tenna­men) und auch das Häkchen ganz unten bei Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den. Klick­en Sie nun auf OK, um den Dia­log zu schließen.

Das war’s prinzip­iell auch schon. Schließen & laden wird die Abfrage schließen und in ein Arbeits­blatt schreiben. Aber da hier mit Sicher­heit auch „Spezis” mitle­sen …

▲ nach oben …

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

Und weil das Jahr einen guten Abschluss haben soll, hier noch ein­mal die Lösung für Anwen­derin­nen und Anwen­der mit fort­geschrit­ten­em Wis­sens­stand in Sachen Pow­er Query. Eine kleine Ein­schränkung muss ich allerd­ings machen: Mein Ehrgeiz ist es, diesen Beitrag noch im Jahr 2018 zu veröf­fentlichen. Und da ab ca. 20:00 Uhr wirk­lich nur Fam­i­lie ange­sagt ist, bleiben mir nur noch weniger als 4 Stun­den, um den Text zu ver­fassen, alles Kor­rek­tur zu lesen (und natür­lich bei Bedarf zu verbessern) und schlussendlich zu veröf­fentlichen. Darum wird manch­es eher in Stich­worten behan­delt, nicht so detail­liert sein, wie vielle­icht an anderen Stellen hier im Blog. Bitte haben Sie dafür Ver­ständ­nis. – Sie dür­fen mich aber gerne darauf hin­weisen, wenn etwas unver­ständlich oder allzu knapp dargestellt wurde, da werde ich dann gewiss nachbessern. 😳 

OK, let’s go! (Hin­weis: Hier ist die Vorge­hensweise ab Excel 2016 beschrieben!)

• Die importierte Datei liegt auf C:\Data. 
• Neue, leere Excel-Mappe, Dat­en, Aus Datei | Aus Arbeitsmappe.
• Wech­sel zu C:\Data, die eben geladene Datei markieren und auf Importieren Klick­en. (Eventuell den Pfad anpassen)
• In der linken Hälfte Tabelle1 markieren und rechts ver­gle­ichen, ob es die richti­gen Dat­en sind.
• Erweit­ern Sie die Schalt­fläche Laden und wählen Sie Laden in…
• Öff­nen Sie die eben erstellte und durch PQ geschlossene Abfrage wieder.
• „Reg­istri­eren” Sie für sich sel­ber, dass die erste Zeile eine durch Pow­er Query erstellte, nicht aus­sagekräftige Über­schrift bekom­men hat.
• Start, Gruppe Trans­formieren, Erste Zeile als Über­schrift ver­wen­den.
• Sortieren Sie die erste und auch zweite Spalte auf­steigend.
• Reg­is­ter (nicht Spalte!) Trans­formieren, die ersten bei­den Spal­ten markieren und Spal­ten zusam­men­führen. Hin­weis: Geht auch per Recht­sklick)
• Als Trennze­ichen beispiel­sweise den Tab­stopp auswählen, dann OK.
• Start, Gruppe Abfrage | Sym­bol Ver­wal­ten | Ver­weis. Alter­na­tive: Im linken Seit­en­fen­ster per Recht­sklick.
• Zusam­menge­führt markieren, Recht­sklick in die Über­schrift, und Grup­pieren nach…
• Neuer Spal­tenname: Summe, Vor­gang: Summe, Spalte: ?

Diese Abfrage beste­ht nun aus 2 Spal­ten: Zusam­menge­führt und Summe (die sum­mierte Spalte ?). Weit­er geht’s…

• Erstellen Sie einen weit­eren Ver­weis der Tabelle1.
• Löschen Sie die Spalte ?.
• Spalte Zusam­menge­führt markieren, Recht­sklick in die Über­schrift, Andere Spal­ten ent­piv­otieren.
• Spalte Attrib­ut markieren und Auf­steigend sortieren.
• Reg­is­ter Trans­formieren, Gruppe Beliebige Spalte | Spalte piv­otieren.
• Werte-Spalte: Wert.
• Klick auf den Text Erweit­erte Optio­nen, Aggre­gatwert­funk­tion: Summe und OK.
• Aktivieren Sie Tabelle1 (2).
• Start, Kom­binieren | Abfra­gen als neue Abfrage zusam­men­führen.
• Wählen Sie im mit­tleren, kleinen Kom­bi­na­tions­feld Tabelle1 (3).
• Markieren Sie in bei­den großen Kas­ten jew­eils die Spalte Zusam­menge­führt durch einen Klick in die Dat­en oder die Über­schrift.
• Belassen Sie Join-Art bei der Vor­gabe, per OK schließen.
• Erweit­ern Sie in der Abfrage Merge1 die Spalte Tabelle1 (3) durch Klick auf den Dop­pelpfeil.
• Ent­fer­nen Sie das Häkchen bei Zusam­menge­führt und unter­halb des Kas­tens bei Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den.
• Schließen Sie den Dia­log.
• Zusam­menge­führt markieren, Start, Spalte Teilen | Nach Trennze­ichen | Tab­stopp akzep­tieren und Dia­log schließen. (→ auch per Recht­sklick möglich)
• Start, Schließen & laden 
• Löschen oder ver­steck­en Sie die Tabel­len­blät­ter Tabelle1 .. Tabelle3 und benen­nen Sie Tabelle4 nach eigen­em Wun­sch um.

Das war’s. Viel Text, ich weiß. Aber ger­ade für Ein­steiger ist es mitunter wichtig, dass der Weg aus­führlich, Schritt für Schritt beschrieben wird. Und im Abschnitt direkt hierüber habe ich ja auch gezeigt, dass es auch mit weni­gen Stich­worten mach­bar ist. 😉 

Danke fürs Lesen und vor eini­gen viel Erfolg beim umset­zen in die Prax­is, falls Sie dieses The­ma so inter­es­sant gefun­den haben, dass sie es sel­ber nachvol­lziehen wollen. Und eventuell kön­nen Sie ja den einen oder anderen Tipp auch in Ihren eige­nen Pro­jek­ten ein­set­zen.

▲ nach oben …

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

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 4,00 € freuen …