User-Defined Functions w Power Query: jak je budować i testować, żeby nie psuły wydajności

1. Wprowadzenie: czym są UDF w Power Query i kiedy warto je stosować

User-Defined Functions (UDF) w Power Query to funkcje zdefiniowane przez użytkownika, czyli własne, wielokrotnego użytku „klocki” logiki zapisane w języku M. Zamiast powtarzać te same kroki transformacji w wielu zapytaniach, można je zamknąć w funkcji i wywoływać z różnymi danymi wejściowymi. W praktyce UDF działają jak nazwana procedura: przyjmują argumenty, wykonują serię operacji i zwracają wynik.

Najważniejsza różnica między UDF a zwykłym zapytaniem polega na tym, że zapytanie typowo kończy się tabelą, listą lub rekordem jako gotowym wynikiem do dalszego użycia, natomiast UDF jest narzędziem do budowania takich wyników w sposób powtarzalny. Funkcję można traktować jako warstwę „biblioteki” w projekcie Power Query: jedne zapytania pobierają i łączą dane, a inne (funkcje) dostarczają wspólne reguły przekształceń.

UDF szczególnie dobrze sprawdzają się, gdy:

• Powtarzasz tę samą logikę w wielu miejscach (np. identyczne czyszczenie tekstu, ujednolicanie formatów, mapowanie wartości, normalizacja nazw).
• Chcesz parametryzować transformację (np. ta sama operacja, ale z innym zestawem kolumn, innym separatorem, inną listą wartości do filtrowania).
• Potrzebujesz spójności i łatwiejszego utrzymania: zmiana reguły w jednym miejscu aktualizuje zachowanie we wszystkich miejscach użycia.
• Budujesz bardziej „modułowe” zapytania, w których część logiki jest wydzielona dla czytelności (krótsze, prostsze główne zapytania).

Jednocześnie UDF w Power Query potrafią być mieczem obosiecznym. W porównaniu do kroków zapisanych bezpośrednio w zapytaniu, funkcje mogą utrudniać przewidywanie wydajności, szczególnie gdy są wywoływane wiele razy lub gdy nieświadomie wymuszają obliczenia w sposób „po jednym wierszu”. Dlatego warto myśleć o UDF nie tylko jako o porządkowaniu kodu, ale też jako o elemencie architektury, który może przyspieszyć pracę (przez redukcję duplikacji) albo ją spowolnić (przez nieoptymalny sposób użycia).

Kiedy lepiej nie sięgać po UDF? Zwykle wtedy, gdy logika jest jednorazowa, krótka i nie będzie ponownie używana, albo gdy jej wydzielenie pogorszy czytelność (np. ukryje prostą operację za niepotrzebną abstrakcją). Warto też zachować ostrożność, gdy funkcja ma być wywoływana masowo w kontekście dużych tabel — w takich scenariuszach kluczowe staje się, jak funkcja współpracuje z silnikiem Power Query i źródłem danych.

Podsumowując, UDF w Power Query to sposób na re-używalność, parametryzację i standaryzację transformacji. Stosowane świadomie pomagają budować czystsze i łatwiejsze w utrzymaniu rozwiązania, ale wymagają uwagi, by nie wprowadzać kosztów obliczeniowych, które ujawnią się dopiero przy większej skali danych.

2. Projektowanie funkcji w M: typowanie, sygnatury, parametry opcjonalne i domyślne

Dobrze zaprojektowana funkcja w języku M zaczyna się od jasnej sygnatury: co funkcja przyjmuje na wejściu, co zwraca na wyjściu i jakie ma założenia. To nie jest wyłącznie kwestia „ładnego API” — czytelna sygnatura ułatwia utrzymanie zapytań, zmniejsza ryzyko błędów przy ponownym użyciu i pozwala szybciej diagnozować problemy, gdy funkcja trafia do innych plików lub zespołów. Podczas szkoleń Cognity ten temat wraca regularnie — dlatego zdecydowaliśmy się go omówić również tutaj.

Typowanie: po co, skoro M jest elastyczne?

M jest językiem z dynamicznym typowaniem, ale w praktyce opłaca się traktować typy jako kontrakt. Typowanie argumentów i typu wyniku pomaga w dwóch obszarach: (1) dokumentuje oczekiwania funkcji, (2) ogranicza niejednoznaczności, gdy dane wejściowe bywają „prawie takie same”, ale jednak inne (np. liczba vs tekst, data vs datetime). Warto rozróżniać typy proste (liczby, tekst, logiczne, daty) oraz strukturalne (rekordy, listy, tabele), bo od tego zależy, jak funkcja powinna walidować i interpretować dane.

