Introducere în Power Query M Code

Power Query este un instrument foarte puternic de extragere și transformare a datelor care vine integrat în Excel 2016 (sau mai târziu), Excel pentru Office 365 și Power BI.

Se găsește în fila Date în secțiunea Obține & Transformare date din panglică.

Este foarte puternic și, de asemenea, foarte ușor de utilizat, iar editorul de interogare are o interfață de utilizator super intuitivă pentru un utilizator Excel. Mulți pași de transformare pot fi realizați cu ușurință de pe panglica editorului de interogare puternică și nu este nevoie să cunoașteți niciun cod pentru a vă curăța și modela datele.

În spatele scenei editorului ușor de utilizat, Excel traduce fiecare pas din procesul de transformare din acele comenzi de pe panglică în limbajul de cod al editorului de interogare puternică M.

Această postare vă va prezenta elementele de bază ale limbajului de cod M al power query și va presupune că știți deja elementele de bază ale power query.

Ce este codul M?

M vine de la data Mash-up, deoarece power query se referă la conectarea la diverse surse de date diferite și „Mashing” (amestecarea) lor.

Codul M este limbajul din spatele scenei power query. Atunci când creați o transformare de date în interfața de utilizare a editorului power query, Excel scrie codul M corespunzător pentru interogare.

M este un limbaj funcțional, ceea ce înseamnă că este scris în principal cu funcții care sunt apelate pentru a evalua și a returna rezultate. Codul M vine cu o bibliotecă foarte mare de funcții predefinite disponibile și puteți, de asemenea, să vă creați propriile funcții.

Unde puteți scrie codul M al Power Query?

Dacă doriți să începeți să scrieți sau să editați cod M, va trebui să știți unde puteți face acest lucru. Există două locuri în care acest lucru este posibil, în bara de formule sau în editorul avansat.

Bara de formule

Pentru fiecare pas creat în interfața de utilizare a editorului, puteți vizualiza codul M corespunzător în bara de formule.

Dacă nu vedeți bara de formule, mergeți la fila View (Vizualizare) și asigurați-vă că opțiunea Formula Bar (Bară de formule) este bifată.

Puteți edita codul M pentru orice pas dintr-o interogare făcând clic în formulă și editând codul existent. Când ați terminat, puteți accepta orice modificări fie făcând clic pe semnul de verificare, fie apăsând Enter. De asemenea, puteți renunța la modificări făcând clic pe X sau apăsând Esc.

De asemenea, puteți crea pași complet noi în interogare cu ajutorul barei de formule, făcând clic pe simbolul fx de lângă bara de formule. Acest lucru va crea un nou pas care face trimitere la pasul anterior prin nume și apoi puteți crea orice cod M de care aveți nevoie.

Editorul avansat

Bara de formule afișează doar codul M pentru pasul selectat în mod curent în interogare, dar editorul avansat este locul unde puteți vizualiza și edita codul M pentru întreaga interogare.

Puteți deschide editorul avansat din două locuri din panglica editorului. Fie din fila Home (Acasă), fie din fila View (Vizualizare), apăsați butonul Advanced Editor (Editor avansat).

În ciuda apelativului „avansat”, editorul este cel mai elementar editor de cod pe care îl veți vedea și nu conține (încă) nicio funcție de completare automată Intellisense, evidențiere a sintaxei sau formatare automată.

Editorul avansat va afișa numele interogării, va afișa codul M pentru interogare și va afișa un avertisment cu privire la orice încălcări de sintaxă din codul M. Asta este tot!

Biblioteca de funcții standard

Din moment ce codul M este un limbaj funcțional, totul se rezumă la funcții, iar codul M vine cu o bibliotecă mare de funcții predefinite numită biblioteca standard.

Informații despre toate funcțiile disponibile din biblioteca standard pot fi găsite pe pagina web Power Query M Reference a Microsoft, inclusiv sintaxa funcțiilor și exemple.

Biblioteca standard poate fi, de asemenea, explorată din editorul Power Query folosind cuvântul cheie #shared.

Când este introdusă în bara de formule, puteți explora apoi toate funcțiile disponibile făcând clic pe cuvântul Function din dreapta numelui funcției. Veți găsi aceeași sintaxă și exemple ca și în pagina web de referință.

Case Sensi Sensitivity

Unul dintre primele lucruri de care cineva trebuie să fie conștient atunci când scrie cod M este faptul că este un limbaj care ține cont de majuscule și minuscule.

Acest lucru înseamnă că x nu este același lucru cu X sau „abc” nu este același lucru cu „ABC”. Acest lucru este valabil pentru orice valori, variabile, funcții etc.

Expresii și valori în Power Query

