Fill&Concat, Klassisch und „elegant”

Im Forum Office-Loesung.de ist in diesem Beitrag die Frage aufge­wor­fen wor­den, wie die erste von 2 Spal­ten so umfor­matiert wer­den kann, dass daraus eine 4‑stellige Ganz­zahl mit führen­den Nullen gener­iert wird und anschließend durch einen Unter­strich mit der zweit­en Spalte ver­ket­tet wird.

Es wur­den zwei recht prak­tik­able Lösun­gen auf Formel-Basis präsen­tiert, welche gut funk­tion­ieren und ich räume ein, dass ich das typ­is­cher­weise auch so anwen­den würde. Aus dem Grunde habe ich mich dann auch mit ein­er PQ-Lösung zurück­ge­hal­ten. Und dann kam von einem anderen Helfer doch noch ein Lösungsvorschlag auf der Basis Pow­er Query. Da auch ich gerne andere Wege zum Ziel ken­nen lerne, sah ich mir die entsprechende Datei an. Ein zielführen­der, wenn auch ungewöhn­lich­er Weg…

Da die Schar der PQ-Anwen­der noch über­schaubar ist aber durch die Umstel­lung auf neuere Office-Ver­sio­nen in Unternehmen (wo in den neuen Ver­sio­nen dann typ­is­cher­weise Pow­er Query bere­its inte­gri­ert ist) langsam aber stetig wächst habe ich mich ein­mal daran gemacht, zwei Wege zum Ziel aufzuzeigen. Jed­er davon wird gewiss eher von Ein­steigern ver­wen­det und auch von dieser Gruppe der Anwen­der vielle­icht etwas bess­er ver­standen wer­den. Diese bei­den Lösungsvorschläge habe ich dann auch im Beitrag (und natür­lich auch hier im Blog in leicht angepasster Form) zur Ver­fü­gung gestellt. Und speziell für Sie hier noch zwei Lösun­gen, die etwas fort­geschrit­ten­er, pro­fes­sioneller sind. Jede der hier vorgestell­ten Möglichkeit­en werde ich kurz aber aus­re­ichend kom­men­tieren. Und meine *.xlsx find­en Sie hier zum Down­load.

▲ nach oben …

Vorgeplänkel

Bevor Sie sich mit dem Import der Dat­en abgeben, befassen Sie sich nach dem öff­nen des Files erst ein­mal mit den Dat­en. In A2 sehen Sie zwar schon 0001 (also mit führen­den Nullen) und diesen Wert kön­nten Sie ganz ein­fach übernehmen. Aber die Edi­tierzeile sagt ganz klar, dass in Wirk­lichkeit in der Zelle „nur” eine 1 ste­ht. Und spätestens nach dem Import in den Pow­er Query-Edi­tor wer­den Sie dann noch eine weit­ere Über­raschung erleben.

Also gut, wie auch immer das sein mag: In C2 ste­ht das von Hand eingegebene Ergeb­nis und nur das zählt. Und für die weit­eren Schritte wer­den Sie (nur) die For­matierte Tabelle in den Edi­tor importieren, die Spalte mit der angedacht­en Lösung also ignori­eren.

Ach ja, auch wenn es zu Beginn so scheint, dass auss­chließlich die Dat­en der Tabelle1 in der Arbeitsmappe enthal­ten sind: Da haben sich alle vier fer­ti­gen Abfra­gen „ver­steckt”. 💡 Reg­is­ter  Dat­en und in der Gruppe Abfra­gen und Verbindun­gen ein Klick auf das gle­ich­namige Sym­bol wird die Auflis­tung der Querys im recht­en Seit­en­fen­ster sicht­bar machen. Sie kön­nen, Sie soll­ten die einzel­nen Abfra­gen anhand des beschreiben­den Textes in einem neuen Work­book sel­ber nach­bauen und eventuell (später) mit meinem Ergeb­nis ver­gle­ichen .

▲ nach oben …

RawData

Der Name sagt es, die Roh-Dat­en. Hier ist nur die importierte Tabelle vom Arbeits­blatt Tabelle1 enthalten.Und spätestens jet­zt sehen Sie, was ich weit­er oben zum Aus­druck brin­gen wollte: Was in Excel so schick aussieht sind großen­teils Dez­i­malzahlen. Und „natür­lich” soll nur der ganz­zahlige Teil in das Ergeb­nis über­nom­men wer­den.

