Adresssuche mit Fuzzy-Logik entwickelt mit Power Query

[ws-53]

Um der Frage zuvorzukommen, welche Frage ich denn habe, gleich die Anmerkung, dass ich hier eine Lösung präsentiere zu der ich keine Frage habe. Diese würde ich eigentlich lieber im Forum “Komplettlösungen“ einstellen. Aber da dort keiner Nachfragen stellen und ich auch keine Antworten geben kann, stelle ich den Beitrag hie ein. Die Antwort, das Nachfragen im Forum “Diskussionen zu den Beispielen“ eingestellt werden sollen, finde ich auch nicht sonderlich prickelnd, weil ja dann jeder, der eine Frage hat, u.U. einen eigenen Thread eröffnet und es somit später ziemlich unmöglich wird, alle Fragen und Antworten zu einer vorgestellten Lösung zu finden.

Aber ich werde in das moderierte Forum “Komplettlösungen“ einen Link auf diesen Beitrag einstellen, da dann so dieses Thema sehr leicht gefunden werden kann.
 
Nun aber zum eigentlichen Thema:

Vor einigen Jahren habe ich eine unscharfe (Fuzzy) Adresssuche mit Power Query entwickelt und damals auch im ms-office-forum veröffentlich. Da es das mof seit einiger Zeit nicht mehr gibt, ich aber die PQ-Codes, sowohl für die Datenverschlüsselung, als auch für die Suche, optimiert habe, stelle ich das verbesserte Ergebnis nun  hier ein.

Für beide Teile ist es mir gelungen, die Laufzeiten in etwa zu halbieren. Sodass die Aufbereitung von 25.000 Adressen auf meinem alten i7 nur noch rund 70 Sek. Benötigt und die Ergebnisse einer Suche meinst nach 0,5 Sek. vorliegen.

Gemäß einer Nutzung durch mehrere User habe ich folgendes Konzept realisiert.
Es gibt eine zentrale Adresstabelle, die kontinuierlich gepflegt wird.

In einer weiteren zentralen Tabelle sind alle Verschlüsselungsregeln und Synonyme abgelegt.

Die Mappe "cef - Fuzzy Search prepare addresses_v2 (PQ).xlsm" enthält die Abfragen, um die Tabellen zu lesen, die Adressen für die Suche aufzubereiten und in dieser die Ergebnisse der Verschlüsselung abzulegen. Makrocode wird nur für die Ablaufsteuerung verwendet. Im Tabellenblatt "Param" werden die Quellen zugeordnet. In einer Echtumgebung könnte eine Aktualisierung 2 bis 3 mal täglich stattfinden.

Mit der Mappe "cef - Fuzzy search_v2 (PQ).xlsb" wird die Suche durchgeführt. Hier ist der normale Ablauf der, dass beim Öffnen der Mappe die Adressen, sowie die für die Suche notwendigen, verschlüsselten, Daten importiert und beim Schließen der Mappe wieder gelöscht werden. Es ist zwar für Power Query überhaupt kein Problem, bei jeder Suchanfrage, die Daten aus einer Mappe zu importieren, aber das wirkt sich jedes Mal mit ca. 0,5 Sek. zusätzlicher Antwortzeit aus. Deshalb habe ich mich für die Variante entschieden, dass beim Öffnen die Daten importiert und diese vor dem Schließen wieder gelöscht werden.

Aktuell sind die Parameter aber so eingestellt, dass die Adressen und Suchdaten beim Öffnen nicht geladen und beim Schließen auch nicht gelöscht werden. Somit sollte die Mappe “cef – Fuzzy search_v2 (PQ)“, nachdem die Ausführung von Makros erlaubt wurde, direkt einsatzbereit sein.

[Luschi]

Hallo WS-53,

habe mir viel Zeit genommen, Deine neueste PQ-Arbeit zu studieren. Habe natürlich an der Abfrage 'Prepare_data' Hand angelegt und folgende Änderungen durchgeführt:

Code:

/* diese 2 nachfolgenden internen Funktionen wurden durch 'fx_ReplacementFunctions' ersetzt
      - ReplacementFunction     = each List.Accumulate(Replacements, _, (t, r) => Text.Replace(Text.From(t), Text.From(r{0}), (r{1}) )),
      - ReplacementFunction3    = each List.Accumulate(Rest1_replace, _, (t, r) => Text.Replace(Text.From(t), Text.From(r{0}), (r{1}) )),
      - dabei wird nicht in der Funktion statisch festgelegt, welche Liste von der List.Accumulate()-Fkt. abgearbeitet wird
        sondern als Parameter beim Aufruf der Fkt. übergeben
    */
    fx_ReplacementFunctions = (Liste, z1, z2)=> each List.Accumulate(Liste, _,
                                     (t, r) => Text.Replace(Text.From(t), Text.From(r{z1}), (r{z2}) )),

sowie:

Code:

    /*diese 3 internen Funktionen wurden durch 'fx_ReplFunc_Synonyms' ersetzt
      - ReplFunc_Synonyms_1b    = each List.Accumulate(Synonyms_1, _, (t, r) => Text.Replace(Text.From(t), Text.From(r{1}), (r{0}) )),
      - ReplFunc_Synonyms_2     = (Liste, z1, z2)=> List.Accumulate(Liste, _, (t, r) => Text.Replace(Text.From(t), Text.From(r{z1}), (r{z2}) )),
      - ReplFunc_Synonyms_2b    = each List.Accumulate(Synonyms_2, _, (t, r) => Text.Replace(Text.From(t), Text.From(r{1}), (r{0}) )),
    */
    fx_ReplFunc_Synonyms    = (Liste, Wert, z1, z2)=> List.Accumulate(Liste, Wert,
                                     (t, r) => Text.Replace(Text.From(t), Text.From(r{z1}), (r{z2}) )),