Power Query se referă la expresii și valori.

O expresie este ceva care poate fi evaluat pentru a returna o valoare în Power Query. 1 + 1 este o expresie care se evaluează la valoarea 2.

O valoare este o singură bucată de date. Valorile pot fi valori unice, cum ar fi numere, text, logic, null, binar, data, ora, datatime, datatimezone sau durate.

Valorile pot avea, de asemenea, structuri mai complexe decât valori unice, cum ar fi liste, înregistrări și tabele. De asemenea, puteți avea valori care sunt o combinație de liste, înregistrări și tabele. Listele de liste, tabelele de liste, tabelele de tabele etc… sunt toate structuri de valori posibile.

Valori literale unice

Valorile literale unice sunt blocul de bază al tuturor celorlalte valori.

• 123.45 este o valoare numerică.
• „Hello World!” este o valoare textuală.
• true este o valoare logică.
• null reprezintă absența unei valori.

Valori intrinseci unice

Valorile intrinseci se construiesc cu ajutorul diferitelor funcții intrinseci.

• #time(ore, minute, secunde)
• #date(ani, luni, zile)
• #datetime(ani, luni, zile, ore, minute, secunde)
• #datetimezone( ani, luni, zile, ore, minute, secunde, offset-hours, offset-minutes)
• #duration(days, hours, minutes, seconds)

De exemplu, pentru a construi data 2018-12-31 ar trebui să o construiți folosind funcția intrinsecă #date(2018, 12, 31).

Valori structurate

Liste

O listă este o secvență ordonată de valori.

Puteți defini o listă folosind paranteze curbe. {1, 2, 3} este o listă care conține numerele 1, 2 și 3. Deoarece ordinea este importantă, aceasta nu este aceeași listă cu {3, 2, 1}.

{„Hello”, „World”} este o listă care conține textele „Hello” și „World”.

Listele de liste sunt, de asemenea, posibile, astfel încât {{1, 2}, {3, 4, 5}} este o listă de două liste. Prima listă conține numerele 1 și 2, iar a doua listă conține numerele 3, 4 și 5.

Se pot crea liste secvențiale folosind formatul {x..y}. {2..5} va produce lista {2, 3, 4, 5}. Acest lucru funcționează, de asemenea, și pentru caractere de text. {„a”.. „d”} va produce lista {„a”, „b”, „c”, „d”}.

De asemenea, puteți avea o listă fără elemente, {} este lista goală.

Din moment ce listele sunt ordonate, putem face referire la elementele din listă cu un număr de index bazat pe zero. {1, 2, 3}{2} va fi evaluat la 3, deoarece acesta este al doilea element din listă (pe baza unui index zero).

Înregistrări

O înregistrare este o secvență ordonată de câmpuri. Fiecare câmp este format dintr-un nume de câmp care identifică în mod unic câmpul și o valoare de câmp care poate fi orice tip de valoare.

Puteți defini o înregistrare folosind paranteze pătrate. este o înregistrare cu două câmpuri. Primul câmp din înregistrare are un nume de câmp FirstName și valoarea „John”. Al doilea câmp din înregistrare are un nume de câmp „Age” și o valoare de 38.

Înregistrările de înregistrări sunt, de asemenea, posibile, ] este o înregistrare cu un singur câmp cu un nume de câmp „Person” și o valoare de câmp care este o înregistrare.

Înregistrările goale sunt, de asemenea, posibile, este înregistrarea goală.

Puteți face referire la valoarea câmpului dintr-o înregistrare prin numele câmpului. se va evalua la „John”.

Tabele

Un tabel este o secvență ordonată de rânduri în care fiecare rând este o listă.

Tabelele pot fi construite numai folosind o funcție intrinsecă. Puteți construi un tabel folosind funcția #table() pornind de la o listă de titluri de coloane și o listă de rânduri.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) va crea un tabel cu 2 coloane, 3 rânduri și cu titlurile de coloane Litere și Numere.

Este posibil să creați un tabel gol folosind liste goale în funcția intrinsecă #table(). #table({}, {}) va produce un tabel gol.

Puteți face referire la orice valoare dintr-un tabel cu ajutorul indicelui de rând bazat pe zero și a numelui titlului de coloană. se va evalua la „C”, deoarece acesta este al doilea rând (bazat pe un indice zero) al coloanei Litere.

Expresii

Expresiile sunt orice poate fi evaluat la o valoare. Acest lucru este valabil și pentru valorile în sine. De exemplu, expresia 1 se evaluează la valoarea 1.

Deși de obicei vă gândiți la expresii ca fiind formate din operații sau funcții mai complexe.

De exemplu:

• Expresia 1 + 1 se evaluează la 2.
• Expresia 3 > 2 se evaluează la adevărat.
• Expresia „Hello ” & „World” se evaluează la „Hello World”.
• Expresia Text.Upper(„Hello World”) se evaluează la „HELLO WORLD”.

Operatori

Pe lângă biblioteca standard, codul M are, de asemenea, o listă de funcții speciale numite operatori. Aceștia iau exact două valori (sau expresii care se evaluează la o valoare) și returnează o singură valoare.

Aritmetică

Codul M vine cu operatorii aritmetici de bază la care vă așteptați și cu care sunteți obișnuiți din Excelul obișnuit +, -, * și /. Aceștia vă vor permite să adunați, să scădeți, să înmulțiți și, respectiv, să împărțiți valori.

Aceștia pot fi folosiți cu diverse alte tipuri de valori în afară de numere. De exemplu, puteți adăuga o durată la o dată.

#date(2018,12,25) + #durata(7, 0, 0, 0, 0) se va evalua la 2019-01-01.

Comparație

Puteți compara valori în codul M folosind operatorii de comparație <, >, <=, >=, =, <>.

• x < y va fi evaluat la adevărat dacă x este mai mic decât y.
• x > y va fi evaluat la adevărat dacă x este mai mare decât y.
• x <= y va fi evaluat la adevărat dacă x este mai mic sau egal cu y.
• x >= y va fi evaluat la adevărat dacă x este mai mare sau egal cu y.
• x = y se va evalua la adevărat dacă x este egal cu y.
• x <> y se va evalua la adevărat dacă x nu este egal cu y.

Acestea pot fi folosite cu diferite tipuri de valori. De exemplu, puteți compara două liste cu operatorul egal.

{1,2,3,4} = {1,2,3} se va evalua la fals deoarece listele nu sunt identice.

Concatenare și fuziune

Puteți concatena text și fuziona liste, înregistrări și tabele folosind operatorul ampersand &.

De exemplu:

"Hello " & "World" se va evalua la „Hello World”.

{1,2,3} & {3,4,5} se va evalua la {1,2,3,3,3,4,5}.

Logic

Puteți efectua operații asupra valorilor booleene (sau expresii care se evaluează la valori booleene) cu operatorii not, and și și or.

Comentarea codului

Așa cum v-ați aștepta de la orice limbaj de programare, este posibil să adăugați comentarii la codul dumneavoastră.

Există două tipuri de comentarii posibile în codul M. Comentariile pe o singură linie și comentariile pe mai multe linii.

Comentariile pe o singură linie

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

Un comentariu pe o singură linie poate fi creat prin precedarea comentariului cu două caractere slash înainte //. Tot ceea ce se află pe aceeași linie înainte de aceasta va fi interpretat ca un cod M, iar tot ceea ce urmează va fi interpretat ca un comentariu.

Comentarii pe mai multe rânduri

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

Un comentariu pe mai multe rânduri poate fi creat prin plasarea comentariului între caracterele /* și */. Tot ceea ce se află în afara acestora va fi interpretat ca un cod M. Tot ceea ce se află între acestea va fi interpretat ca un comentariu.

Expresia let

Expresia let permite ca un set de valori să fie evaluat și atribuit unor nume de variabile, apoi utilizat într-o expresie ulterioară care urmează expresiei in.

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

Această expresie este alcătuită din trei expresii care sunt evaluate după expresia let. Fiecare expresie este separată de o virgulă, cu excepția ultimei expresii care precede instrucțiunea in. În acest exemplu, întreaga expresie let și in va fi evaluată la 3.

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

S-ar putea să vă gândiți că expresiile din cadrul unei instrucțiuni let trebuie să apară în ordinea în care trebuie evaluate. Acest lucru nu este cazul! Codul de mai sus este perfect valid și va evalua, de asemenea, la 3. Evaluatorul de cod M va calcula automat ordinea calculelor necesare pe baza dependențelor expresiilor.

Este evident mai ușor pentru o persoană să citească codul M dacă acesta este scris în ordinea evaluării, dar mai există și un alt avantaj. Expresiile vor apărea ca pași separați în fereastra Pași aplicați. Atunci când sunt scrise în altă ordine, expresiile vor apărea ca un singur pas combinat.

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

De asemenea, puteți evalua expresii în cadrul părții in a unei expresii let… in…

Nume de variabile

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

Puteți atribui aproape orice nume expresiilor dumneavoastră folosind caracterele #””. Puteți folosi chiar și caractere de spațiu și alte caractere speciale. Utilizarea cuvintelor cheie rezervate este singura excepție.

