Introduction To Power Query M Code

Power query to bardzo potężne narzędzie do ekstrakcji i transformacji danych, które jest zapiekane w programie Excel 2016 (lub nowszym), Excel dla Office 365 i Power BI.

Można go znaleźć na karcie Dane w sekcji Get & Transform Data na wstążce.

Jest bardzo potężny, a także bardzo łatwy w użyciu, a edytor zapytań ma super intuicyjny interfejs użytkownika dla użytkownika programu Excel. Wiele kroków transformacji można łatwo wykonać z poziomu wstążki edytora zapytań i nie trzeba znać żadnego kodu, aby oczyścić i ukształtować dane.

Za kulisami przyjaznego dla użytkownika edytora, Excel tłumaczy każdy krok w procesie transformacji z tych poleceń wstążki na język kodu Power Query M.

Ten wpis wprowadzi Cię w podstawy języka kodu M power query i zakłada, że znasz już podstawy power query.

Spis treści

Co to jest kod M?

M oznacza data Mash-up, jako że power query polega na łączeniu się z różnymi źródłami danych i „Mashing” ich w jedną całość.

Kod M jest językiem, który kryje się za kulisami power query. Podczas tworzenia transformacji danych w interfejsie użytkownika edytora Power Query, Excel pisze odpowiedni kod M dla zapytania.

M jest językiem funkcjonalnym, co oznacza, że jest pisany głównie za pomocą funkcji, które są wywoływane w celu oceny i zwrócenia wyników. Kod M jest dostarczany z bardzo dużą biblioteką predefiniowanych funkcji i można również tworzyć własne.

Gdzie można napisać kod Power Query M?

Jeśli chcesz zacząć pisać lub edytować kod M, będziesz musiał wiedzieć, gdzie możesz to zrobić. Są dwa miejsca, w których jest to możliwe, na pasku formuły lub w edytorze zaawansowanym.

Pasek formuły

Dla każdego kroku, który jest tworzony w edytorze UI, można zobaczyć odpowiadający mu kod M na pasku formuły.

Jeśli nie widzisz paska formuły, przejdź do zakładki Widok i upewnij się, że opcja Pasek formuły jest zaznaczona.

Możesz edytować kod M dla dowolnego kroku w zapytaniu, klikając na formułę i edytując istniejący kod. Po zakończeniu możesz zaakceptować wszystkie zmiany klikając na znak zaznaczenia lub naciskając Enter. Możesz również odrzucić zmiany klikając na znak X lub naciskając Esc.

Możesz również tworzyć całkowicie nowe kroki w zapytaniu za pomocą paska formuły klikając na symbol fx obok paska formuły. Spowoduje to utworzenie nowego kroku, który odwołuje się do poprzedniego kroku przez nazwę, a następnie można utworzyć dowolny kod M, którego potrzebujesz.

Edytor zaawansowany

Pasek formuły pokazuje tylko kod M dla aktualnie wybranego kroku w zapytaniu, ale edytor zaawansowany jest miejscem, gdzie można zobaczyć i edytować kod M dla całego zapytania.

Edytor zaawansowany można otworzyć z dwóch miejsc na wstążce edytora. Na zakładce Strona główna lub zakładce Widok naciśnij przycisk Edytor zaawansowany.

Mimo nazwy „zaawansowany”, edytor ten jest najbardziej podstawowym edytorem kodu jaki zobaczysz i nie zawiera (jeszcze) żadnych funkcji automatycznego uzupełniania intellisense, kolorowania składni czy automatycznego formatowania.

Edytor zaawansowany wyświetli nazwę zapytania, pokaże kod M dla zapytania i wyświetli ostrzeżenie o jakimkolwiek naruszeniu składni w kodzie M. To wszystko!

Biblioteka funkcji standardowych

Ponieważ kod M jest językiem funkcjonalnym, wszystko sprowadza się do funkcji, a kod M jest dostarczany z dużą biblioteką predefiniowanych funkcji zwaną biblioteką standardową.

Informacje na temat wszystkich dostępnych funkcji biblioteki standardowej można znaleźć na stronie internetowej Microsoft Power Query M Reference, w tym składnię funkcji i przykłady.

