Introduzione a Power Query M Code

Power query è uno strumento molto potente di estrazione e trasformazione dei dati che viene inserito in Excel 2016 (o successivo), Excel per Office 365 e Power BI.

Si può trovare nella scheda Dati nella sezione Ottieni & Trasforma dati del nastro.

È molto potente e anche molto facile da usare e l’editor di query ha un’interfaccia utente super intuitiva per un utente Excel. Molti passaggi di trasformazione possono essere facilmente eseguiti dalla barra multifunzione dell’editor di query e non è necessario conoscere alcun codice per pulire e modellare i dati.

Dietro le scene dell’editor facile da usare, Excel sta traducendo ogni passaggio del processo di trasformazione da quei comandi della barra multifunzione nel linguaggio del codice di Power Query M.

Questo post vi introdurrà alle basi del linguaggio di codice M di Power Query e presuppone che conosciate già le basi di Power Query.

Cos’è il codice M?

La M sta per data Mash-up, dato che Power Query si occupa di connettersi a varie fonti di dati diverse e “mischiarle”.

Il codice M è il linguaggio dietro le quinte di Power Query. Quando si crea una trasformazione di dati nell’editor di Power Query, Excel scrive il codice M corrispondente per la query.

M è un linguaggio funzionale, il che significa che è scritto principalmente con funzioni che vengono chiamate per valutare e restituire risultati. Il codice M viene fornito con una vasta libreria di funzioni predefinite disponibili e si può anche creare il proprio.

Dove si può scrivere il codice Power Query M?

Se vuoi iniziare a scrivere o modificare il codice M, devi sapere dove puoi farlo. Ci sono due posti dove è possibile, nella barra della formula o nell’editor avanzato.

La barra della formula

Per ogni passo creato nell’editor UI puoi visualizzare il codice M corrispondente nella barra della formula.

Se non vedi la barra della formula, vai alla scheda Visualizza e assicurati che l’opzione Barra della formula sia selezionata.

Puoi modificare il codice M per ogni passo di una query cliccando nella formula e modificando il codice esistente. Quando hai finito, puoi accettare le modifiche cliccando sul segno di spunta o premendo Invio. Puoi anche scartare le tue modifiche cliccando sulla X o premendo Esc.

Puoi anche creare passi completamente nuovi nella tua query con la barra della formula cliccando sul simbolo fx accanto alla barra della formula. Questo creerà un nuovo passo che fa riferimento al passo precedente per nome e poi puoi creare qualsiasi codice M di cui hai bisogno.

L’editor avanzato

La barra della formula mostra solo il codice M per il passo attualmente selezionato nella query, ma l’editor avanzato è dove puoi visualizzare e modificare il codice M per l’intera query.

Puoi aprire l’editor avanzato da due posti nella barra multifunzione dell’editor. Dalla scheda Home o dalla scheda Visualizza premi il pulsante Editor avanzato.

Nonostante il soprannome “avanzato”, l’editor è l’editor di codice più basilare che vedrai e non contiene (ancora) nessuna funzione di completamento automatico intellisense, evidenziazione della sintassi o formattazione automatica.

L’editor avanzato mostrerà il nome della query, il codice M per la query e un avviso su qualsiasi violazione della sintassi nel codice M. Questo è tutto!

Libreria di funzioni standard

Siccome il codice M è un linguaggio funzionale, è tutto incentrato sulle funzioni e il codice M viene fornito con una grande libreria di funzioni predefinite chiamate libreria standard.

Le informazioni su tutte le funzioni disponibili della libreria standard possono essere trovate sulla pagina web Power Query M Reference di Microsoft, compresa la sintassi delle funzioni e gli esempi.

La libreria standard può essere esplorata anche dall’editor di Power Query usando la parola chiave #shared.

Una volta inseriti nella barra della formula, è possibile esplorare tutte le funzioni disponibili cliccando sulla parola Function a destra del nome della funzione. Troverete la stessa sintassi e gli stessi esempi della pagina web di riferimento.

Sensibilità alle maiuscole e alle minuscole

Una delle prime cose di cui qualcuno deve essere consapevole quando scrive codice M è che si tratta di un linguaggio case sensitive.

Questo significa che x non è la stessa cosa di X o “abc” non è la stessa cosa di “ABC”. Questo è vero per qualsiasi valore, variabile, funzione ecc.

