Ich erlebe es immer wieder, dass Power Query-Nutzern das Wechselspiel von List-, Record- und Table-Funktionen in M Probleme bereitet. Daher habe ich ein funktionales Beispiel entworfen, welches genau dieses Zusammenspiel (hoffentlich) gut veranschaulicht. Aus diesem Grund zeige ich Dir im aktuellen Beitrag, wie Du eine große Anzahl an Spalten mittels Power Query prüfen kannst und zwar auf das Zutreffen (oder auch nicht Zutreffen) bestimmter Bedingungen. Viel Spaß beim Lesen 🙂

Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!

Die Aufgabe/ Problemstellung

Nimm folgende Situation als gegeben: Du hast eine (im Zweifel sehr breite) Tabelle, die über Wertspalten und Prüfspalten verfügt. An dieser Stelle ist egal, unter welchen Bedingungen eine Prüf_Spalte ein „x“ auswirft. Wichtig ist nur, dass ich als letzte Spalte eine Check-Spalte benötige, die zeilenweise prüft, ob in mindestens einer der Prüfspalten ein „x“ auftaucht, um dann über diese Check-Spalte filtern zu können, ob in der Zeile ein Fehler („x“) aufgetreten ist, oder nicht. Die folgende Abbildung zeigt, wie Du Dir das vorstellen kannst:

Natürlich kann ich diese Aufgabe lösen, indem ich eine berechnete Spalte hinzufüge, die folgende Formel beinhaltet:

Doch diese Lösung hat zwei Nachteile, die ich mit meiner angestrebten Lösung umgehen möchte:

1. Bei vielen Check-Spalten (stell Dir vor es sind mehr als 10) ist diese Lösung ganz einfach sehr aufwändig, denn ich muss manuell jede einzelne Spalte in die Formel eintragen
2. Diese Lösung ist nicht dynamisch, d. h. sofern beispielsweise „Prüf_Spalte6“ hinzu käme, würde sich die Formel nicht automatisch anpassen.

Schauen wir uns also gemeinsam an, wie man diesen Problemen beikommen kann.

Schritt 0: Eine dynamische Liste der zu prüfenden Spalten erzeugen

Alle meine zu prüfenden Spalten haben den Präfix „Prüf_“. Auf diese Weise ist es mir möglich, eine dynamische Liste zu erzeugen, die all die zu prüfenden Spaltennamen beinhaltet. Die folgende Abbildung zeigt die Erstellung dieser dynamischen Liste in zwei Schritten:

