Różne zapisy tej samej nazwy w danych - ujednolicanie wielkości liter w Power Query

Power Query w Excelu, Power BI i Fabric

W skrócie

Ten sam sklep występuje w danych jako „Nordvella Wrocław”, „NORDVELLA WROCŁAW” i „Nordvella Wrocław ” ze spacją, więc raport pokazuje go kilka razy. W Power Query wielkie litery ustawisz jednym poleceniem, po którym każdy wyraz zaczyna się wielką literą.
Power Query porównuje tekst dokładnie, z wielkością liter i spacjami. Sama zmiana wielkości liter nie wystarczy: w naszym teście trzy warianty po Text.Proper dały dwie wartości, a dopiero z przycięciem jedną.
Zastosuj Przycięcie i Zamień pierwszą literę każdego wyrazu na wielką z menu Format, skróty w rodzaju SA popraw osobnym krokiem i sprawdź liczbę wartości odrębnych w profilu kolumny.

Na ten problem trafiasz, gdy dane wpisywało kilka osób albo pochodzą z kilku systemów: ta sama nazwa sklepu, klienta czy produktu występuje w kilku zapisach, a tabela przestawna liczy je osobno. W Power Query wielkie litery ustawisz jednym poleceniem, po którym każdy wyraz zaczyna się wielką literą, ale żeby warianty naprawdę się połączyły, liczy się kolejność kroków. Pokazujemy ją na danych fikcyjnej sieci Nordvella z naszego kursu, razem z pułapką skrótów i sposobem sprawdzenia wyniku.

Jak to wygląda w praktyce

W kolumnie Sklep ten sam sklep występuje jako „Nordvella Wrocław”, „NORDVELLA WROCŁAW” i „Nordvella Wrocław ” ze spacją na końcu. Lista wartości w filtrze kolumny ma kilka pozycji dla jednej nazwy, a tabela przestawna po załadowaniu danych pokazuje osobny wiersz dla każdego zapisu i dzieli między nie sprzedaż.

Skala bywa duża. W kursie, w przepływie danych w Microsoft Fabric uruchomionym celowo bez kroków czyszczenia, zapytanie SQL na tabeli wynikowej pokazało 30 różnych zapisów nazwy sklepu przy 15 sklepach. Ten sam problem dotyczy kodów: „bex-1021 ” zapisany małymi literami ze spacją nie znajdzie pary w katalogu, w którym stoi „BEX-1021”.

Dlaczego tak się dzieje

Power Query porównuje tekst znak po znaku, więc inna wielkość liter albo spacja na końcu tworzy nową wartość. Polecenie Zamień pierwszą literę każdego wyrazu na wielką odpowiada funkcji Text.Proper, która według swojego opisu zamienia na wielką pierwszą literę każdego wyrazu, a wszystkie pozostałe litery na małe. Sprawdziliśmy w polskim Excelu, że poprawnie obsługuje polskie znaki i łącznik:

Text.Proper("NORDVELLA wROCŁAW")      // Nordvella Wrocław
Text.Proper("BIELSKO-BIAŁA")          // Bielsko-Biała
Text.Proper("NORDVELLA SP. Z O.O.")   // Nordvella Sp. Z O.O.
Text.Proper("ABC SA")                 // Abc Sa

Dwa ostatnie wyniki pokazują pierwszą pułapkę: skróty i formy prawne też dostają małe litery. Druga pułapka to spacje. Zmiana wielkości liter ich nie usuwa, więc „Nordvella Wrocław ” zostaje osobną wartością. W naszym teście trzy warianty nazwy po samym Text.Proper dały dwie różne wartości, a po Text.Proper(Text.Trim(_)) jedną.

Jak to rozwiązać krok po kroku