Praktyczna zasada: jeśli funkcja ma być używana wielokrotnie i przez różne zapytania, jawnie komunikuj oczekiwane typy wejścia i wyjścia. Jeśli natomiast funkcja jest jednorazowa i ściśle związana z jednym strumieniem danych, typowanie może być lżejsze, ale nadal warto dbać o jednoznaczność.

Sygnatura funkcji jako „interfejs”

Sygnatura to nie tylko lista parametrów. To również decyzje projektowe: które parametry są obowiązkowe, które są opcjonalne, a które w ogóle nie powinny być parametrami (bo lepiej zamknąć je w ciele funkcji). Dobra sygnatura jest minimalna — wystawia tylko to, co faktycznie trzeba zmieniać między wywołaniami.

• Parametry obowiązkowe powinny obejmować dane, bez których funkcja nie ma sensu (np. źródło danych lub kluczowe ustawienie logiki).
• Parametry konfiguracyjne powinny być grupowane i nazywane spójnie, żeby użytkownik nie musiał zgadywać kolejności lub znaczenia.
• Parametry „techniczne” (np. przełączniki diagnostyczne) warto ograniczać, bo komplikują użycie funkcji; jeśli są potrzebne, powinny mieć bezpieczne wartości domyślne.

Parametry opcjonalne: kiedy je stosować

Parametry opcjonalne są dobre, gdy funkcja ma wspierać kilka wariantów użycia bez tworzenia wielu podobnych funkcji. Typowe scenariusze to: dodatkowe filtrowanie, zmiana sposobu dopasowania, kontrola kultury/formatu, wybór zachowania w szczególnych przypadkach. Z perspektywy projektowania ważne jest, by opcjonalność była przewidywalna: brak parametru nie może powodować „magicznych” skutków ubocznych ani radykalnie innej semantyki, której nie widać w sygnaturze.

Warto też uważać na sytuacje, w których opcjonalny parametr przyjmuje wiele typów (np. raz tekst, raz lista). Taka elastyczność bywa kusząca, ale zwiększa liczbę ścieżek wykonania i utrudnia późniejsze utrzymanie.

Wartości domyślne: stabilność i ergonomia

Wartości domyślne są kluczowe dla ergonomii: pozwalają wywołać funkcję w prosty sposób, a jednocześnie umożliwiają zaawansowaną konfigurację tylko wtedy, gdy jest potrzebna. Dobra wartość domyślna powinna być:

• bezpieczna (nie prowadzi do nieoczekiwanych wyników),
• powszechna (pasuje do większości przypadków),
• spójna (zgodna z innymi funkcjami i konwencjami w projekcie).

Istotne jest również, by domyślne zachowanie było stabilne w czasie. Zmiana domyślnych wartości w „współdzielonej” funkcji może zmienić wyniki wielu zapytań naraz, dlatego takie decyzje powinny być przemyślane.

Nazewnictwo i czytelność parametrów

Projektowanie UDF w Power Query to w dużej mierze projektowanie użytecznego interfejsu. Parametry powinny mieć nazwy, które mówią co reprezentują, a nie jak są używane wewnątrz. Dobrą praktyką jest też konsekwencja językowa (np. wszędzie po polsku albo wszędzie po angielsku) oraz unikanie skrótów, które nie są oczywiste.

Jeśli funkcja ma kilka parametrów o podobnym znaczeniu, rozważ uporządkowanie ich logicznie: najpierw dane wejściowe, potem konfiguracja, na końcu przełączniki rzadziej używane. Dzięki temu funkcja jest łatwiejsza do stosowania i mniej podatna na pomyłki.

Granice odpowiedzialności funkcji

Na etapie projektowania warto świadomie zdecydować, co funkcja robi, a czego nie robi. Funkcje, które próbują jednocześnie: pobierać dane, czyścić, mapować, agregować i formatować wynik, szybko stają się trudne do ponownego użycia. Lepszym podejściem jest budowanie funkcji o jednej, jasno opisanej odpowiedzialności, które można składać w większe procesy.

To podejście ma też konsekwencje dla parametrów: im bardziej „wielozadaniowa” funkcja, tym więcej opcji i wyjątków trzeba wystawić na zewnątrz. Im bardziej skoncentrowana, tym sygnatura jest prostsza, a użycie bardziej przewidywalne.

3. Wydajność w praktyce: unikanie row-by-row, praca na listach/tabelach i wzorce wektorowe

Najczęstszy powód, dla którego User-Defined Functions (UDF) w Power Query spowalniają odświeżanie, to używanie ich w trybie row-by-row (wiersz po wierszu) na dużych tabelach. Power Query jest najbardziej efektywne, gdy operuje na całych kolumnach, listach i tabelach, a nie gdy dla każdego rekordu uruchamia osobną ścieżkę obliczeń. W praktyce oznacza to, że ta sama logika bywa szybka albo bardzo wolna — zależnie od tego, czy została „zwektoryzowana”.

