Tworzenie kolumn niestandardowych w Power Query – jak pisać formuły w M

Wprowadzenie do Power Query i języka M

Power Query to potężne narzędzie dostępne w programach takich jak Microsoft Excel i Power BI, które umożliwia pobieranie, transformację i łączenie danych z różnych źródeł w celu ich dalszej analizy. Dzięki niemu użytkownicy mogą zautomatyzować proces przygotowania danych bez potrzeby ręcznego przetwarzania każdego zestawu informacji.

Jednym z kluczowych elementów działania Power Query jest język M — specjalistyczny język programowania zaprojektowany z myślą o transformacjach danych. Język ten działa „pod maską” Power Query i odpowiada za wszystkie operacje wykonywane na danych, od prostych filtrów po bardziej złożone modyfikacje struktury tabel.

Podstawową zaletą języka M jest jego elastyczność. Choć większość użytkowników korzysta z interfejsu graficznego Power Query, umożliwiającego wykonywanie operacji za pomocą kilku kliknięć, język M pozwala na tworzenie własnych rozwiązań i reguł, które wykraczają poza dostępne opcje w menu. Dzięki temu można pisać własne formuły, tworzyć kolumny niestandardowe i dostosowywać przekształcenia do indywidualnych potrzeb.

Power Query i język M znajdują zastosowanie w wielu scenariuszach, takich jak:

• łączenie danych z różnych plików i źródeł (np. Excel, pliki tekstowe, bazy danych),
• czyszczenie i standaryzacja danych (np. usuwanie duplikatów, zmiana formatu dat),
• tworzenie obliczeń i kolumn pomocniczych bez ingerencji w dane źródłowe,
• automatyzacja powtarzalnych operacji bez konieczności pisania makr VBA.

Dzięki znajomości Power Query oraz języka M analitycy danych, finansowi i biznesowi mogą znacząco skrócić czas przygotowania danych i zwiększyć dokładność analiz, jednocześnie zachowując pełną kontrolę nad procesem transformacji.

Podstawy składni języka M

Język M to funkcjonalny język programowania używany w Power Query do przekształcania i kształtowania danych. Choć na pierwszy rzut oka może wydawać się nietypowy dla osób przyzwyczajonych do języków proceduralnych, jego składnia została zaprojektowana z myślą o efektywnym przetwarzaniu danych krok po kroku. Temat tego artykułu pojawia się w niemal każdej sesji szkoleniowej Cognity – czasem w formie pytania, czasem w formie frustracji.

Podstawowym elementem języka M jest wyrażenie, które można traktować jako pojedynczą instrukcję lub transformację. Język ten jest rozróżnialny pod względem wielkości liter, co oznacza, że małe i wielkie litery mają znaczenie przy definiowaniu nazw zmiennych i funkcji.

W M duży nacisk położony jest na czytelność kodu – każda transformacja danych może być zapisana w przejrzystym i modularnym stylu. Kluczową konstrukcją jest tutaj słowo kluczowe let, które pozwala definiować kolejne kroki przekształcenia danych. Każdy z tych kroków może odwoływać się do wcześniejszych etapów, co sprzyja tworzeniu logicznych i łatwych do zrozumienia łańcuchów transformacji.

Składnia języka M opiera się również na szerokim użyciu funkcji – zarówno wbudowanych, jak i definiowanych samodzielnie. Wiele operacji, takich jak filtrowanie, przekształcanie tekstu czy obliczenia matematyczne, realizowanych jest właśnie przy pomocy funkcji, co czyni język elastycznym i potężnym narzędziem do pracy z danymi.

Warto również zaznaczyć, że M obsługuje różne typy danych, takie jak liczby, tekst, wartości logiczne, listy, rekordy czy tabele, a operacje są często wykonywane w sposób deklaratywny, czyli opisujący co ma być zrobione, a nie jak to zrobić krok po kroku.

Znajomość podstawowych zasad składni języka M jest kluczowa do efektywnego korzystania z Power Query i tworzenia bardziej zaawansowanych transformacji, w tym kolumn niestandardowych.