Zaznacz kolumnę z nazwami, na przykład Sklep. Na karcie Przekształć rozwiń Format i wybierz Przycięcie. Krok Przycięty tekst usunie odstępy z początku i końca każdej komórki.
Przy tej samej kolumnie jeszcze raz rozwiń Format i wybierz Zamień pierwszą literę każdego wyrazu na wielką. Pojawi się krok Zmieniono pierwszą literę każdego wyrazu na wielką, a „NORDVELLA WROCŁAW” zmieni się w „Nordvella Wrocław”.
Kody i symbole ujednolicaj inaczej. Dla kolumny Kod produktu wybierz w tym samym menu Przycięcie i Wielkie litery. W naszym teście „bex-1021 ” zmienił się w „BEX-1021”, czyli zapis z katalogu produktów, z którym kolumna jest potem scalana.
Jeśli chcesz zachować oryginalną kolumnę, użyj menu Format na karcie Dodaj kolumnę. Te same polecenia utworzą wtedy nową kolumnę z poprawionym tekstem, a kolumna źródłowa zostanie bez zmian.
Skróty popraw osobnym krokiem. Dla stałej formy prawnej wystarczy zamiana tekstu, na przykład Text.Replace(Text.Proper([Kontrahent]), "Sp. Z O.O.", "Sp. z o.o."), która w naszym teście dała „Nordvella Sp. z o.o.”. Dla pojedynczych słów, takich jak SA, użyj kolumny niestandardowej z formułą Text.Combine(List.Transform(Text.Split(Text.Proper([Kontrahent]), " "), each if List.Contains({"Sa"}, _) then Text.Upper(_) else _), " "). Z tekstu „HURTOWNIA PÓŁNOC SA” powstało „Hurtownia Północ SA”.
Pilnuj kolejności. Przycięcie i zmiana wielkości liter muszą stać przed scalaniem, grupowaniem i usuwaniem duplikatów. Jeśli te kroki już istnieją, kliknij na liście Zastosowane kroki krok poprzedzający, dodaj formatowanie i potwierdź wstawienie w oknie Wstawianie kroku.

Menu Format w edytorze Power Query z poleceniami Małe litery, Wielkie litery, Zamień pierwszą literę każdego wyrazu na wielką, Przycięcie, Wyczyść oraz Dodaj prefiks i sufiks — Menu Format. Przycięcie usuwa spacje z początku i końca tekstu, Wyczyść usuwa znaki niedrukowalne, a trzy pierwsze pozycje zmieniają wielkość liter.

Jak sprawdzić, że zadziałało

Na karcie Widok włącz Rozkład kolumn i Profil kolumny, a na pasku stanu przełącz profilowanie z pierwszych 1000 wierszy na cały zestaw danych. Liczba wartości odrębnych w kolumnie powinna odpowiadać liczbie rzeczywistych obiektów. W kursie po przycięciu i ujednoliceniu wielkości liter kolumna Sklep ma dokładnie 15 wartości odrębnych, po jednej na sklep. Przejrzyj też listę wartości w filtrze kolumny: każda nazwa powinna wystąpić raz.

Żeby nowe warianty nie wracały, trzymaj oba kroki formatowania zaraz po ustawieniu typów. Przy każdym odświeżeniu obejmą także nowe pliki. W przepływie danych Gen2 w Microsoft Fabric dodasz je tymi samymi poleceniami z menu Format na karcie Transformacja.

Poczytaj więcej
Lekcja 2 bezpłatnego kursu Power Query: Czyszczenie danych: wiersze techniczne, nagłówki, typy i duplikaty
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

Jak w Power Query zmienić pierwszą literę każdego wyrazu na wielką?

Zaznacz kolumnę, na karcie Przekształć rozwiń Format i wybierz Zamień pierwszą literę każdego wyrazu na wielką. W kodzie M odpowiada temu funkcja Text.Proper. Pozostałe litery w każdym wyrazie zostaną zamienione na małe.

Dlaczego po zmianie wielkości liter nadal mam dwa zapisy tej samej nazwy?

Najczęściej przez spację na początku albo na końcu tekstu, której zmiana wielkości liter nie usuwa. W naszym teście trzy warianty nazwy po samym Text.Proper dały dwie wartości, a po dodaniu przycięcia jedną. Zastosuj Format, Przycięcie przed zmianą wielkości liter.

Jak zachować wielkie litery w skrótach takich jak SA?

Text.Proper zamienia wszystkie litery poza pierwszą w każdym wyrazie na małe, więc „ABC SA” staje się „Abc Sa”. Popraw takie słowa osobnym krokiem: stałe formy, takie jak Sp. z o.o., funkcją Text.Replace, a pojedyncze skróty formułą, która dzieli tekst na wyrazy i wybranym słowom przywraca wielkie litery.

Czy Text.Proper poprawnie obsługuje polskie znaki?

Tak. W naszym teście w polskim Excelu Text.Proper zamienił „NORDVELLA wROCŁAW” na „Nordvella Wrocław”, a Text.Upper zamienił „zażółć” na „ZAŻÓŁĆ”. Poprawnie obsłużył też łącznik, bo „BIELSKO-BIAŁA” dało „Bielsko-Biała”.