Kolumna warunkowa daje zły wynik - kolejność warunków ma znaczenie

Power Query w Excelu, Power BI i Fabric

Kolumna warunkowa daje zły wynik - kolejność warunków ma znaczenie

W skrócie

Na ten problem trafiasz przy każdym podziale na przedziały: segmenty cenowe, progi rabatowe, klasy klientów. W Power Query kolumna warunkowa działa jak zagnieżdżona funkcja JEŻELI i zwraca wynik pierwszego spełnionego warunku, więc o wyniku decyduje kolejność klauzul. Pokazujemy, jak ją ustawić, jak obsłużyć puste komórki i jak przetestować granice, na przykładzie segmentu cenowego z naszego kursu.

Jak to wygląda w praktyce

Tworzysz kolumnę Segment cenowy z regułami: Cena netto jest większe niż lub równe 1000, to Średni, a w drugiej klauzuli 3000, to Premium. W polu W przeciwnym razie wpisujesz Podstawowy. Okno nie zgłasza błędu, ale w danych nie ma ani jednego produktu Premium. Sprawdziliśmy to w polskim Excelu: dla cen 3500, 1500 i 500 taki zestaw warunków zwrócił Średni, Średni i Podstawowy.

Druga odsłona: w wierszach z pustą ceną nowa kolumna zamiast segmentu pokazuje Error, a po kliknięciu komórki widać komunikat Expression.Error: Nie możemy przekonwertować wartości null na typ Logical. (ang. We cannot convert the value null to type Logical.). Gdy kolumna ceny ma typ tekstowy, błąd dotyczy wszystkich wierszy z ceną, a komunikat brzmi:

Expression.Error: Nie możemy zastosować operatora < dla typów Number i Text.

Angielski oryginał to We cannot apply operator < to types Number and Text. W naszym teście reguła z porównaniem większe lub równe dała komunikat z operatorem <, więc nie szukaj w regule dokładnie tego znaku.

Dlaczego tak się dzieje

Okno Dodawanie kolumny warunkowej zapisuje reguły jako zagnieżdżone wyrażenie if, then, else. Według dokumentacji Microsoft klauzule są sprawdzane w kolejności z okna, od góry do dołu. Cena 3500 spełnia już pierwszy warunek, większe lub równe 1000, więc dostaje Średni, a warunek dla Premium nie jest sprawdzany. Szeroki warunek na górze przykrywa węższe pod nim.

if [Cena netto] >= 1000 then "Średni"
else if [Cena netto] >= 3000 then "Premium"   // ta gałąź nigdy nie zadziała
else "Podstawowy"

Puste komórki to osobny mechanizm. Porównanie null z liczbą nie daje prawdy ani fałszu, tylko null: w naszym teście null >= 3000 zwróciło null. Wyrażenie if wymaga wartości logicznej, stąd błąd konwersji null na typ Logical. Dotyczy on tylko komórek z null, a zapytanie odświeża się dalej. W naszym teście tabela z cenami 3500, null i 800 miała dokładnie jeden wiersz z błędem.

Jak to rozwiązać krok po kroku

1. Kliknij ikonę koła zębatego przy kroku kolumny warunkowej na liście Zastosowane kroki, żeby otworzyć okno Dodawanie kolumny warunkowej ponownie.
2. Ustaw reguły od najwyższego progu: najpierw jest większe niż lub równe 3000, to Premium, potem 1000, to Średni. Kolejność zmienisz przyciskiem z trzema kropkami na końcu wiersza reguły, który ma opcje Przenieś w górę, Przenieś w dół i Usuń. Nową regułę dodasz przyciskiem Dodaj klauzulę.
3. Wartość domyślną wpisz w polu W przeciwnym razie. Trafi do niej każdy wiersz z ceną, która nie spełniła żadnej reguły, w kursie Podstawowy.
4. Dodaj obsługę pustych komórek. Kliknij OK, a potem w pasku formuły dopisz na początku wyrażenia warunek na null, tak żeby krok miał postać each if [Cena netto] = null then "Brak ceny" else if [Cena netto] >= 3000 then "Premium" else if [Cena netto] >= 1000 then "Średni" else "Podstawowy". W naszym teście ceny 3500, null, 800 i 1500 dały Premium, Brak ceny, Podstawowy i Średni.
5. Sprawdź typ kolumny źródłowej. Jeśli Cena netto ma typ tekstowy (ikona ABC w nagłówku), ustaw typ Liczba dziesiętna przed krokiem kolumny warunkowej. Bez tego porównanie tekstu z liczbą daje błąd w każdym wierszu z ceną.
6. Nadaj typ nowej kolumnie. Według dokumentacji Microsoft kolumna warunkowa nie ma zdefiniowanego typu danych, więc kliknij ikonę w jej nagłówku i wybierz Tekst.

Jak sprawdzić, że zadziałało

Przetestuj granice. Utwórz puste zapytanie, wklej formułę poniżej i porównaj wynik z oczekiwanym. W naszym teście dla cen 999,99, 1000, 2999,99 i 3000 poprawna kolejność warunków dała Podstawowy, Średni, Średni i Premium.

= List.Transform({999.99, 1000, 2999.99, 3000}, each
    if _ >= 3000 then "Premium"
    else if _ >= 1000 then "Średni"
    else "Podstawowy")

Zwróć uwagę na operator. Przy jest większe niż sama wartość progu nie spełnia warunku: w naszym teście cena 1000 trafiła wtedy do segmentu Podstawowy, a 3000 do Średni. Na koniec włącz Jakość kolumn na karcie Widok i sprawdź, czy nowa kolumna nie ma błędów, a w rozkładzie wartości są wszystkie segmenty. Brak segmentu Premium przy drogich produktach w danych to sygnał, że warunki znów stoją w złej kolejności.

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