Jak scalić chaotyczne dane o klientach w jedną bazę?

Z lekcji dowiesz się:

• Przy jakich sytuacjach korzystać ze scalania rozmytego?

• Gdzie sprawdza się takie łączenie różnych danych?

• Jak scalić różnie zapisane: adresy, nazwy, numery telefonów – w spójną bazę!

Zapraszamy do poznania możliwości dopasowania rozmytego (nazywanego inaczej scalaniem rozmytym) w Power Query. Zadanie to wykonamy na przykładowych danych zawierających numery telefonów (rys. 1).

Rys. 1 – Przykładowe dane do zadania

Przykładowe dane zawierają dwie tabelki, w jednej zostały podane numery telefonów i dopasowane do nich zmyślone nazwy sieci komórkowych, w drugiej natomiast mamy właścicieli podanych numerów telefonów. Naszym zadaniem jest dopasowanie obu tabelek do siebie według numerów telefonów. Tutaj pojawia się problem, ponieważ zapisy poszczególnych numerów telefonów różnią się od siebie, mimo że składają się z tych samych cyfr, nawet w tej samej kolejności. W związku z tym dopasowanie dokładne nie odnalazłoby numerów telefonów z pierwszej tabelki w drugiej. W Power Query możemy rozwiązać ten problem.

Przede wszystkim musimy zaczytać dane do Power Query. Tabelkę z osobami już mamy zaczytaną, więc musimy jeszcze zaczytać tabelkę z sieciami. Od Excela 2016 dane z tabeli zaczytujemy za pomocą polecenia Z tabeli/ zakresu z karty Dane (rys. 2).

Rys. 2 – Polecenie Z tabeli/ zakresu z karty Dane

W Excelu 2010 i 2013 natomiast mamy osobną kartę o nazwie Power Query, gdzie również znajduje się polecenie Z tabeli/ zakresu (rys. 3).

Rys. 3 – Polecenie Z tabeli/zakresu z karty Power Query

Po kliknięciu otworzy się Edytor zapytań Power Query z wczytaną tabelą tTelefony. Obie tabelki są już zaczytane, ale w osobnych zapytaniach: tOsoby i tTelefony (rys. 4).

Rys. 4 – Tabelka tTelefony zaczytana do Power Query

Jak połączyć te dane, tak aby dowiedzieć się np. do jakiej sieci należy numer telefonu danej osoby lub do kogo należy numer telefonu z danej sieci.

W okienku zapytań zaznaczamy zapytanie tOsoby i wybieramy polecenie Scal zapytania z karty Strona główna (w zależności od Excela karta może nazywać się Narzędzia główne) – rys. 5.

Rys. 5 – Polecenie Scal zapytania z karty Strona główna

Otwiera się okno Scalania, w którym jest tabela z zapytania tOsoby (punkt 1 na rys. 6), a poniżej należy z wybrać - z listy rozwijanej - tabelę z drugiego zapytania, czyli tTelefony (punkt 2). W obu tabelach trzeba zaznaczyć kolumnę, na której chcemy połączyć (scalić) dane, czyli kolumnę Telefon. Następnie wybieramy z listy rozwijanej rodzaj sprzężenia (punkt 3). Wybieramy dopasowanie Lewe zewnętrzne (wszystkie z pierwszej, pasujące z drugiej). Power Query wyciągnie wszystkie wiersze z pierwszej tabeli (tOsoby) i pasujące z drugiej tabeli (tTelefony). Można zauważyć, że Power Query na dole okna Scalania podaje informacje ile wyników pasuje z drugiej tabeli – tylko 8 wierszy z 20 ma dopasowanie (zaznaczone strzałką na rys. 6).

Rys. 6 – Ustawianie parametrów scalania w oknie Scalania

Podsumowując dopasowanie dokładne nie znajduje odpowiednich połączeń, mimo że pokrywają się cyfry i ich kolejność w zapisie numerów telefonów. Nie zgadza się natomiast sposób zapisu czyli nawiasy, myślniki w innych miejscach. Dlatego trzeba użyć w tej sytuacji dopasowania rozmytego, czyli pod rodzajem sprzężenia zaznaczamy checkbox przy opcji Użyj dopasowania rozmytego w celu wykonania scalenia (zaznaczone strzałką na rys. 7). Następnie rozwijamy Opcje dopasowania rozmytego, gdzie w pierwszym kroku ustalamy Próg podobieństwa, czyli wartość zapisaną procentowo od 0% do 100%. Wartość domyślna dla tej opcji to 80%, którą zapisujemy w formacie 0,8. Przy ustawieniach domyślnych możemy zauważyć w podsumowaniu na dole okna Scalania, że pasuje 17 wierszy z 20. Tak ustawione parametry dopasowania rozmytego zatwierdzamy przyciskiem OK.

Rys. 7 – Opcje dopasowania rozmytego w oknie Scalania

Otrzymamy scalone dane przedstawione na rys. 8.

Rys. 8 – Scalone dane (dwie tabele połączone na zasadzie dopasowania rozmytego)

Kiedy przejrzymy kolumnę tTelefony zauważymy, że nie w każdym przypadku nastąpiło dopasowanie co widać na rys. 9.

Rys. 9 – Puste dopasowanie

Jeśli mamy pewność, że w obu tabelkach znajdują się te same dane i powinny do siebie pasować, to możemy zmniejszyć Próg podobieństwa w oknie Scalania. Można to zrobić klikając koło zębate przy nazwie kroku Scalone zapytania w Ustawieniach zapytania (zaznaczone strzałką na rys. 10).

