W Power Query często zachodzi potrzeba tworzenia nowych kolumn na podstawie istniejących danych, zwłaszcza gdy chcemy wprowadzić dodatkowe przekształcenia lub skróty. Proces ten może być realizowany za pomocą funkcji „Kolumna z przykładów” (Column From Examples), co pozwala szybko wygenerować odpowiednią formułę, bazując na podanych przez użytkownika przykładach. Jednak automatyczne rozpoznawanie wzorców nie zawsze działa idealnie i może wymagać doprecyzowania poprzez dodanie kolejnych przykładów lub zawężenie zakresu analizowanych kolumn.

Przykład ilustruje, jak w przypadku próby utworzenia nowej kolumny z nazwiskiem zapisywanym wielkimi literami i pierwszą literą imienia, Power Query początkowo nie rozpoznało prawidłowej formuły. Dopiero po dodaniu dwóch dodatkowych przykładów i ograniczeniu zakresu do kolumn zawierających tylko tekst, uzyskano oczekiwany efekt. Ostateczna formuła używa funkcji Text.Upper oraz Text.Start, łącząc je za pomocą Text.Combine, co pozwala na uzyskanie wyniku w formacie „NAZWISKO, I.”. Taki sposób tworzenia kolumny jest bardziej precyzyjny i eliminuje błędy powstające na skutek nieprawidłowego doboru źródłowych danych, np. błędne użycie kolumny z adresem email.

Ważnym elementem pracy z Power Query jest też prawidłowe ustawienie typów danych. Przykład pracy z danymi dotyczącymi czasu pracy pokazuje, że automatyczna detekcja typów w przypadku kolumn z czasem potrafi błędnie rozpoznać wartości jako liczby dziesiętne, gdy w rzeczywistości reprezentują one ułamki doby. Zrozumienie, że w Excelu i Power Query czas jest przechowywany jako ułamek dnia, jest kluczowe do dalszej poprawnej pracy z tymi danymi.

Aby wyświetlić prawidłowy format czasu, należy ręcznie zmienić typ danych na „Czas” (Time) dla kolumn z godzinami wejścia i wyjścia oraz na „Data” dla kolumny z datą. Następnie, aby obliczyć faktyczny czas pracy, wykorzystujemy funkcję odejmowania czasu – wybierając kolumny w odpowiedniej kolejności i korzystając z polecenia Dodaj kolumnę > Czas > Odejmij. Wynik automatycznie przyjmie typ „Czas trwania” (Duration), który reprezentuje okres w dniach, godzinach, minutach i sekundach.

W kolejnym kroku, aby wyodrębnić konkretną liczbę godzin, a nie ułamki doby, należy skorzystać z rozszerzonych funkcji w Power Query: dodać kolumnę z polecenia Czas trwania i wybrać opcję „Całkowite godziny” (Total Hours). Jest to ważne rozróżnienie, ponieważ standardowe opcje zwracają jedynie liczbę godzin lub minut w bieżącym dniu, podczas gdy „Całkowite” sumują cały czas trwania. Dzięki temu możliwe jest uzyskanie precyzyjnej wartości np. 8,33 godziny zamiast 8 godzin i 20 minut wyrażonych oddzielnie.

Po załadowaniu danych z powrotem do Excela, użytkownik ma możliwość formatowania komórek zawierających wartości czasu trwania. Ustawienie formatu niestandardowego „d.hh:mm:ss” pozwala na wyświetlenie zarówno liczby dni, jak i pozostałego czasu w godzinach, minutach i sekundach, co jest szczególnie przydatne w przypadku dłuższych okresów pracy przekraczających jedną dobę.

Warto pamiętać, że przy pracy z Power Query kluczowa jest optymalizacja zapytań – na przykład zmiana nazw kolumn bezpośrednio w kodzie M zmniejsza liczbę kroków w zapytaniu, co poprawia jego wydajność i czytelność. Dodatkowo, jeśli automatyczne generowanie formuł na podstawie przykładów zawodzi, należy rozważyć ręczne napisanie formuły lub rozbicie procesu na kilka prostszych kroków.