Tworzenie kolumn niestandardowych – pierwsze kroki

Kolumny niestandardowe w Power Query umożliwiają użytkownikom tworzenie zaawansowanych transformacji danych, które nie są dostępne z poziomu standardowych opcji interfejsu. Dzięki wykorzystaniu języka M, użytkownik może manipulować danymi w sposób precyzyjny i dopasowany do indywidualnych potrzeb.

Dodawanie kolumny niestandardowej pozwala na:

• łączenie i modyfikowanie wartości z innych kolumn,
• tworzenie warunkowych logik (np. instrukcje if),
• wykonywanie obliczeń matematycznych i tekstowych,
• stosowanie funkcji daty i czasu,
• normalizowanie i przygotowywanie danych do dalszej analizy.

W Power Query kolumnę niestandardową dodaje się za pomocą opcji Dodaj kolumnę > Kolumna niestandardowa. Otwiera się wtedy okno edytora formuł, w którym użytkownik wpisuje kod w języku M.

Przykładowo, chcąc połączyć imię i nazwisko z dwóch kolumn oddzielonych spacją, można użyć formuły:

Text.Combine({[Imię], [Nazwisko]}, " ")

W poniższej tabeli przedstawiono kilka podstawowych zastosowań kolumn niestandardowych:

Warto pamiętać, że język M jest czuły na wielkość liter i wymaga precyzyjnej składni. Nawet prosta pomyłka może skutkować błędem w kolumnie, dlatego szczególna uwaga w tej fazie pracy jest kluczowa.

Tworzenie kolumn niestandardowych to pierwszy krok do bardziej zaawansowanej analizy i automatyzacji przekształceń w Power Query. Już na tym etapie warto eksperymentować z prostymi formułami, by lepiej zrozumieć logikę działania języka M. Jeśli chcesz pogłębić swoją wiedzę i poznać więcej praktycznych zastosowań, sprawdź Kurs Microsoft Power Query - analiza danych przy użyciu języka M i optymalizacja procesu analizy danych.

Przykłady prostych formuł i ich zastosowanie

Tworzenie kolumn niestandardowych w Power Query pozwala na dynamiczną modyfikację i rozszerzanie danych. Dzięki prostym formułom w języku M użytkownik może łatwo przeprowadzać podstawowe operacje, dostosowując dane do własnych potrzeb analitycznych. Poniżej przedstawiono kilka typowych przykładów formuł oraz ich zastosowań. Na szkoleniach Cognity pokazujemy, jak poradzić sobie z tym zagadnieniem krok po kroku – poniżej przedstawiamy skrót tych metod.

1. Łączenie tekstu

Scalanie wartości z kilku kolumn tekstowych jest często wykorzystywane do tworzenia unikalnych identyfikatorów lub opisów.

Text.Combine({[Imię], [Nazwisko]}, " ")

Zastosowanie: stworzenie pełnego imienia i nazwiska w jednej kolumnie.

2. Warunki logiczne (IF...THEN...ELSE)

Formuły warunkowe są podstawą filtrowania i przypisywania wartości na podstawie logicznych przesłanek.

if [Wartość] > 100 then "Duża" else "Mała"

Zastosowanie: klasyfikacja danych liczbowych według progu wartości.

3. Wyciąganie fragmentów tekstu

Funkcje tekstowe umożliwiają ekstrakcję określonych części ciągów znaków.

Text.Start([Kod], 3)

Zastosowanie: pobranie prefiksu z kodu produktu.

4. Operacje matematyczne

Podstawowe działania arytmetyczne mogą być wykorzystywane do przeliczania wartości lub tworzenia nowych wskaźników.

[Cena netto] * 1.23

Zastosowanie: obliczenie ceny brutto na podstawie stawki VAT.

5. Przekształcenia dat

Power Query pozwala również na manipulację datami, np. wyodrębnianie roku czy miesiąca.

Date.Year([Data sprzedaży])

Zastosowanie: grupowanie danych według roku sprzedaży.

Podsumowanie porównawcze