Rys. 10 – Przejście do edycji kroku Scalone zapytania

Otworzy się okno Scalania, gdzie można zmienić Próg podobieństwa. Ustawiając próg 0,35 otrzymujemy od Power Query informację, że nie może ustalić ile wyników zostanie zwróconych. Wynika to z błędnego zapisu progu podobieństwa, ponieważ Power Query nie rozpoznaje przecinka – musimy użyć kropki jako separatora (rys. 11).

Rys. 11 – Błędny zapis progu podobieństwa

Po zmianie zapisu Progu podobieństwa na 0.35 (rys. 12) Power Query podpowie ile jest poprawnych rezultatów.

Rys. 12 – Poprawny zapis progu podobieństwa

W ten sposób otrzymamy scalone dane przedstawione na rys. 13. Jednak w tym przypadku można zauważyć, że próg podobieństwa został ustalony zbyt nisko. Okazuje się, że do jednego numeru telefonu zostały dopasowane aż trzy sieci telefoniczne.

Rys. 13 – Błędne dopasowanie kilku sieci do jednego numeru telefonu

Podsumowując - użyliśmy zbyt dużego rozmycia (zbyt duży próg podobieństwa). Kolejny raz przechodzimy do edycji kroku Scalone zapytania poprzez kliknięcie koła zębatego obok nazwy kroku (rys. 10). Otwiera się okno Scalania, w którym można ustawić dodatkową opcję pt. Maksymalna liczba dopasowań. Ustawiamy wartość 1, czyli Power Query do każdego numeru dopasuje tylko jedną sieć. Tak ustawiony opcjonalny parametr należy zatwierdzić przyciskiem OK (rys. 14).

Rys. 14 – opcja Maksymalna liczba dopasowań

Otrzymujemy dane, gdzie do każdej osoby i numeru telefonu została dopasowana jedna sieć (rys. 15).

Rys. 15 – Dopasowane dane z przypisaną jedną siecią do każdego numeru telefonu

Należy pamiętać, że dopasowanie rozmyte może zwracać zbyt mało lub za dużo wyników. Z tego względu trzeba trochę poeksperymentować przy tym dopasowaniu i zmieniać próg podobieństwa lub ustawiać dodatkowe (opcjonalne) parametry.

Na tym etapie można rozwinąć kolumnę tTelefony. Klikamy na ikonkę przy nazwie kolumny, zaznaczamy polecenie Rozwiń i zaznaczamy checkboxy przy danych Telefon i Sieć. Powinniśmy pamiętać o odznaczeniu checkboxa przy opcji Użyj oryginalnej nazwy kolumny jako prefiksu. Tak ustawione parametry rozwijania danych zatwierdzamy przyciskiem OK (rys. 16).

Rys. 16 – Parametry rozwijania danych

Otrzymamy rozwinięte dane przedstawione na rys. 17.

Rys. 17 – Rozwinięte dane

Tak przygotowane dane możemy załadować do Excela przy użyciu polecenia Zamknij i załaduj do - z karty Strona główna (rys. 18).

Rys. 18 – Polecenie Zamknij i załaduj do

W Excelu otworzy się okno Importowania danych, gdzie ustawiamy sposób wyświetlania danych jako Tabela i wskazujemy miejsce wstawienia danych – Istniejący arkusz oraz konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy klikając w OK (rys. 19).

Rys. 19 – Okno Importowania danych

Otrzymamy wczytane dane przedstawione na rys. 20.

Rys. 20 – Dane wczytane do Excela

Okazuje się, że zostały wczytane dane z zapytania tTelefony, a musimy jeszcze zaczytać tabelę tOsoby, bo na niej wykonywaliśmy scalanie. Tabelę tTelefony najlepiej usunąć za pomocą skrótu klawiszowego Ctrl+-. Użycie tego skrótu klawiszowego sprawi, że dane z tej tabeli zostaną w arkuszu ale tylko jako połączenie, nie zostaną trwale usunięte. Musimy włączyć sobie okno Zapytań i połączeń za pomocą polecenia Zapytania i połączenia z karty dane (rys. 21). W Excelu 2019 jest to połączone polecenie, natomiast w wersjach poprzednich polecenie Zapytania jest osobnym poleceniem.

Rys. 21 – Uruchamianie okna Zapytań i połączeń

Aby zaczytać do Excela zapytanie tOsoby klikamy na jego nazwę prawym przyciskiem myszy w oknie Zapytania i połączenie i z podręcznego menu wybieramy polecenie Załaduj do (rys. 22).

Rys. 22 – Polecenie Załaduj do

Otworzy się okno Importowania danych, gdzie ustawiamy sposób wyświetlania danych jako tabela i wskazujemy miejsce wstawienia danych – istniejący arkusz, konkretna komórka. Tak przygotowane parametry importowania danych zatwierdzamy przyciskiem OK (rys. 19).

Otrzymamy dane z zapytania tOsoby zaczytane do Excela przedstawione na rys. 23.

Rys. 23 – Dane z zapytania tOsoby zaczytane do Excela

Przy tak przygotowanych danych możemy porównać numery telefonów i sprawdzić czy dopasowanie rozmyte zadziałało poprawnie. Przede wszystkim przy tak dopasowanych danych łatwo jest już wprowadzić ostatnie poprawki ręcznie.

Polecamy także:

• Porównanie budżetu planowanego do faktycznego na wykresie

• Wyszukiwanie bliskich wartości np. nieco tańszego i droższego laptopa!