Po scaleniu zapytań przybyło wierszy - duplikaty kluczy w tabeli słownika

Power Query w Excelu, Power BI i Fabric

Po scaleniu zapytań przybyło wierszy - duplikaty kluczy w tabeli słownika

W skrócie

Scalanie w Power Query zwiększa liczbę wierszy po cichu: okno scalania pokazuje pełną zgodność, rozwinięcie kolumn przebiega bez błędu, a w raporcie sprzedaż jednego produktu nagle się podwaja. Trafisz na to przy każdym słowniku prowadzonym ręcznie albo sklejanym z kilku systemów, na przykład przy katalogu produktów z lekcji o scalaniu zapytań. Pokazujemy, skąd biorą się dodatkowe wiersze, jak znaleźć zdublowane klucze i jak zabezpieczyć zapytanie przed nawrotem.

Jak to wygląda w praktyce

Po załadowaniu do arkusza tabela ma więcej wierszy niż eksport sprzedaży, a sumy kontrolne się nie zgadzają. W naszym teście w Excelu trzy wiersze sprzedaży (100, 200 i 50 zł) po scaleniu z katalogiem, w którym kod BEX-1021 występuje dwa razy (jako „Krem” i „Krem 50 ml”), zamieniły się w cztery, a suma wartości wzrosła z 350 do 550. Wiersz z BEX-1021 pojawił się dwukrotnie, z dwiema różnymi nazwami produktu.

Okno Scalanie niczego tu nie zdradza: licznik dopasowań mówi, ile wierszy pierwszej tabeli znalazło parę, więc przy dublach w słowniku nadal pokazuje pełną zgodność. Zmiana rodzaju sprzężenia też nie pomaga, bo Wewnętrzne w tym samym teście również dało cztery wiersze. Jeśli ten sam słownik przeszukujesz formułą w rodzaju Slownik{[Kod = "BEX-1021"]}, problem ujawnia się wprost jako błąd Expression.Error: Klucz pasował do co najmniej dwóch wierszy w tabeli. (ang. The key matched more than one row in the table.).

Dlaczego tak się dzieje

Scalanie zapisuje w każdym wierszu pierwszej tabeli zagnieżdżoną tabelę ze wszystkimi pasującymi wierszami słownika. Gdy klucz jest w słowniku unikatowy, ta tabela ma jeden wiersz. W teście dla BEX-1021 miała dwa wiersze, a dla pozostałych kodów po jednym. Rozwinięcie kolumny tworzy osobny wiersz wyniku dla każdego wiersza zagnieżdżonej tabeli, więc wiersz sprzedaży powtarza się tyle razy, ile razy jego klucz występuje w słowniku. Wartość sprzedaży kopiuje się razem z nim i stąd rosnące sumy.

Duble w słowniku powstają między innymi przy ręcznym dopisywaniu pozycji, przy sklejaniu katalogów z dwóch systemów albo wtedy, gdy w zapytaniu słownika ujednolicisz wielkość liter i spacje, przez co dwa różne zapisy tego samego kodu stają się identyczne. Do scalania potrzebny jest słownik, w którym każdy klucz występuje dokładnie raz.

Jak to rozwiązać krok po kroku

1. Porównaj liczbę wierszy przed scaleniem i po nim. Pasek stanu edytora przy dużych tabelach pokazuje tylko „999+”, więc na karcie Widok zaznacz Profil kolumny, kliknij na pasku stanu napis Profilowanie kolumn w oparciu o następującą liczbę pierwszych wierszy: 1000 i wybierz Profilowanie kolumn w oparciu o cały zestaw danych. Kliknij krok przed scaleniem, a potem krok Rozwinięty element tProdukty i porównaj pole Liczba w statystykach kolumny.
2. Sprawdź unikatowość klucza w słowniku. Otwórz zapytanie słownika (u nas tProdukty) i na karcie Widok zaznacz Rozkład kolumn. Pod nagłówkiem kolumny klucza pojawi się napis w rodzaju „Odrębne: 3, unikatowe: 2”. Odrębne to liczba różnych wartości, a unikatowe to wartości występujące tylko raz. W poprawnym słowniku obie liczby są równe liczbie wierszy. Nasz testowy słownik miał 4 wiersze, 3 wartości odrębne i 2 unikatowe, co od razu wskazuje dubel.
3. Znajdź zdublowane klucze. Zaznacz kolumnę klucza, na karcie Strona główna rozwiń Zachowaj wiersze i wybierz Zachowaj duplikaty. Zostaną wiersze z kluczami występującymi więcej niż raz, razem z resztą kolumn, więc zobaczysz, czym duble się różnią. Zestawienie da też Grupowanie według kolumny klucza z operacją Zlicz wiersze i filtrem wartości większych od 1: w teście zwróciło BEX-1021 z liczbą 2. Po obejrzeniu usuń ten krok krzyżykiem.
4. Napraw słownik, nie wynik. Jeśli duble są identyczne, zaznacz kolumnę klucza w zapytaniu słownika, na karcie Strona główna rozwiń Usuń wiersze i wybierz Usuń duplikaty. W teście słownik zmalał do 3 wierszy, a scalenie dało z powrotem 3 wiersze i sumę 350. Gdy duble różnią się treścią, jak „Krem” i „Krem 50 ml”, zdecyduj, który zapis jest właściwy, i popraw źródło słownika, bo przy usuwaniu duplikatów nie ma gwarancji, który wiersz zostanie.
5. Nie usuwaj dubli w tabeli wynikowej. Usunięcie duplikatów po rozwinięciu nie pomoże, gdy duble słownika różnią się nazwą, a usunięcie po samym kluczu skasowałoby prawdziwe transakcje tego samego produktu. Poprawka należy do zapytania słownika, przed scaleniem.

Jak sprawdzić, że zadziałało

Po poprawce porównaj trzy liczby: wiersze sprzedaży przed scaleniem, wiersze po rozwinięciu i sumę kontrolną wartości. Liczba wierszy i suma powinny być takie same jak przed scaleniem, w teście 3 wiersze i 350 zł. Żeby dubel nie wrócił niezauważony przy następnym imporcie katalogu, kliknij prawym przyciskiem krok Rozwinięty element tProdukty, wybierz Wstaw krok po i wpisz w pasku formuły kontrolę:

= if Table.RowCount(#"Rozwinięty element tProdukty") = Table.RowCount(#"Krok przed scaleniem")
  then #"Rozwinięty element tProdukty"
  else error "Duplikaty kluczy w słowniku produktów"

W miejsce #"Krok przed scaleniem" wpisz nazwę kroku, który poprzedza scalanie. Gdy liczby się zgadzają, krok przepuszcza dane bez zmian, a gdy słownik znów dostanie dubel, odświeżenie zatrzyma się z błędem Expression.Error i Twoim komunikatem zamiast po cichu zawyżyć sumy.

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