Nach dem Import in den PQ-Edi­tor habe ich die erste, durch Pow­er Query selb­st­ständig einge­fügte Aktion Geän­dert­er Typ im recht­en Seit­en­fen­ster unter Angewen­dete Schritte gelöscht und dann die Query via Datei | Schließen & laden in… | Nur Verbindung erstellen so gespe­ichert, dass in Excel keine Tabelle aus den Dat­en der Abfrage geschrieben wird.

▲ nach oben …

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

Ab hier geht es in Stich­worten weit­er. Sie wer­den vielle­icht hier und da etwas suchen müssen, aber das trainiert den Umgang mit Pow­er Query wirk­lich gut. 😉 

• Raw­Da­ta öff­nen und beispiel­sweise über die Gruppe Abfrage | Ver­wal­ten ▼ | Duplizieren eine Kopie, ein Dup­likat der Abfrage erstellen.
• Die erste Spalte durch einen Klick in die Über­schrift markieren.
• Spalte teilen | Nach Trennze­ichen | Kom­ma | Bei jedem Vorkom­men des Trennze­ichens.
• Die let­zte automa­tis­che  im recht­en Seit­en­fen­ster doku­men­tierte Aktion Geän­dert­er 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 Spal­tenname vergeben.
• Tra­gen Sie im Kas­ten Benutzerdefinierte Spal­tenformel  "0000" (mit den Gänse­füßchen ") ein.
• Den Dia­log mit OK schließen.
• Zuerst Prä­fix-Nullen markieren, danach Strg und dann Splate1.1 anklick­en.
• Recht­sklick in eine der bei­den markierten Über­schriften und im Kon­textmenü Spal­ten zusam­men­führen.
• Recht­sklick in Zusam­menge­führt und Spalte teilen | Nach Anzahl von Zeichen… | 4 | Ein­mal, soweit rechts wie möglich.
• Den let­zten Schritt Geän­dert­er Typ2 im recht­en Seit­en­fen­ster löschen.
• Die erste Spalte Zusammengeführt.1 löschen
• In die Über­schrift Zusammengeführt.2 Klick­en (auch wenn sie markiert ist), dann Strg und Klick in Über­schrift  Spalte 2.
• Recht­sklick in eine der bei­den Über­schriften und Spal­ten zusam­men­führen.
• Trennze­ichen bei –Benutzerdefiniert– und darunter dann den Unter­strich _ schreiben.
• Als Neuer Spal­tenname geben Sie Spalte 3 ein. OK
• Schließen & laden und die Arbeit ist getan.

Sie wer­den zwar keine keine in Excel geschriebene Tabelle sehen, weil automa­tisch das Attrib­ut der „Nur Verbindung” über­nom­men wurde, aber hier ist beschrieben, wie Sie eine auf diese Weise gespe­icherte Abfrage an die gewün­schte Posi­tion schreiben kön­nen.

▲ nach oben …

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

Auch hier gilt, dass die Infor­ma­tion stich­wor­tar­tig ver­mit­telt wird.

• Erstellen Sie ein Dup­likat der Abfrage Raw­Da­ta.
• Belassen Sie es bei der automa­tis­chen Änderung des Daten­typs (Geän­dert­er Typ)
  Markieren Sie Splate 1 und erstellen Sie beispiel­sweise per Recht­sklick in die Über­schrift ein Dup­likat dieser Spalte.
• Ändern Sie den Daten­typ dieser kopierten Spalte auf Text.
• Spalte hinzufü­gen | Benutzerdefinierte Spalte und vergeben Sie als Über­schrift beispiel­sweise Nullen und als Formel tra­gen Sie "0000" ein.
• Markieren Sie zuerst die Spalte Nullen, Strg und dann Splate 1 – Kopie.
• Recht­sklick in eine der bei­den markierten Über­schriften und Spal­ten zusam­men­führen | Trennze­ichen  –Keine– belassen.
• Die neue Spalte Zusam­menge­führt und Start | Spalte teilen | Nach Anzahl von Zeichen | 4 | Ein­mal, so weit rechts wie möglich.
• Zusammengeführt.2 markieren, anschließend Strg und auch Spalte 2 per Mausklick in die Über­schrift markieren.
• Recht­sklick in eine der bei­den markierten Über­schriften oder Trans­formieren | Spal­ten zusam­men­führen.
• Als Trennze­ichen  –Benutzerdefiniert– wählen und den _ eingeben.
• Zusam­menge­führt per Recht­sklick markieren und Andere Spal­ten ent­fer­nen.
• Falls gewün­scht gerne noch Über­schrift auf Spalte 3 anpassen.
• Schließen & laden.

▲ nach oben …

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

„Ele­ganz gehört auf den Lauf­steg und nicht unbe­d­ingt in eine Excel-Lösung”, so ein­er mein­er manch­mal leicht iro­nis­chen Sprüche in Foren. Also bleibe ich doch bei der „Ansage”, dass die jet­zt aufge­führte Lösung eher in Rich­tung Pro­fes­sion­al­ität geht. 😆 Auf jeden Fall sind es deut­lich weniger Schritte. Und das fördert natür­lich auch die Über­sichtlichkeit.  – Natür­lich gilt auch hier, dass ich die einzel­nen Schritte nur aufzäh­le, wie gehabt …

• Dup­likat von Raw­Da­ta erstellen.
• Die erste Spalte noch ein­mal durch Klick in die Über­schrift markieren.
• Diese Spalte auf beliebige Weise duplizieren.
• Den Daten­typ dieser neuen Spalte auf Text ändern.
• Spalte hinzufü­gen | Benutzerdefinierte Spalte.
• Geben Sie im Dia­log fol­gen­des ein:
  • Neuer Spal­tenname  Zusam­menge­fasst
  • Benutzerdefinierte Spal­tenformel:  (in exakt dieser Schreib­weise)
    Text.PadStart([#"Splate 1 - Kopie"],4,"0")&"_"&[Spalte 2]
• Markieren Sie die Spalte Zusam­menge­fasst, Recht­sklick in die Über­schrift und Andere Spal­ten ent­fer­nen.
• Ändern Sie den Namen der Über­schrift gegebe­nen­falls auf Spalte 3.

Sie erken­nen, dass dieses deut­lich weniger Schritte sind. Dafür müssen Sie auch die Funk­tion sel­ber in den Dia­log eingeben und sie soll­ten diese auch ver­ste­hen. Das Ganze basiert auf der Sprache M und ich ver­sichere Ihnen, dass sich eine Beschäf­ti­gung damit auf Dauer gese­hen wirk­lich lohnt.

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

Die eben gezeigten Lösung war schon recht kurz. Aber es geht doch noch etwas kom­prim­iert­er. 😎 

• Duplizieren Sie die Abfrage Raw­Da­ta.
• Spalte hinzufü­gen | Benutzerdefinierte Spalte und geben Sie diese Werte ein:
  • Neuer Spal­tenname  Spalte 3
  • Benutzerdefinierte Spal­tenformel:  (in exakt dieser Schreib­weise)
    Text.End("0000" & Text.From([Splate 1]), 4) & "_" & [Spalte 2]
    … wobei die meis­ten Leerze­ichen sein kön­nen aber nicht sein müssen.
• Löschen Sie die bei­den ersten Spal­ten.
• Schließen & laden.

▲ nach oben …

Finito

Wie bere­its weit­er oben erwäh­nt, kön­nen Sie nun eine der erar­beit­eten Lösun­gen an beliebiger Stelle in die Excel-Arbeitsmappe ein­fü­gen. Die Lösungswege zum Ziel war unter­schiedlich, das Ergeb­nis stets gle­ich.
Mir ist (natür­lich) klar, dass dieser Beitrag in ein­er ganz anderen Form war als jene, die Sie bish­er ken­nen­gel­ernt haben. Vielle­icht ist ein Test­lauf für diesen oder jenen weit­eren Beitrag, ich weiß es noch nicht. So kostet es auf jeden Fall erhe­blich weniger Zeit, solch einen Beitrag zu erstellen. Wobei das in diesem Fall gewiss durch die 4 unter­schiedlichen Lösungswege gut aus­geglichen wird.

▲ nach oben …

Hin­weis: In den neueren Ver­sio­nen (ab ²⁰¹⁹⁄₃₆₅) gibt es für das Extrahieren von Text-Teilen sehr schöne Neuerun­gen. Lei­der ist das (derzeit) in den „klas­sis­chen” Ver­sio­nen nicht ver­füg­bar.