Należy również mieć świadomość, że Power Query obsługuje wartości czasu ujemnego, co różni go od Excela i pozwala na bardziej elastyczne operacje, np. w sytuacji, gdy godzina wejścia jest późniejsza niż godzina wyjścia, co w niektórych procesach biznesowych może mieć miejsce.

Jak Power Query może zrewolucjonizować Twoje procesy przetwarzania danych w Excelu?

Power Query jest potężnym narzędziem, które od wersji Excel 2016 stało się integralną częścią aplikacji, dostępną na karcie Dane. Wersja dostępna w Excel 365 w 2025 roku, którą będziemy omawiać, oferuje szereg zaawansowanych funkcji umożliwiających efektywne przekształcanie danych i ich integrację z różnorodnymi źródłami. Najważniejszymi komponentami Power Query są grupy poleceń Pobierz i przekształć dane oraz Zapytania i połączenia.

Celem Power Query jest umożliwienie użytkownikowi łatwego i szybkiego przetwarzania danych bez potrzeby głębokiej znajomości programowania. Dzięki intuicyjnemu interfejsowi użytkownika, nawet osoby z minimalnym doświadczeniem mogą szybko przekształcać dane. Jednak w bardziej zaawansowanych scenariuszach, gdy natrafimy na złożone problemy, Power Query może okazać się niewystarczające. W takich przypadkach konieczne będzie użycie języka M, który umożliwia tworzenie bardziej zaawansowanych i elastycznych przekształceń danych.

Zaletą Power Query jest również to, że pozwala ono na automatyczne unikanie manualnych, czasochłonnych operacji, które są podatne na błędy. Zamiast powtarzać ręcznie te same kroki, można skonfigurować zapytanie raz, a następnie wielokrotnie je wykorzystywać, co znacząco zwiększa efektywność pracy.

Zalety Power Query

Power Query oferuje wiele korzyści, które sprawiają, że jest to niezwykle użyteczne narzędzie w pracy z danymi. Przede wszystkim:

  • Jest łatwe w użyciu i szybkie do nauczenia.

  • Umożliwia bezproblemową integrację danych z różnych źródeł, w tym z wielu plików w wybranym folderze.

  • Po skonfigurowaniu zapytania można je ponownie wykorzystać, co eliminuje potrzebę wielokrotnego wykonywania tych samych operacji.

  • Oryginalne dane pozostają nienaruszone, ponieważ tworzona jest ich kopia, która jest następnie przekształcana.

  • Power Query jest zintegrowane zarówno z Excelem, jak i Power BI, umożliwiając dalszą analizę danych, raportowanie i wizualizację.

Jednak, jak każde narzędzie, Power Query ma również swoje wady.

Wady Power Query

Mimo że Power Query ma szereg zalet, to również wiąże się z pewnymi ograniczeniami. Warto o nich pamiętać:

  • Wynikowe dane nie aktualizują się automatycznie, gdy dane źródłowe ulegną zmianie.

  • Przy pracy z bardzo dużymi plikami lub skomplikowanymi przekształceniami Power Query może działać wolniej.

  • Brak integracji z Visual Basic for Applications (VBA) sprawia, że zaawansowana automatyzacja zapytań w Excelu staje się trudniejsza.

  • Zapytania mogą być wrażliwe na zmiany w strukturze lub zawartości danych źródłowych, co może prowadzić do błędów, jeśli nazwy kolumn lub formaty danych zostaną zmienione.

Właściwy zakres danych

Podstawowym warunkiem skutecznego przekształcania danych jest zapewnienie, że dane źródłowe są prawidłowo sformatowane. W Excelu najczęściej oznacza to, że dane powinny być zapisane w formie tabeli, co ułatwia ich analizę i dalsze przekształcenia. Niestety, wiele danych, z którymi będziemy pracować, nie spełnia tych wymagań. Z tego względu warto rozumieć, jak powinna wyglądać poprawna struktura danych, aby wiedzieć, jak je odpowiednio przekształcić na początku pracy. To pozwoli na znaczne uproszczenie dalszych procesów analitycznych.

