Fill&Concat, Klassisch und „elegant“

  Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ   

Foren-Geflüster – Zahlen mit führenden Nullen versehen und mit anderer Spalte verketten

Im Forum Office-Loesung.de ist in diesem Beitrag die Frage aufgeworfen worden, wie die erste von 2 Spalten so umformatiert werden kann, dass daraus eine 4-stellige Ganzzahl mit führenden Nullen generiert wird und anschließend durch einen Unterstrich mit der zweiten Spalte verkettet wird.

Es wurden zwei recht praktikable Lösungen auf Formel-Basis präsentiert, welche gut funktionieren und ich räume ein, dass ich das typischerweise auch so anwenden würde. Aus dem Grunde habe ich mich dann auch mit einer PQ-Lösung zurückgehalten. Und dann kam von einem anderen Helfer doch noch ein Lösungsvorschlag auf der Basis Power Query. Da auch ich gerne andere Wege zum Ziel kennen lerne, sah ich mir die entsprechende Datei an. Ein zielführender, wenn auch ungewöhnlicher Weg…

Da die Schar der PQ-Anwender noch überschaubar ist aber durch die Umstellung auf neuere Office-Versionen in Unternehmen (wo in den neuen Versionen dann typischerweise Power Query bereits integriert ist) langsam aber stetig wächst habe ich mich einmal daran gemacht, zwei Wege zum Ziel aufzuzeigen. Jeder davon wird gewiss eher von Einsteigern verwendet und auch von dieser Gruppe der Anwender vielleicht etwas besser verstanden werden. Diese beiden Lösungsvorschläge habe ich dann auch im Beitrag (und natürlich auch hier im Blog in leicht angepasster Form) zur Verfügung gestellt. Und speziell für Sie hier noch zwei Lösungen, die etwas fortgeschrittener, professioneller sind. Jede der hier vorgestellten Möglichkeiten werde ich kurz aber ausreichend kommentieren. Und meine *.xlsx finden Sie hier zum Download.

▲ nach oben …

Vorgeplänkel

Bevor Sie sich mit dem Import der Daten abgeben, befassen Sie sich nach dem öffnen des Files erst einmal mit den Daten. In A2 sehen Sie zwar schon 0001 (also mit führenden Nullen) und diesen Wert könnten Sie ganz einfach übernehmen. Aber die Editierzeile sagt ganz klar, dass in Wirklichkeit in der Zelle „nur“ eine 1 steht. Und spätestens nach dem Import in den Power Query-Editor werden Sie dann noch eine weitere Überraschung erleben.

Also gut, wie auch immer das sein mag: In C2 steht das von Hand eingegebene Ergebnis und nur das zählt. Und für die weiteren Schritte werden Sie (nur) die Formatierte Tabelle in den Editor importieren, die Spalte mit der angedachten Lösung also ignorieren.

Ach ja, auch wenn es zu Beginn so scheint, dass ausschließlich die Daten der Tabelle1 in der Arbeitsmappe enthalten sind: Da haben sich alle vier fertigen Abfragen „versteckt“. 💡 Register  Daten und in der Gruppe Abfragen und Verbindungen ein Klick auf das gleichnamige Symbol wird die Auflistung der Querys im rechten Seitenfenster sichtbar machen. Sie können, Sie sollten die einzelnen Abfragen anhand des beschreibenden Textes in einem neuen Workbook selber nachbauen und eventuell (später) mit meinem Ergebnis vergleichen .

▲ nach oben …

RawData

Der Name sagt es, die Roh-Daten. Hier ist nur die importierte Tabelle vom Arbeitsblatt Tabelle1 enthalten.Und spätestens jetzt sehen Sie, was ich weiter oben zum Ausdruck bringen wollte: Was in Excel so schick aussieht sind großenteils Dezimalzahlen. Und „natürlich“ soll nur der ganzzahlige Teil in das Ergebnis übernommen werden.

Nach dem Import in den PQ-Editor habe ich die erste, durch Power Query selbstständig eingefügte Aktion Geänderter Typ im rechten Seitenfenster unter Angewendete Schritte gelöscht und dann die Query via Datei | Schließen & laden in… | Nur Verbindung erstellen so gespeichert, dass in Excel keine Tabelle aus den Daten der Abfrage geschrieben wird.