Bibliotekę standardową można również poznać z poziomu edytora Power Query, używając słowa kluczowego #shared.

Po wprowadzeniu do paska formuły można następnie poznać wszystkie dostępne funkcje, klikając słowo Funkcja po prawej stronie nazwy funkcji. Znajdziesz tam taką samą składnię i przykłady jak na referencyjnej stronie internetowej.

Wrażliwość na wielkość liter

Jedną z pierwszych rzeczy, o których ktoś musi wiedzieć pisząc kod M jest to, że jest to język wrażliwy na wielkość liter.

To oznacza, że x nie jest tym samym co X lub „abc” nie jest tym samym co „ABC”. To jest prawdziwe dla wszystkich wartości, zmiennych, funkcji itp.

Wyrażenia i wartości w Power Query

Power query jest wszystkim o wyrażeniach i wartościach.

Wyrażenie jest czymś, co może być ocenione, aby zwrócić wartość w Power Query. 1 + 1 jest wyrażeniem, które zwraca wartość 2.

Wartość jest pojedynczym elementem danych. Wartości mogą być pojedynczymi wartościami, takimi jak liczby, tekst, logiczne, null, binarne, data, czas, datetime, datetimezone lub czasy trwania.

Wartości mogą mieć również bardziej złożone struktury niż pojedyncze wartości, takie jak listy, rekordy i tabele. Możesz również mieć wartości, które są kombinacją list, rekordów i tabel. Listy list, tablice list, tablice tablic itd… to wszystkie możliwe struktury wartości.

Pojedyncze wartości literalne

Pojedyncze wartości literalne są podstawowym budulcem wszystkich innych wartości.

123.45 jest wartością liczbową.
„Hello World!” jest wartością tekstową.
true jest wartością logiczną.
null reprezentuje brak wartości.

Pojedyncze wartości wewnętrzne

Wartości wewnętrzne są konstruowane przy użyciu różnych funkcji wewnętrznych.

#time(godziny, minuty, sekundy)
#date(lata, miesiące, dni)
#datetime(lata, miesiące, dni, godziny, minuty, sekundy)
#datetimezone( lata, miesiące, dni, godziny, minuty, sekundy, offset-hours, offset-minutes)
#duration(days, hours, minutes, seconds)

Na przykład, aby skonstruować datę 2018-12-31 musiałbyś skonstruować ją używając funkcji wewnętrznej #date(2018, 12, 31).

Wartości strukturalne

Listy

Lista jest uporządkowaną sekwencją wartości.

Możesz zdefiniować listę używając nawiasów klamrowych. {1, 2, 3} jest listą zawierającą liczby 1, 2 i 3. Ponieważ kolejność jest ważna, nie jest to ta sama lista co {3, 2, 1}.

{„Hello”, „World”} jest listą zawierającą tekst „Hello” i „World”.

Listy list są również możliwe, więc {{1, 2}, {3, 4, 5}} jest listą dwóch list. Pierwsza lista zawiera liczby 1 i 2, a druga zawiera liczby 3, 4 i 5.

Można tworzyć listy sekwencyjne używając formatu {x..y}. {2..5} spowoduje utworzenie listy {2, 3, 4, 5}. Działa to również dla znaków tekstowych. {„a”… „d”} wytworzy listę {„a”, „b”, „c”, „d”}.

Możesz również mieć listę bez elementów, {} jest pustą listą.

Ponieważ listy są uporządkowane, możemy odwoływać się do elementów na liście za pomocą numeru indeksu opartego na zerze. {1, 2, 3}{2} będzie odpowiadać 3, ponieważ jest to druga pozycja na liście (na podstawie zerowego indeksu).

Rekordy

Rekord jest uporządkowaną sekwencją Pól. Każde pole składa się z nazwy pola, która jednoznacznie identyfikuje pole oraz wartości pola, która może być dowolnego typu wartością.

Rekord można zdefiniować używając nawiasów kwadratowych. jest rekordem z dwoma polami. Pierwsze pole w rekordzie ma nazwę FirstName i wartość „John”. Drugie pole w rekordzie ma nazwę pola Wiek i wartość 38.

