Foren-Geflüster: Zeilenweise Spalten neu ordnen

KuddelMuddel: Spalten in korrekte Reihenfolge bringen

Prolog

In diesem Forum wurde die Frage gestellt, wie eine 4-spaltige Liste so geordnet werden kann, dass alle in einer falschen Spalte eingetragenen Werte anschließend an der korrekten Position erscheinen:

Die Roh-Daten in fehlerhafter Anordnung

Die Roh-Daten in fehlerhafter Anordnung

In die Spalte Artikelbeschreibung sollen (ausschließlich) alle Werte eingetragen bzw. verschoben werden, die mit Art: beginnen. In die Spalte EAN kommen naturgemäß nur die numerischen Werte und in Farbe werden all jene Feldinhalte positioniert, die mit Farbe: beginnen. Das sollte dann so aussehen:

Diese Anordnung ist gewünscht …

Diese Anordnung ist gewünscht …

Die im Forum vorgestellte Lösungsmöglichkeit habe ich nicht getestet, ich gehe aber davon aus, dass sie korrekt ist. Die Zustimmung des Fragestellers spricht dafür. Ich habe mich für einen Lösungsweg mit Power Query entschieden, weil dieses bei größeren Datenmengen der Ressourcen-schonendere Weg sein kann. Und für mich war es eine interessante Herausforderung. 😎 

Der Lösungsweg

Naturgemäß beginnt es damit, dass die Rohdaten (A2:D11) in den Power Query- Editor importiert werden. Sie können sich auch diese Tabelle hier in unserem Blog herunterladen; wenn Sie diese Mappe verwenden, beginnen die Daten in A1. Und ich habe in unserer Version außer den Quell- und den Ziel-Daten alles entfernt. Mein erster Versuch war ein verschachtelte es Wenn-Konstrukt, welches aber sehr unübersichtlich war und auch im dritten Anlauf nicht zum gewünschten Ziel geführt hat (im vierten Versuch hat es dann geklappt).

Das hat mich nicht zufrieden gestellt, und darum suchte ich nach einem einfacheren, leichteren Weg. Und aus meiner Sicht ist mir das auch gelungen. Der Aufwand ist auf jeden Fall wesentlich geringer, der Lösungsweg transparenter. Hier in Stichworten die Vorgehensweise:

  • Markieren Sie die Spalte Beispiellink durch einen Rechtsklick in die Überschrift.
  • Im Kontextmenü wählen Sie den Punkt Andere Spalten Entpivotieren.
  • Der Effekt ist, dass einerseits für jede der 3 letzten Datenspalten eine eigene Zeile mit dem Attribut und dem Wert erstellt wurde, auf der anderen Seite aber auch leere Zellen gar nicht erst übernommen worden sind. Und das führt zu einer komprimierten Darstellung der Daten in einer anders konfigurierten Liste:

Die Daten sind nach dem entpivotieren anders angeordnet und komprimiert

Die Daten sind nach dem entpivotieren anders angeordnet

Wenn sie mit dieser Art der Umstellung von Daten noch nicht vertraut sind, analysieren Sie das Ergebnis, bis Sie in die Systematik erkannt haben. In Attribut steht jeweils die Überschrift, in Wert der jeweilige Inhalt der Zelle.

Prinzipiell gibt es hier nun zwei Wege: Dem Attribut den passenden Wert zuordnen oder aber dem gegebenen Wert die korrekte Überschrift zuzuweisen. Ich habe mich für den zweiten Weg entschieden.

  • Aktivieren Sie das Register Spalte hinzufügen und klicken dann auf das Symbol Benutzerdefinierte Spalte.
  • Schreiben Sie bei Neuer Spaltenname dieses in das Textfeld: Attribut.1.
  • In den Bereich Benutzerdefinierte Spaltenformel: schreiben Sie diese Formel, gerne in exakt in der hier dargestellten Form mit den entsprechenden Einrückungen und Zeilenschaltungen, in jedem Fall aber dieser Groß- Kleinschreibung:

= if Value.Is([Wert], Int64.Type) then
   "EAN"
else
   if Text.Start([Wert],4)="Art:" then
      "Artikelbeschreibung"
   else if Text.Start([Wert],6)="Farbe:" then
      "Farbe"
else null

Der Dialog mit der eingetragenen Formel

Der Dialog mit der eingetragenen Formel

Bei der Gelegenheit sei mir ein kleiner Hinweis gestattet: In der Formelsprache M des Power Query gibt es direkt keine Funktion, welche dem ISTTEXT() oder dem ISTZAHL() in Excel entspricht. Vielleicht kommt ja noch so etwas als Ergänzung, aber in der ersten Code-Zeile erkennen Sie, ich ein IsNumber() realisiert werden kann. – Weiter geht’s so:

  • Wenn keine Syntaxfehler erkannt worden, ein Klick auf OK.
  • Rechtsklick in die Spalte Attribut und klicken Sie im Kontextmenü auf Entfernen.
  • (Optional:) Benennen Sie die Spalte Attribut.1 um in Attribut.
  • Klick in die Überschrift Attribut (bzw. Attribut.1), anschließend wechseln Sie zum Register Transformieren.
  • Wählen Sie hier im Menüband den Punkt Pivotieren ().
  • Im Dialog wählen Sie bei Wertspalte den Eintrag Wert.
  • Ein Klick auf Erweiterte Optionen und dort wählen Sie Nicht aggregieren.
  • Schließen Sie den Dialog mit OK.
  • Bringen Sie die Spalten durch ziehen in die gewünschte Reihenfolge: Beispiellink | Artikelbeschreibung | EAN | Farbe.
  • Schließen & laden oder Schließen & laden in…, um entweder in einem neuen Blatt oder an gewünschter Position eine Tabelle/Liste mit dem erstellten Ergebnis zu platzieren.

Damit ist das erwartete Ergebnis erreicht. Und spätestens jetzt werden Sie merken, dass die Bezeichnung null in verschiedenen Zellen des Power Query-Editors in der Tabelle zu einer wirklich leeren Zelle führt, die auch kein „“ enthält.

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Foren-Q&A, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M), Spalten bearbeiten, Tabelle und Zelle, Transponieren, Wege nach Rom abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.