Sortowanie znika po scaleniu, grupowaniu albo usunięciu duplikatów

Power Query w Excelu, Power BI i Fabric

Sortowanie znika po scaleniu, grupowaniu albo usunięciu duplikatów

W skrócie

Sortowanie znika w Power Query w sytuacji, która wygląda na usterkę: krok Posortowano wiersze jest na liście, a po scaleniu z katalogiem albo po grupowaniu wiersze stoją inaczej. Trafisz na to, budując zestawienia typu „najlepsze sklepy” albo „ostatni paragon klienta”. Pokazujemy, co o kolejności wierszy mówi dokumentacja Microsoft, które operacje jej nie zachowują i jak zbudować zapytanie, w którym kolejność jest pewna. Przykłady sprawdziliśmy w polskim Excelu na danych sklepów Nordvella.

Jak to wygląda w praktyce

Typowy przebieg: sortujesz sprzedaż malejąco po wartości paragonu, a potem grupujesz po sklepie z sumą. Spodziewasz się sklepów od największej sumy, a dostajesz inną kolejność. W naszym teście w Excelu dane posortowane malejąco po wartości dały po grupowaniu kolejność Nordvella Wrocław 510, Nordvella Gdańsk 700, Nordvella Łódź 350. Wrocław stanął pierwszy, bo miał najwyższy pojedynczy paragon, choć Gdańsk miał wyższą sumę.

Drugi przebieg łatwiej przeoczyć: sortujesz przed scaleniem albo przed Usuń duplikaty i w podglądzie wszystko się zgadza. W naszych testach lokalnych jedno scalenie zachowało kolejność wierszy, a drugie (tabela po grupowaniu scalona po dwóch kolumnach) zwróciło je w odwrotnej kolejności. Dokumentacja Microsoft opisuje właśnie taki przypadek: operacja może wyglądać na działającą, ale to zachowanie nie jest gwarantowane, więc w innych warunkach, na przykład gdy krok trafi do bazy danych, kolejność może się zmienić.

Dlaczego tak się dzieje

Sekcja dokumentacji Microsoft o zachowaniu sortowania (ang. Preserving sort) podaje, że kolejność nie jest gwarantowana po agregacjach (Table.Group), scaleniach (Table.NestedJoin) i usuwaniu duplikatów (Table.Distinct). Powodem jest sposób, w jaki Power Query optymalizuje zapytania: niektóre operacje pomija, a inne przekazuje do źródła danych, które ma własne zasady porządkowania wierszy. Opis funkcji Table.Group w polskim Excelu kończy się zdaniem „Ta funkcja nie gwarantuje porządkowania wierszy, które zwraca.” (ang. This function does not guarantee the ordering of the rows it returns.).

Do tego dochodzi logika samych operacji. Grupowanie liczy sumy, ale nie układa grup według nich. W naszym teście grupy wyszły w kolejności pierwszego wystąpienia sklepu w danych, więc sortowanie przed grupowaniem ustawiło paragony, a nie sumy sklepów. Krok Posortowano wiersze działa poprawnie, tylko stoi w złym miejscu zapytania.

Jak to rozwiązać krok po kroku

1. Przenieś sortowanie na koniec. Na liście Zastosowane kroki usuń krzyżykiem krok Posortowano wiersze stojący przed scaleniem, grupowaniem albo usuwaniem duplikatów. Kliknij ostatni krok, rozwiń menu w nagłówku kolumny wyniku, na przykład sumy sprzedaży, i wybierz Sortuj malejąco. W teście sortowanie po grupowaniu dało właściwą kolejność: Gdańsk 700, Wrocław 510, Łódź 350.
2. Kolejność wewnątrz grup ustaw w samym grupowaniu. Gdy potrzebujesz najwyższego paragonu w każdym sklepie, na karcie Strona główna kliknij Grupowanie według, grupuj po Sklep, nazwij nową kolumnę Posortowane i wybierz operację Wszystkie wiersze. W pasku formuły zamień each _ na each Table.Sort(_, {"Wartość", Order.Descending}), tak jak w przykładzie z dokumentacji Microsoft. Pierwszy wiersz każdej grupy wyciągnie kolumna niestandardowa [Posortowane]{0}[Paragon]. W teście dała WRO-1 dla Wrocławia i GDA-1 dla Gdańska.
3. Pozycję zapisz w kolumnie. Jeśli raport ma pokazywać miejsce w rankingu, posortuj wynik na końcu, a potem na karcie Dodaj kolumnę rozwiń Kolumna indeksu i wybierz Od 1, tak jak w lekcji o grupowaniu. Numer zostaje w danych, więc tabela przestawna albo kolejne kroki mogą po nim sortować. W teście dał 1. Gdańsk, 2. Wrocław, 3. Łódź.
4. Przy remisach użyj rangi. Kliknij prawym przyciskiem ostatni krok, wybierz Wstaw krok po i wpisz = Table.AddRankColumn(#"Posortowano wiersze", "Ranga", {"Suma", Order.Descending}), podstawiając nazwę swojego kroku. Domyślnie remisy dostają tę samą rangę z luką: sumy 700, 510, 510, 350 dały w teście 1, 2, 2, 4. Opcja [RankKind = RankKind.Dense] numeruje bez luki (1, 2, 2, 3), a RankKind.Ordinal nadaje każdemu wierszowi inny numer.
5. Table.Buffer traktuj jako rozwiązanie awaryjne. Dokumentacja podaje, że zbuforowanie danych przed operacją w niektórych przypadkach sprawia, że operacja zachowuje kolejność z bufora. To słabsza obietnica niż sortowanie na końcu, a bufor dodatkowo zatrzymuje składanie zapytań, czyli przekazywanie kroków do bazy, więc przy dużych tabelach może spowolnić odświeżanie.

Jak sprawdzić, że zadziałało

Sprawdź, czy sortowanie jest ostatnim krokiem, który wpływa na kolejność: po nim mogą stać zmiana typu albo nazwy kolumny, ale nie scalanie, grupowanie ani usuwanie duplikatów. Porównaj pierwsze wiersze z oczekiwaniem po odświeżeniu pełnych danych, a nie tylko w podglądzie edytora. Gdy zapytanie zasila tabelę przestawną albo raport Power BI, ustaw sortowanie także w samym raporcie, bo o kolejności na ekranie decyduje wizualizacja. Nawrotom zapobiegniesz, gdy kroki zależne od kolejności oprzesz na kolumnie z numerem albo rangą, a nie na położeniu wiersza.

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