PQ: Keyword aus Liste in Text enthalten?

In einem Forum wurde in einem anderen Zusam­men­hang die Frage aufge­wor­fen, wie aus einem beliebi­gen String (Text) ein­er Zelle ein Stich­wort gefun­den wer­den und dann in der Nebenspalte der gle­ichen Zeile wiedergegeben wer­den kann. Das Stichwort/Suchwort ist aber irgen­deines aus ein­er beliebig lan­gen Liste. Eine kleine Beispiel-Datei mit (der Über­schaubarkeit wegen) weni­gen Daten­sätzen soll Ihnen die Sit­u­a­tion klar machen:

Je eine Tabelle/Liste mit Tex­ten und Key­words

In Spalte A sind ver­schiedene Texte, in Spalte C sind die Stich­worte aufge­führt, die in Spalte A gesucht und bei einem Fund in Spalte B aus­gegeben wer­den sollen. Zugegeben, diese 7 Key­words wären noch gut mit einem WENN bzw- if – Kon­strukt beherrschbar aber bei schon wenig mehr Such­be­grif­f­en kann es ganz schön „eng” wer­den. 🙄 Da sind rasch die Gren­zen des Plain Excel (also Formeln) erre­icht (ins­beson­dere auch in Sachen Per­for­mance); VBA wäre in solchen Fällen schon über Arrays oder select case wesentlich leis­tungs­fähiger. Pow­er Query verzichtet hier auch nicht vol­lkom­men auf Pro­gramm-Code, aber der Code-Block ist wirk­lich über­schaubar. 😉 

Laden Sie zu Beginn erst ein­mal unsere Muster-Datei herunter und öff­nen Sie diese natür­lich. Wenn Sie auf die Tabelle mit der Über­schrift Texte Klick­en, dann wer­den Sie erken­nen, dass diese auch den inter­nen Namen Texte hat, und die Key­words haben eben­falls den iden­tis­chen Namen für die Tabelle. Importieren Sie nun die Tabelle Texte und spe­ich­ern Sie diese gle­ich „ganz nor­mal” per Schließen & laden. Damit wird zwar erst ein­mal die unverän­derte Tabelle als Ergeb­nis des Pow­er Query in einem neuen Blatt gespe­ichert, aber das ist im derzeit­i­gen Sta­di­um gut so. Importieren Sie auch die Tabelle Key­words und hier soll­ten Sie diese Liste gle­ich per Schließen & laden in… als Nur Verbindung spe­ich­ern.

Öff­nen Sie nun beispiel­sweise durch Dop­pelk­lick im recht­en Seit­en­fen­ster die Abfrage Key­words. Wech­seln Sie zum Reg­is­ter Trans­formieren und Klick­en in der Gruppe Beliebige Spalte auf das Sym­bol In Liste kon­vertieren. Dadurch ändert sich die Über­schrift der Spalte zu Liste, der Name der Abfrage bleibt aber beste­hen; beacht­en Sie aber, dass sich das Sym­bol dieser Query von „Tabelle” zu „Liste” geän­dert hat:

Unter­schiedliche Sym­bole der bei­den Abfra­gen im linken Seit­en­fen­ster

Nun kommt der wichtig­ste Schritt, das Erstellen des kleinen Pro­gramms in der Sprache M. Aber ich mache es Ihnen leicht(er) und stelle Ihnen gle­ich den kom­plet­ten Code zur Ver­fü­gung, den Sie dann per copy/paste in das Pro­jekt übernehmen kön­nen.

Begin­nen Sie damit, das Menü-Reg­is­ter Start zu aktivieren. Ganz rechts im Menüband sehen Sie die Gruppe Neue Abfrage und dort Klick­en Sie auf Neue Quelle. Wählen Sie im Drop­Down Andere Quellen und dort wiederum Leere Abfrage. Pow­er Query erstellt eine neue Abfrage, die sich erst ein­mal aus­ge­sprochen spar­tanisch darstellt:

Eine abso­lut leere, neue Abfrage

Wech­seln Sie zum Reg­is­ter Ansicht und wählen in der Gruppe Weit­ere das einzige Sym­bol: Erweit­ert­er Edi­tor. Es öffnet sich ein Dia­log, wo das Grundgerüst ein­er leeren Abfrage in der Sprache M bere­its vorgegeben ist:

Das Grundgerüst ein­er leeren Abfrage in der Sprache M

Markieren Sie den kom­plet­ten Text im großen Kas­ten (alle 4 Zeilen) und löschen alles. Schreiben Sie nun in exakt dieser Schreib­weise den hierunter aufge­führten Code oder kopieren Sie ihn vorzugsweise hier aus dem Blog…

(TextSpalte) => 
let
    //Prüfung, ob ein Element der Keywords-Liste in 'Texte' enthalten ist
    KeyWordGefunden = List.Transform(List.Buffer(Keywords), 
    each Text.Contains(TextSpalte, _, Comparer.OrdinalIgnoreCase)), 
    
    //Zeile/Position bei Treffer
    Position = List.PositionOf(KeyWordGefunden, true),
    
    //Falls kein Treffer in Zeile => null, sonst das KeyWord ausgeben
    RC =  if Position < 0 then null else Keywords{Position}        
in
    RC

… und fügen den Inhalt der Zwis­chen­ablage in das eben geleerte Textfeld ein:

Die neue Abfrage mit dem einge­tra­ge­nen M‑Code

Nach einem Kick auf Fer­tig sieht das alles erst ein­mal recht ominös aus, aber das hat seine Richtigkeit:

Vielle­icht etwas ver­wirrend, aber Sie wer­den es in dieser Form nicht brauchen …

Im linken Seit­en­fen­ster erken­nen Sie den Namen Abfrage1 mit dem Sym­bol für eine Funk­tion links des Namens. Und da es sich ja auch um eine Funk­tion han­delt und der Name sowieso nicht ger­ade aus­sagekräftig ist, ändern Sie den Namen beispiel­sweise auf fn_KeyWordSuche (oder einen Ihnen genehmen Begriff nach dem „fn_”). Das führende fn_ ist aus der Sicht des Pow­er Query nicht erforder­lich dient aber der besseren Unter­schei­d­barkeit.

Damit ist die eigentliche Arbeit getan, zumin­d­est was die entschei­dende Vorar­beit bet­rifft. Bleibt „nur” noch, die erstellte Funk­tion zu nutzen. Dazu wech­seln Sie zum Reg­is­ter Spalte hinzufü­gen | Benutzerdefinierte Funk­tion aufrufen und tra­gen zuerst bei Neuer Spal­tenname beispiel­sweise Gefun­den ein. Anschließend erweit­ern Sie das Feld Funk­tion­s­abfrage und wählen die gewün­schte Abfrage:

Auswahl der (einzi­gen) Benutzerdefinierten Funk­tion

Sofort danach wird für die Vari­able TextSpalte das optionale Argu­ment in einen neuen Block unter­halb der bish­eri­gen Anzeige dargestellt:

Das optionale Argu­ment für die Vari­able TextSpalte wird bere­its vorgegeben

Jet­zt noch OK und das Ergeb­nis zeigt sich in voller Pracht:

Das Ergeb­nis der Benutzerdefinierten Funk­tions-Anwen­dung

Last but not least die Anmerkung, dass die Zeile 5 keineswegs poli­tisch aber sehr wohl geografisch gemeint ist. Bei „Sibirien” wird wohl fast jed­er an die riesige Region im Osten unseres Kon­ti­nents denken, aber schauen Sie gerne ein­mal in eine Land­karte Ihrer Wahl und suchen in Schleswig-Hol­stein die Stadt Elmshorn. Es ist prak­tisch ein Stadt­teil und auch eine Straße hat dort diesen Namen. 😎 

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag (z.B. 5,00€) Ihrer­seits freuen …