Office-Blog

Wer hat schon mal versucht, eine Tabelle in Excel oder Power BI nach Merkmalen zu filtern, die über mehrere Spalten verteilt sind? Standardfilter stoßen hier an ihre Grenzen, denn sie funktionieren nur für je eine Spalte. Mit umständlichen Workarounds und Wenn-Hilfsspalten ließe sich das Problem lösen. Oder aber in Power Query mit schlauen List-Funktionen.

Im heutigen Rezept zeige ich, wie Filter dynamisch gestaltet werden, um Daten über mehrere Spalten nach bestimmten Kriterien zu ermitteln oder auszuschließen.

Die folgende Produktliste illustriert das Problem: Die Merkmale für Materialen wurden über mehrere Spalten erfasst. Gesucht werden die Produkte, in denen bestimmte Materialien auf jeden Fall vorkommen – hier Holz und Metall – und andere auf keinen Fall – hier Kunststoff.

Merkmale in verschiedenen Spalten machen das Filtern zum Aus- oder Abwählen von bestimmten Merkmalen kompliziert

Die Nadel im (Daten-)Heuhaufen finden: So gehe ich vor

• In zwei Listen hinterlege ich die Materialien, die die gesuchten Produkte unbedingt enthalten (tbl_Verpflichtend) bzw. auf keinen Fall aufweisen sollen (tbl_Ausschluss).
• Auf Basis dieser beiden Listen wird dann die Produktliste (tbl_Produkte) passend gefiltert.

Liste nach gewünschten Materialien filtern

Zunächst lade ich 1) die Produktliste, 2) die Liste mit den verpflichtenden Materialien und 3) die Liste mit den auszuschließenden Materialien nach Power Query. Dort prüfe ich mit List-Funktionen pro Zeile, ob die im jeweiligen Produkt enthaltenen Materialien passend sind. So gehe ich vor:

• Über Daten > Daten abrufen > Aus Tabelle/Bereich lade ich alle drei Tabellen nach Power Query und benenne die Abfragen Produkte, Verpflichtend und Ausschluss.
• Als Datentyp für die relevanten Spalten wähle ich Text.
• Über Spalte hinzufügen > Benutzerdefinierte Spalte erstelle ich eine neue Spalte mit dem Namen Passend.
• Bevor ich Listenfunktionen anwenden kann, erstelle ich aus den Daten zu jedem Produkt eine Liste. Das erledige ich mit der Anweisung Record.ToList(_).

Alle Einträge in einer Zeile sind ein Record und werden mit Record.ToList in eine Liste transformiert

• Die Einträge jeder Zeile sind damit zu einer Liste geworden. Alle Einträge in einer Zeile sind ein Record und der Unterstrich _ stellt den Bezug auf die aktuelle Zeile her.
• Eine Vorschau auf das Ergebnis erhalte ich per Klick in eine Zelle mit List-Eintrag.

Per Klick in eine Zelle mit List-Eintrag lässt sich der Inhalt der jeweiligen Liste einsehen

• Um später nur die Materialspalten zu prüfen, entferne ich mit List.Skip und dem Parameter 1 den ersten Eintrag aus der Liste – nämlich den Eintrag mit dem Namen des Produktes.

Mit List.Skip und dem Paramater 1 den ersten Eintrag in der Liste übergehen

• Nun prüfe ich mit der Funktion List.ContainsAll, ob die Liste der Materialien eines Produkts alle Elemente aus der Liste Verpflichtend[Material] enthält. Die Funktion liefert dann den Wert TRUE oder FALSE zurück.

Mit List.ContainsAll für jedes Produkt die Liste seiner Materialien prüfen, ob darin alle verpflichtenden Materialien enthalten sind

• Gleichzeitig soll auch sichergestellt werden, dass keine Materialien aus der Liste Ausschluss[Material] vorkommen.
  Dazu erweitere ich die Formel um eine UND-Bedingung mit der verneinten Funktion List.ContainsAny unter Angabe der Liste Ausschluss[Material].

Mit not List.ContainsAny für jedes Produkt die Liste seiner Materialien prüfen, ob sie ein auszuschließendes Material  enthält

• Nun muss nur noch die Spalte Passend nach TRUE-Werten gefiltert werden.
  Per Rechtsklick auf einen TRUE-Wert wähle ich Logische Filter > Ist gleich und erhalte damit die Produkte mit den passenden Materialien.

Per Rechtsklick in der Spalte Passend die Liste der Produkte mit passenden Materialien filtern

• Die Spalte Passend wird nun nicht mehr benötigt und kann per Rechtsklick im Spaltenkopf entfernt werden.

Tipp 1: Fehler durch falsche Groß-/Kleinschreibung vermeiden

• Power Query beachtet überall die Groß-/Kleinschreibung.
• Ist ein Material beispielsweise in der Produktliste groß geschrieben und in der Verpflichtend-Liste klein, so wird dieses Material nicht gefunden. Die Filterung der passenden Produkte läuft fehl.
• Um die Groß-/Kleinschreibung bei der Prüfung der Listeneinträge zu ignorieren, kann sowohl der Funktion List.ContainsAll als auch der Funktion List.ContainsAny der Parameter Comparer.OrdinalIgnoreCase mitgegeben werden.

Mit dem Parameter Comparer.OrdinalIgnoreCase die Groß-/Kleinschreibung unbeachtet lassen

Tipp 2: Unerwünschte Ergebnisse bei Leereinträgen vermeiden

• Enthalten die Tabelle tbl_Verpflichtend oder die Tabelle tbl_Ausschluss Leereinträge, so können diese ebenfalls zu einem falschen Filterergebnis führen.
• Das lässt sich jeweils leicht beheben durch Filterung im Spaltenkopf der Spalte Material mit dem Befehl Leere entfernen.

Fazit und weitere Einsatzmöglichkeiten

Die beschriebene Technik lässt sich in unterschiedlichsten Branchen und Szenarien nutzen. Hier drei Beispiele:

• Kunden- und Vertriebsanalyse: Bei einer Messe wird das Interesse von Kunden an verschiedenen Produkten in mehreren Spalten erfasst („Produkt X“, „Produkt Y“, „Produkt Z“). Nach der Messe sollen nur die Kunden selektiert werden, die mindestens an Produkt X interessiert sind, nicht aber an Produkt Z.
• Lager- und Bestandsmanagement: Ein Logistiker verwaltet Lagerbestände mit verschiedenen Eigenschaften: Gefahrgut, Verderblich, Schwer transportierbar. Es sollen Artikel gefunden werden, die mindestens eine bestimmte Eigenschaft besitzen, aber nicht als Gefahrgut klassifiziert sind.
• Personalmanagement und Schichtplanung: Die Qualifikationen von Mitarbeitern wird in mehreren Spalten erfasst (z. B. „Zertifikat für Maschine M8“, „Erfahrung mit Prozess P6“). Nun sollen nur Mitarbeiter gefunden werden, die mindestens eine bestimmte Qualifikation haben, aber nicht nur für eine bestimmte Maschine zertifiziert sind.

Interessiert an weiteren Tipps zu Power Query?

Wer sein Wissen zu Power Query systematisch auf- und ausbauen will, kann dazu einen meiner Online-Kurse nutzen. Auf dieser Seite ist das aktuelle Angebot zu finden.

TIPP: Wer hier im Blog alle Rezepte zu Power Query finden möchte, gibt einfach oben rechts in das Suchfeld Power-Query-Rezept ein.