Jak Power Query zaokrągla czas i radzi sobie z ograniczeniami formatów daty w Excelu?

Formatowanie liczb i dat w Excelu posiada pewne ograniczenia, które często pozostają niewidoczne na pierwszy rzut oka. Na przykład pojedyncza litera „d” w niestandardowym formacie daty odnosi się do numeru dnia w miesiącu i poprawnie wyświetla wartości do 31 dni. Jednakże, jeśli liczba dni przekroczy ten limit, Excel zacznie automatycznie przesuwać datę do następnego miesiąca, co może prowadzić do nieprawidłowego wyświetlania danych. W przeciwieństwie do Excela, Power Query nie nakłada takich restrykcji na formatowanie dni, co pozwala na większą elastyczność w operowaniu na okresach czasu. W praktyce jednak, ponieważ dane najczęściej prezentuje się w Excelu, warto rozważyć podział długości trwania (duration) na osobne kolumny dni i czasu, aby uniknąć błędów wyświetlania.

Dane w Power Query, takie jak data, czas czy długość trwania, są oparte na wartościach numerycznych, ale konwersje nie zachodzą automatycznie. To może powodować błędy podczas stosowania niektórych funkcji matematycznych. Dlatego niezbędne jest użycie funkcji Number.From, która konwertuje wartości czasu na liczby. Przykładowo, aby zaokrąglić czas pracy do 15-minutowych kwadransów, należy przeliczyć czas na liczbę reprezentującą ilość tych kwadransów. Ponieważ doby mają 24 godziny, a każda godzina zawiera 4 kwadranse, mnożymy konwertowaną liczbę przez 96 (24 * 4).

Po przeliczeniu wartości na liczbę kwadransów można użyć funkcji Number.RoundDown, aby zaokrąglić wyniki w dół, eliminując części dziesiętne. Następnie, aby powrócić do formatu czasu, dzielimy wynik przez 96 i konwertujemy go za pomocą funkcji Time.From. Taki sposób pozwala uzyskać czas zaokrąglony do najbliższych 15 minut bez bezpośredniego zaokrąglania wartości czasu, których Power Query nie obsługuje natywnie.

Ważne jest również, aby po wykonaniu tych operacji ustawić właściwy typ danych kolumny na „czas”, ponieważ bez tego Power Query załaduje wyniki jako wartości dziesiętne, które w Excelu będą widoczne jako liczby, a nie czas. Dodanie do kodu M odpowiedniego typu (type time) tuż przed końcowym nawiasem zamykającym pozwala uniknąć tego problemu.

Należy pamiętać, że choć Power Query znacznie ułatwia przekształcenia danych, to nie zawsze działa automatycznie z typami czasu i daty. W praktyce oznacza to, że czasem konieczne jest ręczne wprowadzenie odpowiednich funkcji konwersji i kontroli typów, by uniknąć błędów i uzyskać spójne, czytelne dane.

Jak skutecznie stosować wielokrotne progi w Power Query do oceny wyników?

W sytuacji, gdy wartość musi być sprawdzona względem wielu progów, konieczna staje się systematyczna ocena całej listy warunków. W Power Query kolejność testów może zaczynać się zarówno od najwyższych, jak i od najniższych progów – wybór kierunku zależy wyłącznie od preferencji użytkownika oraz sposobu formułowania kryteriów. Mimo że kolejność operacji w Power Query jest technicznie nieistotna, logika warunków musi pozostać spójna i ciągła. Jest to spowodowane tym, że w oknie „Dodaj kolumnę warunkową” kolejne testy wykonywane są tylko wtedy, gdy poprzedni warunek zwraca fałsz, czyli nie jest spełniony.

Taka konstrukcja warunków przypomina funkcję przybliżonego wyszukiwania w Excelu, gdzie wartości porównuje się względem progów ustalonych w tabeli. Po przeprowadzeniu analizy danych i progów, a następnie zaimportowaniu tabeli do Power Query, można uruchomić polecenie „Dodaj kolumnę warunkową” i wprowadzić odpowiednie warunki w oknie dialogowym. Aby dodać nowy warunek, należy użyć przycisku „Dodaj klauzulę”. Zmiana kolejności warunków lub ich usuwanie odbywa się przez menu kontekstowe dostępne przy ikonach opcji przy każdym warunku.

