Office-Blog

Wer in Power Query Daten nach Kostenstellen, Artikel-, Kunden- oder Lieferantennummern filtert, kann das mühelos erledigen. Um beispielsweise die Daten in der unten gezeigten grauen Tabelle nach Kostenstellen zu filtern, die mit “A“ oder “B“ beginnen, formuliere ich zwei einzelne Filterkriterien.

Wie lassen sich die Daten links nach Kostenstellen mit Buchstaben oder mit Zahlen am Anfang filtern?

Problematisch wird es jedoch, wenn es um deutlich mehr Filterkriterien geht: beispielsweise, wenn nach beliebigen Buchstaben oder Ziffern am Anfang gefiltert werden soll. Ich zeige, wie ich den Aufwand bei gleichartigen Mehrfachfiltern mit List.Contains vereinfache und minimiere.

Was passiert, wenn mehrere gleichartige Filterkriterien gebraucht werden?

Die folgende Abbildung zeigt, dass es bei einer Vielzahl von Filterkriterien schnell unübersichtlich und aufwändig werden kann. Es funktioniert zwar, hat aber einige Nachteile:

• Das Definieren und Ändern der Filterkriterien sind aufwändig,
• der M-Code wird unnötig lang,
• die Lesbarkeit leidet.
• Zudem muss Power Query für jede Zeile mehrere Bedingungen nacheinander auswerten.

Über den Spaltenkopf ließen sich mit Hilfe vieler Oder-Bedingungen die Kostenstellen herausfiltern, die mit einem Buchstaben beginnen, doch der Aufwand wäre hoch

Die folgende Abbildung zeigt, dass bei diesem Standard-Vorgehen der M-Code unnötig lang und unübersichtlich wird.

Viele Oder-Bedingung beim Filtern erzeugen einen langen, unübersichtlichen M-Code

Die schlaue Alternative mit List.Contains

• Eleganter ist es, die zulässigen Anfangszeichen in einer Liste zu definieren.
• Anschließend prüfe ich mit List.Contains, ob das erste Zeichen der Kostenstelle in dieser Liste enthalten ist.
• Der Code wird dadurch kompakter, leichter verständlich und flexibler.

Fall 1: Alle Kostenstellen beginnend mit einem Buchstaben filtern

Um alle Kostenstellen nach einem beliebigen Anfangsbuchstaben mit einer einzigen Listenabfrage zu filtern, gehe ich wie folgt vor:

• Ich lade die Kosten-Tabelle über Daten > Daten abrufen > Aus Tabelle/Bereich in den Power Query-Editor.
• Anschließend bestimme ich die Datentypen der Spalten, insbesondere wähle ich den Datentyp Text für die Spalte KST.
• Um einen Musterschritt zur Filterung zu erzeugen, filtere ich über die Häkchenfelder nach einer bestimmten Kostenstelle.
• Die Anweisung, die dem Wörtchen each folgt, tausche ich aus durchList.Contains({“A“..“Z“},Text.Start([KST], 1)).

Der Trick: Die Filteranweisung nach einer bestimmten Kostenstelle durch die List.Contains-Anweisung austauschen

Zur Erläuterung:

• Die Funktion List.Contains prüft, ob die im ersten Argument angegebene Liste den im zweiten Argument angegeben Wert enthält.
• Für das erste Argument wird mit der Anweisung {“A“..“Z“} eine Liste aller Großbuchstaben erzeugt.
• Für das zweite Argument wird mit der Funktion Text.Start und der Zahl 1 als zweiten Parameter der erste Buchstabe der Kostenstelle ermittelt.

Die Funktion List.Contains prüft, ob der angegebene Wert in einer Liste von Werten vorliegt

Fall 2: Achtung Falle, Power Query ist »Case sensitive«

Problematisch wird es, wenn eine Kostenstelle mit klein geschriebenem Anfangsbuchstaben vorliegt. Hier beispielsweise die Kostenstelle x9999. Sie wird bei der obigen Filterung nicht berücksichtigt.

Um nun nicht eine weitere Liste für “a“..“z“ angeben zu müssen, umgehe ich das Problem mit Hilfe eines dritten, optionalen Parameters in der Funktion List.Contains.

So geht’s:
Ich erweitere die List.Contains-Anweisung durch Eingabe eines Kommas und anschließender Auswahl des Vergleichswertes Comparer.OrdinalIgnoreCase als drittes Argument.

Durch Angabe des dritten Arguments als Comparer.OrdinalIgnoreCase wird die Groß-/Kleinschreibung ignoriert

Fall 3: Alle Kostenstellen beginnend mit einer Ziffer filtern

Um alle Kostenstellen beginnend mit einer Ziffer zu filtern, gehe ich wie oben beschrieben vor. Allerdings ersetze ich die im ersten Argument angegebene Liste {“A“..“Z“} durch {“0“..“9“}.

Statt einer Liste von Großbuchstaben eine Liste von Ziffern vorgeben

FAZIT

• Für alle, die Power Query regelmäßig einsetzen, ist dies ein Beispiel dafür, wie M-Code nicht nur zum Laufen gebracht, sondern bewusst so gestaltet wird, dass er verständlich und leicht anpassbar ist.
• Hinzu kommt, dass sich je nach Datenquelle und Ausführungsplan zusätzlich ein Performancevorteil ergeben kann. Insbesondere bei umfangreicheren Wertelisten oder dynamisch erzeugten Listen ist ein solcher Ansatz dann die performantere Lösung.