(Forum) PQ: Filter „Spezial“ statt SVERWEIS() mit mehreren Bedingungen

Quelle:  https://www.clever-excel-forum.de/Thread-SVerweis-mit-3-Bedingungen-wobei-eine-einen-festen-Wert-hat-und-die-2-einen-Wert
Stand: 07.11.2024, 17:45

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

Aus­nahm­sweise habe ich die vorgegebe­nen Dat­en des Fragestellers geän­dert, denn nach dem Mot­to „Das Gegen­teil von gut ist gut gemeint“ ist mir nach einigem Hin und Her aufge­fall­en, dass der Satz „Form fol­lows func­tion“ hier abso­lut zutrifft. Dass die Größer-Zeichen (>) wegen des dort verän­derten Zahlen­for­mats kein­er­lei Wirkung in Sachen Berech­nung zeigten, das ist eine echte „Spitzen­leis­tung“ des Erstellers. 🙁

Und die leeren Zellen im Bere­ich der bei­den Tabellen habe ich der besseren Auswert­barkeit wegen mit dem Wert 0 (Zahl null) gefüllt. Bei­de Änderun­gen habe ich im zu importieren­den Excel-Arbeits­blatt vorgenom­men.

Und noch ein Wort zu mein­er Lösung und dem zwis­chen­zeitlich deut­lich aus­geweit­eten Thread­ver­lauf: Ich habe meine Lösung nach der anfangs eingestell­ten und von mir angepassten *.xlsx erstellt. Die späteren Änderun­gen (Stand per 09.11.24) ver­wirren mich mehr als aufzuk­lären und ich finde stetige Änderun­gen und Anpas­sun­gen der Grund­forderung nur gut, wenn es sich um zahlende Kun­den han­delt, welche sich des Mehr­auf­wands für mich bewusst sind. 😉 Wegen dieser ganzen Ungereimtheit­en habe ich auch recht lange gezögert, ob ich meine Lösung hier im Blog veröf­fentlichen soll. Mein Entschluss zum „ja“ ist dadurch bed­ingt, dass das evaluieren eines Aus­drucks in Pow­er Query zu den sel­teneren aber dur­chaus inter­es­san­ten Vorge­hensweisen gehört.

Mir ist bewusst, dass diese Lösung weit über meine Devise „PQ möglichst nur mit Mausklicks“ hin­aus­ge­ht, aber ich kann mir gut vorstellen, dass eine Formel-Lösung auch nicht ger­ade pro­fan daherkommt. 🙂  Und ich habe auch bewusst einige Schritte mehr inkauf genom­men, um die Abfolge möglichst trans­par­ent zu hal­ten. Der nach­fol­gende M-Code ist etwas kom­men­tiert; wenn Sie mehr Erk­lärun­gen oder Hil­fe möcht­en, schreiben Sie mir eine E‑Mail.

let
    // Abfrage "RawData"
    // Die von mir angepassten Roh-Daten des Fragestellers
    Quelle = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    // Größe und Länge: Einheitlich Typ Text
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Name", type text}, {"Größe", type text}, {"Länge", type text}, {"Preis", type any}}),
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Geänderter Typ", "Index", 0, 1, Int64.Type),
    // KÜRZEN()
    #"Abgeschnittener Text" = Table.TransformColumns(#"Hinzugefügter Index",{{"Größe", Text.Trim, type text}, {"Länge", Text.Trim, type text}}),
    // Leerzeichen entfernt
    #"Ersetzter Wert" = Table.ReplaceValue(#"Abgeschnittener Text","> ",">",Replacer.ReplaceText,{"Größe", "Länge"}),
    #"Ersetzter Wert1" = Table.ReplaceValue(#"Ersetzter Wert",null,"0",Replacer.ReplaceValue,{"Größe", "Länge"}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Ersetzter Wert1", "HSp", each 0),
    // Erst einmal für alle…
    #"Präfix hinzugefügt" = Table.TransformColumns(#"Hinzugefügte benutzerdefinierte Spalte", {{"Größe", each "=" & _, type text}}),
    #"Präfix hinzugefügt1" = Table.TransformColumns(#"Präfix hinzugefügt", {{"Länge", each "=" & _, type text}}),
    // … und bei '>' wieder entfernt
    #"Ersetzter Wert2" = Table.ReplaceValue(#"Präfix hinzugefügt1","=>",">",Replacer.ReplaceText,{"Größe", "Länge"}),
    // Hier wird die Hilfsspalte 'HSp' gebraucht
    #"Zusammengeführte Abfragen" = Table.NestedJoin(#"Ersetzter Wert2", {"HSp"}, Bedingungen, {"HSp"}, "Bedingungen", JoinKind.FullOuter),
    #"Erweiterte Bedingungen" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Bedingungen", {"Name V", "Größe V", "Meter V", "Index"}, {"Name V", "Größe V", "Meter V", "Index.1"}),
    // Dient als Datenquelle beim Zusammenfügen; so bleiben beim Zusammenfügen die ursprünglichen Spalten erhalten (werden säter zum Anzeigen des Ergebnisses gebraucht)
    #"Duplizierte Spalte" = Table.DuplicateColumn(#"Erweiterte Bedingungen", "Größe V", "Größe V - Kopie"),
    #"Duplizierte Spalte1" = Table.DuplicateColumn(#"Duplizierte Spalte", "Meter V", "Meter V - Kopie"),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Duplizierte Spalte1", "Benutzerdefiniert", each [Name]=[Name V]),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte1", each ([Benutzerdefiniert] = true)),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Gefilterte Zeilen",{"Benutzerdefiniert", "Name V"}),
    // SO ist eine Evaluierung per PQ möglich
    #"Zusammengeführte Spalten" = Table.CombineColumns(#"Entfernte Spalten",{"Größe V - Kopie", "Größe"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"GrößeZgef"),
    #"Zusammengeführte Spalten1" = Table.CombineColumns(#"Zusammengeführte Spalten",{"Meter V - Kopie", "Länge"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"LängeZgef"),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Zusammengeführte Spalten1", "GrößeEval", each Expression.Evaluate([GrößeZgef])),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte2", each ([GrößeEval] = true)),
    #"Entfernte Spalten1" = Table.RemoveColumns(#"Gefilterte Zeilen1",{"GrößeEval"}),
    #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Entfernte Spalten1", "LängeEval", each Expression.Evaluate([LängeZgef])),
    #"Gefilterte Zeilen2" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte3", each ([LängeEval] = true)),
    #"Entfernte Spalten2" = Table.RemoveColumns(#"Gefilterte Zeilen2",{"GrößeZgef", "LängeZgef", "Index", "HSp", "LängeEval"}),
    // Der Optik wegen… ;-)
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Entfernte Spalten2",{"Name", "Größe V", "Meter V", "Preis"}),
    #"Sortierte Zeilen" = Table.Sort(#"Neu angeordnete Spalten",{{"Index.1", Order.Ascending}}),
    #"Entfernte Spalten3" = Table.RemoveColumns(#"Sortierte Zeilen",{"Index.1"})
in
    #"Entfernte Spalten3"

let
    // Abfrage "Bedingungen"
    Quelle = Excel.CurrentWorkbook(){[Name="Bedingungen"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Name V", type text}, {"Größe V", type text}, {"Meter V", type text}}),
    // Erforderlich, damit die beiden Tabellen 'sauber' zusammengeführt werden können
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "HSp", each 0),
    // So wird im Ergebnis die gewünschte Reihenfolge gewährleistet
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Index", 0, 1, Int64.Type)
in
    #"Hinzugefügter Index"

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.