Habe 'ne ganze Weile daran geknaupelt, denn ob in der internen Funktion vor List.Accumulate() 1 'each' stehen muß, hängt ganz vom Befehl ab, der die Funktion aufruft:
- bei Table.TransformColumns(): ja
- bei Table.AddColumn()          : nein

Ansonsten hast du aber viel Arbeit in dieses Projekt investiert und 1 interessante PQ-Lösung geschaffen.

Gruß von Luschi
aus klein-Paris

[ws-53]

Hallo Luschi,

vielen Dank für die Mühe, die du dir gemacht hast und auch für die Blumen.

Bei meinen Bemühungen, den ursprünglichen Code zu optimieren, der ja nun tatsächlich kürzer (weniger Schritte) und wesentlich schneller ist, es mir manchmal passiert, dass das neue Ergebnis Unterschiede zum alten enthielt. Deshalb habe ich immer "neu" gegen "alt" verglichen, um dann festzustellen, dass ich noch etwas nacharbeiten musste.

Ich habe zwar jetzt noch nicht feststellen können, warum es unterschiede gibt, aber zwischen deiner optimierten Lösung und meiner gibt es einige Unterschiede.

Weiterhin ist es natürlich so, dass die Ersetzungen, die sowohl in der Datenaufbereitung, als auch in der Suche zur Anwendung kommen, natürlich in beiden Abfragen identische Ergebnisse liefern müssen, da sonst nicht alles gefunden wird.

Bezüglich Aufwand kann ich nur immer wieder feststellen, dass ich für meine SAP-Lösung damals rund 10.000 Zeilen ABAP-Code benötigt habe, während die PQ-Lösung mit einem Bruchteil davon auskommt. Auch wenn ich in der SAP-Lösung noch ein paar Komfort-Gimmicks mehr eingebaut hatte. Auf der anderen Seite aber für jede Adressspalte ein separates Eingabefeld hatte. Da finde ich die Lösung mit einem einzigen Suchfeld für alles, wesentlich komfortabler.

Auf der Website "dasoertliche.de" gibt es das Ankreuzfeld "Auch ähnliche finden", die ist aber nicht so leistungsfähig. Zwar liefert die Suche nach "Meier" in allen möglichen Schreibweisen mit Frankfurt am Main, jeweils 252 Treffer,  aber Frankfurt am Main in Verbindung mit Christoph (36), Cristof (0), Kristof (0), zeigt gravierende Unterschiede. Wobei meine Suche nach Christoph, nicht unbedingt alle, aber auch sehr viele osteuropäische Schreibweisen als Treffer liefert.

[snb]

Obwohl ohne PQ scheint mir dieses Suchverfahren 'fuzzy' genug und vielleicht schneller.

[ws-53]

Obwohl ohne PQ scheint mir dieses Suchverfahren 'fuzzy' genug und vielleicht schneller.

Auf jeden Fall zuerst einmal vielen Dank für deinen Ansatz, der in der Tat sehr schnell ist.

Das Problem bei diesem Ansatz ist halt nur, dass der gesuchte Begriff in exakter Schreibweise in der Adresse vorhanden sein muss, um gefunden zu werden. Wenn sich bei mir ein Meier meldet, dann muss ich halt wissen wie er sich schreibt, um diesen mit deiner Lösung zu finden. Und wenn ich bei Meier, Schmitt, Peter die Trefferliste einschränken will um nur  die eines Orts, Straße Land zu sehen, dann wird die Suche mit einem Laufzeitfehler quittiert.

Wobei ich davon überzeugt bin, dass du in der Lage bist auch eine richtig gute VBA-Lösung entwickeln zu können!

[snb]

Wenn du mit einschränken meinst: mehrere Filterstrings, getrennt von einer Leerstelle: siehe Anhang

[ws-53]

Hi snb,

es hat zwar absolut nichts mit "fuzzy" zu tun, aber ich bin schon sehr beeindruckt, mit wie wenig Code eine sehr schnelle String-Suche realisierbar ist, bei der du auch auch beliebig viele Strings eingeben kannst, die dann alle als AND-Condition behandelt werden. 

[snb]

Im Gegenteil: es hat doch genau mit 'Fuzzy logic' zu tun.

[ws-53]

Hi snb,

ich finde deine Stringsuche wirklich gut!

Aber Fuzzy heißt "unscharf". Das heißt wenn ich "Meier" suche, dann will ich unter anderem auch die holländische Schreibweise "Meijer" sowie die österreichische Schreibweise "Meir", sowie auch die, die mit "ay" und "ey" geschrieben sind, finden. Aber diese findet deine Stringsuche nicht. Das einzig unscharfe bei deiner Stringsuche ist, dass die Groß-/Kleinschreibung nicht beachtet werden muss.

Natürlich kann ich bei der Suche "Charles de Gaulle Avenue" die Avenue einfach weglassen, da dies ja in der Adresse mit Av. oder Ave. abgekürzt sein kann. Aber sobald es um unterschiedliche Schreibweisen eines Namens geht, dann ist eine Stringsuche schnell am Ende.

[snb]

Für Meier, Meir oder Meijer reciht schon  "mei' (Like mei*)

Insgesamt für die andere : 'M er'.

Wahrscheinlichkeit basiert auf 'Erfahrung' ist etwas anderes als 'fuzzy'.