Espressioni e valori in Power Query

Power Query è tutto su espressioni e valori.

Un’espressione è qualcosa che può essere valutata per restituire un valore in Power Query. 1 + 1 è un’espressione che valuta il valore 2.

Un valore è un singolo pezzo di dati. I valori possono essere valori singoli come numeri, testo, logico, null, binario, data, ora, datetime, datetime o durata.

I valori possono anche avere strutture più complesse di valori singoli come elenchi, record e tabelle. Si possono anche avere valori che sono una combinazione di elenchi, record e tabelle. Liste di liste, tabelle di liste, tabelle di tabelle ecc… sono tutte possibili strutture di valori.

Valori letterali singoli

I valori letterali singoli sono il blocco base di tutti gli altri valori.

• 123.45 è un valore numerico.
• “Hello World!” è un valore di testo.
• true è un valore logico.
• null rappresenta l’assenza di un valore.

Valori intrinseci singoli

I valori intrinseci sono costruiti usando le varie funzioni intrinseche.

• #time(ore, minuti, secondi)
• #date(anni, mesi, giorni)
• #datetime(anni, mesi, giorni, ore, minuti, secondi)
• #datetime( anni, mesi, giorni, ore, minuti, secondi, offset-hours, offset-minutes)
• #duration(days, hours, minutes, seconds)

Per esempio, per costruire la data 2018-12-31 dovresti costruirla usando la funzione intrinseca #date(2018, 12, 31).

Valori strutturati

Liste

Una lista è una sequenza ordinata di valori. {1, 2, 3} è una lista contenente i numeri 1, 2 e 3. Poiché l’ordine è importante, questa non è la stessa lista di {3, 2, 1}.

{“Hello”, “World”} è una lista contenente il testo “Hello” e “World”.

Sono possibili anche liste di liste, così {{1, 2}, {3, 4, 5}} è una lista di due liste. La prima lista contiene i numeri 1 e 2 e la seconda lista contiene i numeri 3, 4 e 5.

Si possono creare liste sequenziali usando il formato {x..y}. {2..5} produrrà la lista {2, 3, 4, 5}. Questo funziona anche per i caratteri di testo. {“a”.. “d”} produrrà la lista {“a”, “b”, “c”, “d”}.

Si può anche avere una lista senza elementi, {} è la lista vuota.

Siccome le liste sono ordinate, possiamo fare riferimento agli elementi della lista con un numero di indice basato su zero. {1, 2, 3}{2} valuterà 3 poiché questo è il secondo elemento della lista (basato su un indice zero).

Records

Un Record è una sequenza ordinata di Campi. Ogni campo consiste in un nome di campo che identifica univocamente il campo e un valore di campo che può essere qualsiasi tipo di valore.

Puoi definire un record usando le parentesi quadre. è un record con due campi. Il primo campo del record ha un nome di campo FirstName e un valore di “John”. Il secondo campo nel record ha un nome di campo di Age e un valore di 38.

I record di record sono anche possibili, ] è un record con un campo con un nome di campo di Person e un valore di campo che è un record.

I record vuoti sono anche possibili, è il record vuoto.

Puoi fare riferimento al valore del campo in un record tramite il suo nome di campo. valuterà a “John”.

Tabelle

Una tabella è una sequenza ordinata di righe dove ogni riga è una lista.

Le tabelle possono essere costruite solo usando una funzione intrinseca. Puoi costruire una tabella usando la funzione #table() da una lista di titoli di colonna e una lista di righe.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) creerà una tabella con 2 colonne, 3 righe e i titoli di colonna di Lettere e Numeri.

È possibile creare una tabella vuota usando liste vuote nella funzione intrinseca #table(). #table({}, {}) produrrà una tabella vuota.

È possibile fare riferimento a qualsiasi valore in una tabella con l’indice di riga basato su zero e il nome dell’intestazione di colonna. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2}valuterà a “C” poiché questa è la seconda riga (basata su un indice zero) della colonna Lettere.

Espressioni

Le espressioni sono qualsiasi cosa che può essere valutata a un valore. Questo è vero per i valori stessi. Per esempio, l’espressione 1 valuta il valore 1.

Anche se in genere si pensa alle espressioni come a operazioni o funzioni più complesse.

Per esempio:

• L’espressione 1 + 1 valuta 2.
• L’espressione 3 > 2 valuta vero.
• L’espressione “Hello ” & “World” valuta a “Hello World”.
• L’espressione Text.Upper(“Hello World”) valuta a “HELLO WORLD”.

Operatori

Insieme alla libreria standard, il codice M ha anche una lista di funzioni speciali chiamate operatori. Questi prendono esattamente due valori (o espressioni che valutano un valore) e restituiscono un singolo valore.

Aritmetica

Il codice M ha gli operatori aritmetici di base che vi aspettereste e a cui siete abituati dai regolari Excel +, -, * e /. Questi vi permetteranno rispettivamente di aggiungere, sottrarre, moltiplicare e dividere valori.

Questi possono essere usati con vari altri tipi di valori oltre ai semplici numeri. Per esempio, puoi aggiungere una durata a una data.

#date(2018,12,25) + #duration(7, 0, 0, 0) valuterà 2019-01.

Confronto

Puoi confrontare valori nel codice M usando gli operatori di confronto <, >, <=, >=, =, <>.

• x < y valuterà come vero se x è minore di y.
• x > y valuterà come vero se x è maggiore di y.
• x <= y verrà valutato come vero se x è minore o uguale a y.
• x >= y verrà valutato come vero se x è maggiore o uguale a y.
• x = y valuterà vero se x è uguale a y.
• x <> y valuterà vero se x non è uguale a y.

Questi possono essere usati con vari tipi di valori. Per esempio, puoi confrontare due liste con l’operatore equal.

{1,2,3,4} = {1,2,3} valuterà falso poiché le liste non sono uguali.

Concatenazione e fusione

Puoi concatenare testo e unire liste, record e tabelle usando l’operatore ampersand &.

Per esempio:

"Hello " & "World" valuterà “Hello World”.

{1,2,3} & {3,4,5} valuterà a {1,2,3,3,4,5}.

Logico

Puoi eseguire operazioni su valori booleani (o espressioni che valutano valori booleani) con gli operatori not, and e or.

Commentare il codice

Come ci si aspetta da qualsiasi linguaggio di programmazione, è possibile aggiungere commenti al proprio codice.

Sono possibili due tipi di commenti nel codice M. Commenti a linea singola e commenti a più linee.

Commenti a linea singola

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

Un commento a linea singola può essere creato facendo precedere il commento da due caratteri barra avanti //. Qualsiasi cosa sulla stessa linea prima di questo sarà interpretata come codice M, qualsiasi cosa dopo sarà interpretata come un commento.

Commenti a linee multiple

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

Un commento a linee multiple può essere creato mettendo il commento tra i caratteri /* e */. Qualsiasi cosa al di fuori di questi sarà interpretata come codice M. Qualsiasi cosa tra questi sarà interpretata come un commento.

Dichiarazione Let

La dichiarazione let permette a un insieme di valori di essere valutati e assegnati a nomi di variabili poi usati in una successiva espressione che segue la dichiarazione in.

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

Questa espressione è composta da tre espressioni che sono valutate dopo la dichiarazione let. Ogni espressione è separata da una virgola tranne l’ultima prima dell’istruzione in. In questo esempio l’intera espressione let e in valuterà 3.

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

Potreste pensare che le espressioni all’interno di una dichiarazione let devono apparire nell’ordine in cui devono essere valutate. Questo non è il caso! Il codice di cui sopra è perfettamente valido e valuterà anche a 3. Il valutatore di codice M calcolerà automaticamente l’ordine dei calcoli necessari in base alle dipendenze delle espressioni.

È ovviamente più facile per una persona leggere il codice M se è scritto in ordine di valutazione, ma c’è anche un altro vantaggio. Le espressioni appariranno come passi separati nella finestra dei passi applicati. Quando sono scritte fuori dall’ordine, le espressioni appariranno come un passo combinato.

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

Puoi anche valutare espressioni all’interno della parte in di un’espressione let… in…

Nomi delle variabili

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

Puoi assegnare praticamente qualsiasi nome alle tue espressioni usando i caratteri #””. Potete anche usare caratteri di spazio e altri caratteri speciali. L’uso di parole chiave riservate è l’unica eccezione.

I nomi delle variabili sono ciò che apparirà nei passi applicati dell’editor di query, quindi essere in grado di usare caratteri di spazio è una grande caratteristica.

Ogni espressione

