Kolumna z przykładów wybiera złą regułę - jak to sprawdzić i poprawić

Power Query w Excelu, Power BI i Fabric

Kolumna z przykładów wybiera złą regułę - jak to sprawdzić i poprawić

W skrócie

Na ten problem trafiasz, gdy wyciągasz fragment tekstu, na przykład kod sklepu z numeru paragonu, bez pisania formuły. W Power Query kolumna z przykładów zgaduje regułę z tego, co wpiszesz, i po jednym przykładzie potrafi wybrać regułę, która działa tylko w części wierszy. Pokazujemy, jak rozpoznać złą regułę przed zatwierdzeniem, jak ją poprawić i co oznacza komunikat o braku pasującej transformacji.

Jak to wygląda w praktyce

Nowa kolumna wygląda dobrze w pierwszych wierszach, ale w innych zwraca ucięte albo błędne wartości. Przykład z kursu: z numeru paragonu w postaci WAW1-202601-00001 wyciągasz kod sklepu. Reguła „pierwsze cztery znaki” daje WAW1 i KRK2, ale gdyby pojawił się sklep z pięcioznakowym kodem, na przykład GDA10, z numeru GDA10-202601-00001 zostałoby GDA1. Sprawdziliśmy to w polskim Excelu: Text.Start z liczbą 4 zwróciło dla tych dwóch numerów WAW1 i GDA1, a Text.BeforeDelimiter z myślnikiem WAW1 i GDA10. Wynik jest błędny bez żadnego komunikatu.

Druga odsłona problemu to komunikat w trakcie wpisywania przykładów, gdy żadne przekształcenie nie pasuje do wszystkich przykładów naraz:

Nie można odnaleźć transformacji zgodnej z podanymi przykładami.
Zmień lub usuń istniejące przykłady przed określeniem nowych.

Angielski oryginał to We could not find a transform that matches the examples you provided. Please change or remove the existing examples before specifying new ones.

Dlaczego tak się dzieje

Kolumna z przykładów nie zapamiętuje wpisanych wartości, tylko szuka przekształcenia, które daje je z kolumn źródłowych. Gdy Power Query je znajdzie, według dokumentacji Microsoft wypełnia pozostałe wiersze wynikami i pokazuje nad podglądem tekst formuły M, w kursie Przekształć: Text.BeforeDelimiter([Nr paragonu], "-"). Po zatwierdzeniu formuła trafia do kroku i działa na danych z kolejnych miesięcy, także tych, których nikt nie oglądał.

Jeden przykład może pasować do kilku reguł naraz: dla WAW1-202601-00001 wynik WAW1 dają i pierwsze cztery znaki, i tekst przed pierwszym myślnikiem. Dlatego w kursie dopisujemy drugi przykład z innej grupy danych. Druga granica: według dokumentacji kolumna z przykładów działa tylko na pierwszych 100 wierszach podglądu, więc nietypowy wiersz z dalszej części danych nie bierze udziału w doborze reguły.

Jak to rozwiązać krok po kroku

1. Zaznacz kolumnę źródłową, na przykład Nr paragonu. Na karcie Dodaj kolumnę rozwiń Kolumna z przykładów i wybierz Z zaznaczenia. Opcję Ze wszystkich kolumn wybierz wtedy, gdy wynik ma łączyć wartości z kilku kolumn.
2. Wpisz pierwszy przykład, na przykład WAW1, i od razu przeczytaj formułę nad siatką. Jeśli reguła opiera się na liczbie znaków, na przykład Text.Start([Nr paragonu], 4), a fragmenty w danych mogą mieć różną długość, nie zatwierdzaj jeszcze kolumny.
3. Dopisz drugi przykład z innej grupy danych, w kursie KRK2 w wierszu paragonu z Krakowa. Wybieraj wiersze nietypowe: inną długość kodu, inną wielkość liter, brak ogranicznika. W kursie po dwóch przykładach Power Query wybrał regułę Text.BeforeDelimiter([Nr paragonu], "-"), czyli tekst przed pierwszym myślnikiem.
4. Jeśli nietypowe wiersze leżą dalej niż w pierwszych 100 wierszach, dodaj przed kolumną z przykładów tymczasowy krok, który przesunie je na górę, na przykład sortowanie albo filtr. Według dokumentacji Microsoft po utworzeniu kolumny takie wcześniejsze kroki możesz usunąć bez wpływu na nową kolumnę.
5. Gdy pojawi się komunikat Nie można odnaleźć transformacji zgodnej z podanymi przykładami, sprawdź, czy wszystkie przykłady da się uzyskać tą samą regułą i czy w żadnym nie ma literówki. Zgodnie z treścią komunikatu zmień albo usuń istniejące przykłady, zanim wpiszesz nowe.
6. Przed kliknięciem OK zmień nazwę nowej kolumny dwuklikiem w nagłówku, na przykład na Kod sklepu. Po zatwierdzeniu kliknij nowy krok i obejrzyj formułę w pasku formuły. Zbyt prostą regułę poprawisz wprost w formule, na przykład zamieniając Text.Start([Nr paragonu], 4) na Text.BeforeDelimiter([Nr paragonu], "-").

Jak sprawdzić, że zadziałało

Na karcie Widok włącz Rozkład kolumn i Profil kolumny, przełącz profilowanie na cały zestaw danych i obejrzyj wartości nowej kolumny. Liczba wartości odrębnych powinna odpowiadać liczbie obiektów, które kolumna opisuje, na przykład sklepów, a w liście wartości filtra nie powinno być uciętych kodów. Dokładniejszy test: dodaj tymczasową kolumnę niestandardową z formułą = [Kod sklepu] <> Text.BeforeDelimiter([Nr paragonu], "-") i odfiltruj wartość true. W naszym teście taki filtr wskazał jedyny wiersz, w którym reguła czterech znaków się myliła.

Żeby problem nie wrócił, czytaj formułę nad siatką przy każdej kolumnie z przykładów i pamiętaj, że zatwierdzona reguła obejmie także przyszłe pliki. Jeśli w danych może zabraknąć ogranicznika, sprawdź i ten przypadek: Text.BeforeDelimiter("WAW1", "-") zwróciło w naszym teście cały tekst WAW1.

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