Warto podkreślić, że operatorzy dostępni w oknie dodawania warunków zależą od typu danych w kolumnie, którą analizujemy. Po zatwierdzeniu warunków Power Query generuje kod M, który zagnieżdża funkcje if, zapewniając przejrzystość i czytelność formuły. W praktyce formułę można dodatkowo formatować w oknie „Kolumna niestandardowa”, gdzie dodanie wcięć i łamań linii ułatwia analizę i modyfikacje.

Jeśli w warunkach nie został określony typ danych zwracanych wartości, warto go dodać ręcznie do kodu, dopisując odpowiedni parametr, np. „type text”. To nie zmienia funkcjonalności kolumny warunkowej, lecz poprawia stabilność i jednoznaczność wyników.

W kolejnym etapie można rozważyć bardziej zaawansowane podejście, gdy progi oceny nie są wpisywane ręcznie, lecz pobierane dynamicznie z osobnej tabeli. W takim przypadku wykorzystanie polecenia „Dodaj zapytanie” (Append Queries) pozwala połączyć dwie tabele: z wynikami i z progami ocen. Kluczowym elementem jest zachowanie identycznych nazw kolumn – Power Query rozróżnia je z uwzględnieniem wielkości liter, co jest istotne, by połączenie danych przebiegło prawidłowo.

Po połączeniu tabel konieczne jest posortowanie wyników według kolumny z punktami rosnąco. W ten sposób wartości null (puste) zostaną umieszczone na początku, a duplikaty będą odpowiednio uporządkowane. W przypadku powtarzających się wartości w kolumnie z punktami wiersze z tabeli progów pojawią się nad wierszami z wynikami, co umożliwia prawidłowe uzupełnienie ocen za pomocą funkcji „Wypełnij w dół” (Fill Down). Dzięki temu każdemu wynikowi zostanie przypisana odpowiednia ocena zgodnie z progami, a zmiana wartości progów w tabeli wymaga jedynie ponownego odświeżenia zapytania, bez konieczności ręcznej edycji warunków.

Ważne jest zrozumienie, że w Power Query warunki warunkowe działają sekwencyjnie i są ze sobą powiązane, co wymaga przemyślanego podejścia do ich formułowania, szczególnie przy wielu progach oceny. Klauzula else stanowi naturalny sposób na przypisanie wartości domyślnej, eliminując potrzebę definiowania warunków dla wszystkich możliwych przypadków. Wykorzystanie funkcji Append pozwala natomiast na elastyczne zarządzanie progami i ich łatwą aktualizację bez konieczności przebudowywania logiki.

Dodatkowo, podczas pracy z warunkami warto zwrócić uwagę na typy danych kolumn – nieprawidłowo ustawiony typ może powodować błędy lub nieoczekiwane zachowania. Równie istotna jest konsekwentna nazwa kolumn w tabelach, które mają zostać połączone, aby uniknąć problemów z integracją danych. W przypadku bardziej złożonych warunków i licznych progów warto rozważyć przejście do ręcznej edycji kodu M, co daje większą kontrolę nad logiką oraz pozwala na formatowanie i komentarze, które ułatwiają dalszą pracę.

Jak tworzyć i stosować funkcje w Power Query do przekształcania tekstu?

Proces przekształcania tekstu w Power Query można rozłożyć na kilka kluczowych etapów, które pozwalają na efektywne dzielenie, filtrowanie i łączenie zawartości tekstowej. Na przykładzie prostego ciągu znaków „Hanna has a Cat,” pokazujemy, jak za pomocą funkcji M manipulować tekstem krok po kroku. Pierwszym etapem jest wprowadzenie tekstu jako źródła w zapytaniu. Następnie przy użyciu polecenia „Split Text” dzielimy tekst na listę elementów na podstawie zadanego separatora, którym w podstawowym przykładzie jest spacja.

