Zamień wartości zamienia fragmenty słów - jak zamienić tylko całą zawartość komórki

Power Query w Excelu, Power BI i Fabric

Zamień wartości zamienia fragmenty słów - jak zamienić tylko całą zawartość komórki

W skrócie

Na ten problem trafiasz, gdy porządkujesz słowniki: chcesz zmienić jedną nazwę kanału, kategorii albo miasta, a zmieniają się też dłuższe nazwy, które ją zawierają. W Power Query Zamień wartości dla kolumny tekstowej szuka fragmentu tekstu i nie sprawdza, czy pasuje cała komórka. Pokazujemy, jak przełączyć okno w tryb całej zawartości komórki, jak wygląda to w kodzie M i jak sprawdzić wynik.

Jak to wygląda w praktyce

Zaznaczasz kolumnę, na karcie Strona główna klikasz Zamienianie wartości, w polu Wartość do znalezienia wpisujesz „Sklep”, a w polu Zamień na „Stacjonarny”. Po kliknięciu OK pojawia się krok Zamieniono wartość, bez błędu, ale zmienia się każda komórka zawierająca to słowo. Sprawdziliśmy to w polskim Excelu na kolumnie z wartościami „Sklep” i „Sklep internetowy”: po zamianie były w niej „Stacjonarny” i „Stacjonarny internetowy”. W kursie ten sam mechanizm opisujemy na przykładzie, w którym „Kraków” zamieniłby się także wewnątrz „Kraków Galeria”.

Druga cecha zamiany: rozróżnia wielkość liter. Funkcja Text.Replace("Koszt kosztów", "Koszt", "Przychód") zwróciła „Przychód kosztów”, czyli zmieniła pierwsze słowo, a drugiego, zaczynającego się małą literą, nie ruszyła.

Dlaczego tak się dzieje

Okno Zamienianie wartości ma dwa tryby. Według dokumentacji Microsoft dla kolumn tekstowych domyślny jest tryb zamiany wystąpień tekstu: Power Query szuka podanego ciągu w każdej komórce i podmienia wszystkie jego wystąpienia. Dla kolumn innych typów, na przykład liczbowych, domyślnie zamieniana jest cała zawartość komórki, a opcje zaawansowane są dostępne tylko w kolumnach tekstowych.

W kodzie M oba tryby różnią się czwartym argumentem funkcji Table.ReplaceValue: Replacer.ReplaceText zamienia fragmenty, Replacer.ReplaceValue tylko całe wartości. Wyniki z naszego testu na kolumnie Kanał:

Table.ReplaceValue(Źródło, "Sklep", "Stacjonarny", Replacer.ReplaceText, {"Kanał"})
// Stacjonarny, Stacjonarny internetowy

Table.ReplaceValue(Źródło, "Sklep", "Stacjonarny", Replacer.ReplaceValue, {"Kanał"})
// Stacjonarny, Sklep internetowy

Jak to rozwiązać krok po kroku

1. Jeśli krok zamiany już istnieje, kliknij ikonę koła zębatego przy nazwie kroku Zamieniono wartość na liście Zastosowane kroki, żeby otworzyć jego okno ponownie. Możesz też usunąć krok krzyżykiem i dodać go od nowa.
2. Zaznacz kolumnę i na karcie Strona główna kliknij Zamienianie wartości (to samo polecenie jest na karcie Przekształć i w menu pod prawym przyciskiem myszy jako Zamień wartości). W polu Wartość do znalezienia wpisz całą wartość komórki, na przykład „Sklep”, a w polu Zamień na nową wartość.
3. Rozwiń Opcje zaawansowane, zaznacz Dopasuj do całej zawartości komórki i kliknij OK. W tej samej sekcji jest opcja Zamień przy użyciu znaków specjalnych, przydatna, gdy zamieniasz tabulator albo znak nowego wiersza.
4. Sprawdź pasek formuły. Krok powinien zawierać Replacer.ReplaceValue, na przykład = Table.ReplaceValue(#"Zmieniono typ", "Sklep", "Stacjonarny", Replacer.ReplaceValue, {"Kanał"}). Jeśli widzisz Replacer.ReplaceText, zmień tę nazwę wprost w formule i zatwierdź klawiszem Enter.
5. Pamiętaj, że dopasowanie całej komórki jest dosłowne. W naszym teście wartość „Sklep ” ze spacją na końcu nie została zamieniona, a szukane „sklep” pisane małą literą nie pasowało do „Sklep”. Przed zamianą przytnij kolumnę i ujednolić wielkość liter poleceniami z menu Format na karcie Przekształć.
6. W kolumnach liczbowych nic nie musisz zaznaczać, bo tam zamiana zawsze obejmuje całą wartość. W naszym teście zamiana 10 na 0 w kolumnie Rabat % zmieniła komórkę z wartością 10, a komórka ze 110 została bez zmian.

Jak sprawdzić, że zadziałało

Po zamianie otwórz filtr kolumny i przejrzyj listę wartości: dłuższe nazwy, które zawierały szukane słowo, mają zostać bez zmian, a zniknąć ma tylko stara wartość. Lista w filtrze wczytuje według dokumentacji Microsoft najwyżej 1000 różnych wartości naraz, więc przy dużych słownikach sprawdź wynik filtrem Zawiera z menu Filtry tekstu, wpisując nową wartość.

Żeby problem nie wrócił, przy każdej zamianie w kolumnie tekstowej zaglądaj od razu do paska formuły: Replacer.ReplaceValue oznacza całą komórkę, Replacer.ReplaceText fragmenty. Jeśli zamiana ma działać także na przyszłych plikach z inną wielkością liter, umieść kroki przycięcia i formatowania tekstu przed krokiem zamiany.

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