Kurs Power BI: Język M: zaawansowane transformacje

W skrócieJęzyk M to język formuł Power Query, w którym zapisany jest każdy krok przekształcenia danych. W tej lekcji piszemy M ręcznie tam, gdzie kończy się menu: struktura let..in, funkcje tekstowe i daty, parametry, własna funkcja walidacji NIP oraz query folding na danych Nordvelli.

W poprzednim rozdziale klikaliśmy transformacje myszką w Power Query, a Power BI zapisywał je za nas jako listę kroków. Pod spodem każdy taki krok to linijka kodu w języku M, języku formuł Power Query. W tym rozdziale schodzimy poziom niżej i zaczynamy pisać M ręcznie: tam, gdzie menu się kończy, a Ty potrzebujesz czegoś, czego w menu po prostu nie ma. Zrobimy to na danych Nordvelli, polskiej sieci elektroniki, której arkusz ma między innymi tabelę faktów sprzedaży (108 675 wierszy) oraz wymiary produktów, sklepów, pracowników i kalendarza.

Cel jest praktyczny. Zbudujemy własną funkcję, która czyści i waliduje polski numer NIP, sterowany parametrem próg segmentacji transakcji, oraz zrozumiemy query folding, czyli mechanizm, który decyduje o tym, czy Twój raport na dużym źródle będzie się odświeżał w sekundy, czy w minuty.

Struktura let..in i pasek formuły

Co chcemy osiągnąć. Każde kliknięcie w edytorze Power Query zapisuje się jako kod w języku M. Chcemy umieć ten kod przeczytać i poprawić, bo część przekształceń danych Nordvelli w tej lekcji, jak walidacja numerów NIP, wymaga napisania kodu.

Zacznij od otwarcia edytora. W widoku raportu kliknij na wstążce Narzędzia główne, a potem Przekształć dane. Otworzy się osobne okno, Edytor Power Query. Po lewej masz listę zapytań (każda tabela Nordvelli to jedno zapytanie), na środku podgląd danych, a po prawej panel Ustawienia zapytania z sekcją Zastosowane kroki. Ta lista kroków to nic innego jak zapis kodu M w postaci graficznej.

Żeby zobaczyć cały kod, zaznacz zapytanie FaktSprzedaz i na wstążce Widok kliknij Edytor zaawansowany. W pliku startowym z lekcji 04 to zapytanie czyta jeszcze zabrudzony arkusz i ma inne kroki. W tej lekcji pracujemy na czystym arkuszu, więc zaznacz całą zawartość okna (Ctrl+A), wklej w to miejsce kod poniżej, popraw ścieżkę w kroku Zrodlo na miejsce, w którym zapisałeś Nordvella_dane.xlsx, i kliknij Gotowe. W oknie zobaczysz wtedy dokładnie to:

let
    // Każdy krok to nazwana zmienna. Kroki oddziela przecinek.
    Zrodlo = Excel.Workbook(File.Contents("C:\Kurs_PBI\Nordvella_dane.xlsx"), null, true),
    FaktSprzedaz_Arkusz = Zrodlo{[Item="FaktSprzedaz", Kind="Sheet"]}[Data],
    NaglowkiPodwyzszone = Table.PromoteHeaders(FaktSprzedaz_Arkusz, [PromoteAllScalars=true]),
    // Usuwamy transakcje oznaczone jako zwrot (CzyZwrot = 1)
    BezZwrotow = Table.SelectRows(NaglowkiPodwyzszone, each [CzyZwrot] = 0),
    // Ustawiamy typy tych kolumn, na których będziemy liczyć
    TypyKolumn = Table.TransformColumnTypes(BezZwrotow, {
        {"DataID", Int64.Type},
        {"Ilosc", Int64.Type},
        {"WartoscNetto", type number},
        {"WartoscBrutto", type number},
        {"MarzaNetto", type number}
    })
in
    TypyKolumn   // to, co stoi po in, jest wynikiem zapytania

Przeczytajmy to spokojnie. Sekcja let to lista przypisań: Zrodlo otwiera plik, FaktSprzedaz_Arkusz wyciąga z niego arkusz o tej nazwie, BezZwrotow odfiltrowuje zwroty, a TypyKolumn ustawia typy. Nazwy zmiennych są dowolne, ale w jednej rzeczy trzeba być konsekwentnym: każdy krok bierze na wejście wynik poprzedniego, dlatego zwykle w kodzie widzisz łańcuszek, w którym nazwa poprzedniego kroku pojawia się jako pierwszy argument następnego. To dlatego usunięcie kroku ze środka listy potrafi zepsuć wszystko, co jest za nim.

