Scalone komórki w Excelu dają puste wartości w Power Query - Wypełnij w dół
W skrócie
Raport ze scalonymi komórkami, na przykład z miastem wpisanym raz dla kilku sklepów, trafia do Power Query z wartością tylko w pierwszym wierszu grupy. Pozostałe wiersze mają null, a grupowanie i filtry gubią część danych.
Scalona komórka przechowuje wartość tylko w lewej górnej komórce scalonego obszaru. Power Query czyta komórki pojedynczo, więc pozostałe komórki tego obszaru są dla niego puste.
Zaznacz kolumnę i na karcie Przekształć wybierz Wypełnij, W dół. Wypełniaj tylko kolumny pochodzące ze scalonych komórek, a puste teksty zamień najpierw na null, bo polecenie uzupełnia wyłącznie wartości null.
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.
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
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.
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.
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.
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ół.
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.
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.
Karta Przekształć edytora Power Query. W grupie Dowolna kolumna są przyciski Wypełnij i Zamienianie wartości, a w grupie Tabela Transponuj i Użyj pierwszego wiersza jako nagłówków.
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.
Dlaczego Wypełnij w dół nie uzupełnia niektórych pustych komórek?
Polecenie wypełnia wyłącznie wartości null. Jeśli komórka zawiera pusty tekst, na przykład po imporcie z pliku CSV albo po wcześniejszej zamianie wartości, Power Query traktuje ją jak wartość i kopiuje ją w dół. Zamień pusty tekst na null poleceniem Zamienianie wartości i dopiero wtedy wypełnij kolumnę.
Czy lepiej rozscalić komórki w Excelu, zanim wczytam dane?
Rozscalenie w arkuszu nie przywróci wartości w pozostałych komórkach, bo zostają one puste. Prościej zostawić arkusz bez zmian i wypełnić kolumnę w Power Query, bo ten krok zadziała przy każdym odświeżeniu, także dla nowych grup w kolejnych wersjach raportu.
Co się stanie, gdy w źródle brakuje wartości na początku grupy?
Wypełnij w dół skopiuje wtedy wartość z poprzedniej grupy, bo uzupełnia każdy null najbliższą wartością powyżej i nie zna granic grup. Jeśli null stoi w pierwszym wierszu tabeli, zostanie pusty, bo nie ma nic powyżej. Przy takich danych porównaj wynik grupowania z raportem źródłowym.
Skąd w nagłówkach bierze się nazwa Column3 przy scalonym nagłówku?
Scalona komórka nagłówka nad dwiema kolumnami ma wartość tylko w pierwszej z nich, więc druga kolumna dostaje przy promowaniu nagłówków nazwę zastępczą, w naszym teście Column3. Połącz dwa wiersze nagłówków przez transpozycję i wypełnianie w dół albo nadaj nazwę ręcznie.
Komentarze (0)
Brak komentarzy...