Scalanie zapytań daje puste wartości po rozwinięciu - brak dopasowań kluczy

Power Query w Excelu, Power BI i Fabric

Scalanie zapytań daje puste wartości po rozwinięciu - brak dopasowań kluczy

W skrócie

Scalanie w Power Query daje null w rozwiniętych kolumnach wtedy, gdy brakuje dopasowań kluczy, a okno scalania sygnalizuje to tylko licznikiem na dole. Trafisz na to, gdy dociągasz do eksportu sprzedaży nazwy produktów albo dane sklepów ze słownika, tak jak w lekcji o scalaniu zapytań. Pokazujemy, jak znaleźć wiersze bez pary, które różnice w kluczach blokują dopasowanie i jak je usunąć przed scaleniem. Każdy przypadek sprawdziliśmy w polskim Excelu.

Jak to wygląda w praktyce

Po kliknięciu ikony rozwijania w nagłówku kolumny z tabelami (u nas tProdukty) nowe kolumny, na przykład Nazwa produktu i Kategoria, mają w części wierszy wartość null. Po załadowaniu do arkusza te komórki są puste, więc raport pokazuje sprzedaż bez nazwy i kategorii.

Zapowiedź problemu widać wcześniej, w oknie Scalanie. Komunikat pod tabelami podaje, ile wierszy pierwszej tabeli znalazło parę. W kursie brzmiał „Zaznaczenie jest zgodne z 9250 z 9250 wierszy z pierwszej tabeli.” (ang. The selection matches 9250 of 9250 rows from the first table.), czyli każdy kod ze sprzedaży był w katalogu. Pierwsza liczba mniejsza od drugiej oznacza wiersze, które po rozwinięciu dostaną null. W teście cztery wiersze z kodami BEX-1021, bex-1021, NOR-1030 (ze spacją na końcu) i KEL-1005 scaliliśmy z katalogiem zawierającym BEX-1021, NOR-1030 i KEL-1005. Dopasowały się dwa wiersze, a rozwinięta kolumna miała wartości Krem, null, null, Laptop.

Dlaczego tak się dzieje

Scalanie (ang. merge) porównuje klucze dokładnie: wartości muszą mieć ten sam typ i identyczną treść znak po znaku. Sprawdziliśmy w Excelu cztery typowe różnice i każda dała zero dopasowań:

• inny typ danych: liczba 1021 po jednej stronie i tekst "1021" po drugiej,
• wielkość liter: bex-1021 i BEX-1021,
• spacja na końcu: BEX-1021 i BEX-1021,
• zera wiodące: kod zapisany przez system jako liczba 123, a w słowniku jako tekst 000123. Sama zamiana liczby na tekst daje "123", które nadal nie pasuje.

Przy domyślnym rodzaju sprzężenia Lewe zewnętrzne (wszystkie z pierwszej, pasujące z drugiej) wiersze bez pary zostają w wyniku, a ich zagnieżdżona tabela jest pusta. Rozwinięcie pustej tabeli daje null w każdej dociąganej kolumnie. Przy rodzaju Wewnętrzne (tylko pasujące wiersze) te same wiersze znikają: w naszym teście z czterech zostały dwa, bez żadnego ostrzeżenia.

Jak to rozwiązać krok po kroku

1. Znajdź wiersze bez pary. Zaznacz zapytanie sprzedaży, na karcie Strona główna rozwiń strzałkę przy Scal zapytania i wybierz Scal zapytania jako nowe. Wskaż te same kolumny klucza co wcześniej, a z listy Rodzaj sprzężenia wybierz Lewe anty (wiersze tylko w pierwszej). Wynik to klucze, których nie ma w słowniku. W naszym teście były to bex-1021 i NOR-1030 ze spacją.
2. Porównaj typy kluczy. Ikona w nagłówku kolumny pokazuje typ, na przykład ABC dla tekstu i 123 dla liczby całkowitej. Jeśli strony się różnią, zaznacz kolumnę klucza i na karcie Przekształć ustaw Typ danych: Tekst w obu zapytaniach. W teście liczba 1021 zamieniona na tekst od razu znalazła parę w słowniku.
3. Ujednolić zapis tekstu. Zaznacz kolumnę klucza, na karcie Przekształć rozwiń Format i wybierz Przycięcie, a potem jeszcze raz Format i Wielkie litery. Zrób to w obu zapytaniach. W teście bex-1021 po tych dwóch krokach trafiło na BEX-1021. Jeśli klucz nadal nie pasuje, sprawdź twardą spację (U+00A0) w środku tekstu, którą zamienisz formułą Text.Replace([Kod], "#(00A0)", " ").
4. Odtwórz zera wiodące. Gdy system zapisał kod jako liczbę, dodaj kolumnę niestandardową z formułą Text.PadStart(Text.From([Kod]), 6, "0"), gdzie 6 to długość kodu w słowniku. W teście liczba 123 zamieniła się w 000123 i znalazła parę, a samo Text.From dawało zero dopasowań.
5. Sprawdź licznik przed OK. Kliknij ikonę koła zębatego przy kroku Scalone zapytania na liście Zastosowane kroki. Okno Scalanie otworzy się z poprzednimi ustawieniami. Przeczytaj komunikat pod tabelami i kliknij OK dopiero wtedy, gdy obie liczby są równe albo różnica dotyczy kodów, których naprawdę nie ma w katalogu.

Jak sprawdzić, że zadziałało

Po poprawkach komunikat w oknie Scalanie powinien pokazywać pełną zgodność, a rozwinięte kolumny nie powinny mieć wartości null poza kodami, których słownik nie zna. Szybki test na wyniku: filtr kolumny Nazwa produktu pokaże, czy na liście wartości jest jeszcze null. Zapytanie z rodzajem sprzężenia Lewe anty zostaw w pliku jako stałą kontrolę: po każdym odświeżeniu powinno zwracać zero wierszy, a każdy wiersz oznacza nowy kod, którego brakuje w słowniku. Nawrotom zapobiegnie krok czyszczenia klucza (typ, przycięcie, wielkie litery) na początku obu zapytań, zanim dane trafią do scalenia.

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