Dzięki tym prostym formułom użytkownicy mogą szybko zacząć przekształcać dane w Power Query bez potrzeby zaawansowanego programowania. Ich elastyczność sprawia, że są one przydatne w wielu typowych scenariuszach analizy danych.

Przydatne funkcje do manipulacji danymi

Power Query oferuje szeroki wachlarz funkcji w języku M, które pozwalają na elastyczne przekształcanie danych. Poniżej przedstawiamy wybrane kategorie funkcji, które okazują się szczególnie przydatne podczas tworzenia kolumn niestandardowych.

• Funkcje tekstowe – umożliwiają manipulację ciągami znaków, np. łączenie tekstów, wyodrębnianie fragmentów czy zmiana wielkości liter.
• Funkcje liczbowe – pozwalają na wykonywanie działań arytmetycznych, zaokrąglanie wartości czy porównywanie liczb.
• Funkcje dat i czasu – użyteczne do wyodrębniania elementów daty, takich jak dzień tygodnia, miesiąc czy kwartał, a także do obliczania różnic między datami.
• Funkcje logiczne – umożliwiają warunkowe przekształcenia danych przy użyciu instrukcji takich jak if...then...else.
• Funkcje związane z listami – pozwalają na pracę z kolekcjami wartości, np. filtrowanie, sortowanie lub agregowanie danych.

Poniższa tabela przedstawia kilka przykładów funkcji z różnych kategorii wraz z ich krótkimi opisami:

Wykorzystanie tych funkcji znacząco zwiększa możliwości analizy i transformacji danych w Power Query. Elastyczność języka M pozwala łączyć różne typy funkcji, co umożliwia tworzenie złożonych logik dopasowanych do konkretnych potrzeb użytkownika. Jeśli chcesz poszerzyć swoją wiedzę i sprawnie wykorzystywać język M w praktyce, sprawdź Kurs Język M - Microsoft Business Intelligence - sprawne wykorzystanie Power BI podczas analizy danych i stosowanie języka M.

Typowe błędy podczas tworzenia kolumn i jak ich unikać

Tworzenie kolumn niestandardowych w Power Query przy użyciu języka M daje dużą elastyczność, ale niesie też ryzyko popełnienia typowych błędów, które mogą prowadzić do nieprawidłowych wyników, błędów w zapytaniach lub spadku wydajności. Poniżej przedstawiamy najczęściej występujące problemy oraz sposoby ich unikania.

• Nieprawidłowe odwołania do kolumn
  

  Jednym z najczęstszych błędów jest użycie niewłaściwej składni przy odwoływaniu się do nazw kolumn, szczególnie gdy zawierają one spacje lub znaki specjalne. Np. kolumna "Data sprzedaży" powinna zostać zapisana jako:

  ["Data sprzedaży"]

  Unikaj: [Data sprzedaży] – zadziała tylko, jeśli nie ma spacji.

• Nieprawidłowe porównania wartości typu null
  

  W języku M nie można porównywać wartości do null za pomocą operatorów logicznych (=, <>). Zamiast tego należy używać funkcji Record.FieldValues lub słowa kluczowego is null:

  if [Wartość] = null then "Brak" else [Wartość]  // Błąd!

  if [Wartość] is null then "Brak" else [Wartość]  // Poprawnie

• Niezgodność typów danych
  

  Język M jest typowany, więc operacje na danych muszą uwzględniać ich typy. Próba dodania liczby do tekstu zakończy się błędem:

  "Wynik: " + 123  // Błąd typów

  "Wynik: " & Number.ToText(123)  // Poprawnie

• Nadmierne kopiowanie kodu
  

  Często spotykanym błędem jest duplikowanie tych samych fragmentów kodu w wielu kolumnach zamiast tworzenia funkcji pomocniczych. Powoduje to trudności w konserwacji i zwiększa ryzyko błędów logicznych.

