Jak poprawnie stosować ranking z uwzględnieniem remisów w Power Query?

W pracy z danymi w Power Query niejednokrotnie będziemy musieli poradzić sobie z tworzeniem rankingów. W szczególności w przypadkach, gdy mamy do czynienia z remisami, czyli z sytuacją, w której kilka wartości jest identycznych. Sytuacja ta może być wyzwaniem, ponieważ standardowe podejście do tworzenia rankingu nie uwzględnia takich przypadków w sposób oczekiwany. W tej części omówimy, jak można to zrobić, krok po kroku.

Po zaimportowaniu danych do Power Query i ich wstępnym przetworzeniu (np. grupowaniu po sprzedawcach oraz obliczeniu sumy dochodów), należy przystąpić do sortowania wyników. W tym celu wybieramy kolumnę z sumą dochodów i klikamy przycisk „ZA” w zakładce Home, aby posortować dane w kolejności malejącej. Dzięki temu najlepsi sprzedawcy pojawią się na początku tabeli. Następnie można dodać kolumnę indeksu, która będzie pełniła rolę początkowego rankingu.

Aby dodać kolumnę indeksu, przechodzimy do zakładki „Add Column” i wybieramy „Index Column”. Dla tego zadania wystarczy opcja „From 1”, ale jeśli zależy nam na większej elastyczności, możemy użyć opcji „Custom”, co pozwoli na dokładniejsze określenie, od jakiej liczby mają zaczynać się numery rankingu oraz o jaką wartość mają wzrastać z wiersza na wiersz. Przykładowo, możemy ustawić wartości początkowe na 1, a inkrementację na 1,2, aby uzyskać rankingi z uwzględnieniem przecinków.

Warto jednak pamiętać, że Power Query domyślnie nie obsługuje tworzenia indeksów z wartościami zmiennoprzecinkowymi. Aby obejść ten problem, konieczne jest zmodyfikowanie kodu M. Na przykład, możemy zmienić nazwę tworzonej kolumny na bardziej odpowiednią, np. „Ranking”, i dostosować kod do obsługi liczb zmiennoprzecinkowych. W przypadku chęci utworzenia malejącego rankingu lub rozpoczęcia numeracji od wartości ujemnej, wystarczy odpowiednio zmienić wartości w argumentach funkcji Table.AddIndexColumn.

Aby rozwiązać ten problem, możemy zastosować trzy różne metody tworzenia rankingu:

1. Średni ranking – W przypadku remisu przypisujemy średnią z wszystkich miejsc, które miałyby być przypisane do tych samych wyników. W tym przykładzie trzej sprzedawcy mają rankingi 1, 2, i 3, a ich średnia wynosi 2. Kolejna para sprzedawców, którzy mają identyczne wyniki, otrzymuje rankingi 4 i 5, a średnia z tych pozycji wynosi 4,5. Ostatni sprzedawca, który nie ma remisu, otrzymuje rank 6.

2. Najniższy ranking – W tym przypadku przypisujemy najniższą z możliwych pozycji w obrębie remisu. Tak więc trzej sprzedawcy z wynikiem 244,000 otrzymują pozycję 1, dwóch kolejnych sprzedawców, którzy mają wynik 212,800, otrzymują pozycję 4, a ostatni sprzedawca dostaje pozycję 6.

3. Jednakowy ranking – Tutaj każdy z uczestników remisu otrzymuje tę samą pozycję, a kolejne miejsca są przesuwane o 1. Tak więc trzej sprzedawcy z wynikiem 244,000 otrzymują pozycję 1, dwóch sprzedawców z wynikiem 212,800 – pozycję 2, a ostatni sprzedawca – pozycję 3.

Jeśli zastosujemy metodę „średniego rankingu”, musimy dodać kolejną kolumnę z indeksem, ale tym razem zaczynając od 1, a następnie przypisać wartości średnie do grup. W przypadku metody „najniższego rankingu”, wystarczy, że będziemy uwzględniać najmniejszy numer w obrębie remisu.

Ważne jest, aby pamiętać, że odpowiednie grupowanie i agregowanie danych w Power Query może prowadzić do łatwego zarządzania większymi zestawami danych oraz umożliwić efektywne tworzenie rankingów w oparciu o różnorodne metody, które uwzględniają specyfikę analizowanych wyników.