Porównując kod z listą kroków zauważysz jedną rozbieżność, która potrafi wprawić w zakłopotanie: w kodzie drugi krok nazywa się FaktSprzedaz_Arkusz, a na liście po prawej jest Nawigacja. Nie pomyliłeś się. Dla kroków wchodzących w głąb źródła (arkusz w skoroszycie, tabela w bazie) Power Query pokazuje własną, opisową etykietę zamiast nazwy zmiennej. Nazwa z kodu obowiązuje przy odwoływaniu się do kroku, etykieta jest tylko dla oka.

Drugi szczegół jest przyjemniejszy. Przy trzech krokach na liście widać małą ikonę informacji. To komentarze z kodu: linia zaczynająca się od // postawiona nad krokiem staje się jego opisem, który Power Query pokazuje po najechaniu kursorem.

To jest realny argument za komentowaniem kodu M w zespole. Opis kroku zostaje w pliku .pbix i jest pierwszą rzeczą, którą zobaczy osoba przejmująca raport po Tobie.

Po słowie in podajesz nazwę kroku, który ma być wynikiem. Prawie zawsze jest to ostatni krok, ale nie musi. Zmień na chwilę in TypyKolumn na in BezZwrotow, zatwierdź i zobacz, co się stanie z podglądem.

Dane rzeczywiście są sprzed zmiany typów, ale zwróć uwagę na dwie rzeczy po prawej i u góry, bo o nich rzadko się pisze. Po pierwsze, lista kroków zwinęła się do jednej pozycji o nazwie zapytania. Power Query rysuje listę kroków tylko wtedy, gdy zapytanie jest prostym łańcuchem zakończonym ostatnim krokiem. Gdy po in jest coś ze środka, łańcucha nie da się narysować i panel pokazuje całe zapytanie jako jeden blok. Po drugie, pasek formuły pokazuje wtedy samo = let, a nie pojedynczą linijkę.

To nie jest usterka, tylko konsekwencja tego, jak działa panel kroków. Warto o tym wiedzieć, bo na czas takiego podglądu tracisz klikalną listę kroków - dlatego traktuj tę sztuczkę jako chwilowe zajrzenie do środka, a nie sposób pracy. Po obejrzeniu danych wróć do in TypyKolumn i lista wraca w komplecie.

Na pasku stanu u dołu okna zobaczysz przy okazji LICZBA KOLUMN: 14, LICZBA WIERSZY: 199+. Ten plus jest istotny: to liczba wierszy pobranych do podglądu, a nie liczba wierszy w tabeli. Power Query profiluje domyślnie pierwsze 1000 wierszy i nigdy nie podaje tu wielkości całego źródła.

Do drobnych poprawek nie musisz otwierać całego edytora. Nad podglądem danych jest pasek formuły, który pokazuje kod M pojedynczego, aktualnie zaznaczonego kroku. Kliknij krok BezZwrotow na liście kroków, a w pasku formuły zobaczysz dokładnie linijkę = Table.SelectRows(...). Jeśli paska nie widzisz, włącz go na wstążce Widok, zaznaczając pole Pasek formuły.

Funkcje tekstowe i daty w M

Co chcemy osiągnąć. Nazwy miast i produktów Nordvelli bywają zapisane niespójnie, a z dat trzeba wyciągać rok, miesiąc i kwartał. Chcemy poznać funkcje M, które rozwiązują te zadania, i warunek if..then..else, którym podzielimy transakcje na segmenty według wartości.

Kod M piszesz najczęściej w dwóch miejscach: w kolumnie niestandardowej (wstążka Dodaj kolumnę, przycisk Kolumna niestandardowa) oraz w Edytorze zaawansowanym. Zanim przejdziemy do własnych funkcji, poznaj zestaw, który wraca w prawie każdym projekcie na danych Nordvelli. Zaczniemy od tekstu, bo to on sprawia najwięcej kłopotów w praktyce (spacje, wielkość liter, kropki i myślniki w kodach).

// FUNKCJE TEKSTOWE na kolumnach Nordvella
Text.Upper([Miasto])                 // "WARSZAWA"
Text.Proper([Miasto])                // "Warszawa" (pierwsza litera wielka)
Text.Trim([Nazwa])                   // usuwa spacje z początku i końca
Text.Clean([Nazwa])                  // usuwa niewidoczne znaki sterujące
Text.Length([Model])                 // liczba znaków w nazwie modelu
Text.Contains([Podkategoria], "Smart")  // true / false
Text.StartsWith([Nazwa], "Laptop")   // czy zaczyna się od słowa
Text.Split([Manager], " ")           // "Jan Nowak" -> {"Jan", "Nowak"}
Text.Combine({[Imie], [Nazwisko]}, " ")  // "Anna Kowalska"
Text.Replace([Marka], ".", "")       // usuwa kropki
Text.PadStart(Text.From([SklepID]), 3, "0")  // 7 -> "007"

