Prolog
Vorab angemerkt: Ich habe mehrfach hin und her überlegt, ob ich diesen Beitrag überhaupt in meinem Blog veröffentlichen soll und falls ja, ob ich den Hinweis auf das Forum setzen soll oder nicht. Letztendlich habe ich mich beide Male für ein „Ja” entschieden, denn einerseits denke ich, dass solch eine Lösung hier am Blog recht gut aufgehoben ist und andererseits ist es aus meiner Sicht eine ethische Pflicht, den Ursprung einer Idee (hier: Fragestellung) zu nennen. Dass ich keinen Link zu dem Forum setze liegt daran, dass die Diskussion Wege genommen hat, die nicht meinen Vorstellungen entsprechen. Und ich habe den Titel einfach einmal so übernommen, wie er in den (diversen) Foren verwendet worden ist. Mir fiel auch kein besserer ein, der kurz genug wäre. 😎
Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
Die Aufgabe
Wie schon erwähnt, diese Aufgabe ist eine Fragestellung aus einem Forum. Ich möchte auch nicht den Text umformulieren, darum füge ich hier den Inhalt des ersten Beitrags (also der ursprünglichen Frage) dieses Threads ein:
Hallo zusammen
Ihr könnt meine aktuelle Herausforderung bestimmt spielend leicht lösen. Ich weiss man kann dies im Excel handeln, aber ich finde einfach nicht die passende Lösung:
Problemstellung:
Ich habe tausende Datensätze, die ich aus einem Textfile in Excel importiere. Hierbei sind die Überschriften in der Spalte A untereinander aufgeführt (immer der gleiche Inhalt). In der Spalte B sind dann die sich ändernden Daten (unterschiedliche Inhalte). Zwischen den Überschriften in Spalte A ist immer noch irgend ein Text, der für die Tabelle nicht verwendet werden soll. Ich möchte nun, dass die Überschriften im Excel in Zeile 1 aufgeführt wird und anschliessend über eine automatische Funktion erreichen, dass die variablen Daten aus Spalte B unterhalb der Überschrift eingefügt werden. Da es tausende Daten sind, ist es mir nicht möglich über Kopieren / Inhalte Einfügen / Transponieren die Datensätze manuell zu erfassen. Das geht bestimmt irgend über eine Funktion oder ein Makro.
Ziel:
Die variablen Datensätze aus Spalte B sollen in eine Tabelle eingefügt werden, damit anschliessend der Filter / Pivot über die Tabelle gelegt werden kann.
Um das Ganze für euch anschaulicher zu machen, habe ich eine stark vereinfachte Beispiel-Datei erstellt, woraus die Ausgangslage und das Ziel ersichtlich sind.
Ich danke euch jetzt schon für Eure Hilfe.
Ich habe darauf geantwortet, dass ich mich haargenau an die genannten Vorgaben gehalten habe obwohl ich der Überzeugung sei, dass die Realität sich dann doch ganz anders darstellt (und nicht nur „stark vereinfacht”). Was sich dann leider auch bewahrheitete … 😥 Die Muster-Datei können Sie hier in unserem Blog herunterladen. Ein Screenshot der Aufgabe:
Die Ausgangslage stellt sich in der Datei so dar
Aus meiner Sicht mehr als irritierend ist die „Ziel”-Beschreibung; die zweispaltige Liste ist ‑sofern ohne überflüssige Zeilen- ideal für eine Pivot-Auswertung geeignet, die angestrebte Kreuztabelle ist es in keiner Weise… 😕 Aber wie so oft im Leben gilt der Satz: „Des Menschen Wille ist sein Himmelreich”.
Der Lösungsweg
Sie brauchen für die Auswertung ja ausschließlich die Daten aus dem Bereich A4:B25. Darum markieren Sie den gesamten Bereich (einschließlich der Leerzeilen), StrgL oder StrgT, um daraus eine formatierte Tabelle zu erstellen; setzen Sie auch das Häkchen, dass die Tabelle Überschriften hat. Anschließend über das Menü Daten (bzw. Power Query in älteren Versionen), Aus Tabelle bzw. Von Tabelle die Daten in den Power Query-Editor importieren. Das stellt sich dann so dar:
Die Daten sind in den Abfrage-Editor importiert
Sie erkennen, dass dort in regelmäßigen Abständen jeweils 2 Leerzeilen (enthalten den Wert null) eingefügt sind. Diese gilt es erst einmal zu entfernen. Dazu wählen Sie im Menüband das Symbol Zeilen verringern | Zeilen entfernen | Leere Zeilen entfernen. Was jetzt noch etwas „stört” sind die wiederholten Zeilen, wo in der 1. Spalte das Wort Überschrift und in der 2. Spalte das Wort Daten steht. Das sind ja ganz offensichtlich Wiederholungen der eigentlichen Überschrift. Auch diese Zeilen sollen nun aus den importierten Daten entfernt werden. Erweitern Sie dazu die 1. Kopfzeile, die eigentliche Überschrift der Tabelle) → (Überschrift 
) und entfernen Sie das Häkchen beim Listen-Eintrag Überschrift. Sofort liegen nur noch die reinen Daten in der Tabelle vor.
Nun gilt es zu erkennen, dass in den Daten eine Regelmäßigkeit vorliegt: Es sind 5er Blöcke, die sich in der Spalte Überschrift wiederholen. Und somit ist das Ziel klar: Die 5 Überschriften sollen ein einziges Mal transponiert werden, also in der 1. Zeile einer neuen Tabelle einmalig erscheinen und die dazugehörigen Werte aus der Spalte Daten sollen darunter geschrieben werden. Wie auch in der Muster-Datei dargestellt.
Um das zu erreichen, wechseln Sie nun zum Register Spalte hinzufügen und Klicken im Menüband auf Indexspalte. Umgehend wird eine neue Spalte mit einem 0‑basierten Index erstellt. Im Prinzip sind das Zeilennummern, nur dass es hier auch die Zeile 0 gibt. Die links der Daten stehenden „echten” Zeilennummern können Sie nicht so ganz einfach abfragen und auswerten. Nun soll eine Art Gruppierung erstellt werden, allerdings hat die nichts mit dem Menüpunkten aus Start oder Transformieren zu tun. Jeweils 5 zusammengehörende Werte der Spalte Überschrift bilden ja einen Block, eine Gruppe; und hier soll für jeden Eintrag der einzelnen Gruppe ein numerische Wert vergeben werden. Also für die ersten 5 Zeilen jeweils der Wert 0, für die nächsten der Wert 1, usw.
Hier kommen sie nicht ohne eine selbst erstellte Formel aus. Spalte hinzufügen, Benutzerdefinierte Spalte und geben Sie im Dialog bei Neuer Spaltenname beispielsweise Idxx (oder wenn Ihnen das lieber ist auch Block) als Überschrift der neuen Spalte ein. Im großen Textfeld Benutzerdefinierte Spaltenformel; schreiben Sie diese Formel ganz genau so wie hier gezeigt nach dem vorgegebenen Gleichheitszeichen:
= Number.From(Number.RoundDown([Index]/5,0))
In diesem Dialog geben Sie eine Überschrift und die Formel ein
Verkürzt ausgedrückt entspricht das der Excel-Formel ABRUNDEN(Index/5; 0); der Inhalt der Spalte Index soll also in jeder Zeile durch 5 dividiert und dann auf 0 Stellen abgerundet werden. Das Ergebnis stellt sich dann so dar:
Die Spalte für die Gruppen-Bildung wurde hinzugefügt
Die Spalte Index brauchen Sie jetzt nicht mehr. Nicht nur aus diesem Grunde sollten Sie diese Spalte löschen, denn für den nächsten Schritt ist es ausgesprochen hilfreich, wenn nur noch 3 Spalten auszuwerten sind. Also markieren Sie die Überschrift Index und Entf oder Rechtsklick in die Überschrift und Entfernen.
Der nächste Schritt macht Einsteiger in Sachen Power Query öfter einmal Probleme. Sie sollten also exakt den Weg so gehen, wie er hier beschrieben ist. Markieren Sie die Spalte, welche die künftigen Überschriften enthalten soll durch einen Klick in das Überschrift-Feld. Das ist in diesem Fall naturgemäß die Spalte Überschrift.
Register Transformieren und ein Klick auf Spalte pivotieren 
. Als Wertespalte wählen Sie jene Spalte, wo die zu den Überschriften gehörigen Werte eingetragen sind, hier also Daten. Klicken Sie anschließend auf den Text Erweiterte Optionen und wählen dort den Punkt Nicht aggregieren. Bestätigen Sie mit OK.
Das Ergebnis ist fast schon perfekt; jetzt brauchen Sie nur noch die 1. Spalte Idxx löschen und anschließend über das Register Datei Schließen & laden in… wählen, um das Ergebnis dieser Abfrage an gewünschter Position zu platzieren. Sie erkennen, dass das Ergebnis dem Wunsch des Fragestellers entspricht.
Hinweis: Dass die eigentliche Aufgabe dann doch deutlich mutierte, hat mich ziemlich geärgert. Ich habe an dem Punkt die weitere Hilfe abgebrochen, weil ich es gar nicht mag, wenn aus beispielsweise Bequemlichkeit eine Fragestellung so vereinfacht, verkürzt wird, dass sich wesentliche Eckpunkte anders darstellen und praktisch eine komplette neue Lösung erstellt werden muss, um zum veränderten Ziel zu gelangen. Hier waren es „plötzlich” unterschiedliche Anzahl von Zeilenbezeichnungen der Spalte A (Name) und die Leerzeilen waren auch keine Leerzeilen mehr sondern enthielten diverse unterschiedliche Inhalte, Texte verschiedenster Art. Selbstredend ist auch solch ein Fall mit Power Query lösbar, aber im Rahmen der (kostenlosen) Forenhilfe übersteigt das bei den gegebenen Umständen bei weitem meine Hilfsbereitschaft.