▲ nach oben …

Klassisch einfach (1) →   Ganz 'klassische' Lösung

Ab hier geht es in Stichworten weiter. Sie werden vielleicht hier und da etwas suchen müssen, aber das trainiert den Umgang mit Power Query wirklich gut. 😉 

  • RawData öffnen und beispielsweise über die Gruppe Abfrage | Verwalten ▼ | Duplizieren eine Kopie, ein Duplikat der Abfrage erstellen.
  • Die erste Spalte durch einen Klick in die Überschrift markieren.
  • Spalte teilen | Nach Trennzeichen | Komma | Bei jedem Vorkommen des Trennzeichens.
  • Die letzte automatische  im rechten Seitenfenster dokumentierte Aktion Geänderter Typ löschen. Dadurch wird die erste Spalte wieder zum Typ Text.
  • Splate 1.2 (der Schreibfehler stammt nicht von mir 😉 ) löschen.
  • Spalte hinzufügen | Benutzerdefinierte Spalte | Präfix-Nullen als Neuer Spaltenname vergeben.
  • Tragen Sie im Kasten Benutzerdefinierte Spaltenformel  "0000" (mit den Gänsefüßchen ") ein.
  • Den Dialog mit OK schließen.
  • Zuerst Präfix-Nullen markieren, danach Strg und dann Splate1.1 anklicken.
  • Rechtsklick in eine der beiden markierten Überschriften und im Kontextmenü Spalten zusammenführen.
  • Rechtsklick in Zusammengeführt und Spalte teilen | Nach Anzahl von Zeichen… | 4 | Einmal, soweit rechts wie möglich.
  • Den letzten Schritt Geänderter Typ2 im rechten Seitenfenster löschen.
  • Die erste Spalte Zusammengeführt.1 löschen
  • In die Überschrift Zusammengeführt.2 klicken (auch wenn sie markiert ist), dann Strg und Klick in Überschrift  Spalte 2.
  • Rechtsklick in eine der beiden Überschriften und Spalten zusammenführen.
  • Trennzeichen bei –Benutzerdefiniert– und darunter dann den Unterstrich _ schreiben.
  • Als Neuer Spaltenname geben Sie Spalte 3 ein. OK
  • Schließen & laden und die Arbeit ist getan.

Sie werden zwar keine keine in Excel geschriebene Tabelle sehen, weil automatisch das Attribut der „Nur Verbindung“ übernommen wurde, aber hier ist beschrieben, wie Sie eine auf diese Weise gespeicherte Abfrage an die gewünschte Position schreiben können.

▲ nach oben …

Klassisch einfach (2) →  Typisch 'klassische' Lösung

Auch hier gilt, dass die Information stichwortartig vermittelt wird.

  • Erstellen Sie ein Duplikat der Abfrage RawData.
  • Belassen Sie es bei der automatischen Änderung des Datentyps (Geänderter Typ)
    Markieren Sie Splate 1 und erstellen Sie beispielsweise per Rechtsklick in die Überschrift ein Duplikat dieser Spalte.
  • Ändern Sie den Datentyp dieser kopierten Spalte auf Text.
  • Spalte hinzufügen | Benutzerdefinierte Spalte und vergeben Sie als Überschrift beispielsweise Nullen und als Formel tragen Sie "0000" ein.
  • Markieren Sie zuerst die Spalte Nullen, Strg und dann Splate 1 – Kopie.
  • Rechtsklick in eine der beiden markierten Überschriften und Spalten zusammenführen | Trennzeichen  –Keine– belassen.
  • Die neue Spalte Zusammengeführt und Start | Spalte teilen | Nach Anzahl von Zeichen | 4 | Einmal, so weit rechts wie möglich.
  • Zusammengeführt.2 markieren, anschließend Strg und auch Spalte 2 per Mausklick in die Überschrift markieren.
  • Rechtsklick in eine der beiden markierten Überschriften oder Transformieren | Spalten zusammenführen.
  • Als Trennzeichen  –Benutzerdefiniert– wählen und den _ eingeben.
  • Zusammengeführt per Rechtsklick markieren und Andere Spalten entfernen.
  • Falls gewünscht gerne noch Überschrift auf Spalte 3 anpassen.
  • Schließen & laden.

▲ nach oben …

Eher professionell →  'elegante' Lösung (1)

„Eleganz gehört auf den Laufsteg und nicht unbedingt in eine Excel-Lösung“, so einer meiner manchmal leicht ironischen Sprüche in Foren. Also bleibe ich doch bei der „Ansage“, dass die jetzt aufgeführte Lösung eher in Richtung Professionalität geht. 😆 Auf jeden Fall sind es deutlich weniger Schritte. Und das fördert natürlich auch die Übersichtlichkeit.  – Natürlich gilt auch hier, dass ich die einzelnen Schritte nur aufzähle, wie gehabt …

  • Duplikat von RawData erstellen.
  • Die erste Spalte noch einmal durch Klick in die Überschrift markieren.
  • Diese Spalte auf beliebige Weise duplizieren.
  • Den Datentyp dieser neuen Spalte auf Text ändern.
  • Spalte hinzufügen | Benutzerdefinierte Spalte.
  • Geben Sie im Dialog folgendes ein:
    • Neuer Spaltenname  Zusammengefasst
    • Benutzerdefinierte Spaltenformel:  (in exakt dieser Schreibweise)
      Text.PadStart([#"Splate 1 - Kopie"],4,"0")&"_"&[Spalte 2]
  • Markieren Sie die Spalte Zusammengefasst, Rechtsklick in die Überschrift und Andere Spalten entfernen.
  • Ändern Sie den Namen der Überschrift gegebenenfalls auf Spalte 3.

Sie erkennen, dass dieses deutlich weniger Schritte sind. Dafür müssen Sie auch die Funktion selber in den Dialog eingeben und sie sollten diese auch verstehen. Das Ganze basiert auf der Sprache M und ich versichere Ihnen, dass sich eine Beschäftigung damit auf Dauer gesehen wirklich lohnt.

Eher professionell →  'elegante' Lösung (2)

Die eben gezeigten Lösung war schon recht kurz. Aber es geht doch noch etwas komprimierter. 😎 

  • Duplizieren Sie die Abfrage RawData.
  • Spalte hinzufügen | Benutzerdefinierte Spalte und geben Sie diese Werte ein:
    • Neuer Spaltenname  Spalte 3
    • Benutzerdefinierte Spaltenformel:  (in exakt dieser Schreibweise)
      Text.End("0000" & Text.From([Splate 1]), 4) & "_" & [Spalte 2]
      … wobei die meisten Leerzeichen sein können aber nicht sein müssen.
  • Löschen Sie die beiden ersten Spalten.
  • Schließen & laden.

▲ nach oben …

Finito

Wie bereits weiter oben erwähnt, können Sie nun eine der erarbeiteten Lösungen an beliebiger Stelle in die Excel-Arbeitsmappe einfügen. Die Lösungswege zum Ziel war unterschiedlich, das Ergebnis stets gleich.
Mir ist (natürlich) klar, dass dieser Beitrag in einer ganz anderen Form war als jene, die Sie bisher kennengelernt haben. Vielleicht ist ein Testlauf für diesen oder jenen weiteren Beitrag, ich weiß es noch nicht. So kostet es auf jeden Fall erheblich weniger Zeit, solch einen Beitrag zu erstellen. Wobei das in diesem Fall gewiss durch die 4 unterschiedlichen Lösungswege gut ausgeglichen wird.

▲ nach oben …

Hinweis: In den neueren Versionen (ab 2019/365) gibt es für das Extrahieren von Text-Teilen sehr schöne Neuerungen. Leider ist das (derzeit) in den „klassischen“ Versionen nicht verfügbar.

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

Dann würde ich mich über einen Beitrag Ihrerseits freuen …

Dieser Beitrag wurde unter Basics-PQQ, Foren-Q&A, Power Query, PQ-Formeln (Sprache M), PQ-Quickies, Text-Behandlung abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.