Jak poprawnie posortować dane w Power Query, uwzględniając listy niestandardowe?

W tym przykładzie praca odbywa się na tabeli tSort, znajdującej się w pliku CustomSort.xlsx. Tabela ta jest przedstawiona na Figurze 10.4:

Zadaniem jest poprawne posortowanie danych, najpierw po nazwie miesiąca (które stanowi ważniejszy kryterium w tym przypadku), a następnie po roku. Prawidłowa kolejność wierszy została wskazana w kolumnie #No. W Excelu jest to stosunkowo łatwe, ponieważ dostępne są listy niestandardowe, a ich sortowanie jest obsługiwane. W Power Query sortowanie według list niestandardowych jest bardziej skomplikowane. Dodatkowym wyzwaniem w tym przykładzie jest fakt, że niektóre nazwy miesięcy zapisane są małymi literami, a inne zaczynają się od wielkiej litery.

Zanim zaprezentowane zostanie możliwe rozwiązanie problemu, warto zauważyć, że podejście to zostało opracowane na podstawie informacji zawartych w źródle: https://stackoverflow.com/questions/26364388/power-query-sort-by-custom-list. Kluczowym punktem jest to, że w wielu przypadkach warto sprawdzić, czy ktoś nie rozwiązał już napotkanego problemu, zamiast próbować opracować rozwiązanie od podstaw.

W Power Query znaczenie kolumn przy sortowaniu zależy ściśle od kolejności, w jakiej są one sortowane. Dane posortowane domyślnie od A do Z przedstawiają się jak na Figurze 10.5:

Pierwszą rzeczą, którą należy zauważyć, jest kolejność miesięcy. Przy sortowaniu Power Query umieszcza litery wielkie przed literami małymi. W związku z tym "april" zapisany małymi literami pojawia się po "May" (wielka litera), a nie bezpośrednio po "April" (wielka litera). Taka kolejność nie spełnia wymaganych kryteriów i wymaga poprawek poprzez odpowiednie transformacje lub zastosowanie odpowiedniej funkcji.

Kolejnym punktem do analizy jest funkcja Table.Sort, która widoczna jest w pasku formuły:

Warto zaznaczyć, że czasami opisy funkcji dostępne bezpośrednio w edytorze różnią się od tych opublikowanych na oficjalnej stronie dokumentacji Microsoftu. Niemniej jednak, po zapoznaniu się z wcześniej wspomnianą stroną, z której pochodzi rozwiązanie, ujawnia się dodatkowa metoda sortowania kolumn, która wykorzystuje niestandardową logikę sortowania. Używa się tu funkcji, która określa kolejność sortowania (przypisując każdemu wierszowi alternatywną lub przekształconą wartość).

