Dołączanie zapytań: kolumny się rozjeżdżają i pojawiają się puste wartości

Power Query w Excelu, Power BI i Fabric

W skrócie

Po dołączeniu zapytań w Power Query wynik ma więcej kolumn niż tabele źródłowe, a w części wierszy pojawia się null, choć w źródłach te wartości były wypełnione.
Dołączanie (Table.Combine) dopasowuje kolumny po nazwach, nie po kolejności. Nazwa różniąca się jedną literą, spacją na końcu albo wielkością litery tworzy osobną kolumnę, pustą dla wierszy z pozostałych tabel.
Ujednolić nazwy kolumn przed dołączeniem (zmiana nazwy w nagłówku albo Table.TransformColumnNames), wyrównaj typy, a po dołączeniu porównaj listę kolumn wyniku z listą oczekiwaną.

Polecenie Dołącz zapytania w Power Query daje null tam, gdzie spodziewasz się liczb, gdy tabele źródłowe mają choć trochę inaczej nazwane kolumny. Trafisz na to przy łączeniu eksportów z różnych systemów, oddziałów albo okresów, na przykład gdy dział ekspansji Nordvelli przysyła sprzedaż nowych sklepów w osobnym pliku, jak w lekcji o dołączaniu zapytań. Pokazujemy, jak Power Query dopasowuje kolumny, jak znaleźć tę, która się rozjechała, i jak ujednolicić nazwy, zanim tabele trafią do jednej.

Jak to wygląda w praktyce

Okno Dołączanie pokazuje tylko nazwy tabel, a nie ich kolumny, więc niczego nie ostrzega. Problem widać dopiero w wyniku: obok oczekiwanej kolumny pojawia się druga o prawie takiej samej nazwie, a wartości są rozdzielone między nie. W naszym teście w Excelu dołączyliśmy tabelę z kolumnami Sklep i Ilość do tabeli z kolumnami Sklep i Ilosc (bez ogonka). Wynik miał trzy kolumny, Sklep, Ilość i Ilosc:

wiersz z pierwszej tabeli: Wrocław, 5, null,
wiersz z drugiej tabeli: Rzeszów, null, 7.

Suma kolumny Ilość pomija więc całą drugą tabelę, a raport zbudowany na tej kolumnie pokazuje za mało. Ten sam efekt dały w teście nazwa ze spacją na końcu (Ilość ) i nazwa pisana małą literą (ilość).

Okno Dołączanie w Power Query: opcja dwie tabele, pierwsza tabela tSklepy, druga tabela tSklepy_nowe — Okno Dołączanie wybiera tylko tabele, ich kolumn nie pokazuje. Power Query łączy kolumny po nazwach, więc rozbieżna nazwa ujawni się dopiero w wyniku.

Dlaczego tak się dzieje

Dołączanie (ang. append) zapisuje się w języku M jako Table.Combine({tSklepy, tSklepy_nowe}). Funkcja układa wiersze tabel jeden pod drugim i dopasowuje kolumny po nazwach, nie po kolejności. Wynikają z tego wprost dwie rzeczy:

kolejność kolumn nie ma znaczenia: w teście tabela z kolumnami w kolejności Ilość, Sklep dołączyła się poprawnie do tabeli Sklep, Ilość,
każda różnica w nazwie tworzy nową kolumnę: Power Query nie zgaduje, że Ilosc to Ilość, więc dokłada kolumnę i wypełnia ją wartością null w wierszach tabel, które jej nie mają.

Tak samo działa dołączanie co najmniej trzech tabel: w teście trzecia tabela miała dodatkową kolumnę Uwagi i wiersze dwóch pierwszych dostały w niej null. Gdy nazwa się zgadza, a typy są różne (liczba w jednej tabeli, tekst w drugiej), kolumna wyniku traci typ: w teście dostała typ ogólny Any.Type.

Jak to rozwiązać krok po kroku