Rekordy rekordów są również możliwe, ] jest rekordem z jednym polem o nazwie pola Osoba i wartości pola, które jest rekordem.

Puste rekordy są również możliwe, jest pustym rekordem.

Możesz odwołać się do wartości pola w rekordzie przez jego nazwę. będzie odpowiadać „John”.

Tabele

Tabela jest uporządkowaną sekwencją wierszy, gdzie każdy wiersz jest listą.

Tabele mogą być skonstruowane tylko przy użyciu funkcji wewnętrznych. Możesz skonstruować tabelę używając funkcji #table() z listy nagłówków kolumn i listy wierszy.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}})utworzy tabelę z 2 kolumnami, 3 wierszami i nagłówkami kolumn Litery i Liczby.

Możliwe jest utworzenie pustej tabeli używając pustych list w wewnętrznej funkcji #table(). #table({}, {}) wytworzy pustą tabelę.

Możesz odwołać się do dowolnej wartości w tabeli za pomocą indeksu wiersza opartego na zerze i nazwy nagłówka kolumny. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} będzie odpowiadać „C”, ponieważ jest to drugi wiersz (na podstawie indeksu zerowego) kolumny Letters.

Wyrażenia

Wyrażenia są wszystkim, co może być obliczone na wartość. Jest to prawda o samych wartościach. Na przykład, wyrażenie 1 oblicza wartość 1.

Ale zazwyczaj myślisz o wyrażeniach jako o bardziej złożonych operacjach lub funkcjach.

Na przykład:

Wyrażenie 1 + 1 oblicza wartość 2.
Wyrażenie 3 > 2 oblicza wartość true.
Wyrażenie „Hello ” & „World” zwraca wartość „Hello World”.
Wyrażenie Text.Upper(„Hello World”) zwraca wartość „HELLO WORLD”.

Operatory

Wraz z biblioteką standardową, kod M posiada również listę specjalnych funkcji zwanych operatorami. Przyjmują one dokładnie dwie wartości (lub wyrażenia, które obliczają wartość) i zwracają pojedynczą wartość.

Arytmetyka

Kod M jest wyposażony w podstawowe operatory arytmetyczne, których można się spodziewać i do których jesteś przyzwyczajony z regularnych operatorów Excela +, -, * i /. Pozwolą ci one odpowiednio dodawać, odejmować, mnożyć i dzielić wartości.

Mogą one być używane z różnymi innymi typami wartości niż tylko liczby. Na przykład, możesz dodać czas trwania do daty.

#date(2018,12,25) + #duration(7, 0, 0, 0, 0) oceni się na 2019-01-01.

Porównywanie

Możesz porównywać wartości w kodzie M używając operatorów porównania <, >, <=, >=, =, <>.

x < y zostanie ocenione jako prawdziwe, jeśli x jest mniejsze niż y.
x > y zostanie ocenione jako prawdziwe, jeśli x jest większe niż y.
x <= y will evaluate to true if x is less than or equal to y.
x >= y will evaluate to true if x is greater than or equal to y.
x = y będzie oceniane jako prawdziwe, jeśli x jest równe y.
x <> y będzie oceniane jako prawdziwe, jeśli x nie jest równe y.

Można ich używać z różnymi typami wartości. Na przykład można porównać dwie listy za pomocą operatora równości.

{1,2,3,4} = {1,2,3} zakończy się wynikiem false, ponieważ listy nie są takie same.

Konkatenacja i łączenie

Można konkatenować tekst i łączyć listy, rekordy i tablice za pomocą operatora ampersand &.

Na przykład:

"Hello " & "World" zakończy się wynikiem „Hello World”.

{1,2,3} & {3,4,5} będzie miało wartość {1,2,3,3,4,5}.

Logiczne

Możesz wykonywać operacje na wartościach logicznych (lub wyrażeniach, które mają wartość logiczną) za pomocą operatorów not, and oraz or.

Komentowanie kodu

Jak można się spodziewać po każdym języku programowania, możliwe jest dodawanie komentarzy do kodu.

