Scalone komórki w Excelu dają puste wartości w Power Query - Wypełnij w dół

Power Query w Excelu, Power BI i Fabric

Scalone komórki w Excelu dają puste wartości w Power Query - Wypełnij w dół

W skrócie

Scalone komórki spotkasz w Power Query przy raportach przygotowanych do druku, a nie do analizy: miasto, region albo dział stoi raz, a obok jest kilka wierszy szczegółów. Pokazujemy, co Power Query widzi w takim arkuszu, jak uzupełnić puste wartości poleceniem Wypełnij w dół i jak nie zepsuć przy tym kolumn, które powinny zostać puste. Zachowanie poleceń sprawdziliśmy w polskim Excelu na raporcie sprzedaży sklepów Nordvelli.

Jak to wygląda w praktyce

W arkuszu źródłowym kolumna Miasto wygląda na pełną: Warszawa obejmuje scaloną komórką trzy sklepy, Kraków dwa. W Power Query wartość ma tylko pierwszy wiersz każdej grupy, a pozostałe pokazują null. W naszym pliku testowym kolumna Miasto miała po wczytaniu wartości Warszawa, null, null, Kraków, null, Wrocław.

Skutki widać dopiero w wynikach. Grupowanie po mieście dało cztery grupy zamiast trzech: Warszawa ze sprzedażą 120, osobną grupę null z sumą 245 oraz Kraków ze sprzedażą 110 zamiast 180. Filtr Miasto równe Warszawa zostawia jeden sklep z trzech, a scalanie ze słownikiem miast nie znajduje dopasowania dla wierszy z null. Ten sam efekt dają scalone nagłówki: kolumna pod scaloną komórką Sprzedaż, obejmującą dwie kolumny, dostała po promowaniu nagłówków nazwę Column3.

Dlaczego tak się dzieje

Dokumentacja Excela opisuje scalanie wprost: w scalonej komórce zostaje zawartość tylko jednej komórki, lewej górnej, a zawartość pozostałych jest usuwana. Scalenie to w praktyce sposób wyświetlania. W pliku dalej istnieją osobne komórki, z których tylko pierwsza ma wartość.

Power Query czyta arkusz komórka po komórce i nie interpretuje formatowania, więc komórki objęte scaleniem, poza pierwszą, dostają wartość null. Tak samo traktuje każdą pustą komórkę arkusza. Polecenie Wypełnij, W dół wywołuje funkcję Table.FillDown, która według swojego opisu przenosi wartość z poprzedniej komórki do komórek z wartością null położonych niżej w tej samej kolumnie. Wypełnia wszystkie null bez wyjątku, więc nie odróżni null ze scalenia od prawdziwego braku danych.

Jak to rozwiązać krok po kroku

1. Wczytaj arkusz i na karcie Strona główna kliknij Użyj pierwszego wiersza jako nagłówków. Kolumny ze scalonych komórek poznasz po wartościach null w wierszach, które w Excelu wyglądały na wypełnione.
2. Zaznacz kolumnę pochodzącą ze scalonych komórek, na przykład Miasto, i na karcie Przekształć kliknij Wypełnij, a potem W dół. Na liście kroków pojawi się Wypełniono w dół z formułą Table.FillDown(#"Nagłówki o podwyższonym poziomie", {"Miasto"}). W naszym teście kolumna dostała wartości Warszawa, Warszawa, Warszawa, Kraków, Kraków, Wrocław.
3. Nie zaznaczaj przy tym kolumn, w których brak wartości coś znaczy, na przykład Uwagi. W naszym teście uwaga „remont” przy jednym sklepie po wypełnieniu kolumny Uwagi trafiła do wszystkich sześciu sklepów. Wypełniaj tylko kolumny, które w źródle były scalone.
4. Sprawdź, czy puste komórki to naprawdę null. Table.FillDown traktuje pusty tekst jak wartość: w naszym teście pusty tekst pod Warszawą został skopiowany w dół zamiast miasta. Zamień go najpierw na null: zaznacz kolumnę, na karcie Przekształć kliknij Zamienianie wartości, pole Wartość do znalezienia zostaw puste, a w polu Zamień na wpisz null. Dopiero potem wypełnij w dół.
5. Gdy wartość grupy stoi w ostatnim wierszu grupy, a nie w pierwszym, użyj polecenia Wypełnij, W górę. Działa tak samo, tylko kopiuje wartość z dołu do pustych komórek powyżej.
6. Nagłówek w dwóch wierszach ze scaloną komórką, na przykład Sprzedaż nad kolumnami Styczeń i Luty, połącz w jeden wiersz przed promowaniem. Kliknij Transponuj, wypełnij w dół kolumnę Column1, zaznacz Column1 i Column2, użyj Scal kolumny ze spacją jako separatorem, a potem znowu kliknij Transponuj i Użyj pierwszego wiersza jako nagłówków. W naszym teście powstały nazwy Sprzedaż Styczeń i Sprzedaż Luty. Przed drugą transpozycją przytnij scaloną kolumnę poleceniem Format, Przycięcie, bo przy pustej komórce scalanie zostawia spację na końcu nazwy, u nas w nazwie Sklep.

Jak sprawdzić, że zadziałało

Otwórz filtr wypełnionej kolumny i sprawdź, że na liście wartości nie ma już pozycji null. Potem pogrupuj dane po tej kolumnie poleceniem Grupowanie według i porównaj sumy z raportem źródłowym: w naszym teście po wypełnieniu grupy miały sprzedaż 295 dla Warszawy, 180 dla Krakowa i 90 dla Wrocławia, zgodnie z arkuszem. Krok grupowania usuń po sprawdzeniu.

Kolumny, których nie wypełniałeś, powinny mieć tyle samo wartości null co przed zmianą. Żeby problem nie wrócił, poproś autorów raportu o dane bez scalonych komórek, z wartością powtórzoną w każdym wierszu. Jeśli raport musi zostać w formie do druku, zostaw w zapytaniu krok wypełniania w dół, bo zadziała przy każdym odświeżeniu, także na nowych grupach.

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