Dwie uwagi do tej listy. Text.Proper bierzesz wtedy, gdy jeden system eksportuje WARSZAWA, drugi warszawa, a Ty chcesz po tej kolumnie łączyć tabele - łączenie po tekście rozróżnia wielkość liter i takie dwa zapisy nigdy się nie spotkają. W eksporcie Nordvelli, na którym pracujemy, pisownia miast jest akurat spójna, więc ta funkcja nic by tu nie zmieniła. Trzymaj ją na dzień, w którym źródło nie będzie tak grzeczne, i stosuj profilaktycznie po obu stronach złączenia. Text.PadStart przyda się od razu: identyfikatory sklepów idą od 1 do 15, a system zewnętrzny oczekuje kodu stałej długości w formacie 001, 002.

Spróbujmy funkcji tekstowych na prawdziwych danych. Zapytania DimSklepy w pliku startowym jeszcze nie ma, więc najpierw je dodaj: na wstążce Narzędzia główne kliknij Nowe źródło, wybierz Puste zapytanie, otwórz Edytor zaawansowany, wklej poniższy kod (ze swoją ścieżką do pliku), kliknij Gotowe i w panelu Ustawienia zapytania zmień nazwę zapytania na DimSklepy.

let
    Zrodlo = Excel.Workbook(File.Contents("C:\Kurs_PBI\Nordvella_dane.xlsx"), null, true),
    DimSklepy_Arkusz = Zrodlo{[Item="DimSklepy", Kind="Sheet"]}[Data],
    NaglowkiPodwyzszone = Table.PromoteHeaders(DimSklepy_Arkusz, [PromoteAllScalars=true])
in
    NaglowkiPodwyzszone

Teraz zaznacz zapytanie DimSklepy, przejdź na wstążkę Dodaj kolumnę i kliknij Kolumna niestandardowa.

Jedna pułapka, na którą łatwo wejść: pole formuły ma już swój znak = po lewej stronie. Jeśli wkleisz formułę razem ze znakiem równości, dostaniesz = = i zapytanie się nie wykona. W polu Nazwa nowej kolumny wpisujesz KodSklepu, a w polu formuły samo Text.PadStart(Text.From([SklepID]), 3, "0"), i klikasz OK.

Power Query nada nowemu krokowi nazwę Dodano kolumnę niestandardową. Nadaj mu nazwę kolumny, bo do kroków będziemy się zaraz odwoływać po nazwie: kliknij krok prawym przyciskiem na liście Zastosowane kroki, wybierz Zmień nazwę i wpisz KodSklepu. Tak samo postępujesz z każdą kolumną niestandardową w tej lekcji: krok nosi nazwę kolumny, którą dodaje.

Dołóż w ten sam sposób drugą kolumnę, NazwaKrotka, z formułą Text.Replace([Nazwa], "Nordvella ", "") - obcina powtarzany prefiks sieci, który na wykresie tylko zajmuje miejsce. Nowe kolumny lądują na końcu tabeli, daleko od źródłowych, więc żeby zobaczyć efekt obok oryginału, dołóż na chwilę jeszcze jeden krok. Kliknij przycisk fx po lewej stronie paska formuły. Power BI doda krok, który w pasku formuły pokazuje samą nazwę kroku poprzedniego, na przykład = NazwaKrotka. Zastąp go formułą poniżej, zostawiając jako pierwszy argument nazwę Twojego ostatniego kroku. Jeśli dodawałeś kolumny w kolejności z tej lekcji, Twoim ostatnim krokiem jest NazwaKrotka i właśnie ta nazwa jest w formule niżej. W pliku efektu i na zrzucie kolumny powstały w odwrotnej kolejności, dlatego w pasku formuły na zrzucie pierwszym argumentem jest KodSklepu. Wynik jest identyczny. Nowy krok nazwij Porownanie.

= Table.SelectColumns(NazwaKrotka, {"SklepID", "KodSklepu", "Nazwa", "NazwaKrotka", "Miasto"})

Krok Table.SelectColumns skasuj, gdy już obejrzysz wynik - w modelu przydadzą Ci się wszystkie kolumny wymiaru. Do takiego chwilowego zaglądania służy właśnie lista kroków: dokładasz krok, patrzysz, usuwasz, a reszta zapytania zostaje nietknięta.

Teraz daty. W Nordvelli klucz daty siedzi w kolumnie DataID tabeli FaktSprzedaz, a pełna data w kolumnie Data wymiaru DimKalendarz. Gdy jednak trzeba samodzielnie wyciągnąć rok czy miesiąc z surowej kolumny typu data, użyjesz tych funkcji:

// FUNKCJE DATY (na kolumnie [Data] z DimKalendarz)
Date.Year([Data])                    // 2026
Date.Month([Data])                   // 1..12
Date.Day([Data])                     // 1..31
Date.QuarterOfYear([Data])           // 1..4
Date.DayOfWeek([Data], Day.Monday)   // 0=poniedziałek, 6=niedziela
Date.DayOfWeekName([Data])           // "poniedziałek"
Date.StartOfMonth([Data])            // pierwszy dzień miesiąca
Date.EndOfMonth([Data])              // ostatni dzień miesiąca
Date.AddDays([Data], -7)             // data sprzed tygodnia
Date.From(20260118)                  // z liczby DataID na typ date
DateTime.LocalNow()                  // bieżąca data i czas
Duration.Days([DataZwrotu] - [Data]) // ile dni minęło (typ liczba)

Ostatni przykład jest ważny biznesowo. W tabeli FaktZwroty Nordvelli jest kolumna CzasDniOdZakupu, ale gdyby jej nie było, policzysz ją samodzielnie odejmując datę zakupu od daty zwrotu i owijając wynik w Duration.Days, żeby dostać czystą liczbę dni. To typowa transformacja, gdy analizujesz, jak szybko klienci Nordvella zwracają towar (czy mieszczą się w ustawowych 14 dniach, czy przekraczają termin).

Zobacz też: Szkolenie Power BI - Desktop, DAX i Online + Copilot

Warunki, czyli if..then..else w M

M ma własną składnię warunków, inną niż DAX. Zbudujmy praktyczną kolumnę: segmentację transakcji Nordvella po wartości brutto. Zaznacz zapytanie FaktSprzedaz i na liście kroków kliknij ostatni krok, TypyKolumn. Kliknij Dodaj kolumnę, potem Kolumna niestandardowa, nazwij ją SegmentTransakcji (krok też, jak przy sklepach) i wpisz:

if [WartoscBrutto] >= 5000 then "Premium"
else if [WartoscBrutto] >= 1000 then "Standard"
else "Podstawowy"

Po zatwierdzeniu w podglądzie zobaczysz nową kolumnę tekstową, gdzie każda transakcja dostała etykietę. Uwaga na trzy różnice względem Excela i DAX: w M piszesz małe if, then, else, każde if musi mieć swoje else (nie ma niejawnej wartości pustej), a zamiast zagnieżdżonych funkcji jak w DAX robisz łańcuch else if, który czyta się z góry na dół.

Nowe kolumny znów lądują na końcu tabeli, a FaktSprzedaz ma ich czternaście, więc zastosuj tę samą sztuczkę co przy sklepach: przycisk fx i krok Porownanie z formułą Table.SelectColumns(SegmentTransakcji, {"SprzedazID", "WartoscBrutto", "SegmentTransakcji"}). Wtedy widać wszystko naraz:

Kilka wierszy przepisanych dla czytelności, razem z tym najważniejszym:

Sprawdźmy jeszcze, czy podział ma sens biznesowy, bo progi 1000 i 5000 wzięliśmy z sufitu. Na 105 961 transakcjach bez zwrotów wychodzi: Premium 36 589, Standard 59 661, Podstawowy 9 711. Najliczniejszy jest środek, a najmniej liczny koszyk najtańszy - dla sieci sprzedającej elektronikę to wiarygodny rozkład. Gdyby jedna z etykiet zgarniała 95 procent wierszy, progi trzeba by przesunąć, bo taki podział niczego nie dzieli.

Zakres wartości brutto w tej tabeli to od 225,19 zł do 62 676,37 zł, więc żaden z trzech koszyków nie jest pusty ani nie wykracza poza dane. To jest ta sama zasada, co przy funkcji walidującej NIP kilka akapitów niżej: etykieta, której nie widziałeś na własne oczy, jest na razie tylko obietnicą kodu.

Parametry: sterowanie transformacją bez dotykania kodu

Co chcemy osiągnąć. Progi segmentów transakcji Nordvelli ustala dział analiz i mogą się zmienić. Chcemy, żeby taką zmianę dało się wprowadzić w jednym oknie, bez szukania liczb w kodzie zapytania.

Progi 5000 i 1000 z poprzedniej sekcji wpisaliśmy na sztywno w kod. Gdy za pół roku dział analiz Nordvella zdecyduje, że Premium zaczyna się od 8000, ktoś musi wejść w Edytor zaawansowany, znaleźć liczbę i ją zmienić. To zła praktyka. Zamiast tego wyciągamy próg do parametru, czyli nazwanej wartości, którą zmienia się z okna, bez wchodzenia w kod.

W Edytorze Power Query kliknij na wstążce Narzędzia główne, rozwiń Zarządzaj parametrami i wybierz Nowy parametr. W oknie ustaw:

Okno ma własny suwak i przy pełnej liście wartości nie mieści wszystkiego naraz, więc nie zdziw się, że musisz je przewinąć, żeby dojść do dwóch ostatnich pól. Wartość domyślna to ta, do której parametr wraca, gdy ktoś wyczyści pole. Wartość bieżąca to ta, którą policzy zapytanie. Na start ustawiamy obie na 5000.