• Brak sprawdzenia wartości przed ich użyciem
  

  Stosowanie funkcji takich jak Text.Middle czy Number.FromText bez wcześniejszego sprawdzenia, czy dane wejściowe są prawidłowe, może prowadzić do błędów w czasie wykonywania. Przed przekształceniem danych warto sprawdzić ich typ lub zawartość:

  if Value.Is([Cena], type text) then Number.FromText([Cena]) else null

Poniższa tabela ilustruje różnice między błędnym a poprawnym podejściem:

Unikanie powyższych błędów pomoże tworzyć bardziej stabilne i czytelne kolumny niestandardowe w Power Query, co z kolei przełoży się na większą efektywność pracy z danymi.

Najlepsze praktyki w pracy z kolumnami niestandardowymi

Tworzenie kolumn niestandardowych w Power Query z wykorzystaniem języka M może znacząco zwiększyć elastyczność i automatyzację przekształcania danych. Aby jednak praca była efektywna i łatwa do utrzymania, warto stosować się do kilku sprawdzonych praktyk.

• Nadawaj czytelne nazwy kolumnom i zmiennym: Ułatwia to zrozumienie działania transformacji, zarówno dla Ciebie, jak i dla innych użytkowników pracujących z tym samym raportem.
• Unikaj złożonych formuł w jednym kroku: Dzielenie skomplikowanych operacji na kilka prostszych kroków sprawia, że kod jest bardziej przejrzysty i łatwiejszy do debugowania.
• Dokumentuj swoje działania: Komentarze i odpowiednio nazwane kroki w edytorze Power Query pomagają w przyszłości zrozumieć logikę przekształceń.
• Testuj formuły etapami: Zamiast pisać rozbudowaną formułę od razu, warto sprawdzić poprawność działania każdego fragmentu osobno.
• Unikaj duplikowania logiki: Jeżeli ta sama operacja wykonywana jest w wielu miejscach, rozważ utworzenie funkcji użytkownika, którą można wielokrotnie wykorzystywać.
• Zwracaj uwagę na typy danych: Niewłaściwe typy mogą prowadzić do błędów lub nieprawidłowych wyników – upewnij się, że dane są odpowiednio przekształcone przed wykonywaniem obliczeń.
• Utrzymuj spójność formatowania kodu: Estetyczny i uporządkowany kod ułatwia jego analizę oraz przyszłe modyfikacje.

Stosując te praktyki, możesz znacznie poprawić jakość swojej pracy w Power Query, a także ułatwić sobie i innym analizę oraz rozwijanie przekształceń danych w przyszłości.

Podsumowanie i dalsze kroki w nauce języka M

Power Query to potężne narzędzie do przekształcania i przygotowywania danych, a język M stanowi jego fundament. Dzięki niemu użytkownicy mogą budować niestandardowe logiki transformacji, które wykraczają poza możliwości standardowych interfejsów graficznych. Język M pozwala na tworzenie własnych funkcji, obsługę warunków, operacje na danych tekstowych, liczbowych i datowych oraz pracę z różnymi strukturami danych, takimi jak listy czy rekordy.

Podstawową różnicą między korzystaniem z interfejsu Power Query a bezpośrednim pisaniem formuł w M jest poziom kontroli i elastyczności. Choć interfejs graficzny pozwala na wiele operacji, formuły w M umożliwiają użytkownikom tworzenie bardziej zaawansowanych i zoptymalizowanych przekształceń, szczególnie w złożonych scenariuszach.

Dalsza nauka języka M otwiera drogę do głębszego zrozumienia struktury zapytań, lepszego zarządzania transformacjami danych oraz automatyzacji powtarzalnych zadań. Warto systematycznie eksplorować dostępne funkcje i eksperymentować z własnymi formułami, by w pełni wykorzystać potencjał Power Query w codziennej pracy z danymi. Podczas szkoleń Cognity pogłębiamy te zagadnienia w oparciu o konkretne przykłady z pracy uczestników.

Najlepsze narzędzia AI do monitorowania przetargów publicznych i komercyjnych 28 listopada 2025

AI w przetargach: porównanie Brainial, Minerva, Tendium, Altura i AutoRFP.ai – który system wybrać w 2025? 26 listopada 2025