(Forum) PQ: Ziffern einer Zahl zu Min- und Max-Wert umstellen

Bitte beacht­en Sie die hier beschriebene Beson­der­heit in Sachen des Her­ber-Forums.

Quelle:  https://www.herber.de/forum/messages/1994865.html
Stand: 30.10.2024 22:00

Hier der Eröff­nungs­beitrag aus dem Forum:

Hin­weis von mir: Das ist tat­säch­lich der kom­plette Start­beitrag; keine Begrüßung, kein abschließen­der Gruß, kein … 🙁 Und das ist ein­er der Gründe, dass ich mich lange mit der Veröf­fentlichung dieser bei­den Lösungsvorschläge zurück­ge­hal­ten habe. Und an dieser Stelle auch noch ein Hin­weis auf eine dort vorgeschla­gene PQ-Lösung (26.10.2024 20:07:13). So etwas erin­nert mich sehr stark an den Schrift­steller Franz Kaf­ka, der Texte ver­fasste, die total „ver­schwurbelt“ und keineswegs für den „Nor­mal-Leser“ ver­ständlich sind. Ich finde es unab­hängig vom Ergeb­nis des Codes schade, dass am Pow­er Query inter­essierte User eher abgeschreckt wer­den kön­nen als einen schrit­tweisen Ein­stieg in dieses wun­der­bare Tool zu find­en.

Lösungsmöglichkeit 1 (Für Einsteiger, immer 4‑stellige Zahlen)

Zugegeben, der ein­ma­lige Aufwand ist höher als beispiel­sweise eine der im Thread vorgeschla­ge­nen Formel­lö­sun­gen. Aber beispiel­sweise als Übung für den Ein­stieg in Pow­er Query oder für ergänzen­des Ler­nen kann ich mir das gut vorstellen. Der Kern dieses Weges sind zwei eigene Abfra­gen, welche aus der als Argu­ment übergebene Text-Zif­fer­n­folge den min­i­malen und den max­i­malen Wert ein­er Zusam­men­stel­lung „berech­nen“.

Ergänzen Sie gerne einige selb­st die eine oder andere Zahl für die Auswer­tung. Gerne auch weniger oder mehr als 4 Stellen (Zif­fern) und beobacht­en Sie die Auswer­tun­gen. Bei Inter­esse an diesem The­ma aber fehlen­den Grund­la­gen scheuen Sie sich nicht, ein­fach nachzufra­gen (siehe Fußbere­ich). – Hier die entsprechende Code-Blöcke:

let
    // Mein Tabellen-Name: 'RawData'
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    // Den autoomatisch erstellten Schritt händisch geändert
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}}),
    // Prinzipiell: Zeilennummer, die auch nach dem Entpivotieren beibehalten wwird
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Geänderter Typ", "Index", 0, 1, Int64.Type),
    #"Spalte nach Position teilen" = Table.SplitColumn(Table.TransformColumnTypes(#"Hinzugefügter Index", {{"Spalte1", type text}}, "de-DE"), "Spalte1", Splitter.SplitTextByRepeatedLengths(1), {"Spalte1.1", "Spalte1.2", "Spalte1.3", "Spalte1.4"}),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Spalte nach Position teilen", {"Index"}, "Attribut", "Wert")
in
    #"Entpivotierte andere Spalten"

Erstel­lung der Min­i­mum-Zif­fer­n­folge

let
    // Von RawData als neue Tabelle (Liste) erstellt -> Minimum
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}}),
    // Aufteilen in einzelne Zeichen (Ziffern)
    #"Spalte nach Position teilen" = Table.SplitColumn(Table.TransformColumnTypes(#"Geänderter Typ", {{"Spalte1", type text}}, "de-DE"), "Spalte1", Splitter.SplitTextByRepeatedLengths(1), {"Spalte1.1", "Spalte1.2", "Spalte1.3", "Spalte1.4"}),
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Spalte nach Position teilen", "Index", 0, 1, Int64.Type),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Hinzugefügter Index", {"Index"}, "Attribut", "Wert"),
    // Es werden nur die Werte innerhalb der Index-Gruppe sortiert
    #"Sortierte Zeilen" = Table.Sort(#"Entpivotierte andere Spalten",{{"Index", Order.Ascending}, {"Wert", Order.Ascending}}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Sortierte Zeilen",{"Attribut"}),
    #"Hinzugefügter Index1" = Table.AddIndexColumn(#"Entfernte Spalten", "Index.1", 0, 1, Int64.Type),
    #"Gruppierte Zeilen" = Table.Group(#"Hinzugefügter Index1", {"Index"}, {{"Minimum", each Text.Combine([Wert],""), type text}})