Po zatwierdzeniu na liście zapytań po lewej pojawia się nowy wpis ProgPremium z ikoną parametru. Teraz wróć do kolumny SegmentTransakcji, kliknij krok, w którym ją dodajesz, i w pasku formuły podmień liczbę 5000 na nazwę parametru:

Table.AddColumn(TypyKolumn, "SegmentTransakcji", each
    if [WartoscBrutto] >= ProgPremium then "Premium"
    else if [WartoscBrutto] >= 1000 then "Standard"
    else "Podstawowy",
    type text)

Drobiazg, który oszczędza nerwów: podgląd Power Query nie przewija się strzałkami z klawiatury. Gdy klikniesz krok SegmentTransakcji (a nie Porownanie), kolumna SegmentTransakcji jest za czternastoma kolumnami źródłowymi FaktSprzedaz, więc żeby zobaczyć ją obok WartoscBrutto, złap suwak poziomy na dole okna i przeciągnij go w prawo.

Od teraz, żeby przesunąć próg Premium, klikasz parametr ProgPremium na liście zapytań i w polu Wartość bieżąca wybierasz 8000 z listy. Podgląd przelicza się od razu, jeszcze w edytorze - przycisk Zamknij i zastosuj jest potrzebny dopiero wtedy, gdy chcesz przenieść wynik do modelu.

To samo podejście stosuje się do ścieżki pliku (parametr SciezkaPliku zamiast twardo wpisanego C:\Kurs_PBI\Nordvella_dane.xlsx) oraz do daty granicznej, od której ładujesz sprzedaż. Dzięki temu ten sam plik raportu przenosisz między komputerami bez edycji zapytań.

Własna funkcja M: walidacja i formatowanie NIP

Co chcemy osiągnąć. Nordvella sprzedaje też firmom, a numer NIP na fakturach jest zapisany na kilka sposobów. Chcemy jedną funkcję, która każdy taki numer oczyści, sprawdzi jego cyfrę kontrolną i sformatuje jednakowo, zanim dane faktur trafią do raportu.

W surowym źródle NIP bywa zapisany na kilka sposobów: 5252597520, 525-259-75-20, 525 259 75 20, czasem z prefiksem PL. Chcemy jedną funkcję, która taki numer oczyści, sprawdzi cyfrę kontrolną i zwróci rekord z trzema polami: czy numer jest poprawny, jego kanoniczny zapis oraz powód odrzucenia. Trzy pola zamiast jednego tekstu to świadoma decyzja, do której wrócimy za chwilę. Cyfra kontrolna NIP liczy się z wag 6, 5, 7, 2, 3, 4, 5, 6, 7 dla dziewięciu pierwszych cyfr, sumy tych iloczynów modulo 11, co musi się równać dziesiątej cyfrze. To realny algorytm, którego menu Power Query nie zna, więc piszemy własną funkcję.

Na wstążce Narzędzia główne kliknij Nowe źródło i na dole listy wybierz Puste zapytanie. Otwórz Edytor zaawansowany, wklej poniższy kod, zatwierdź przyciskiem Gotowe, a dopiero potem zmień nazwę zapytania na fnWalidujNIP w panelu Ustawienia zapytania po prawej. Ta kolejność ma znaczenie: nazwa nadana przed wklejeniem kodu potrafi się nie zatwierdzić i wtedy przemianujesz nie to zapytanie, co trzeba.

let
    fnWalidujNIP = (nip as nullable text) as record =>
        let
            Cyfry = Text.Select(Text.From(nip ?? ""), {"0".."9"}),
            Dlugosc = Text.Length(Cyfry),
            Wagi = {6, 5, 7, 2, 3, 4, 5, 6, 7},
            Suma = if Dlugosc = 10
                   then List.Sum(List.Transform({0..8}, each Number.FromText(Text.At(Cyfry, _)) * Wagi{_}))
                   else null,
            Kontrolna = if Suma = null then null else Number.Mod(Suma, 11),
            Poprawny = Dlugosc = 10 and Kontrolna <> 10
                       and Kontrolna = Number.FromText(Text.At(Cyfry, 9)),
            Sformatowany = if Poprawny
                           then Text.Range(Cyfry, 0, 3) & "-"
                              & Text.Range(Cyfry, 3, 3) & "-"
                              & Text.Range(Cyfry, 6, 2) & "-"
                              & Text.Range(Cyfry, 8, 2)
                           else null,
            Powod = if Dlugosc <> 10 then "zła liczba cyfr: " & Text.From(Dlugosc)
                    else if not Poprawny then "błędna cyfra kontrolna"
                    else "ok"
        in
            [Poprawny = Poprawny, Sformatowany = Sformatowany, Powod = Powod]