Wypisz nazwy kolumn każdego źródła. Spacji na końcu nie widać w nagłówku, dlatego w pustym zapytaniu wpisz formułę = List.Transform(Table.ColumnNames(Sprzedaz_nowe_sklepy), each "[" & _ & "]"), podstawiając nazwę swojego zapytania. Każda nazwa pojawi się w nawiasach kwadratowych, więc nazwa ze spacją wyświetli się jako [Ilość ]. Porównaj listy wszystkich dołączanych zapytań.
Zmień nazwę kolumny w zapytaniu, które odstaje. Kliknij dwukrotnie nagłówek i wpisz nazwę identyczną jak w pozostałych tabelach, łącznie z ogonkami i wielkością liter. Power Query zapisze krok Table.RenameColumns. W teście zmiana Ilosc na Ilość dała jedną kolumnę z wartościami 5 i 7, bez null.
Spacje usuń ze wszystkich nazw jednym krokiem. Gdy źródło dokleja spacje do nagłówków, kliknij prawym przyciskiem ostatni krok, wybierz Wstaw krok po i wpisz w pasku formuły = Table.TransformColumnNames(#"Poprzedni krok", Text.Trim), podstawiając nazwę poprzedniego kroku. Formuła przycina wszystkie nazwy naraz, także w przyszłych plikach. W teście kolumna Ilość po tym kroku połączyła się z Ilość.
Wyrównaj typy. Ustaw ten sam typ danych kolumny w każdym źródle (karta Przekształć, Typ danych), tak jak przy kolumnie Otwarty od w lekcji o dołączaniu. Inaczej kolumna wyniku dostanie typ ogólny.
Odśwież albo utwórz dołączenie od nowa. Istniejący krok Table.Combine wystarczy odświeżyć, bo poprawione nazwy przejdą do wyniku. Nowe dołączenie tworzysz tak: zaznacz pierwsze zapytanie, na karcie Strona główna rozwiń strzałkę przy Dołącz zapytania, wybierz Dołącz zapytania jako nowe i wskaż tabele w polach Pierwsza tabela i Druga tabela. Przy większej liczbie źródeł zaznacz Co najmniej trzy tabele.

Jak sprawdzić, że zadziałało

Po dołączeniu porównaj listę kolumn wyniku z oczekiwaną. Kolumny nadmiarowe pokaże formuła w pustym zapytaniu = List.Difference(Table.ColumnNames(Sprzedaz_razem), {"Sklep", "Ilość"}), w której w nawiasach klamrowych wpisujesz nazwy oczekiwane, a Sprzedaz_razem to nazwa zapytania z dołączeniem. Pusta lista oznacza zgodność. W teście formuła zwróciła Ilosc, czyli dokładnie kolumnę, która się rozjechała. Drugi test to lista wartości w filtrze kolumny: null w kolumnie, która w źródłach jest zawsze wypełniona, wskazuje źródło z inną nazwą. Gdy pliki przychodzą od różnych osób, zostaw krok z Table.TransformColumnNames na stałe, a zmianę nazwy w nagłówku powtórz w każdym nowym źródle, zanim je dołączysz.

Poczytaj więcej
Lekcja 3 bezpłatnego kursu Power Query: Scalanie i dołączanie zapytań
Cały bezpłatny kurs Power Query w Excelu, Power BI i Fabric z Copilotem

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

Baner szkolenia Microsoft Excel - Power Query w JSystems z edytorem Power Query na ekranie laptopa

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

Autorzy

Tekst: Redakcja JSystems

Najczęściej zadawane pytania

Czy kolejność kolumn w dołączanych tabelach ma znaczenie?

Nie. Power Query dopasowuje kolumny po nazwach, więc tabela z kolumnami w kolejności Ilość, Sklep dołączy się poprawnie do tabeli Sklep, Ilość. Liczą się wyłącznie identyczne nazwy, łącznie z wielkością liter, ogonkami i spacjami.

Dlaczego po dołączeniu mam dwie kolumny o prawie takiej samej nazwie?

Nazwy różnią się czymś, co łatwo przeoczyć, na przykład spacją na końcu, brakiem ogonka albo wielką literą. Power Query traktuje je jako różne kolumny i każdą wypełnia wartością null w wierszach tabeli, która jej nie ma. Zmień nazwę w zapytaniu źródłowym na identyczną z pozostałymi i odśwież wynik.

Czy mogę dołączyć tabele z różną liczbą kolumn?

Tak. Wynik będzie miał wszystkie kolumny ze wszystkich tabel, a tam, gdzie tabela danej kolumny nie ma, pojawi się null. W naszym teście dodatkowa kolumna Uwagi z trzeciej tabeli dała null w wierszach dwóch pierwszych tabel.

Jak sprawdzić, czy nazwa kolumny ma spację na końcu?

W nagłówku kolumny tej spacji nie widać. Pomoże formuła w pustym zapytaniu, która wypisze nazwy kolumn otoczone nawiasami kwadratowymi, tak jak w pierwszym kroku naprawy. Nazwa ze spacją wyświetli się wtedy jako [Ilość ] i od razu odróżnisz ją od poprawnej.