Row-by-row: kiedy działa, a kiedy boli

Typowy wzorzec row-by-row to dodanie kolumny, w której wywołujesz funkcję dla każdego rekordu (Table.AddColumn + wywołanie UDF). To jest wygodne, czytelne i czasem wystarczające (np. małe dane, proste obliczenia), ale przy dużych wolumenach łatwo prowadzi do:

• nadmiarowych obliczeń (ta sama praca wykonywana setki tysięcy razy),
• wielokrotnych odczytów źródła lub etapów pośrednich,
• blokowania optymalizacji i gorszego wykorzystania operacji tabelarycznych.

Praca na listach i tabelach: myślenie „kolumnami”

Wydajnościowo korzystniejsze jest projektowanie UDF tak, aby przyjmowały i zwracały struktury zbiorcze:

• Listy – gdy logika jest jednolita i dotyczy sekwencji wartości (np. normalizacja, mapowanie, filtracja).
• Tabele – gdy operujesz na zestawie kolumn, łączeniach, grupowaniach, agregacjach.

Zamiast „dla każdego wiersza policz…”, częściej opłaca się „weź kolumnę/listę i przekształć ją jedną operacją”, a potem ewentualnie złączyć wynik z tabelą.

Wzorce wektorowe: jak „przenieść” UDF na cały zbiór

Wzorce wektorowe polegają na tym, że zamiast wielokrotnie wywoływać funkcję na skalarach, budujesz wynik przez operacje na całych kolekcjach. Najczęściej spotkasz:

• Mapowanie listy: pojedyncza transformacja wartości w kolumnie (np. przez List.Transform) i dopiero potem dołączenie/utworzenie kolumny.
• Operacje na kolumnach tabeli: dodawanie kolumn oparte o wyrażenia działające na istniejących polach, bez kosztownych zapytań pomocniczych w środku.
• Słowniki (rekordy) do lookupów: zamiast wyszukiwać w tabeli dla każdego wiersza, budujesz raz strukturę mapującą i stosujesz ją masowo.
• Grupowanie i agregacje: zamiast liczyć metryki „w kółko” dla każdego rekordu, grupujesz dane i liczysz agregaty raz na grupę.

Poniżej minimalistyczny przykład idei: najpierw przygotuj listę wyników, a dopiero potem wstaw ją jako kolumnę — zamiast uruchamiać ciężką logikę w każdej iteracji rekordu.

// Idea (schematycznie): pracuj na liście, potem dodaj jako kolumnę
let
    T = Source,
    Col = T[SomeColumn],
    NewCol = List.Transform(Col, each MyUdf(_)),
    Out = Table.FromColumns(Table.ToColumns(T) & {NewCol}, Table.ColumnNames(T) & {"Result"})
in
    Out

Najczęstsze pułapki, które robią z UDF „hamulec”

• Wyszukiwanie w tabeli wewnątrz UDF dla każdego wiersza (np. filtrowanie i pobieranie wartości jako lookup) – zwykle lepiej przygotować mapowanie raz.
• Wywoływanie funkcji, które dotykają źródła danych w pętli per-row – łatwo mnoży koszty I/O.
• Zagnieżdżone tabele w komórkach używane później w kolejnych krokach – potrafią zwiększać narzut obliczeń i utrudniać optymalizację.
• Rozbudowana logika warunkowa wykonywana setki tysięcy razy – często da się ją uprościć przez transformacje na poziomie kolumn.

Praktyczna reguła projektowa

Jeśli funkcja ma być użyta na dużym zbiorze danych, rozważ projektowanie jej w trybie „zbiorczym”: niech przyjmuje listę lub tabelę i zwraca listę/tabelę. Wywołanie UDF raz na partię danych prawie zawsze skaluje się lepiej niż wywołanie jej 100 000 razy na pojedynczych rekordach.

4. Buforowanie i ponowne użycie wyników: Table.Buffer/List.Buffer „z głową” oraz koszty pamięci

Funkcje (UDF) w Power Query często są wywoływane wielokrotnie: dla wielu zapytań, wielu tabel albo wewnątrz transformacji, które odwołują się do tego samego źródła kilka razy. W takich sytuacjach warto rozumieć, czym jest buforowanie i kiedy może pomóc, a kiedy wręcz zaszkodzić.

W M obowiązuje podejście „obliczaj, gdy potrzebne” (leniwa ewaluacja). To zwykle jest zaletą, ale ma skutek uboczny: jeśli w kilku miejscach odwołujesz się do tej samej wartości (np. tej samej tabeli wejściowej lub listy), silnik może ją odczytać/przeliczyć więcej niż raz, szczególnie gdy te odwołania prowadzą do ponownych odczytów ze źródła lub powtórnego przetwarzania kosztownych kroków. Uczestnicy szkoleń Cognity często mówią, że właśnie ta wiedza najbardziej zmienia ich sposób pracy — bo pozwala świadomie decydować, kiedy „zamrażać” dane, a kiedy zaufać leniwej ewaluacji.