Numele variabilelor sunt cele care vor apărea în pașii aplicați ai editorului de interogări, astfel încât posibilitatea de a utiliza caractere de spațiu este o caracteristică excelentă.

Each Statements

Expresia each este o prescurtare pentru declararea funcțiilor care iau un singur parametru numit _ (subliniere).

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

În acest exemplu creăm o nouă coloană care înmulțește coloana Numbers cu 2 pentru fiecare rând.

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

Potem crea aceeași interogare folosind sintaxa underscore care este semantic echivalentă cu expresia each. Ambele interogări vor funcționa la fel.

If Then Else Statements

Codul M este destul de sărac în comparație cu alte limbaje când vine vorba de expresii logice. Nu sunt disponibile instrucțiuni select case sau loop. Există doar o expresie if… then… else… disponibilă.

if then else 

Sintaxa este simplă și este ca în majoritatea celorlalte limbaje de programare. Poate apărea toată pe o singură linie sau poate fi prezentată pe linii separate pentru a ușura citirea.

Expresii Try Otherwise

Erorile pot apărea atunci când se încearcă efectuarea unor operații care necesită anumite tipuri de date. De exemplu, încercarea de a înmulți un număr cu o valoare text va duce la o eroare.

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

Erorile pot fi evitate prin utilizarea expresiei try… otherwise…..

Aceasta va evita erorile în rezultatele interogării și vă va permite să înlocuiți erorile cu orice valoare sau expresie.

Funcții

O funcție este o corespondență de la un set de valori de parametri la o valoare. Alături de funcțiile din biblioteca standard, codul M vă permite să vă creați propriile funcții.

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

Acestă interogare definește o funcție care înmulțește două numere. Apoi interogarea apelează și evaluează funcția cu valorile 2 și 3, care se evaluează la 6.

Funcții cu parametri opționali

Există două tipuri de parametri de funcție, un parametru obligatoriu și un parametru opțional.

Parametrii obligatorii trebuie să fie întotdeauna specificați atunci când o funcție este invocată.

Parametrii opționali nu trebuie să fie specificați atunci când o funcție este invocată. Dacă parametrul opțional lipsește, atunci valoarea transmisă funcției va fi nulă.

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

Această funcție are un al doilea argument opțional. Apoi, interogarea apelează și evaluează funcția folosind doar parametrul necesar cu valoarea 2, care se evaluează la 2. Observați că funcția trebuie să țină cont de faptul că y este nul, altfel un argument opțional lipsă ar putea duce la o eroare de evaluare a funcției.

Funcții recursive

Este, de asemenea, posibil să se scrie o funcție care se referă la ea însăși prin utilizarea operatorului @ scoping.

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

Succesiunea Fibonacci este un exemplu de funcție definită recursiv. Următorul număr din secvență este definit ca fiind suma celor două numere anterioare. Deci, pentru a obține al n-lea număr, trebuie să cunoașteți al (n-1)-lea și al (n-2)-lea număr.

Această funcție va găsi al n-lea număr Fibonacci prin însumarea (n-1)-lea și (n-2)-lea numere Fibonacci.

Interogarea se evaluează la 13, deoarece 13 este al 7-lea număr Fibonacci.

Iată alte câteva exemple utile în care puteți utiliza funcții recursive pentru a reproduce funcția TRIM din Excel pentru a elimina spațiile în exces dintre cuvinte sau pentru a găsi și înlocui în mare parte valorile pe baza unei liste.

Funcții de interogare

Exemplele de mai sus au definit o funcție în cadrul unei interogări, apoi au apelat și evaluat funcția în cadrul interogării.

Este, de asemenea, posibil să creați o interogare care este o funcție (o funcție de interogare) și care poate fi apelată și evaluată din alte interogări.

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

Acesta este formatul general necesar pentru a crea o funcție de interogare. Rețineți, veți avea nevoie de o instrucțiune let… in… în cadrul instrucțiunii let… in… a funcției de interogare pentru a efectua mai mulți pași.

Concluzii

Power query este o caracteristică grozavă în Excel care vă poate ajuta să automatizați și să simplificați importul și transformarea datelor.

Puteți face multe folosind doar interfața grafică de tip „point and click” fără a atinge vreodată codul M. Dar, pe măsură ce cerințele dumneavoastră devin mai avansate, s-ar putea să apară un moment în care să aveți nevoie să editați codul M pe care l-ați creat sau să vă scrieți propriul cod de la zero.

Care limbaj de programare nou este ilizibil dacă nu cunoașteți mai întâi elementele de bază. Acest ghid sperăm că vă va ajuta să vă familiarizați cu codul M, astfel încât să puteți începe să creați interogări mai avansate.

.