W kodzie M możliwe są dwa rodzaje komentarzy. Komentarze jednoliniowe i komentarze wieloliniowe.

Komentarze jednoliniowe

M code goes hereM code goes here //This is a single line commentM code goes here

Komentarz jednoliniowy może być utworzony przez poprzedzenie komentarza dwoma znakami ukośnika //. Wszystko w tej samej linii przed tym będzie interpretowane jako kod M, wszystko po tym będzie interpretowane jako komentarz.

Komentarze wielowierszowe

M code goes here /*This is a commenton multiple lines*/ M code goes here

Komentarz wielowierszowy może być utworzony przez umieszczenie komentarza między znakami /* i */. Wszystko poza nimi będzie interpretowane jako kod M. Wszystko pomiędzy nimi będzie interpretowane jako komentarz.

Instrukcja let

Konstrukcja let pozwala na obliczenie zestawu wartości i przypisanie ich do nazw zmiennych, a następnie użycie ich w kolejnym wyrażeniu, które następuje po instrukcji in.

let a = 1, b = 2, c = a + bin c

To wyrażenie składa się z trzech wyrażeń, które są obliczane po instrukcji let. Każde wyrażenie jest oddzielone przecinkiem, z wyjątkiem ostatniego przed instrukcją in. W tym przykładzie całe wyrażenie let i in będzie obliczone na 3.

let c = a + b, b = 2, a = 1in c

Możesz pomyśleć, że wyrażenia wewnątrz instrukcji let muszą pojawić się w kolejności, w jakiej mają być obliczone. To nie jest prawda! Powyższy kod jest całkowicie poprawny i również zostanie obliczony na 3. Ewaluator kodu M automatycznie obliczy kolejność potrzebnych obliczeń w oparciu o zależności wyrażeń.

Jest oczywiście łatwiejsze dla osoby czytającej kod M, jeśli jest on napisany w kolejności obliczania, ale jest również inna zaleta. Wyrażenia będą wyświetlane jako oddzielne kroki w oknie Applied Steps. Jeśli wyrażenia są napisane bez kolejności, pojawią się jako jeden połączony krok.

let a = 1, b = 2in a + b

Możesz również oceniać wyrażenia wewnątrz części in wyrażenia let… in…

Nazwy zmiennych

let #"Is this a variable name? Wow!!!" = 1 + 1in #"Is this a variable name? Wow!!!"

Możesz przypisać dowolną nazwę do swoich wyrażeń używając znaków #””. Możesz nawet używać znaków spacji i innych znaków specjalnych. Używanie zarezerwowanych słów kluczowych jest jedynym wyjątkiem.

Nazwy zmiennych są tym, co pojawi się w Stosowanych krokach edytora zapytań, więc możliwość używania znaków spacji jest wspaniałą cechą.

Każde wyrażenie

Każde wyrażenie jest skrótem do deklarowania funkcji przyjmujących pojedynczy parametr o nazwie _ (podkreślenie).

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", each 2*)in #"Added Custom"

W tym przykładzie tworzymy nową kolumnę, która mnoży kolumnę Numbers przez 2 dla każdego wiersza.

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_)in #"Added Custom"

Możemy utworzyć to samo zapytanie używając składni podkreślenia, która jest semantycznie równoważna wyrażeniu each. Oba zapytania będą działać tak samo.

If Then Else Statements

M kod jest dość skąpy w porównaniu z innymi językami, jeśli chodzi o wyrażenia logiczne. Nie ma żadnych wyrażeń select case ani pętli. Jest tylko wyrażenie if… then… else… dostępne.

if then else

Składnia jest prosta i jest jak w większości innych języków programowania. Może ona występować cała w jednej linii lub może być przedstawiona w oddzielnych liniach dla ułatwienia czytania.

Try Otherwise Statements

Błędy mogą się zdarzyć, gdy próbujemy wykonać operacje wymagające określonych typów danych. Na przykład próba pomnożenia liczby przez wartość tekstową spowoduje wystąpienie błędu.

let Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}), #"Added Custom" = Table.AddColumn(Source, "Product", each try * otherwise 0)in #"Added Custom"