Table.Buffer i List.Buffer pozwalają wymusić materializację danych w pamięci: wynik zostaje „zamrożony” w danym momencie, a kolejne odwołania korzystają z już policzonej struktury.

Do czego służy buforowanie (i czym nie jest)

• Cel: ograniczenie wielokrotnych odczytów/przeliczeń tego samego fragmentu danych w ramach jednego odświeżenia.
• Nie jest to trwały cache między odświeżeniami – bufor żyje tylko w trakcie wykonania zapytania.
• Nie jest to automatyczna optymalizacja: może poprawić czas, ale może też zwiększyć zużycie pamięci i zaburzyć inne mechanizmy (np. przenoszenie obliczeń do źródła danych).

Table.Buffer vs List.Buffer — różnice i typowe zastosowania

Praktyczna wskazówka: buforuj najmniejszy sensowny obiekt. Jeśli potrzebujesz wielokrotnie tylko jednej kolumny jako listy, często lepiej zbuforować tę listę niż całą tabelę.

Buforowanie jako element projektowania UDF

W kontekście funkcji użytkownika buforowanie najczęściej pojawia się w dwóch scenariuszach:

• Wielokrotne użycie tych samych danych wejściowych w obrębie jednej funkcji (np. kilka filtrów, joinów lub agregacji opartych o to samo wejście).
• Wspólne przygotowanie danych dla kilku wyników pośrednich (np. funkcja zwraca rekord z kilkoma wartościami, liczonymi na tej samej bazie).

W takich przypadkach sensowne jest wyciągnięcie „drogo kosztującego” kroku do jednej zmiennej i ewentualne zbuforowanie jej przed dalszym użyciem.

let
    FnExample = (tbl as table) as record =>
    let
        // przygotowanie wspólnej bazy
        Base = Table.SelectRows(tbl, each [Active] = true),
        // buforowanie rozważ, jeśli Base jest używane wielokrotnie i koszt jego wyliczenia jest istotny
        BaseBuffered = Table.Buffer(Base),
        A = Table.RowCount(BaseBuffered),
        B = List.Sum(Table.Column(BaseBuffered, "Amount"))
    in
        [ActiveRows = A, ActiveAmount = B]
in
    FnExample

To podejście zwiększa przewidywalność: oba obliczenia (A i B) korzystają z tej samej, raz policzonej bazy.

Koszt pamięci i kiedy buforowanie szkodzi

Buforowanie przenosi ciężar z CPU/IO na RAM. Najczęstsze negatywne skutki to:

• Wzrost zużycia pamięci – zwłaszcza przy szerokich tabelach, dużych tekstach lub wielu zbuforowanych obiektach naraz.
• Spadek stabilności odświeżania – ryzyko wolniejszego działania, a w skrajnych przypadkach błędów braku pamięci.
• „Zamrożenie” danych za wcześnie – jeśli buforujesz przed tym, jak ograniczysz zestaw (np. filtr/wybór kolumn), materializujesz więcej niż potrzebujesz.

Dlatego bezpieczna reguła brzmi: najpierw ogranicz dane (kolumny/wiersze), potem ewentualnie buforuj. Jeśli musisz buforować, rób to jak najpóźniej, ale przed miejscem, w którym zaczyna się wielokrotne użycie tej samej wartości.

Ponowne użycie wyników bez buforowania

Nie każde „ponowne użycie” wymaga Buffer. Często wystarczy:

• Przypisać wynik kosztownego kroku do zmiennej w let i użyć jej w kilku miejscach (czytelność + mniejsza szansa na duplikację pracy).
• Redukować dane wcześniej (wybór kolumn, filtrowanie) i dopiero na tak odchudzonym zestawie wykonywać dalsze kroki.

Buforowanie traktuj jako narzędzie „awaryjne” do sytuacji, gdy widzisz realny koszt powtórnych obliczeń, a jednocześnie akceptujesz dodatkowe obciążenie pamięci.

5. Obsługa błędów i walidacja wejścia: try/otherwise, komunikaty, wartości zastępcze

UDF w Power Query często trafiają do wielu zapytań i są wywoływane w różnych kontekstach (różne typy danych, brakujące kolumny, inne kodowania, puste tabele). Dlatego warto świadomie zaprojektować zachowanie przy błędach i walidację wejścia, aby funkcja była przewidywalna: albo szybko przerywała z czytelnym komunikatem, albo zwracała kontrolowaną wartość zastępczą.

Walidacja wejścia: szybka i jednoznaczna