in
    fnWalidujNIP

Przejdźmy przez to krok po kroku, bo tu jest sedno pisania własnych funkcji. Linijka fnWalidujNIP = (nip as nullable text) as record => to nagłówek funkcji: w nawiasie deklarujesz argument i jego typ (tekst, który może być pusty), po as record deklarujesz typ zwracany, a strzałka => oddziela nagłówek od ciała funkcji. Ciało to zwykły blok let..in, taki sam jak w każdym zapytaniu. Zewnętrzne let ... in fnWalidujNIP tylko nadaje funkcji nazwę wewnątrz zapytania.

Dlaczego rekord, a nie zwykły tekst? Bo funkcja odpowiada na trzy różne pytania naraz, a wciskanie ich w jedną kolumnę zawsze kończy się tak samo: w kolumnie z numerami lądują napisy w rodzaju „BŁĄD: zła długość", kolumna przestaje być numerem, a filtrowanie po jakości danych sprowadza się do szukania tekstu. Rekord rozdziela to czysto: Poprawny jest wartością logiczną i po niej filtrujesz, Sformatowany zawiera numer albo null, a Powod jest wyłącznie dla człowieka. Za chwilę rozwiniemy ten rekord na trzy osobne kolumny jednym kliknięciem.

Krok Cyfry to serce czyszczenia: Text.Select z listą znaków od 0 do 9 zostawia w tekście wyłącznie cyfry, więc myślniki, spacje, kropki i prefiks PL znikają jednym poleceniem. Dalej sprawdzamy długość, liczymy sumę ważoną z wag NIP, bierzemy resztę z dzielenia przez 11 i porównujemy z dziesiątą cyfrą. Warunek Kontrolna <> 10 zabezpiecza szczególny przypadek, w którym reszta wynosi 10 (taki NIP jest z definicji nieprawidłowy). Na końcu, jeśli wszystko się zgadza, składamy ładny format z myślnikami operatorem łączenia tekstu &.

Gdy zatwierdzisz kod, zapytanie zmienia ikonę na symbol funkcji fx i zamiast tabeli pokazuje panel Wprowadź parametr z jednym polem i przyciskiem Wywołaj. Ten panel służy do sprawdzenia funkcji na jednej wartości, zanim zastosujesz ją do całej kolumny. Wpisz 525-259-75-20 i kliknij Wywołaj. Power Query doda wtedy nowe zapytanie Wywołano funkcję z wynikiem: to ono jest zaznaczone na liście zapytań na drugim zrzucie poniżej, a nad wynikiem widać formułę z wpisanym numerem.

Teraz rzecz, której nie wolno pominąć: zrzut z poprawnym numerem niczego jeszcze nie dowodzi. Funkcja, która zawsze zwraca „ok", też by go wyprodukowała. Podmień w pasku formuły ostatnią cyfrę na 1, czyli wywołaj = fnWalidujNIP("525-259-75-21"), i zatwierdź.

Dopiero te dwa wywołania razem coś znaczą. Pierwsze pokazuje, że funkcja przyjmuje numer poprawny, drugie - że odrzuca numer, który wygląda dobrze. Jeżeli kiedykolwiek będziesz sprawdzać cudzą funkcję walidującą, zacznij właśnie od drugiego przypadku: zepsuj dane tak, żeby błąd nie rzucał się w oczy, i zobacz, czy walidator go złapie.

Zastosowanie funkcji na kolumnie

Teraz puszczamy funkcję na całą kolumnę. Faktury Nordvelli leżą w osobnym pliku, bo w firmie pochodzą z systemu księgowego, a nie z hurtowni sprzedaży: Nordvella_faktury.xlsx, 60 wierszy, kolumny FakturaID, SprzedazID, NIP i NazwaKontrahenta. Kolumna NIP jest celowo brudna - ten sam numer bywa zapisany na kilka sposobów, kilka numerów jest wadliwych, a kilka pustych.

Dodaj puste zapytanie, nazwij je Faktury i wklej do Edytora zaawansowanego całość. Dwa ostatnie kroki są tu najważniejsze: Kontrola dokłada kolumnę z wywołaniem funkcji (każda komórka zawiera cały rekord), a Rozwinięta kontrola rozbija ten rekord na trzy zwykłe kolumny.

