Scalanie rozmyte łączy niewłaściwe wartości - próg podobieństwa i tabela przekształceń

Power Query w Excelu, Power BI i Fabric

Scalanie rozmyte łączy niewłaściwe wartości - próg podobieństwa i tabela przekształceń

W skrócie

Scalanie rozmyte w Power Query (ang. fuzzy merge) łączy wartości podobne, a nie identyczne, więc przydaje się przy nazwach wpisywanych ręcznie: sklepach, firmach, adresach. Ta sama elastyczność bywa źródłem błędów, gdy opcje dopasowywania zostają przypadkowe. Pokazujemy, jak działają próg podobieństwa, ignorowanie wielkości liter, łączenie części tekstu, limit dopasowań i tabela przekształceń oraz jak sprawdzić pary przed użyciem wyniku. Wszystkie ustawienia przetestowaliśmy w polskim Excelu na nazwach sklepów Nordvelli z literówkami.

Jak to wygląda w praktyce

Lista sklepów wpisana ręcznie przez kierowników regionów ma literówki, więc scalasz ją ze słownikiem sklepów z zaznaczonym polem Użyj dopasowywania rozmytego w celu wykonania scalenia. Objawy złych ustawień, które zebraliśmy w teście w Excelu:

• przy progu 0,3 wpis „Nordvella Warszawa Mokotow” dostał trzy dopasowania: Mokotów (podobieństwo 1,00), Centrum (0,39) i Ursynów (0,32), więc po rozwinięciu jeden wiersz zamienił się w trzy,
• przy tym samym progu nieistniejący w słowniku „Nordvella Warszawa Wola” został przypisany do trzech innych warszawskich sklepów, a przy progu 0,2 „Nordvella Gdynia” do dziesięciu sklepów z oceną od 0,23 do 0,25,
• przy progu 0,99 nie połączyła się literówka „Nordvela Wrocław”, której podobieństwo do „Nordvella Wrocław” wynosiło 0,98.

Komunikatu błędu w żadnym z tych przypadków nie ma. Wynik wygląda poprawnie, dopóki nie porównasz liczby wierszy albo sum z danymi źródłowymi.

Dlaczego tak się dzieje

Scalanie rozmyte porównuje teksty według podobieństwa i każdej parze nadaje ocenę od 0 do 1. Para zostaje połączona, gdy ocena osiąga wartość z pola Próg podobieństwa (opcjonalnie). Opis funkcji podaje, że próg 1,00 dopuszcza tylko dokładne dopasowania, a wartość domyślna to 0,80. Nazwy z jednym wspólnym członem, takim jak „Nordvella Warszawa”, miały w teście oceny od 0,3 do 0,4, więc niski próg przepuszcza je jako pary. Wysoki próg odrzuca z kolei literówki, które dostały 0,96 czy 0,98.

Pozostałe pozycje sekcji Opcje dopasowywania rozmytego też zmieniają wynik. Ignoruj wielkość liter jest domyślnie włączone: po jego wyłączeniu „NORDVELLA GDAŃSK” nie znalazł pary. Dopasuj, łącząc części tekstu połączyło „Nordvella Rze szów” z „Nordvella Rzeszów” (0,96), a bez tej opcji pary nie było. Maksymalna liczba dopasowań (opcjonalnie) ogranicza liczbę par, ale nie poprawia ich trafności: przy progu 0,2 i limicie 1 „Nordvella Gdynia” trafił do Wrocławia. Skrótu „NV Wwa Ursynów” nie połączył ani próg 0,5, ani tym bardziej domyślne 0,80.

Jak to rozwiązać krok po kroku

1. Dodaj kolumnę z oceną podobieństwa. Kliknij krok Scalone zapytania i w pasku formuły dopisz do rekordu opcji na końcu Table.FuzzyNestedJoin pole SimilarityColumnName = "Podobieństwo", na przykład [IgnoreCase = true, IgnoreSpace = true, Threshold = 0.8, SimilarityColumnName = "Podobieństwo"]. Opis funkcji określa je jako nazwę kolumny pokazującej podobieństwo, więc każde dopasowanie dostanie swoją ocenę.
2. Dobierz próg na wynikach. Rozwiń kolumnę z tabelami razem z kolumną Podobieństwo i posortuj wynik rosnąco po ocenie, żeby na górze zobaczyć najsłabsze pary. W teście poprawne pary literówek miały od 0,96 do 1,00, a błędne najwyżej 0,41, więc domyślne 0,80 rozdzielało je bez pomyłki. Próg zmienisz w oknie Scalanie, w polu Próg podobieństwa (opcjonalnie).
3. Mapuj skróty tabelą przekształceń. Przez Wprowadź dane utwórz tabelę z kolumnami From i To, na przykład NV na Nordvella i Wwa na Warszawa, i wskaż ją w polu Tabela przekształcenia (opcjonalnie). Kolumny muszą się nazywać From i To, choć polski opis funkcji wspomina kolumny „Od” i „Do”. Z kolumnami Od i Do scalenie kończy się błędem Expression.Error: Oczekujemy kolumny typu Tekst o nazwie „From” w tabeli przekształcenia rozmytego. (ang. We expect a type text column with the name 'From' in the selected fuzzy transformation table.). W teście z poprawną tabelą „NV Wwa Ursynów” połączył się z „Nordvella Warszawa Ursynów” z oceną 0,98.
4. Limit dopasowań ustaw na końcu. Wartość 1 w polu Maksymalna liczba dopasowań (opcjonalnie) sprawia, że każdy wiersz wejściowy daje jeden wiersz wyniku, więc liczba wierszy się nie rozjedzie. Błędnej pary limit nie usuwa, tylko zostawia jedną z nich, dlatego najpierw ustaw próg.
5. Przejrzyj pary niepewne i wiersze bez pary. Odfiltruj pary z oceną niższą niż 1 i obejrzyj je. Wiersze z wartością null po rozwinięciu to nazwy spoza słownika, takie jak „Nordvella Warszawa Wola” przy progu domyślnym. Uzupełnij słownik albo dopisz mapowanie do tabeli przekształceń.
6. Sprawdź typ kolumn. Okno scalania przyjmuje do scalania rozmytego wyłącznie kolumny tekstowe, o czym informuje komunikat „Operacje sprzężenia rozmytego obsługują tylko kolumny tekstowe.” (ang. We only support text columns for fuzzy join operations.). Kody liczbowe łącz zwykłym, dokładnym scaleniem.

Jak sprawdzić, że zadziałało

Porównaj liczbę wierszy przed scaleniem i po rozwinięciu: jeśli wzrosła, część wierszy ma po kilka par i próg jest za niski. Policz wiersze z null w rozwiniętej kolumnie nazwy, czyli bez pary, i przejrzyj pary z najniższą oceną. Gdy wynik jest poprawny, zostaw kolumnę z oceną w zapytaniu i po każdym imporcie sprawdzaj nowe wpisy z oceną poniżej 1, bo nowa literówka albo nowy skrót mogą wymagać mapowania. Tam, gdzie dane mają kod sklepu albo inny identyfikator, scalaj po nim zwykłym scaleniem, a dopasowanie rozmyte zostaw dla kolumn, w których identyfikatora nie ma.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5