Aby obliczyć tę niestandardową logikę, wykorzystuje się funkcję List.PositionOf. W tym przypadku można wykonać następujące kroki: 1. Kliknij ikonę fx obok paska formuły. Dodaje to krok, który odwołuje się do poprzedniego kroku tabeli (#"Sorted Rows"). 2. W pasku formuły zamień domyślne odniesienie na: = List.PositionOf. Umożliwi to wybranie argumentu wystąpienia z rozwijanego menu, jak pokazano na Figurze 10.6:

List.PositionOf(list jako lista, value jako dowolny typ, opcjonalnie occurrence jako liczba lub nullable, opcjonalnie equationCriteria jako dowolny typ) jako dowolny typ. Funkcja ta zwraca pozycję (numer) argumentu value w liście podanej w argumencie list. Indeksowanie zaczyna się od zera. Jeśli argument value nie występuje w liście, funkcja zwraca -1. Argument occurrence może być użyty do zmodyfikowania sposobu wyszukiwania wartości.

Do obliczenia kolejności sortowania za pomocą funkcji List.PositionOf, należy dodać ją w formie uproszczonej (z tylko dwoma wymaganymi argumentami) w nowej kolumnie: 1. Usuń poprzednio dodany krok Custom1, który wyświetlał opis funkcji List.PositionOf. 2. Kliknij polecenie Dodaj kolumnę na karcie Dodaj kolumnę. 3. W oknie Dodaj kolumnę: a. Nazwa nowej kolumny pozostaje niezmieniona (ten krok jest tymczasowy i kolumna zostanie usunięta podczas późniejszych transformacji). b. Wprowadź dokładną formułę: List.PositionOf({"January", "february", "March", "April", "May", "June"}, [Month]). Zauważ, że drugi element na liście ("february") jest zapisany małymi literami. Rezultaty, jakie zwraca funkcja, pokazane są na Figurze 10.7:

Aby poprawić formułę, tak aby wszystkie nazwy miesięcy były poprawnie dopasowane, można zastosować przynajmniej dwa rozwiązania: 1. Użyć funkcji Text.Proper, która jest stosowana podczas korzystania z opcji "Kapitalizuj każde słowo" dostępnej w sekcji Format na karcie Transformuj. 2. Wprowadzić czwarty argument funkcji List.PositionOf jako Comparer.OrdinalIgnoreCase. Przy tym argumencie trzeci musi zostać również wypełniony, stosując wartość "Occurrence.First", która może być zapisana jako liczba 1.

Jak importować i przekształcać dane w Power Query w Excelu: podstawy i praktyczne wskazówki

W procesie importowania danych do Power Query w Excelu kluczowym etapem jest odpowiednie załadowanie danych z różnych źródeł. Aby rozpocząć, po wybraniu pojedynczej kolumny w źródłowym pliku, należy skorzystać z polecenia "Close & Load", rozszerzając je o opcję "Close & Load To" (Rysunek 1.13). To pozwala precyzyjnie określić miejsce, w którym dane mają zostać załadowane. Zwykle, klikając tylko "Close & Load", Power Query automatycznie ładuje dane do nowego arkusza, tworząc tabelę w domyślnych ustawieniach. Choć jest to wygodne, nie daje pełnej kontroli nad tym, jak dane będą załadowane, na przykład nie pozwala na załadowanie tabeli do konkretnego miejsca w arkuszu, utworzenie tabeli przestawnej czy załadowanie danych tylko do modelu danych.

Aby w pełni wykorzystać możliwości Power Query, warto skorzystać z okna "Import Data" (Rysunek 1.14), które umożliwia dokładne określenie miejsca docelowego dla zapytania. Zwykle wybieramy opcję "Table", a następnie wskazujemy miejsce w istniejącym arkuszu. Domyślnie Excel wstawia dane do komórki A1, ale jeśli miejsce to jest niewłaściwe, można je łatwo zmienić, klikając w pole z odniesieniem komórki i wybierając nowe miejsce.

Kiedy dane zostaną załadowane, Power Query stworzy nową tabelę, której nazwa będzie zgodna z nazwą zapytania. Zanim jednak dane zostaną załadowane, warto zwrócić uwagę, że w przypadku nazw tabel w Excelu nie można używać specjalnych znaków, jak spacje czy ampersandy. Excel automatycznie zastępuje je znakami podkreślenia. Na przykład, jeśli zapytanie nazywa się "Countries & Capitals", to załadowana tabela będzie nosić nazwę "Countries___Capitals".

Podobnie jak w przypadku wczytywania danych z różnych plików, podczas importowania danych z pliku tekstowego należy pamiętać o właściwym ustawieniu kodowania. Na przykład, jeżeli w pliku tekstowym pojawią się błędy w nazwach krajów, konieczne będzie ręczne wybranie odpowiedniego kodowania, np. UTF-8 (Rysunek 1.20). Power Query zazwyczaj dobrze radzi sobie z wykrywaniem kodowania, jednak w przypadkach z nietypowymi znakami może być konieczne ręczne dostosowanie ustawień.

Po załadowaniu danych, na ogół pojawi się tylko jedna kolumna, z domyślną nazwą. Aby przekształcić dane, najczęściej trzeba podzielić tę kolumnę na dwie, np. na nazwę kraju i stolicę. Aby to zrobić, wystarczy zaznaczyć kolumnę, a następnie wybrać opcję "Split Column" i "By Delimiter" (Rysunek 1.22). Power Query automatycznie wykryje separator w danych, w tym przypadku myślnik, ale należy również uwzględnić spację przed i po tym znaku, jeśli jest to konieczne. Dalsze operacje umożliwiają wybranie, czy chcemy podzielić kolumnę po pierwszym, ostatnim czy każdym wystąpieniu separatora.

Warto również zwrócić uwagę, że po podzieleniu kolumny, Power Query może automatycznie dodać dwa kroki do zapytania: "Split Column by Delimiter" oraz "Changed Type". Jednak w niektórych przypadkach, jak w tym przykładzie, drugi krok może być zbędny i można go usunąć, aby uprościć proces.

Importowanie danych z pliku tekstowego lub z innych źródeł to tylko początek pracy w Power Query. Dzięki odpowiednim transformacjom, takim jak dzielenie kolumn, usuwanie niepotrzebnych kroków czy zmiana formatów danych, możliwe jest dopasowanie danych do własnych potrzeb. Warto pamiętać, że każda operacja w Power Query zostaje zapisana w postaci kroków w zapytaniu, co daje pełną kontrolę nad procesem i umożliwia łatwe modyfikowanie przekształconych danych.

Jak optymalizować zapytania Power Query i korzystać z rozszerzeń?

W poprzednim rozdziale omawialiśmy praktyczne przykłady użycia języka M do rozwiązywania złożonych wyzwań związanych z transformacją danych. W tym rozdziale skupimy się na poprawie wydajności i efektywności rozwiązań Power Query. Omówimy kluczowe techniki optymalizacji, takie jak minimalizacja liczby zastosowanych kroków, dobór odpowiednich typów danych i unikanie kosztownych operacji, takich jak łączenie tabel czy grupowanie, gdy jest to możliwe. Zajmiemy się również narzędziami do mierzenia czasu wykonania zapytań, w tym makrami VBA i diagnostyką Power BI. Na koniec wprowadzimy rozszerzenia Power Query w Visual Studio Code, co zapewni bardziej elastyczne środowisko do pisania i formatowania kodu M.

Jednym z głównych aspektów optymalizacji jest minimalizowanie liczby kroków transformacji. Każdy dodatkowy krok w zapytaniu Power Query wiąże się z dodatkowymi obliczeniami, co może wpłynąć na czas wykonania całego zapytania. Im mniej kroków, tym szybciej zapytanie zostanie przetworzone. Również wybór odpowiednich typów danych ma kluczowe znaczenie – nieodpowiedni typ danych (na przykład tekst zamiast liczby) może spowolnić proces przetwarzania.

Kolejną ważną kwestią jest unikanie kosztownych operacji, takich jak łączenie tabel (merge) czy grupowanie danych. Często te operacje mogą być zastąpione bardziej efektywnymi metodami, które zmniejszają liczbę obliczeń wymaganych do osiągnięcia tego samego wyniku. Na przykład, zamiast stosować operację grupowania na dużych tabelach, lepiej jest wykonać agregację danych na wcześniejszym etapie lub zastosować inne metody filtrowania.

Istotnym elementem w procesie optymalizacji jest również monitorowanie czasu wykonania zapytań. Power Query oferuje wbudowane narzędzia do diagnozowania wydajności zapytań, takie jak funkcje śledzenia czasu wykonania. Można także wykorzystać makra VBA w Power BI lub Visual Studio Code do mierzenia wydajności i testowania różnych rozwiązań. Znajomość tych narzędzi pozwala na bieżąco monitorować i usprawniać proces transformacji danych.

Ponadto, rozszerzenia Power Query w Visual Studio Code oferują bardziej zaawansowane funkcje do tworzenia i formatowania kodu M. Dzięki tym narzędziom programiści mogą pisać kod w bardziej elastycznym środowisku, co zwiększa produktywność i umożliwia łatwiejsze zarządzanie większymi projektami. Visual Studio Code wspiera takie funkcje jak autouzupełnianie, formatowanie kodu, a także integrację z systemami kontroli wersji, co znacząco ułatwia pracę nad rozbudowanymi rozwiązaniami.

Wszystkie te aspekty wpływają na końcowy wynik pracy z Power Query. Optymalizacja zapytań i wykorzystanie odpowiednich narzędzi jest niezbędne, aby osiągnąć najlepszą wydajność przy pracy z dużymi zbiorami danych. Ważne jest, by nie tylko poznać techniki optymalizacji, ale także nauczyć się ich stosować w praktyce, dostosowując je do specyfiki każdego projektu.