Warto zauważyć, że podział tekstu skutkuje powstaniem listy, a nie pojedynczego tekstu, co skutkuje automatyczną zmianą zakładki na „List Tools”. W tym momencie pojawia się potrzeba filtrowania listy w celu usunięcia pustych elementów. Ponieważ Power Query nie oferuje wprost funkcji usuwającej puste elementy na zakładce List Tools, konieczne jest dodanie własnego kroku przy użyciu funkcji List.Select, która zachowuje tylko niepuste elementy listy.

Cały proces może zostać przekształcony w funkcję, co zwiększa elastyczność i umożliwia jej ponowne wykorzystanie dla różnych danych. Funkcja przyjmuje jako argument tekst do przetworzenia oraz opcjonalnie znak separatora, którego domyślną wartością jest spacja. Wewnątrz funkcji wprowadzona zostaje logika warunkowa, która przypisuje separator w zależności od tego, czy parametr został podany. W ten sposób można łatwo dostosować działanie funkcji do różnych potrzeb, np. dla tekstów rozdzielonych przecinkami, tabulatorami lub innymi znakami.

Przekształconą funkcję nadaje się unikalną nazwę, np. FullTrim, i można ją wywołać z poziomu innych zapytań, co pozwala na masowe przetwarzanie wielu wierszy tekstu z wykorzystaniem tej samej logiki. Wywołanie funkcji realizuje się poprzez polecenie „Invoke Custom Function” i podanie odpowiednich argumentów, po czym Power Query generuje nową kolumnę z przetworzonymi tekstami.

Takie podejście umożliwia nie tylko standaryzację tekstu i usuwanie zbędnych spacji, ale też sprawne zarządzanie złożonymi operacjami transformacji, co jest szczególnie ważne przy pracy z dużymi zbiorami danych w Excelu czy Power BI. Warto podkreślić, że tworzenie i stosowanie funkcji w Power Query to nie tylko mechaniczne powtarzanie kroków, ale także sztuka pisania czytelnych i uniwersalnych zapytań, które można łatwo modyfikować i rozszerzać.

Istotnym aspektem jest świadomość, że Power Query nie obsługuje domyślnych wartości parametrów w deklaracji funkcji, co wymusza implementację mechanizmu przypisywania wartości w samym kodzie. To wymaga od użytkownika precyzyjnego planowania struktury funkcji i testowania różnych scenariuszy. Ponadto, choć interfejs Power Query ułatwia wiele operacji, to pełne możliwości oferuje właśnie język M, który pozwala na tworzenie bardziej złożonych transformacji i funkcji.

Ponadto, podczas pracy z funkcjami i transformacjami tekstu należy pamiętać o odpowiednim zarządzaniu typami danych i obsłudze wyjątków, gdyż nieprzewidziane wartości wejściowe mogą prowadzić do błędów lub niepożądanych rezultatów. Dlatego warto wdrażać mechanizmy walidacji i testować funkcje na różnorodnych przykładach.

Jak efektywnie przetwarzać dane w Power Query: praktyczne wskazówki

Po transformacji w tabeli zapytania pojawią się dwie kolumny (Rysunek 1.24). Nazwy tych kolumn będą zawierały numeryczne sufiksy dodane do poprzednich nazw (Rysunek 1.21). W takim przypadku zmiana nazw kolumn staje się niezbędna. Pierwszy wiersz danych nie zawiera poprawnych nazw, dlatego należy je zmienić ręcznie, klikając dwukrotnie nagłówek kolumny, wybierając ją i naciskając F2 lub korzystając z opcji Zmień nazwę dostępnej w zakładce Transformuj. Nowe nazwy powinny brzmieć „Country” i „CAPITOL”. Po zmianie nazwy pierwszej kolumny, nowy krok (Zmienione nazwy kolumn) zostanie dodany, podczas gdy druga zmiana nazwy będzie włączona do tego samego kroku.

