Bezpłatny kurs Power Query: język M, parametry i własne funkcje

W skrócieKażdy kliknięty krok Power Query zapisuje w języku M, a edytor zaawansowany pozwala ten kod czytać i poprawiać. Omawiamy budowę zapytania, przydatne wzorce, parametry zamiast ścieżek wpisanych na sztywno, własną funkcję pobierającą kurs waluty z NBP i obsługę błędów przez try i otherwise.

Język M: co Power Query zapisuje za Ciebie

Każdy przycisk, który klikaliśmy, dopisywał linijkę kodu w języku M (oficjalnie Power Query Formula Language). Cały kod zapytania zobaczysz w oknie Edytor zaawansowany, które otwierasz z karty Strona główna albo Widok. Tak wygląda kod naszego zestawienia realizacji budżetu:

let
    Źródło = Sprzedaz_miesieczna,
    #"Wstawiono początek miesiąca" = Table.AddColumn(Źródło, "Początek miesiąca", each Date.StartOfMonth([Data]), type date),
    #"Pogrupowano wiersze" = Table.Group(#"Wstawiono początek miesiąca", {"Sklep", "Początek miesiąca"}, {{"Sprzedaż netto", each List.Sum([Wartość netto]), type nullable number}, {"Liczba pozycji", each Table.RowCount(_), Int64.Type}}),
    #"Scalone zapytania" = Table.NestedJoin(#"Pogrupowano wiersze", {"Sklep", "Początek miesiąca"}, tBudzet, {"Sklep", "Początek miesiąca"}, "tBudzet", JoinKind.LeftOuter),
    #"Rozwinięty element tBudzet" = Table.ExpandTableColumn(#"Scalone zapytania", "tBudzet", {"Budżet"}, {"Budżet"}),
    #"Dodano kolumnę niestandardową" = Table.AddColumn(#"Rozwinięty element tBudzet", "Realizacja budżetu", each [Sprzedaż netto] / [Budżet]),
    #"Zmieniono typ" = Table.TransformColumnTypes(#"Dodano kolumnę niestandardową",{{"Realizacja budżetu", Percentage.Type}})
in
    #"Zmieniono typ"

Najważniejsze zasady, które pozwolą czytać i poprawiać taki kod:

• let ... in. Między let a in są kroki, każdy w postaci nazwa = wyrażenie, oddzielone przecinkami. Po in stoi nazwa kroku, którego wynik zwraca całe zapytanie, zwykle ostatniego.
• Każdy krok odwołuje się do poprzedniego po nazwie. Table.Group(#"Wstawiono początek miesiąca", ...) grupuje wynik kroku o tej nazwie. Dzięki temu możesz wstawić krok w środku listy albo zmienić kolejność, pilnując tylko, żeby odwołania się zgadzały.
• Nazwy ze spacjami i polskimi znakami zapisuje się w postaci #"nazwa". Zmiana nazwy kroku na liście Zastosowane kroki (prawy przycisk, Zmień nazwę, albo klawisz F2) zmienia ją też w kodzie.
• each i podkreślnik. each [Data] to skrót od funkcji wywoływanej dla każdego wiersza, a _ oznacza bieżący element, na przykład całą grupę w Table.RowCount(_).
• [Kolumna] i {numer}. Nawias kwadratowy pobiera pole (kolumnę w bieżącym wierszu albo pole rekordu), klamra pobiera element listy lub wiersz tabeli, licząc od zera: Źródło{0} to pierwszy wiersz.
• Wielkość liter ma znaczenie. Table.AddColumn zadziała, table.addcolumn zwróci błąd, a kolumna „sklep" to inna kolumna niż „Sklep".
• Komentarze piszesz po // do końca linii albo między /* i */. Edytor zaawansowany je zachowa.

M ma też własne typy wartości, które spotkasz w podglądzie: proste (null, liczba, tekst, data, data i godzina, czas trwania, wartość logiczna), oraz złożone, które w siatce widać jako klikalne napisy: Table (tabela), List (lista), Record (rekord, czyli jeden wiersz z nazwanymi polami) i Function (funkcja). Kliknięcie takiego napisu przechodzi do środka wartości, co wykorzystamy przy danych z API.

Biblioteka standardowa M ma kilkaset funkcji pogrupowanych prefiksami: Table. dla tabel, List. dla list, Text. dla tekstu, Number. dla liczb, Date. i DateTime. dla dat, Record. dla rekordów. Pełną listę wypisze samo Power Query: utwórz puste zapytanie z formułą = #shared i przekonwertuj wynik na tabelę.

Przydatne wzorce w języku M

Kilka formuł, po które sięga się częściej niż po inne. Wszystkie wpiszesz w oknie Kolumna niestandardowa albo w Edytorze zaawansowanym.

Obliczenia na datach. Różnicę dni między dwiema datami, przesunięcie o miesiąc i koniec miesiąca policzysz tak:

// liczba dni między datą zamówienia a datą dostawy (wynik odejmowania dat to czas trwania)
= Duration.Days([Data dostawy] - [Data zamówienia])

// termin płatności: 14 dni od daty faktury
= Date.AddDays([Data faktury], 14)

// ostatni dzień miesiąca, w którym była sprzedaż
= Date.EndOfMonth([Data])

Tabela kalendarza. W Power BI i w modelu danych Excela każda analiza w czasie potrzebuje tabeli z kolejnymi dniami. Utwórz puste zapytanie, wklej poniższy kod w Edytorze zaawansowanym i dopasuj zakres dat:

let
    Start = #date(2026, 1, 1),
    Koniec = #date(2026, 12, 31),
    Dni = List.Dates(Start, Duration.Days(Koniec - Start) + 1, #duration(1, 0, 0, 0)),
    Tabela = Table.FromList(Dni, Splitter.SplitByNothing(), {"Data"}),
    Typ = Table.TransformColumnTypes(Tabela, {{"Data", type date}}),
    Rok = Table.AddColumn(Typ, "Rok", each Date.Year([Data]), Int64.Type),
    Miesiac = Table.AddColumn(Rok, "Miesiąc", each Date.Month([Data]), Int64.Type),
    NazwaMiesiaca = Table.AddColumn(Miesiac, "Nazwa miesiąca", each Date.MonthName([Data], "pl-PL"), type text),
    Kwartal = Table.AddColumn(NazwaMiesiaca, "Kwartał", each "Q" & Text.From(Date.QuarterOfYear([Data])), type text),
    DzienTygodnia = Table.AddColumn(Kwartal, "Dzień tygodnia", each Date.DayOfWeekName([Data], "pl-PL"), type text)
in
    DzienTygodnia

Odporność na brakujące kolumny. Krok wybierania albo zmiany nazwy kolumn zgłasza błąd, gdy w nowym eksporcie którejś kolumny zabraknie. Dodatkowy parametr MissingField.UseNull zamiast błędu tworzy pustą kolumnę, a MissingField.Ignore pomija brakującą:

= Table.SelectColumns(Źródło, {"Data", "Sklep", "Kod produktu", "Wartość netto"}, MissingField.UseNull)

Po takiej zmianie raport się odświeży, a pusta kolumna od razu pokaże w profilu, że coś w źródle się zmieniło. To lepsze niż raport, który rano nie działa wcale, ale nie zastępuje kontroli: brak kolumny to zawsze sygnał, że trzeba porozmawiać z właścicielem eksportu.

Parametry i funkcje niestandardowe

Parametr ścieżki folderu

Nasze zapytanie ma ścieżkę C:\Dane\Nordvella\Sprzedaz_miesieczna wpisaną na sztywno. Gdy plik trafi do kolegi, który trzyma dane w innym folderze, zapytanie przestanie działać. Rozwiązaniem jest parametr, czyli nazwana wartość, którą zmienisz w jednym miejscu.

1. Na karcie Strona główna rozwiń Zarządzaj parametrami i wybierz Nowy parametr. Ustaw nazwę FolderSprzedazy, krótki opis, typ Tekst i w polu Wartość bieżąca wklej ścieżkę folderu.

2. W zapytaniu Sprzedaz_miesieczna kliknij koło zębate przy kroku Źródło. W oknie Folder zmień rodzaj wartości z Tekst na Parametr (mała lista po lewej stronie pola ścieżki) i wybierz FolderSprzedazy.

Parametry są też podstawą przełączania środowisk (serwer testowy i produkcyjny jako dwie wartości jednego parametru) oraz odświeżania przyrostowego w Power BI, gdzie Power Query wymaga dwóch parametrów o ściśle określonych nazwach. Pokażemy to w lekcji o odświeżaniu przyrostowym.

Własna funkcja: kurs waluty z NBP

Funkcja w Power Query to zapytanie, które przyjmuje parametry i zwraca wynik. Jedną już masz: Przekształć plik powstała przy łączeniu folderu. Teraz napiszemy własną, która dla kodu waluty zwraca średni kurs NBP. Korzysta z zapytania Kursy_NBP, które zbudujemy w lekcji o źródłach danych.

3. Utwórz puste zapytanie (Nowe źródło > Inne źródła > Puste zapytanie), otwórz Edytor zaawansowany, zastąp całą zawartość poniższym kodem i kliknij Gotowe. Nazwij zapytanie fxKursNBP.

// Zwraca średni kurs NBP (tabela A) dla podanego kodu waluty, np. "EUR"
(kodWaluty as text) as nullable number =>
let
    Kursy = Kursy_NBP,
    Wiersz = Table.SelectRows(Kursy, each [code] = Text.Upper(Text.Trim(kodWaluty))),
    Kurs = if Table.IsEmpty(Wiersz) then null else Wiersz{0}[mid]
in
    Kurs

Power Query rozpozna funkcję i zamiast danych pokaże formularz do jej przetestowania:

4. Przez Wprowadź dane utwórz małą tabelę zamówień w walutach z kolumnami Waluta i Wartość zamówienia (u nas EUR, USD i CNY, zapytanie tZamowienia_walutowe). Dodaj do niej kolumnę: na karcie Dodaj kolumnę kliknij Wywołaj funkcję niestandardową, nazwij kolumnę Kurs NBP, wybierz funkcję fxKursNBP i jako kodWaluty wskaż kolumnę Waluta.

Odporna formuła: try ... otherwise

5. Dodaj jeszcze kolumnę niestandardową Wartość w PLN. Formuła używa konstrukcji try ... otherwise: jeśli obliczenie się nie uda, na przykład gdy w kolumnie wartości trafi tekst zamiast liczby, wynik nie wywali błędu, tylko zwróci wartość z otherwise. Waluta, której NBP nie publikuje, błędu nie da: funkcja zwróci null, a mnożenie przez null też daje null.

= try Number.Round([Wartość zamówienia] * [Kurs NBP], 2) otherwise null

Nowsze wersje Power Query obsługują też try ... catch (e) => ..., gdzie w catch masz dostęp do rekordu błędu i możesz na przykład zwrócić jego komunikat do osobnej kolumny. Do większości zastosowań wystarcza jednak try ... otherwise.

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

✕