Cechy prawidłowego zakresu danych

Prawidłowy zakres danych charakteryzuje się kilkoma cechami. Po pierwsze, każda tabela powinna mieć nagłówek jasno definiujący zawartość każdej kolumny. Nagłówki te powinny zawierać krótkie opisy, unikać skrótów i być unikalne. Kolejną zasadą jest to, że w jednej kolumnie powinien znajdować się tylko jeden typ danych – nie należy łączyć różnych rodzajów danych w jednej kolumnie. Ważne jest, aby w tabeli nie było pustych wierszy ani kolumn, ponieważ mogą one sugerować obecność innego zbioru danych. Warto również unikać pustych i scalonych komórek, które mogą powodować problemy podczas dalszej analizy, ponieważ nie wiadomo, czy dana komórka powinna być pusta, czy powinna zawierać dane z powyższej komórki.

Kolejną istotną zasadą jest unikanie powielania kategorii w kolumnach. Zamiast mieć dwanaście kolumn dla miesięcy, lepiej jest stworzyć jedną kolumnę, w której nazwy miesięcy będą występowały w osobnych wierszach.

Pobieranie danych ze strony internetowej

Power Query pozwala również na pobieranie danych z zewnętrznych źródeł, takich jak strony internetowe. Aby zaimportować dane z internetu, należy kliknąć polecenie Z sieci w grupie Pobierz i przekształć dane. Po wprowadzeniu odpowiedniego adresu URL, Power Query nawiąże połączenie z witryną i załaduje dostępne tabele. W przypadku prostych stron internetowych, na których dane są prezentowane w tabelach, proces ten przebiega płynnie. Większość stron internetowych wymaga jednak dodatkowych kroków, takich jak uwierzytelnienie czy wybór odpowiednich danych. W niektórych przypadkach strona może nie udostępniać danych w łatwej do odczytania tabeli, co sprawia, że import danych staje się trudniejszy.

Podsumowanie

Power Query jest niezwykle użytecznym narzędziem do przekształcania i integracji danych w Excelu, umożliwiając szybkie i efektywne przetwarzanie danych z różnych źródeł. Z jego pomocą można znacznie uprościć pracę z danymi, automatyzując procesy, które wcześniej wymagałyby ręcznej interwencji. Niemniej jednak, aby skutecznie korzystać z tego narzędzia, należy dobrze rozumieć podstawowe zasady przetwarzania danych oraz znać ograniczenia Power Query. Ważne jest również, aby zawsze starać się pracować z danymi o poprawnej strukturze, co znacząco ułatwia ich przekształcanie i analizowanie.

Jak w Power Query odwołać się do poprzedniego wiersza i obliczyć zmiany procentowe?

Power Query umożliwia odwoływanie się do dowolnego wcześniejszego kroku w zapytaniu, co jest kluczowe przy porównywaniu danych między wierszami, na przykład do wyliczenia zmiany wartości w kolejnych miesiącach. Nie jest jednak możliwe odniesienie się do kroków, które występują później w kolejności przetwarzania danych, ponieważ prowadziłoby to do błędu związanego z zależnościami cyklicznymi.

Aby odczytać wartość z poprzedniego miesiąca, należy wskazać krok, w którym kolumna z wartościami (np. dochodami) jest już posortowana chronologicznie. W omawianym przykładzie takim punktem odniesienia jest krok o nazwie „Changed Type”, gdzie typy danych zostały wcześniej ustalone. Ważne jest, że ten krok nie jest bezpośrednio poprzedzający nowy, do którego dodajemy odwołanie, lecz wcześniejszy, co jest dozwolone.

Kluczową rolę odgrywa kolumna indeksu, która przechowuje numer wiersza. Dzięki temu odwołanie do wartości poprzedniego wiersza staje się dynamiczne, a nie statyczne (np. zamiast wpisywać ręcznie numer 2, formuła odwołuje się do wartości w kolumnie Indeks). Formuła przyjmie postać:

= #"Changed Type"{[Index]}[Income]

gdzie "Changed Type" to nazwa kroku, [Index] to numer wiersza, a [Income] to kolumna z dochodem.