let
    Źródło = Excel.Workbook(File.Contents("C:\Kurs_PBI\Nordvella_faktury.xlsx"), null, true),
    Arkusz = Źródło{[Item="Faktury", Kind="Sheet"]}[Data],
    Nagłówki = Table.PromoteHeaders(Arkusz, [PromoteAllScalars = true]),
    Typy = Table.TransformColumnTypes(Nagłówki, {{"FakturaID", Int64.Type}, {"SprzedazID", Int64.Type}, {"NIP", type text}, {"NazwaKontrahenta", type text}}),
    Kontrola = Table.AddColumn(Typy, "Kontrola", each fnWalidujNIP([NIP])),
    #"Rozwinięta kontrola" = Table.ExpandRecordColumn(Kontrola, "Kontrola", {"Poprawny", "Sformatowany", "Powod"}, {"NIP_Poprawny", "NIP_Sformatowany", "NIP_Powod"})
in
    #"Rozwinięta kontrola"

Nie musisz pisać kroku rozwijania ręcznie. Po kroku Kontrola w nagłówku nowej kolumny pojawia się ikona z dwiema strzałkami - klikasz ją, zaznaczasz trzy pola rekordu i Power Query sam dopisuje Table.ExpandRecordColumn. Kod powyżej jest po prostu tym, co z tego wyjdzie.

Osiem wierszy z tego zrzutu, przepisanych dla czytelności:

Pięć górnych wierszy to ten sam problem w pięciu przebraniach: goły ciąg cyfr, myślniki, spacje, prefiks PL i słowo „NIP" z przodu. Wszystkie dały wynik w jednym, identycznym formacie z myślnikami - i o to chodzi, bo dopiero po takim ujednoliceniu da się po tej kolumnie łączyć tabele i grupować. Trzy dolne pokazują trzy różne powody odrzucenia, każdy nazwany wprost. Na całym pliku wychodzi 42 numery poprawne i 18 odrzuconych z 60.

I tu wraca decyzja o rekordzie. Żeby zobaczyć faktury do ręcznej poprawki, ustawiasz filtr na kolumnie NIP_Poprawny na wartość FALSE - jedno kliknięcie w zwykły filtr wartości logicznej. Gdyby funkcja zwracała tekst, ten sam filtr musiałby szukać fragmentu napisu w kolumnie, w której obok napisów siedzą prawdziwe numery. Działa, dopóki komuś nie przyjdzie do głowy zmienić treści komunikatu.

Query folding: dlaczego jedne raporty odświeżają się szybko, a inne wolno

Co chcemy osiągnąć. Gdy dane Nordvelli pochodzą z bazy SQL Server, część przekształceń może wykonać serwer, a część musi przejść przez Twój komputer. Chcemy wiedzieć, które kroki wykonuje baza, bo od tego zależy czas odświeżania raportu.

Query folding, po polsku zwijanie zapytań, to mechanizm, który decyduje, gdzie fizycznie wykonuje się Twoja transformacja: na serwerze źródłowym czy na Twoim komputerze. Gdy podłączasz się do bazy danych (na przykład SQL Server z dumpem Nordvella), Power Query stara się przetłumaczyć Twoje kroki (filtr, wybór kolumn, grupowanie) na jedno zapytanie SQL i wysłać je do serwera. Serwer liczy, a do Power BI wraca tylko gotowy, mały wynik. To jest właśnie zwijanie. Gdy zwijanie się przerwie, Power BI ściąga wszystkie wiersze do siebie i liczy lokalnie, co przy 108 675 wierszach FaktSprzedaz robi ogromną różnicę w czasie odświeżania.

Które kroki się zwijają, a które nie? Zwijają się typowe operacje relacyjne: filtrowanie wierszy, usuwanie i wybór kolumn, zmiana typów, grupowanie, sortowanie, złączenia (merge) między tabelami z tego samego źródła. Nie zwija się większość rzeczy specyficznych dla M: wywołanie własnej funkcji jak nasz fnWalidujNIP, indeksowanie po pozycji wiersza, część funkcji tekstowych i datowych bez odpowiednika w SQL. Dlatego jest złota zasada kolejności: najpierw rób wszystko, co się zwija (filtry, wybór kolumn), a dopiero na końcu kroki, które zwijanie łamią. Wtedy serwer najpierw obetnie Ci dane do niezbędnego minimum, a lokalnie policzysz już tylko małą resztę.

Zobaczmy to na tabeli, która w bazie Nordvelli naprawdę jest duża: FaktSprzedaz, 108 675 wierszy. Chcemy odsiać zwroty i ujednolicić zapis kanału sprzedaży. Poniżej ta sama transformacja w dwóch kolejnościach. Pierwsza jest zła (własna funkcja przed filtrem, zwijanie pęka na starcie i serwer i tak zwraca wszystkie wiersze), druga dobra (najpierw filtr, który się zwija, potem funkcja). Żeby powtórzyć to u siebie, dodaj puste zapytanie, nazwij je SprzedazSQL i wklej do Edytora zaawansowanego jedną z wersji, w miejsce localhost wpisując nazwę swojego serwera z odtworzoną bazą Nordvella:

// ZŁA KOLEJNOŚĆ: funkcja M przerywa zwijanie, zanim serwer obetnie wiersze
let
    Źródło = Sql.Database("localhost", "Nordvella"),
    dbo_FaktSprzedaz = Źródło{[Schema="dbo", Item="FaktSprzedaz"]}[Data],
    fnKanal = (tekst as text) as text => Text.Proper(Text.Trim(tekst)),
    #"Kanał kanoniczny" = Table.AddColumn(dbo_FaktSprzedaz, "KanalKanoniczny", each fnKanal([KanalSprzedazy]), type text),
    #"Bez zwrotów" = Table.SelectRows(#"Kanał kanoniczny", each [CzyZwrot] = 0)   // ten filtr już się NIE zwinie
in
    #"Bez zwrotów"

// DOBRA KOLEJNOŚĆ: najpierw filtr (zwija się do SQL), potem funkcja
let
    Źródło = Sql.Database("localhost", "Nordvella"),
    dbo_FaktSprzedaz = Źródło{[Schema="dbo", Item="FaktSprzedaz"]}[Data],
    #"Bez zwrotów" = Table.SelectRows(dbo_FaktSprzedaz, each [CzyZwrot] = 0),     // zwija się: WHERE CzyZwrot = 0
    fnKanal = (tekst as text) as text => Text.Proper(Text.Trim(tekst)),
    #"Kanał kanoniczny" = Table.AddColumn(#"Bez zwrotów", "KanalKanoniczny", each fnKanal([KanalSprzedazy]), type text)
in
    #"Kanał kanoniczny"

Jak sprawdzić, czy krok się zwija

Nie zgaduj, sprawdź. W panelu Zastosowane kroki kliknij dany krok prawym przyciskiem. Jeśli w menu kontekstowym pozycja Wyświetl zapytanie natywne jest aktywna (klikalna), to znaczy, że wszystko do tego kroku włącznie zwinęło się do jednego zapytania źródłowego, a po kliknięciu zobaczysz wygenerowany SQL. Jeśli pozycja jest wyszarzona, zwijanie zostało przerwane wcześniej i ten krok liczy się już lokalnie. Sprawdzaj to na kroku, który Cię interesuje, oraz na kroku tuż przed nim, żeby dokładnie zlokalizować, gdzie zwijanie pęka.

Kliknij ją, a zobaczysz kod, którego nikt ręcznie nie pisał. To Power Query przetłumaczył Twój krok na SQL i to ten SQL wykona serwer, zanim cokolwiek pojedzie przez sieć:

Teraz to samo po dołożeniu kroku z własną funkcją fnKanal. Serwer SQL nie zna tej funkcji, więc nie ma czego przetłumaczyć - i widać to od razu:

To jest cała diagnostyka: klikasz kolejne kroki od góry i patrzysz, na którym pozycja z czarnej robi się szara. Krok, na którym to następuje, jest tym, który łamie zwijanie - i to jego warto przesunąć na koniec listy, jeśli się da.

Podsumowanie i co dalej

Zeszliśmy z poziomu klikania do poziomu kodu. Wiesz już, że każde zapytanie to blok let..in, w którym krok bierze wynik poprzedniego. Znasz zestaw funkcji tekstowych i datowych, który pokrywa większość realnej pracy na Nordvelli. Umiesz wyciągnąć wartość do parametru, żeby analityk zmieniał ją z okna, a nie z kodu. Napisaliśmy razem własną funkcję walidującą NIP i puściliśmy ją na całą kolumnę faktur. Wreszcie rozumiesz query folding i wiesz, dlaczego kolejność kroków przekłada się wprost na czas odświeżania raportu.

Zanim przejdziesz dalej, wyłącz ładowanie zapytań pomocniczych: kliknij prawym przyciskiem fnWalidujNIP na liście zapytań i odznacz Włącz ładowanie, to samo zrób dla Wywołano funkcję i SprzedazSQL. Potem kliknij Zamknij i zastosuj i zajrzyj do widoku Dane (druga ikona na pasku po lewej). Tabela Faktury wygląda tam już jak każda inna tabela modelu - po kolumnie NIP_Sformatowany można łączyć i grupować, a po NIP_Poprawny filtrować. Cała „magia" z rekordami została w Power Query, do modelu weszły zwykłe kolumny.

W kolejnym rozdziale zostawiamy Power Query i wchodzimy do widoku modelu. Połączymy dziewięć tabel Nordvelli relacjami w schemat gwiazdy (FaktSprzedaz w środku, wymiary DimKalendarz, DimProdukty, DimSklepy i DimPracownicy wokół). Zaczniemy od pliku z końca lekcji 03, w którym te tabele są wczytane z czystego arkusza, bo pliki z lekcji 04 i 05 służyły ćwiczeniom w Power Query. To jest fundament, na którym w następnych rozdziałach zbudujemy miary DAX.

✕