Walidacja ma dwa cele: (1) wykryć niepoprawne dane wejściowe jak najwcześniej, (2) zwrócić informację, która pozwoli poprawić wywołanie funkcji. W praktyce w M najczęściej sprawdza się:

• Typ i nullowalność: czy parametr jest nullable, czy może być null, czy jest oczekiwanym typem (np. tekst, liczba, tabela).
• Struktura tabel: czy tabela ma wymagane kolumny, czy ich typy są zgodne, czy nie jest pusta, jeśli logika tego wymaga.
• Zakres i domena wartości: np. liczby dodatnie, dozwolone wartości w parametrze tekstowym, poprawność formatu (np. „YYYY-MM”).

Jeżeli błąd wejścia oznacza, że wynik byłby bezsensowny, lepiej zastosować podejście fail fast (czytelny błąd), zamiast „cichego” zwracania czegoś przypadkowego.

try/otherwise: kontrola zamiast wybuchu

Konstrukcja try … otherwise pozwala przechwycić błąd i zamienić go na:

• wartość zastępczą (np. null, pustą tabelę, 0),
• wynik warunkowy (np. alternatywną ścieżkę logiki),
• ułamany zwrot diagnostyczny (np. rekord z polem HasError i opisem).

Najprostszy wzorzec to „spróbuj policzyć, a gdy się nie da – zwróć X”. Warto używać go tam, gdzie błąd jest spodziewany (np. brak dopasowania w wyszukiwaniu), a nie jako plaster na każdy wyjątek.

let
  SafeNumber = (t as text) as nullable number =>
    let
      parsed = try Number.FromText(t)
    in
      if parsed[HasError] then null else parsed[Value]
in
  SafeNumber

Czytelne komunikaty: mniej czasu na debugowanie

Gdy funkcja ma przerwać działanie, komunikat powinien mówić: co jest nie tak i jakie było oczekiwanie. W M można jawnie zgłosić błąd przez error (np. z tekstem) lub przez rekord błędu. Dobra praktyka to komunikaty odnoszące się do parametrów funkcji (np. nazwa parametru, wymagane kolumny), bez wchodzenia w implementacyjne szczegóły.

let
  RequireColumns = (tbl as table, cols as list) as table =>
    let
      missing = List.Difference(cols, Table.ColumnNames(tbl))
    in
      if List.Count(missing) > 0 then
        error "Brak wymaganych kolumn: " & Text.Combine(List.Transform(missing, each Text.From(_)), ", ")
      else
        tbl
in
  RequireColumns

Wartości zastępcze: kiedy null, kiedy pusta tabela, kiedy rekord

Wybór wartości zastępczej wpływa na to, jak łatwo będzie użyć UDF dalej w transformacjach oraz czy błędy nie zostaną przypadkiem ukryte.

Nie „łap wszystkiego” na ślepo

Masowe opakowanie całej funkcji w try … otherwise bywa kuszące, ale często utrudnia wykrycie prawdziwej przyczyny problemu. Lepsze podejście to:

• walidować wejście na początku (czytelny błąd),
• używać try punktowo tam, gdzie błąd jest elementem normalnego przepływu (np. konwersja typu, wyszukiwanie),
• stosować wartości zastępcze, które nie „udają” poprawnych danych, jeśli to krytyczne.

Minimalny koszt walidacji i spójność kontraktu funkcji

Walidacja i obsługa błędów też kosztują (dodatkowe kroki, sprawdzenia list kolumn). Dlatego opłaca się ustalić kontrakt funkcji: jakie parametry dopuszcza, co zwraca w przypadku braku danych oraz czy w razie problemu ma przerywać, czy zwracać wartość zastępczą. Spójny kontrakt ułatwia ponowne użycie UDF i ogranicza nieprzewidywalne zachowania w łańcuchu transformacji.

6. Testowanie funkcji: małe próbki, asercje, porównania wyników i przypadki brzegowe

Testowanie UDF w Power Query (funkcji w M) ma dwa cele: sprawdzenie poprawności (czy funkcja zwraca to, co powinna) oraz stabilności (czy zachowuje się przewidywalnie dla „trudnych” danych). W praktyce chodzi o to, by błędy wychwycić zanim funkcja trafi do wielu zapytań lub zostanie użyta na dużych zbiorach.

Małe próbki zamiast pełnych danych

Najprostszy i najtańszy sposób testowania to uruchamianie funkcji na minimalnych, kontrolowanych próbkach:

• Próbka pozytywna – kilka wierszy reprezentujących typowy przypadek.
• Próbka negatywna – dane niepoprawne lub niekompletne (np. null, zły typ, brak kolumny).
• Próbka mieszana – różne warianty w jednej paczce, by zobaczyć jak funkcja radzi sobie z heterogenicznością.