(1) Liste_AlleSpaltennamen = Table.ColumnNames(#"Geänderter Typ) gibt mir eine Liste aller Spaltenbezeichnungen der Tabelle „Geänderter Typ“ (also der aktuellen Tabelle) zurück.

(2) Liste_AllerPrüfSpalen = List.Select(Liste_AlleSpaltennamen, each Text.Contains(_, "Prüf_")). Diese verschachtelte Funktion nimmt die in Schritt 1 erstellte dynamische Liste aller Spaltenbezeichnungen auf und verwendet diese in der Funktion List.Select(). List.Select()filtert die Liste der Spaltenbezeichnungen dann nach bestimmten Kriterien. In diesem Fall filtere ich die bestehende Liste darauf, dass das entsprechende Element der Liste – in der Formel symbolisiert durch den Unterstrich „_“ – den Text „Prüf_“ beinhaltet. Diese Textprüfung erfolgt durch die Funktion Text.Contains(_, "Prüf_").

Sollten die relevanten Spalten Deiner Tabelle nach keinem Muster wie „Prüf_“ benannt worden sein, so kannst Du diese Liste natürlich auch manuell definieren. Falls Du Dich mit dem Definieren von Listen noch nicht so wohl fühlst, kann ich Dir meinen Beitrag zu den Grundlagen von Listen empfehlen.

Schritt 1: Einen Record erzeugen, der nur Felder aus zu prüfenden Spalten beinhaltet

Um meiner Lösung, zeilenweise alle Sprüfspalten dynamisch auf das Vorhandensein eines „x“ zu prüfen, näher zu kommen, erzeuge ich in diesem Schritt in einer neuen berechneten Spalte zeilenweise einen Record, der lediglich aus dem Prüfspalten besteht. Die Funktion Record.SelectFields() ist hierzu das Mittel der Wahl. Sieh Dir das folgende Beispiel an, dann versteht sich die Funktionalität leichter:

In der berechneten Spalte „Schritt 1“ erzeuge ich zeilenweise folgende Funktion: Record.SelectFields(_, ListeAllerPrüfSpalten). Diese Funktion übergibt mir zeilenweise einen Record, der lediglich diejenigen Felder beinhaltet (ein Record-Feld entspricht gedanklich einer Spalte innerhalb einer Tabelle), die zu den Prüfspalten gehören. Alle anderen Spalten der Tabelle werden ignoriert. Das ganze funktioniert wie folgt:

Die Funktion Record.SelectFields() nimmt mit dem zweiten Argument (in meinem Beispiel ListeAllerPrüfSpalten) eine Liste auf, die die einzubeziehenden Spalten (bei einem Record korrekt als „Field“ bezeichnet) beinhaltet: Also alle Prüfspalten. Der erste Parameter der Funktion muss ein Record sein, aus dem heraus nur bestimmte Felder selektiert werden sollen. Nun muss man wissen, dass jede Zeile einer Tabelle ein Record ist. Mit dem Unterstrich „_“ als erstes Argument meiner Funktion, verweise ich auf eben diese Zeile meiner Tabelle, und damit auf einen Record. Dieser nun auf die Prüfspalten eingeschränkte Record dient der nun folgenden List-Funktion als Input…

Schritt 2: Den Record in eine Liste konvertieren

An dieser Stelle magst Du Dich vielleicht fragen, warum ich all diese umständlichen Konvertierungen von Tabelle, in Liste, in Record und jetzt (das wirst du gleich sehen) wieder in eine Liste notwendig sind. Die Antwort ist einfach: Ich möchte zeilenweise auf das Vorhandensein eines „x“ in bestimmten Spalten prüfen. Am liebsten wäre mir, mit dem nun erzeugten Record etwa eine Funktion Record.Contains() zu nutzen, die prüft, ob in diesem Record in irgendeinem Field ein „x“ steht. Eine solche Funktion gibt es in der Sprache M jedoch leider nicht, wohl aber eine Funktion List.Contains(). Da es sich hierbei um eine List-Funktion handelt, muss ich meinen bestehenden Record nun erst in eine List konvertieren, bevor ich die Funktion List.Contains() anwenden kann. Dies geschieht wie folgt: Record.ToList(Record.SelectFields(_, Liste_AllerPrüfSpalte)).

Zeilenweise existiert nun eine Liste, die die Feldwerte des Records enthält, also „x“ oder leer. Jetzt ist es soweit, dass ich endlich die tatsächliche Prüfung vornehmen kann: Nämlich ob ein „x“ vorhanden ist, oder nicht. Schau Dir den nächsten, finalen Schritt an…

Schritt 3/ Endergebnis: Die Liste auf das Vorhandensein von „x“ prüfen

Mit der nun vorliegenden Liste, kann ich wie eben schon beschrieben, die Funktion List.Contains() verwenden. Diese Funktion prüft, ob in irgendeinem Item (so heißen die Einträge in einer Liste) meiner Liste ein „x“ existiert und gibt dann je Zeile der Tabelle in der diese Funktion aufgerufen wird zurück, ob ein „x“ vorhanden ist (TRUE) oder eben nicht (FALSE).

Damit ist meine Lösung vollständig. Wir können nun auf Basis einer einzigen Spalte (Schritt 3, oder auch Check-Spalte) filtern, ob in einer der Zeilen ein Fehler („x“) aufgetaucht ist, oder nicht. Und nicht nur das: Die Lösung ist auch dynamisch. Sollte also eine neue Spalte mit der Struktur „Prüf_“ hinzukommen, wird sie automatisch mit geprüft, ohne dass ich meinen Code anpassen muss.

Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de

Viele Grüße aus Hamburg,

Lars