Błędów można uniknąć stosując wyrażenie try… otherwise….

Unikniesz w ten sposób błędów w wynikach zapytania i będziesz mógł zastąpić błędy dowolną wartością lub wyrażeniem.

Funkcje

Funkcja jest odwzorowaniem ze zbioru wartości parametrów na wartość. Wraz z funkcjami w bibliotece standardowej, kod M pozwala na tworzenie własnych funkcji.

let Product = (x,y) => x * y, Result = Product(2,3)in Result

To zapytanie definiuje funkcję, która mnoży dwie liczby. Następnie zapytanie wywołuje i ocenia funkcję z wartościami 2 i 3, która ocenia na 6.

Funkcje z parametrami opcjonalnymi

Istnieją dwa typy parametrów funkcji, parametr wymagany i parametr opcjonalny.

Parametry wymagane muszą być zawsze określone, gdy funkcja jest wywoływana.

Parametry opcjonalne nie muszą być określone, gdy funkcja jest wywoływana. Jeśli brakuje parametru opcjonalnego, to wartość przekazana do funkcji będzie równa null.

let Product = (x, optional y) => if y is null then x else x * y, Result = Product(2)in Result

Ta funkcja ma opcjonalny drugi argument. Następnie zapytanie wywołuje i ocenia funkcję używając tylko wymaganego parametru z wartością 2, która ocenia na 2. Zauważ, że funkcja musi uwzględniać, że y jest null, w przeciwnym razie brakujący opcjonalny argument może spowodować, że funkcja ocenia na błąd.

Funkcje rekursywne

Możliwe jest również napisanie funkcji, która odnosi się do samej siebie, używając operatora zakresów @.

let Fibonacci = (n) => if n = 1 then 1 else if n = 2 then 1 else @Fibonacci(n-1) + @Fibonacci(n-2), Result = Fibonacci(7)in Result

Sekwencja Fibonacciego jest przykładem funkcji, która jest zdefiniowana rekursywnie. Następna liczba w sekwencji jest zdefiniowana jako suma dwóch poprzednich liczb. Zatem aby otrzymać n-tą liczbę, musisz znać (n-1)trzecią i (n-2)trzecią liczbę.

Ta funkcja znajdzie n-tą liczbę Fibonacciego przez dodanie (n-1)trzeciej i (n-2)trzeciej liczby Fibonacciego.

Zapytanie da wynik 13, ponieważ 13 jest siódmą liczbą Fibonacciego.

Jest jeszcze kilka użytecznych przykładów, w których można użyć funkcji rekurencyjnych do replikacji funkcji TRIM Excela w celu usunięcia nadmiaru spacji między słowami lub masowego wyszukiwania i zastępowania wartości na podstawie listy.

Funkcje zapytań

Powyższe przykłady definiują funkcję wewnątrz zapytania, a następnie wywołują i oceniają funkcję wewnątrz zapytania.

Możliwe jest również stworzenie zapytania, które jest funkcją (funkcja zapytania) i może być wywoływane i oceniane z innych zapytań.

let FunctionResult = (Argument1, Argument2,...) => let /*M code to evaluate in the function goes here*/ in Resultin FunctionResult

To jest ogólny format potrzebny do stworzenia funkcji zapytania. Uwaga, będziesz potrzebował instrukcji let… in… w ramach instrukcji let… in… funkcji zapytania w celu wykonania wielu kroków.

Wnioski

Power query jest wspaniałą funkcją w Excelu, która może pomóc Ci zautomatyzować i uprościć import i transformację danych.

Możesz zrobić wiele używając tylko graficznego interfejsu typu „wskaż i kliknij” bez dotykania kodu M. Ale gdy twoje wymagania staną się bardziej zaawansowane, może nadejść czas, gdy będziesz musiał edytować utworzony kod M lub napisać własny od podstaw.

Każdy nowy język programowania jest nieczytelny, jeśli najpierw nie poznasz podstaw. Ten przewodnik, miejmy nadzieję, wprowadzi cię w życie z kodem M, abyś mógł zacząć tworzyć bardziej zaawansowane zapytania.