Takie próbki można stworzyć lokalnie (np. z #table, {}, Record.FromList), dzięki czemu test nie zależy od źródła danych ani od odświeżenia.

Asercje: szybkie „testy jednostkowe” w M

M nie ma wbudowanego frameworka testowego, ale można stosować proste asercje, które zatrzymują wykonanie, gdy wynik jest niezgodny z oczekiwaniami. Najczęściej spotkasz dwa podejścia:

• Porównanie i błąd – jeśli warunek nie jest spełniony, zwróć error z komunikatem.
• Flaga statusu – zamiast przerywać, zwróć tabelę/rekord z informacją Pass/Fail i szczegółami (wygodne przy wielu testach naraz).

let
  AssertEqual = (actual as any, expected as any, message as text) as logical =>
    if actual = expected then true else error message,

  // Przykład testu funkcji (tu: fnX)
  _t1 = AssertEqual(fnX(2), 4, "fnX(2) powinno zwrócić 4")
in
  _t1

Asercje są szczególnie przydatne podczas refaktoryzacji: pozwalają szybko potwierdzić, że zmiana wewnątrz funkcji nie zmieniła zachowania na znanych przypadkach.

Porównania wyników: „nowa vs stara” oraz alternatywne implementacje

Gdy optymalizujesz funkcję albo upraszczasz logikę, warto porównać wyniki z wersją referencyjną:

• Nowa implementacja vs stara – sprawdzasz zgodność wyników na tej samej próbce.
• Implementacja „prosta” vs „szybsza” – prosta bywa czytelniejsza i łatwiejsza do zaufania jako punkt odniesienia.
• Porównanie tabel – weryfikujesz liczbę wierszy, zestaw kolumn, typy oraz kluczowe wartości.

W Power Query porównania często sprowadzają się do kontroli różnic: czy są wiersze „tylko w A” lub „tylko w B”, czy pojawiły się nowe wartości null, czy zmieniły się typy.

Przypadki brzegowe, które psują funkcje najczęściej

Większość problemów z UDF wychodzi nie na „ładnych” danych, tylko na wyjątkach. Lista kontrolna do szybkich testów:

• null jako wejście lub w kluczowych polach (np. w kolumnach używanych do łączeń).
• Puste listy/tabele/teksty (np. {}, tabela 0 wierszy, "").
• Nietypowe typy (tekst zamiast liczby, liczba jako tekst, data jako datetime).
• Brakujące kolumny lub inne nazwy kolumn niż oczekiwane.
• Duplikaty w kluczach, gdy spodziewasz się unikalności.
• Znaki specjalne, spacje, różna wielkość liter, lokalne formaty liczb i dat.
• Skrajne wartości (0, liczby ujemne, bardzo duże liczby, daty graniczne).

Dobrą praktyką jest przygotowanie „pakietu” wejść testowych obejmujących te warianty i odpalanie ich po każdej zmianie funkcji.

Minimalny „zestaw testów” jako tabela

Jeśli masz kilka scenariuszy, wygodne jest trzymanie ich jako tabeli: wejście, oczekiwany wynik, opis. Taki format ułatwia przegląd i szybkie dodawanie kolejnych przypadków.

Praktyczne zasady, żeby testy pomagały, a nie przeszkadzały

• Testuj logikę, nie źródło – buduj próbki lokalnie, aby uniknąć losowych różnic w danych.
• Oddziel testy od produkcji – trzymaj testowe zapytania jako osobne queries (np. wyłączone z ładowania), aby nie obciążały modelu.
• Ustal, co jest „kontraktem” – jakie typy i kształt danych funkcja przyjmuje oraz co gwarantuje na wyjściu.
• Dokładaj testy po błędach – gdy trafisz na problem w danych, zamień go w nowy przypadek testowy.

Takie podejście sprawia, że UDF stają się przewidywalne, łatwiejsze w utrzymaniu i bezpieczniejsze do ponownego użycia w wielu miejscach.

7. Ocena wpływu na wydajność i query folding: diagnostyka, typowe pułapki i rekomendacje

User-Defined Functions w Power Query potrafią zarówno uporządkować logikę i ułatwić utrzymanie, jak i niepostrzeżenie spowolnić odświeżanie. Kluczowe są dwie kwestie: ile pracy wykonuje silnik Power Query oraz gdzie ta praca jest wykonywana (u źródła czy lokalnie). Ocena wpływu UDF na wydajność sprowadza się więc do sprawdzenia kosztu samej funkcji oraz tego, czy jej użycie nie blokuje query folding.

Jak myśleć o wydajności: praca „u źródła” vs lokalnie

Query folding oznacza, że Power Query potrafi „przepchnąć” część kroków do źródła danych (np. do silnika SQL), dzięki czemu filtrowanie, agregacje czy selekcja kolumn odbywają się tam, gdzie dane są przechowywane. UDF bywają problematyczne, bo często wprowadzają logikę, której nie da się przełożyć na natywny język źródła. W praktyce warto rozróżnić:

• Funkcje transformujące na poziomie tabeli — zwykle łatwiej utrzymać folding, jeśli używają operacji „zrozumiałych” dla konektora.
• Funkcje wywoływane per wiersz — najczęstszy powód utraty foldingu i skokowego wzrostu czasu odświeżania, bo wymuszają iterację i pracę lokalną.

Diagnostyka: skąd wiesz, że UDF psuje wydajność

W ocenie wydajności nie chodzi o zgadywanie, tylko o szybkie potwierdzenie faktów. Najbardziej użyteczne podejścia diagnostyczne to:

• Weryfikacja foldingu na kluczowych krokach: sprawdzaj, czy kroki po zastosowaniu funkcji nadal składają się do źródła. Jeśli folding urywa się dokładnie w miejscu użycia UDF, to sygnał ostrzegawczy.
• Porównanie czasu odświeżania przed/po: traktuj UDF jak zmianę architektoniczną. Mierz czas odświeżenia oraz zachowanie na większej próbce danych, bo koszty często rosną nieliniowo.
• Analiza etapów ładowania: obserwuj, czy po dodaniu funkcji rośnie ilość danych pobieranych ze źródła (np. mniej filtrów „zadziałało” po stronie serwera), albo czy rośnie liczba kroków wykonywanych lokalnie.
• Diagnostyka zapytań i logów konektora: gdy korzystasz ze źródeł wspierających podgląd zapytań, sprawdź, czy generowane zapytania są nadal selektywne (filtry, projekcja kolumn), czy też następuje pobranie „wszystkiego” i obróbka u klienta.

Typowe pułapki: gdzie UDF najczęściej „odcina” folding

• Logika niemożliwa do przetłumaczenia na język źródła: złożone warunki, niestandardowe parsowanie tekstu, rozbudowane dopasowania wzorców czy operacje zależne od lokalnych ustawień.
• Wywołania funkcji w kontekście wiersza: nawet prosta funkcja, jeśli jest stosowana per rekord na dużej tabeli, może zamienić szybkie operacje zbiorcze w kosztowną iterację.
• Łączenie z zewnętrznymi danymi „w środku” funkcji: jeśli UDF wykonuje dodatkowe odwołania do źródeł (np. pobiera słownik, listę wyjątków), łatwo o wielokrotne odpytywanie i efekt „N+1”.
• Nieintencjonalne materializowanie danych: kroki, które wymuszają pełne przeliczenie lub pobranie dużych fragmentów danych, mogą sprawić, że kolejne operacje nie będą już składane.
• Nadmierna „ogólność” funkcji: funkcje projektowane jako uniwersalne często zawierają rozgałęzienia i warunki, które utrudniają optymalizację oraz składanie do źródła.

Rekomendacje: jak ograniczyć ryzyko utraty wydajności

• Najpierw filtruj i ograniczaj dane, potem stosuj UDF: buduj przepływ tak, by jak najwięcej redukcji (filtry, wybór kolumn, agregacje) odbyło się przed zastosowaniem funkcji.
• Traktuj UDF jako „ostatni etap” logiki niestandardowej: jeśli coś da się zrobić w standardowych krokach, zwykle lepiej zostawić to poza funkcją, bo łatwiej utrzymać folding.
• Unikaj wywołań per wiersz na dużych zbiorach: jeśli funkcja musi być użyta wielokrotnie, szukaj sposobu na zastosowanie jej w sposób zbiorczy albo przebudowę transformacji tak, by ograniczyć liczbę wywołań.
• Ostrożnie z funkcjami, które same sięgają do źródeł: jeśli funkcja potrzebuje danych pomocniczych, staraj się je przygotować raz i przekazywać jako parametr, zamiast pobierać je w środku.
• Weryfikuj folding po każdej istotnej zmianie: utrata foldingu często pojawia się po „niewinnej” modyfikacji funkcji, np. dodaniu jednego warunku lub dodatkowej transformacji tekstu.
• Rozdziel funkcje na „foldowalne” i „niefoldowalne”: utrzymuj jasny podział na logikę, która ma działać u źródła, oraz na końcowe transformacje, które muszą być wykonane lokalnie.

Ocena końcowa: co jest „dobrą” funkcją z perspektywy wydajności

Dobra UDF w Power Query to taka, która nie wymusza nadmiarowej pracy i nie przenosi przetwarzania z serwera na klienta bez uzasadnienia. Jeśli po jej zastosowaniu rośnie ilość pobieranych danych, znikają składane filtry lub czas odświeżania rośnie nieproporcjonalnie do wolumenu, to znak, że warto przeprojektować miejsce użycia funkcji lub sposób jej wywoływania. W praktyce najskuteczniejsza jest zasada: maksymalizuj folding i minimalizuj liczbę wywołań funkcji na dużych tabelach.

8. Przykład UDF end-to-end: implementacja, użycie w zapytaniu oraz refaktoryzacja pod czytelność i wydajność

Najlepiej zrozumieć sens UDF w Power Query, przechodząc przez pełny, praktyczny scenariusz: od pomysłu na funkcję, przez jej pierwszą wersję, aż po świadome uproszczenie i przyspieszenie. W tym przykładzie UDF nie jest „sztuką dla sztuki” — ma rozwiązać powtarzalny problem w kilku zapytaniach i jednocześnie nie wprowadzić ukrytych kosztów wydajności.

Załóżmy typowy przypadek: w danych z wielu źródeł musisz ustandaryzować wartości (np. nazwy, identyfikatory, kody), wykonać walidację (czy pole spełnia minimalne wymagania) i zwrócić wynik w ujednoliconej postaci. Bez UDF kończy się to kopiowaniem kroków między zapytaniami, trudnymi do utrzymania różnicami i ryzykiem, że poprawka w jednym miejscu „nie dojedzie” do reszty.

• Implementacja: zaczynasz od najprostszej wersji funkcji, która bierze wartość wejściową i zwraca wartość po oczyszczeniu/normalizacji. Na tym etapie liczy się przede wszystkim poprawność i czytelność.
• Użycie w zapytaniu: następnie włączasz funkcję do transformacji danych — zwykle jako element kroku dodającego/zmieniającego kolumnę. Tu szybko widać, czy funkcja pasuje do realnego przepływu danych oraz czy nie komplikuje logiki zapytania.
• Refaktoryzacja: na końcu wracasz do funkcji i poprawiasz to, co w praktyce boli: zbyt wiele warunków, powtarzające się fragmenty, niejasne nazwy parametrów, a czasem przede wszystkim — zbyt wolne działanie w skali całego zestawu danych.

Kluczowe w podejściu end-to-end jest to, że UDF traktujesz jak mały komponent: ma jasny kontrakt (co przyjmuje, co zwraca), powinna być przewidywalna (te same dane → ten sam wynik) i możliwa do ponownego użycia w wielu miejscach. Jednocześnie warto pamiętać, że Power Query uruchamia transformacje w określony sposób, więc to, co wygląda „czysto” w pojedynczym wywołaniu, może być kosztowne przy tysiącach lub milionach wierszy.

W praktyce refaktoryzacja pod czytelność i wydajność zwykle obejmuje:

• Uproszczenie interfejsu: ograniczenie liczby parametrów do niezbędnego minimum oraz doprecyzowanie, które są wymagane, a które opcjonalne.
• Rozdzielenie odpowiedzialności: jeśli funkcja „robi wszystko”, łatwo ją zepsuć. Często lepiej rozdzielić ją na mniejsze kroki (nawet logicznie, wewnątrz jednej funkcji), aby łatwiej panować nad zmianami.
• Eliminację pracy powtarzalnej: przeniesienie stałych elementów (np. mapowań, reguł, list wyjątków) poza fragment wykonywany wielokrotnie, tak aby nie były liczone w kółko.
• Dopasowanie do sposobu przetwarzania danych: funkcja powinna współgrać z tym, jak Power Query operuje na tabelach i kolumnach — w przeciwnym razie może wymuszać kosztowne przetwarzanie „wiersz po wierszu”.
• Lepsze nazwy i komunikaty: nazwy parametrów i wyników powinny sugerować intencję, a nie implementację; dodatkowo warto, aby błędne wejście skutkowało przewidywalnym rezultatem (np. wartościami zastępczymi albo kontrolowanym błędem), zamiast losowych wyjątków.

Ważna obserwacja z takiego przykładu: UDF jest najbardziej opłacalne, gdy redukuje powtarzalność i stabilizuje logikę, ale może stać się wąskim gardłem, jeśli zostanie użyte bez refleksji w krytycznym miejscu transformacji. Dlatego w podejściu end-to-end najpierw upewniasz się, że funkcja rzeczywiście upraszcza proces, a dopiero potem doszlifowujesz ją tak, aby była łatwa do czytania, testowania i bezpieczna wydajnościowo.

Jeśli chcesz poznać więcej takich przykładów, zapraszamy na szkolenia Cognity, gdzie rozwijamy ten temat w praktyce.

Aplikacje webowe z Claude Code: gdzie AI oszczędza dni pracy, a gdzie psuje projekt 28 maja 2026

Debugowanie z Claude Code: najszybsza droga od błędu do poprawki 26 maja 2026