in
    #"Gruppierte Zeilen"

Dito für die Max­i­ma

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}}),
    #"Spalte nach Position teilen" = Table.SplitColumn(Table.TransformColumnTypes(#"Geänderter Typ", {{"Spalte1", type text}}, "de-DE"), "Spalte1", Splitter.SplitTextByRepeatedLengths(1), {"Spalte1.1", "Spalte1.2", "Spalte1.3", "Spalte1.4"}),
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Spalte nach Position teilen", "Index", 0, 1, Int64.Type),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Hinzugefügter Index", {"Index"}, "Attribut", "Wert"),
    #"Sortierte Zeilen" = Table.Sort(#"Entpivotierte andere Spalten",{{"Index", Order.Ascending}, {"Wert", Order.Descending}}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Sortierte Zeilen",{"Attribut"}),
    #"Hinzugefügter Index1" = Table.AddIndexColumn(#"Entfernte Spalten", "Index.1", 0, 1, Int64.Type),
    #"Gruppierte Zeilen" = Table.Group(#"Hinzugefügter Index1", {"Index"}, {{"Maximum", each Text.Combine([Wert],""), type text}})
in
    #"Gruppierte Zeilen"

… Und die Ergeb­nis-Tabelle

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}}),
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Geänderter Typ", "Index", 0, 1, Int64.Type),
    #"Zusammengeführte Abfragen" = Table.NestedJoin(#"Hinzugefügter Index", {"Index"}, #"Calc Min", {"Index"}, "Tabelle1 (Min)", JoinKind.LeftOuter),
    #"Erweiterte Tabelle1 (Min)" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Tabelle1 (Min)", {"Minimum"}, {"Minimum"}),
    #"Zusammengeführte Abfragen1" = Table.NestedJoin(#"Erweiterte Tabelle1 (Min)", {"Index"}, #"Calc Max", {"Index"}, "Tabelle1 (Max)", JoinKind.LeftOuter),
    #"Erweiterte Tabelle1 (Max)" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen1", "Tabelle1 (Max)", {"Maximum"}, {"Maximum"}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Tabelle1 (Max)",{"Index"})
in
    #"Entfernte Spalten"

Die ersten 3 Queries sind bei mir als „Nur Verbindung“ gespe­ichert und somit nicht in der Arbeitsmappe sicht­bar. Nur die Lösungs-Query ist im Arbeits­blatt (sicht­bar) gespe­ichert.

Lösungsmöglichkeit 2 (Auch längere Ganzzahlen, Sie haben solide Kenntnisse in Excel)

Eine grund­sät­zlich andere Herange­hensweise, wobei die Mausklick-Aktio­nen deut­lich eingeschränkt sind. Ich habe mehr von den M-Funk­tio­nen Gebrauch gemacht, deren Funk­tion­al­ität Sie bei Inter­esse auch recht gut in der MS-Hil­fe nach­le­sen kön­nen.

Und vor dem Code-Block möchte ich Sie ani­mieren, statt (oder zusät­zlich) des gezeigten Weges für die Berech­nung des Max­i­mums doch ein­mal eigenini­tia­tiv die M-Funk­tion Text.Reverse() einzuset­zen. 😉

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    // Automatischer Schritt;
    // per Hand auf den Typ 'Text' ändern
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}}),
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Geänderter Typ", "Index", 0, 1, Int64.Type),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Hinzugefügter Index", "Ziffern", each Text.ToList([Spalte1])),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Ziffern TopDown", each List.Sort([Ziffern],Order.Descending)),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "Ziffern DownTop", each List.Sort([Ziffern],Order.Ascending)),
    #"Hinzugefügte benutzerdefinierte Spalte4" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte2", "Minimum", each Text.Combine([Ziffern DownTop],"")),
    #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte4", "Maximum", each Text.Combine([Ziffern TopDown],"")),
    #"Andere entfernte Spalten" = Table.SelectColumns(#"Hinzugefügte benutzerdefinierte Spalte3",{"Minimum", "Maximum"})
in
    #"Andere entfernte Spalten"

Und ja, es ist tat­säch­lich nur 1 Tabelle/Abfrage. 😉

Und hier, beim Stich­wort last but not least find­en Sie noch einen Hin­weis, wo und wie Sie Antworten auf eventuelle Fra­gen oder auch die aus­gear­beit­ete Datei bekom­men kön­nen. Grundle­gen­des zum The­ma M-Code lesen Sie hier im Blog nach.