Power BI – wstęp do języka M czyli LET, IN, listy,rekordy i kilka innych słów kluczowych

Transformacje Power BI dostępne z poziomu interfejsu graficznego są intuicyjne i bardzo proste w użyciu. Jednakże prawdziwa siła tego narzędzia tkwi w języku M pozwalającym osiągnąć niestandardowe funkcjonalności – dziś zrobimy sobie krótkie wprowadzenie do tego języka po to aby rozszerzyć go w kolejnych wpisach – tak więc zapraszam do lektury.

Na samym początku warto wiedzieć, że nasze działania w Power Query transformujące dane generują tak naprawdę polecenia języka M (jest to nazwa nieformalna – oficjalnie język ten nazywa się Power Query Formula Language ale wygodniej będzie posługiwać się krótszą nazwą). Zilustrujmy sobie to na prostym przykładzie – będziemy opierać się o prosty model bazy AdventureWorksDW2014 i tabel w niej zawartych tj. FactInternetSales, DimDate oraz DimProduct. Po pobraniu danych na zakładce View w edytorze zapytań Power BI zaznaczmy opcję Formula Bar aby zobaczyć formułę dla aktualnie zaznaczonego kroku zapytania transformującego.

Jak można zobaczyć na poniższym zrzucie ekranowym krok o nazwie Navigation w zapytaniu ładującym DimDate jest tak naprawdę komendą w języku M:

Powyżej zilustrowano zapytanie dla tylko jednego kroku – aby zobaczyć wszystkie kroki transformujące danego zapytania trzeba na zakładce View kliknąć przycisk Advanced Editor po kliknięciu którego zobaczymy prosty edytor kodu podobny do poniższego.

W tym właśnie oknie mamy cały interesujący nas kod. Okno to nie jest jakoś nadmiernie rozbudowane i pozwala jedynie na wpisanie kodu i sprawdzenie go pod kątem poprawności składniowej.Oprócz wygenerowanego kodu możemy oczywiście dopisywać poszczególne frazy ręcznie lub też zmodyfikować to co już zostało wygenerowane. Ponadto można również napisać zapytanie w M niejako od początku, aby to zrobić wystarczy wybrać na zakładce Home ->Get Data -> Blank Query co pozwoli nam otworzyć puste okno zapytania M. Na ten moment usuńmy wszystko co tam się znajduje i napiszmy nasz pierwszy program w M czyli tradycyjne “Hello World”. Aby to zrobić wystarczy wpisać “Hello World” i zatwierdzić przyciskiem Done. Rezultat naszych działań został przedstawiony poniżej:

Warto zapamiętać, że zapytanie w języku M może zwracać różne typy danych takie jak np. tekst, liczba czy też tabela. Ta ostatnia opcja jest najpopularniejsza lecz nie ma żadnych przeszkód aby zwrócić którykolwiek z innych typów – co właśnie zrobiliśmy. Ikona obok nazwy naszego zapytania wskazuje na typ danych jaki zwraca nasze zapytanie np. poniżej jedno zapytanie zwraca tabelę kolejne liczbę, datę oraz tekst:

Po co nam zapytanie które zwraca pojedynczą wartość zamiast tabeli? Może ono być przydatne w bardzo wielu przypadkach np. podczas parametryzacji zapytań o czym powiemy sobie w późniejszym czasie. Oprócz wpisywania samej wartości wprost, możemy również wpisywać działania arytmetyczne, które zostaną przeliczone przez silnik Power Query np.

Najistotniejszym słowem kluczowym opisywanego języka jest LET. Klauzula ta pozwala rozbić jedną dużą komendę na kilka mniejszych przy czym każda z pomniejszych komend posiada swoją własną nazwę i wartość. Klauzula LET kończy się słowem kluczowym IN, które jednocześnie określa co ma być zwrócone przez zapytanie. Poniżej przedstawiony został zapis w języku M posiadający kilka pomniejszych komend:

W momencie ładowania danych do każdego kroku przypisana zostanie określona wartość, po czym na samym końcu zwrócona zostanie wartość komendy o nazwie Step3 (która jest sumą kroku Step1 oraz Step2). Nie jest obligatoryjne to aby zwracany był ostatni krok – równie dobrze może być zwracany pierwszy krok lub jakaś wartość nie powiązana z wyrażeniami w sekcji LET. W przypadku gdy nazwy naszych komend zawierają spacje musimy je umieścić w cudzysłowie oraz poprzedzić hashem (#). To zachowanie jest niejako analogiczne do kroków zapytania w Power Query, które widzimy w oknie Query Settings w sekcji Applied Steps prawda? Tak! Ponieważ to są właśnie te kroki tylko zapisane w języku M! Po zatwierdzeniu powyższego okna kroki te można zobaczyć poprzez interfejs graficzny:

Tak jak już wspomniałem każdy krok może zwracać dowolny typ oraz tabelę. Oprócz tego występują również takie byty jak listy oraz rekordy. Rekord to nic innego jak tabela z jednym wierszem, Lista z kolei jest to sekwencja pewnych wartości – może być utożsamiana z tablicą. Stwórzmy sobie zatem prostą listę z wartości poszczególnych kroków tak jak zostało to przedstawione poniżej:

Aby stworzyć listę wystarczy zawrzeć poszczególne kroki w pomiędzy nawiasami { }. W powyższym przykładzie jako elementy listy wstawione zostały wartości poszczególnych kroków oraz 100. Na poniższym zrzucie możecie zobaczyć jak wygląda lista w graficznym interfejsie użytkownika- o tym, że jest to lista informuje nas napis List w nagłówku. Ważne jest to żeby takiej listy nie utożsamiać z jednokolumnową tabelą – poniższy zapis jedynie ją przypomina.

Warto również wspomnieć o tym, że zapis {1…10} wygeneruje listę liczb od 1 do 10 – takie automatyczne generowanie liczb może okazać się przydatne w bardzo wielu przypadkach.Oczywiście w przypadku gdy mamy jednokolumnową tabelę bardzo łatwo przekonwertujemy ją na listę. Aby to zademonstrować przejdźmy do tabeli DimDate i usuńmy wszystkie kolumny oprócz FullDayAlternateKey klikając na nią prawym przyciskiem myszy i z menu kontekstowego wybierając Remove Other Columns.  Po tym jak pozostanie nam jedna kolumna zmieńmy jej typ wybierając z menu kontekstowego znajdującym się pod prawym przyciskiem opcję Change Type, a następnie Text. Po tym kroku wejdźmy do zaawansowanego edytora kodu i dopiszmy tam krok o nazwie ConvertToList, który używa funkcji Table.ToList, a jako parametr przyjmuje nazwę poprzedniego kroku ( w moim przypadku #”Changed Type”):

ConvertToList=Table.ToList(#"Changed Type")

Nie zapomnijmy aby nasz krok ConvertToList znalazł się w sekcji IN aby wartość tego kroku została zwrócona przez całe zapytanie. Cały kod wygląda następująco:

let
    Source = Sql.Databases(".\sql16", [CreateNavigationProperties=false]),
    AdventureWorksDW2014 = Source{[Name="AdventureWorksDW2014"]}[Data],
    dbo_DimDate = AdventureWorksDW2014{[Schema="dbo",Item="DimDate"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_DimDate,{"FullDateAlternateKey"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"FullDateAlternateKey", type text}}),
    ConvertToList=Table.ToList(#"Changed Type")
in
    ConvertToList

Po zatwierdzeniu okna edytora kodu nasza jednokolumnowa tabela została zamieniona na listę. W przypadku gdy działamy na liście to na wstążce pojawi się nowa zakładka o nazwie List Tools -> Transform:

Z tego poziomu możemy użyć następujących opcji:

• To Table – przekonwertować listę na tabelę
• Keep Items – przefiltrować wartości według określonej kolejności
• Remove Items – usunąć wartości według określonej kolejności
• Remove Duplicates – usunąć zduplikowane wartości
• Reverse Items – odwrócić kolejność wartości
• Sort – posortować wartości
• Statistics – obliczyć określone statystki związane z wartościami listy

Nic nie stoi na przeszkodzie aby lista jako elementy przyjmowała inne listy – dlatego też poniższy zapis jest w pełni prawidłowy:

{
{1,2,3},
{1},
{12,3}
}

Kolejnym podstawowym typem związanym z językiem M jest rekord. Tak jak zostało już wspomniane wyżej rekord jest po prostu jedno wierszową tabelą lub inaczej zestawem wartości. Aby stworzyć rekord należy użyć zapisu z kwadratowym nawiasem np.

Jak widać również rekord jest wyróżniony graficznie. W przypadku obiektu tego typu jedyną opcją dostępną z poziomu GUI (na moment publikacji tego artykułu) jest konwersja na tabelę:

Możliwe jest np. stworzenie tabeli z rekordów – poniższy zapis stworzy tabelę z listy rekordów:

= Table.FromRecords({
[Imie="Adrian",Nazwisko="Chodkowski",City="Katowice"],
[Imie="Jan",Nazwisko="Kowalski",City="Kraków"],
[Imie="Tomasz",Nazwisko="Nowak",City="Warszawa"]}
)

Oczywiście zarówno tabela, lista jak i rekord posiadają całą innych funkcji pozwalających je przekształcić w praktycznie dowolny sposób.Obiekty te są przydatna w bardzo wielu przypadkach np. podczas wspomnianej wcześniej parametryzacji zapytań – o tym oraz o pisaniu własnych funkcji powiemy sobie w następnym artykule  – zapraszam!