L’espressione ogni è un’abbreviazione per dichiarare funzioni che prendono un singolo parametro chiamato _ (underscore).

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

In questo esempio stiamo creando una nuova colonna che moltiplica la colonna Numeri per 2 per ogni riga.

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

Possiamo creare la stessa query usando la sintassi underscore che è semanticamente equivalente all’espressione each. Entrambe le query funzioneranno allo stesso modo.

Dichiarazioni If Then Else

Il codice M è piuttosto scarno rispetto ad altri linguaggi quando si tratta di espressioni logiche. Non ci sono dichiarazioni select case o loop disponibili. C’è solo un’espressione if… then… else… disponibile.

if then else 

La sintassi è diretta ed è come la maggior parte degli altri linguaggi di programmazione. Può apparire tutta su una linea, o può essere presentata su linee separate per facilità di lettura.

Dichiarazioni Try Otherwise

Gli errori possono verificarsi quando si cerca di eseguire operazioni che richiedono particolari tipi di dati. Per esempio cercando di moltiplicare un numero con un valore di testo si otterrà un errore.

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

Gli errori possono essere evitati usando l’espressione try… otherwise….

Questo eviterà errori nei risultati della vostra query e vi permetterà di sostituire gli errori con qualsiasi valore o espressione.

Funzioni

Una funzione è una mappatura da un insieme di valori di parametri a un valore. Insieme alle funzioni della libreria standard, il codice M vi permette di creare le vostre funzioni.

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

Questa query definisce una funzione che moltiplica due numeri. Poi la query chiama e valuta la funzione con i valori 2 e 3, che valuta 6.

Funzioni con parametri opzionali

Ci sono due tipi di parametri di funzione, un parametro richiesto e un parametro opzionale.

I parametri richiesti devono sempre essere specificati quando viene invocata una funzione.

I parametri opzionali non devono essere specificati quando viene invocata una funzione. Se il parametro opzionale manca, allora il valore passato alla funzione sarà nullo.

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

Questa funzione ha un secondo argomento opzionale. Quindi la query chiama e valuta la funzione usando solo il parametro richiesto con un valore di 2, che valuta 2. Notate che la funzione deve tenere conto del fatto che y è nullo, altrimenti un argomento opzionale mancante potrebbe risultare in una funzione che valuta un errore.

Funzioni ricorsive

È anche possibile scrivere una funzione che si riferisce a se stessa usando l’operatore @ 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

La sequenza di Fibonacci è un esempio di una funzione che è definita ricorsivamente. Il numero successivo nella sequenza è definito come la somma dei due numeri precedenti. Quindi, per ottenere l’ennesimo numero, è necessario conoscere il (n-1)° e (n-2)° numero.

Questa funzione troverà l’ennesimo numero di Fibonacci aggiungendo il (n-1)° e (n-2)° numero di Fibonacci.

La query valuta a 13, poiché 13 è il 7° numero di Fibonacci.

Ecco alcuni esempi più utili in cui è possibile utilizzare le funzioni ricorsive per replicare la funzione TRIM di Excel per rimuovere gli spazi in eccesso tra le parole o trovare e sostituire valori in blocco basati su un elenco.

Funzioni di query

Gli esempi precedenti hanno definito una funzione all’interno di una query e poi hanno chiamato e valutato la funzione all’interno della query.

È anche possibile creare una query che è una funzione (una funzione di query) e può essere chiamata e valutata da altre query.

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

Questo è il formato generale necessario per creare una funzione di query. Nota, avrai bisogno di una dichiarazione let… in… all’interno della dichiarazione let… in… della funzione di query per eseguire più passi.

Conclusioni

Power query è una grande caratteristica in Excel che può aiutarti ad automatizzare e semplificare l’importazione e la trasformazione dei dati.

Puoi fare molto usando solo l’interfaccia grafica punta e clicca senza mai toccare il codice M. Ma man mano che le vostre esigenze diventano più avanzate, potrebbe arrivare il momento in cui dovrete modificare il codice M che avete creato o scriverne uno vostro da zero.

Ogni nuovo linguaggio di programmazione è illeggibile se prima non si conoscono le basi. Questa guida si spera che vi farà conoscere il codice M in modo da poter iniziare a creare query più avanzate.

Si spera che questa guida vi farà conoscere il codice M in modo da poter iniziare a creare query più avanzate.