Dodając nową kolumnę „Change” z obliczoną zmianą dochodu między miesiącami, formuła opiera się na odwołaniu do wartości z poprzedniego wiersza i dzieli ją przez wartość z wiersza bieżącego. W efekcie powstaje wyrażenie:

1 - #"Changed Type"{[Index]}[Income] / [Income]

aby wynik był czytelny i wyrażony procentowo, dodaje się typ danych procentowych poprzez Percentage.Type. Finalny kod w języku M przyjmuje postać:

= Table.AddColumn(#"Added Index", "Change", each 1 - #"Changed Type"{[Index]}[Income] / [Income], Percentage.Type)

W pierwszym wierszu pojawia się błąd, wynikający z braku „poprzedniego” wiersza dla indeksu 0, co w praktyce w Excelu powodowałoby pustą komórkę. Aby zamiast błędu uzyskać wartość zero, stosuje się obsługę błędów za pomocą słów kluczowych try i otherwise:

try 1 - #"Changed Type"{[Index]}[Income] / [Income] otherwise 0

Zmiana nazwy kroku z „Changed Type” na krótsze „Types” usprawnia czytelność formuł i jest łatwa do przeprowadzenia w interfejsie Power Query. Po wyczyszczeniu zbędnej kolumny indeksu, sfinalizowaniu nazwy zapytania i ustawieniu odpowiedniego formatu danych, wynik jest gotowy do załadowania do Excela.

Istotnym detalem jest fakt, że Excel nie rozpoznaje znaków specjalnych na początku nazw zapytań przy tworzeniu nazw tabel wynikowych, co warto mieć na uwadze, planując nazwy zapytań.

Poza samym zastosowaniem odwołań do wcześniejszych wierszy, ważne jest zrozumienie, że Power Query wymaga sekwencyjności kroków, dlatego każda kolejna operacja powinna opierać się na wynikach kroków wcześniejszych, by nie dochodziło do cyklicznych zależności. Dzięki temu transformacje danych mogą być dynamiczne i adaptowalne.

Warto również zwrócić uwagę na zarządzanie błędami – w dużych zestawach danych często pojawiają się niepełne lub uszkodzone wpisy, a dzięki try/otherwise można zapobiegać przerwaniu całego procesu przetwarzania danych przez pojedynczy problematyczny wiersz.

Dodatkowo, przy pracy z czasem i datami w Power Query, warto znać funkcję #duration, która pozwala na tworzenie i manipulację przedziałami czasowymi, co jest niezbędne przy analizach trendów i zmian w danych historycznych.

Umiejętność tworzenia kolumn warunkowych i korzystania z operatorów logicznych (and, or, not) pozwala na budowanie zaawansowanych reguł transformacji i walidacji danych bez konieczności opuszczania Power Query.

W kontekście praktycznego zastosowania, efektywne zarządzanie nazwami kroków, indeksowaniem oraz formatowaniem wyników znacznie ułatwia dalszą pracę z danymi, zwłaszcza podczas przygotowywania raportów, które wymagają automatyzacji i powtarzalności analiz.

Jak wykorzystać Visual Studio Code do rozszerzenia funkcjonalności Power Query i Power BI

Visual Studio Code, po zainstalowaniu, staje się potężnym narzędziem wspomagającym pracę z Power Query. Choć domyślnie nie jest on częścią środowiska Power Query, jego zastosowanie w roli zewnętrznego edytora może znacząco ułatwić pracę z kodem M oraz poszerzyć możliwości edycyjne. W tej części książki omówimy, jak skonfigurować Visual Studio Code oraz jak wykorzystywać dostępne rozszerzenia do pracy z Power Query.

Po pierwsze, należy pobrać i zainstalować Visual Studio Code ze strony: https://code.visualstudio.com/docs/setup/windows. Po uruchomieniu aplikacji, przechodzimy do sekcji rozszerzeń, klikając ikonę „Extensions” po lewej stronie ekranu. W wyszukiwarce należy wpisać „Power Query”, co pozwoli na znalezienie rozszerzenia o nazwie „Power Query / M Language”, które należy zainstalować. Drugie rozszerzenie, Power Query SDK, jest bardziej zaawansowane i dotyczy tworzenia niestandardowych konektorów, więc nie jest omawiane w ramach tej książki.

Po zainstalowaniu rozszerzenia Power Query / M Language, możemy przejść do tworzenia plików M w Visual Studio Code. Warto pamiętać, że nowo utworzony plik będzie domyślnie plikiem tekstowym, więc trzeba zmienić jego tryb na odpowiedni dla kodu M. Zmianę trybu języka dokonuje się, klikając w dolnym prawym rogu okna Visual Studio Code na „Plain Text” i wybierając odpowiednią opcję z listy języków. Dopiero wtedy kod M będzie odpowiednio formatowany, a edytor zaoferuje podpowiedzi do funkcji oraz kolorowanie składni.

Przykład kodu M, który można wkleić do Visual Studio Code, może wyglądać następująco:

m
let 


    Players = Excel.CurrentWorkbook() {[Name="tPlayers"]}[Content][Name],
    Indexes = List.Positions(Players),
    Matches = List.Transform(Indexes, (i) => List.Transform(
        List.Range(Indexes, i + 1, List.Count(Indexes) - i - 1),
        (j) => [Player1=Players{i}, Player2=Players{j}]
    )),
    AllMatches = Table.FromRecords(List.Combine(Matches))
in 
    AllMatches

Po wklejeniu kodu, można go sformatować automatycznie, korzystając ze skrótu klawiszowego Shift + Alt + F lub klikając prawym przyciskiem myszy i wybierając odpowiednią opcję w menu kontekstowym. Dobrze sformatowany kod jest łatwiejszy do zrozumienia i dalszej edycji. Co więcej, Visual Studio Code oferuje dodatkowe rozszerzenia, które mogą ulepszyć doświadczenia programistyczne, umożliwiając m.in. dostosowanie czcionki czy zmianę innych ustawień, które w Power Query są niedostępne.

Po sformatowaniu kodu można go skopiować i wkleić z powrotem do Power Query, gdzie będzie gotowy do dalszej obróbki. Dzięki temu, nawet w przypadku bardziej skomplikowanych zapytań, proces edycji kodu staje się bardziej komfortowy i wydajny.

Warto również zauważyć, że przy korzystaniu z Visual Studio Code można łączyć różne narzędzia, by poprawić wydajność procesu analizy danych. Na przykład, można używać zaawansowanych opcji związanych z zarządzaniem kodem, co w przypadku Power Query nie jest tak bezpośrednio dostępne. Dzięki rozszerzeniom, takim jak Power Query SDK, programiści mogą także tworzyć niestandardowe konektory i rozszerzenia, które mogą znacząco wpłynąć na funkcjonalność całego środowiska analitycznego.

Po zakończeniu pracy w Visual Studio Code, sformatowany kod M można łatwo zaimportować do Power Query, gdzie już gotowy będzie mógł zostać użyty w zapytaniach. Warto więc rozważyć wykorzystanie tego edytora, szczególnie w przypadku bardziej złożonych operacji na danych, które wymagają starannego przygotowania kodu.

Ważnym aspektem jest również ciągła kontrola jakości danych w Power Query. Możliwość monitorowania wartości błędów, pustych danych oraz ich niejednoznaczności jest nieoceniona w kontekście dużych zbiorów danych. Również optymalizacja zapytań, czyli ograniczenie liczby kroków, unikanie nieefektywnych operacji (takich jak niepotrzebne złączenia czy referencje), jest kluczowa dla zapewnienia szybszego przetwarzania danych. Używanie odpowiednich typów danych oraz wczesne usuwanie zbędnych kolumn to techniki, które pozwalają na lepszą wydajność i czystszy kod.

Jak naučit psa různým trikům a jak na problémy během trénování
Jak komunikují a brání se zvířata v přírodě?
Jaká je cena proklatí a jak moc může оно změnit život člověka?
Jak správně analyzovat rychlosti v mechanismech: analytická a grafická metoda