Warto zauważyć, że kolejność ostatnich trzech kroków nie ma większego znaczenia, dopóki nie spróbujemy ich uporządkować po ich utworzeniu. Jeśli nazwy kolumn zostaną zmienione jako pierwsze, kolejne transformacje będą stosowane do nowych nazw kolumn, które będą zapisane w kodzie M. Dlatego przeniesienie tych kroków nad krokiem zmiany nazw spowoduje błąd, ponieważ Power Query nie będzie w stanie znaleźć kolumn o określonych nazwach. W tym przypadku należy wykonać jeszcze dwie dodatkowe czynności. Pierwsza to zmiana kolejności kolumn. Aby to zrobić, wystarczy kliknąć nagłówek kolumny „CAPITOL” i przeciągnąć ją na „Country”, trzymając wciśnięty lewy przycisk myszy. Można także użyć polecenia Przenieś dostępnego w zakładce Transformuj. Po dokonaniu tej zmiany warto zmienić nazwę zapytania. Zamiast domyślnej „Countries_and_capitals” (utworzonej na podstawie nazwy pliku) należy zmienić ją na „CAPITALS and Countries”.

Po przeglądzie wyników zapytania w trzecim wierszu tabeli (Rysunek 1.25) zauważono niewielki problem z danymi: zbędną spację w nazwie stolicy. Takie błędy należy idealnie poprawiać bezpośrednio w danych źródłowych. Jednak po dokonaniu zmian i zapisaniu pliku, zapytanie należy odświeżyć, aby aktualizacje pojawiły się w Power Query, a następnie w Excelu. Można to zrobić na trzy sposoby:

• Korzystając z polecenia Odśwież wszystko w zakładce Dane (Rysunek 1.1).
• Wybierając opcję Odśwież z menu kontekstowego tabeli, klikając prawym przyciskiem myszy na dowolną komórkę w tabeli.
• Wybierając opcję Odśwież z menu kontekstowego zapytania, klikając prawym przyciskiem myszy na zapytanie w oknie Zapytania i połączenia (Rysunek 1.26).

Jeśli zapytanie wymaga dalszych transformacji, można je edytować, wybierając opcję Edytuj z menu kontekstowego zapytania (Rysunek 1.26) lub klikając polecenie Edytuj w zakładce Zapytanie (Rysunek 1.15).

W przypadku importu danych z pliku .csv, jak w przykładzie z danymi sprzedaży (Sales.csv), dane są oddzielone przecinkami. Ciekawostką jest to, że w plikach .csv przecinek (,) nie zawsze jest używany jako separator kolumn. Na przykład w Stanach Zjednoczonych, Wielkiej Brytanii i Kanadzie zamiast przecinka stosuje się średnik (;), który jest również powszechnie używany w Polsce, Francji i Niemczech. Plik „Sales.csv” zawiera dane sprzedaży, ale także dodatkowe wiersze na początku i na końcu, które nie są potrzebne do analizy (Rysunek 1.27).

• Pochodzenie pliku: Kodowanie pliku.
• Separator: Używany do zdefiniowania podziału kolumn (w tym przypadku przecinek).
• Wykrywanie typu danych: Lista rozwijana do automatycznego rozpoznawania typu danych.

Ponieważ plik zawiera dodatkowe wiersze przed rzeczywistymi danymi, Power Query nie mogło poprawnie zidentyfikować wiersza nagłówka. W związku z tym przypisano domyślne nazwy kolumn (Rysunek 1.28). Z tego samego powodu można pominąć wykrywanie typów danych, wybierając opcję „Nie wykrywaj typów danych” (Rysunek 1.28). Jeśli ta opcja nie zostanie wybrana, Power Query przypisze domyślnie typ tekstowy do każdej kolumny.

Kiedy importowane dane mają już odpowiednią strukturę, kolejnym krokiem jest usunięcie zbędnych wierszy, które mogą być trudne do zauważenia, zwłaszcza te na końcu pliku. Ponieważ edytor Power Query domyślnie wyświetla tylko pierwsze 1000 wierszy, aby zobaczyć pozostałe dane, należy przewinąć do końca tabeli. Z racji tego, że edytor Power Query nie jest zaprojektowany do wyświetlania wszystkich danych, lecz do wydajnych transformacji, kluczową zasadą podczas pracy z danymi jest: poznaj swoje dane.