Uso di funzioni personalizzate

Se ci si trova in una situazione in cui è necessario applicare lo stesso set di trasformazioni a query o valori diversi, la creazione di una funzione personalizzata di Power Query che può essere riutilizzata il maggior numero di volte necessario potrebbe essere utile. Una funzione personalizzata di Power Query è un mapping da un set di valori di input a un singolo valore di output e viene creato da funzioni e operatori M nativi.

Sebbene sia possibile creare manualmente una funzione personalizzata di Power Query usando codice come descritto in Informazioni sulle funzioni M di Power Query, l'interfaccia utente di Power Query offre funzionalità per velocizzare, semplificare e migliorare il processo di creazione e gestione di una funzione personalizzata.

Questo articolo è incentrato su questa esperienza, fornita solo tramite l'interfaccia utente di Power Query e su come sfruttare al meglio questa esperienza.

Importante

Questo articolo illustra come creare una funzione personalizzata con Power Query usando trasformazioni comuni accessibili nell'interfaccia utente di Power Query. È incentrato sui concetti di base per creare funzioni personalizzate e collegamenti ad articoli aggiuntivi nella documentazione di Power Query per altre informazioni sulle trasformazioni specifiche a cui si fa riferimento in questo articolo.

Creare una funzione personalizzata da un riferimento a una tabella

Nota

L'esempio seguente è stato creato usando l'esperienza desktop disponibile in Power BI Desktop e può essere seguito anche usando l'esperienza di Power Query disponibile in Excel per Windows.

È possibile seguire questo esempio scaricando i file di esempio usati in questo articolo dal collegamento di download seguente. Per semplicità, questo articolo usa il connettore Folder. Per altre informazioni sul connettore Cartelle, passare a Cartella. L'obiettivo di questo esempio è creare una funzione personalizzata che può essere applicata a tutti i file in tale cartella prima di combinare tutti i dati di tutti i file in una singola tabella.

Per iniziare, usare l'esperienza connettore Cartelle per passare alla cartella in cui si trovano i file e selezionare Trasforma dati o Modifica. Questi passaggi consentono di visualizzare l'esperienza di Power Query. Fare clic con il pulsante destro del mouse sul valore binario scelto dal campo Contenuto e selezionare l'opzione Aggiungi come nuova query . Per questo esempio, la selezione è stata effettuata per il primo file dall'elenco, che si verifica come il file april 2019.csv.

Questa opzione crea in modo efficace una nuova query con un passaggio di spostamento direttamente a tale file come file binario e il nome di questa nuova query è il percorso del file selezionato. Rinominare questa query come File di esempio.

Creare un nuovo parametro con il nome File Parameter e il tipo binary. Usare la query File di esempio come valore predefinito e valore corrente.

Nota

È consigliabile leggere l'articolo sui parametri per comprendere meglio come creare e gestire i parametri in Power Query.

È possibile creare funzioni personalizzate usando qualsiasi tipo di parametro. Non è necessario che alcuna funzione personalizzata abbia un file binario come parametro.

Il tipo di parametro binario viene visualizzato solo all'interno del menu a discesa Tipo di finestra di dialogo Parametri quando si dispone di una query che restituisce un file binario.

È possibile creare una funzione personalizzata senza un parametro. Questo viene comunemente visualizzato negli scenari in cui un input può essere dedotto dall'ambiente in cui viene richiamata la funzione. Ad esempio, una funzione che accetta la data e l'ora correnti dell'ambiente e crea una stringa di testo specifica da tali valori.

Fare clic con il pulsante destro del mouse su Parametro file nel riquadro Query. Selezionare l'opzione Riferimento .

Rinominare la query appena creata dal parametro file (2) in Trasforma file di esempio.

Fare clic con il pulsante destro del mouse su questa nuova query trasforma file di esempio e selezionare l'opzione Crea funzione .

Questa operazione crea in modo efficace una nuova funzione che collega la query Trasforma file di esempio. Tutte le modifiche apportate alla query trasforma file di esempio vengono replicate automaticamente nella funzione personalizzata. Durante la creazione di questa nuova funzione, usare Transform file come nome della funzione.

Dopo aver creato la funzione, si noti che viene creato automaticamente un nuovo gruppo con il nome della funzione. Questo nuovo gruppo contiene:

• Tutti i parametri a cui si fa riferimento nella query del file di esempio transform.
• Query di esempio di trasformazione, comunemente nota come query di esempio.
• La funzione appena creata, in questo caso trasforma il file.

Applicazione di trasformazioni a una query di esempio

Dopo aver creato la nuova funzione, selezionare la query con il nome Trasforma file di esempio. Questa query è ora collegata alla funzione Trasforma file , pertanto tutte le modifiche apportate a questa query vengono riflesse nella funzione . Questa connessione è il concetto di query di esempio collegata a una funzione.

La prima trasformazione che deve verificarsi in questa query è una che interpreta il file binario. È possibile fare clic con il pulsante destro del mouse sul file binario nel riquadro di anteprima e selezionare l'opzione CSV per interpretare il file binario come file CSV .

Il formato di tutti i file CSV nella cartella è lo stesso. Tutti hanno un'intestazione che si estende sulle prime quattro righe. Le intestazioni di colonna si trovano nella riga 5 e i dati iniziano dalla riga 6 verso il basso, come illustrato nell'immagine successiva.

Il set successivo di passaggi di trasformazione che devono essere applicati al file di esempio di trasformazione sono:

1. Rimuovere le prime quattro righe: questa azione elimina le righe considerate parte della sezione intestazione del file.

   

   Nota

   Per altre informazioni su come rimuovere righe o filtrare una tabella in base alla posizione della riga, passare a Filtra per posizione riga.

2. Alzare di livello le intestazioni: le intestazioni per la tabella finale si trovano ora nella prima riga della tabella. È possibile alzarle di livello come illustrato nell'immagine successiva.

   

Dopo aver alzato di livello le intestazioni di colonna, Power Query aggiunge automaticamente un nuovo passaggio Tipo modificato che rileva automaticamente i tipi di dati per ogni colonna. La query del file di esempio transform ha un aspetto simile all'immagine successiva.

Nota

Per altre informazioni su come alzare di livello e abbassare di livello le intestazioni di colonna, passare a Alzare di livello o abbassare di livello le intestazioni di colonna.

Attenzione

La funzione trasforma file si basa sui passaggi eseguiti nella query Trasforma file di esempio. Tuttavia, se si tenta di modificare manualmente il codice per la funzione di file Transform, verrà visualizzato un avviso che legge The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Richiamare una funzione personalizzata come nuova colonna

Dopo aver creato la funzione personalizzata e tutti i passaggi di trasformazione incorporati, è possibile tornare alla query originale in cui è presente l'elenco di file dalla cartella (file CSV in questo esempio). Nella scheda Aggiungi colonna della barra multifunzione selezionare Richiama funzione personalizzata dal gruppo Generale. Nella finestra Invoke Custom Function (Richiama funzione personalizzata) immettere Output Table (Tabella di output) come Nome nuova colonna. Selezionare il nome della funzione, Trasforma file, dall'elenco a discesa Query funzione. Dopo aver selezionato la funzione dal menu a discesa, viene visualizzato il parametro per la funzione ed è possibile selezionare la colonna dalla tabella da usare come argomento per questa funzione. Selezionare la colonna Contenuto come valore/argomento da passare per il parametro file.

Dopo aver selezionato OK, viene creata una nuova colonna con il nome Tabella di output. Questa colonna contiene valori di tabella nelle celle, come illustrato nell'immagine successiva. Per semplicità, rimuovere tutte le colonne da questa tabella, ad eccezione di Name e Output Table.

Nota

Per altre informazioni su come scegliere o rimuovere colonne da una tabella, vedere Scegliere o rimuovere colonne.

La funzione è stata applicata a ogni singola riga della tabella usando i valori della colonna Content come argomento per la funzione. Ora che i dati vengono trasformati nella forma che si sta cercando, è possibile espandere la colonna Tabella di output selezionando l'icona Espandi. Non usare alcun prefisso per le colonne espanse.

È possibile verificare di avere dati di tutti i file nella cartella controllando i valori nella colonna Nome o Data . In questo caso, è possibile controllare i valori della colonna Date , in quanto ogni file contiene solo i dati per un singolo mese da un determinato anno. Se vengono visualizzati più file, significa che sono stati combinati correttamente i dati di più file in una singola tabella.

Nota

Quello che hai letto finora è fondamentalmente lo stesso processo che si verifica durante l'esperienza Combina file , ma fatto manualmente.

È consigliabile leggere anche l'articolo Combinare i file di panoramica e Combinare file CSV per comprendere meglio il funzionamento dell'esperienza di combinazione dei file in Power Query e il ruolo svolto dalle funzioni personalizzate.

Aggiungere un nuovo parametro alla funzione personalizzata esistente

Si supponga di aver creato un nuovo requisito. Il nuovo requisito richiede che prima di combinare i file, si filtrano i dati all'interno di essi per ottenere solo le righe in cui il Paese è uguale a Panama.

Per eseguire questo requisito, creare un nuovo parametro denominato Market con il tipo di dati text. Per Valore corrente immettere il valore Panama.

Con questo nuovo parametro, selezionare la query Trasforma file di esempio e filtrare il campo Country usando il valore del parametro Market .

Nota

Per altre informazioni su come filtrare le colonne in base ai valori, vedere Filtrare i valori.

L'applicazione di questo nuovo passaggio alla query aggiorna automaticamente la funzione Transform file , che ora richiede due parametri in base ai due parametri usati dal file Transform Sample.

Tuttavia, la query dei file CSV presenta un segno di avviso accanto. Ora che la funzione è stata aggiornata, sono necessari due parametri. Il passaggio in cui si richiama la funzione genera quindi valori di errore, poiché solo uno degli argomenti è stato passato alla funzione file Transform durante il passaggio Funzione personalizzata richiamata.

Per correggere gli errori, fare doppio clic su Funzione personalizzata richiamata nella procedura applicata per aprire la finestra Richiama funzione personalizzata. Nel parametro Market immettere manualmente il valore Panama.

È ora possibile tornare alla tabella di output espansa nei passaggi applicati. Controllare la query per verificare che vengano visualizzate solo le righe in cui Country è uguale a Panama nel set di risultati finale della query file CSV.

Creare una funzione personalizzata da una parte riutilizzabile della logica

Se sono presenti più query o valori che richiedono lo stesso set di trasformazioni, è possibile creare una funzione personalizzata che funge da componente riutilizzabile della logica. Successivamente, questa funzione personalizzata può essere richiamata in base alle query o ai valori di propria scelta. Questa funzione personalizzata consente di risparmiare tempo e di gestire il set di trasformazioni in una posizione centrale, che è possibile modificare in qualsiasi momento.

Si supponga, ad esempio, che una query con diversi codici come stringa di testo e si voglia creare una funzione che decodifica tali valori, come nella tabella di esempio seguente:

codice
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Si inizia con un parametro con un valore che funge da esempio. In questo caso, è il valore PTY-CM1090-LAX.

Da questo parametro si crea una nuova query in cui si applicano le trasformazioni necessarie. In questo caso, si vuole suddividere il codice PTY-CM1090-LAX in più componenti:

• Origin = PTY
• Destination = LAX
• Compagnia aerea = CM
• FlightID = 1090

Il codice M seguente illustra il set di trasformazioni.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Nota

Per altre informazioni sul linguaggio della formula M di Power Query, vedere Linguaggio di formula M di Power Query.

È quindi possibile trasformare la query in una funzione facendo clic con il pulsante destro del mouse sulla query e scegliendo Crea funzione. Infine, è possibile richiamare la funzione personalizzata in una qualsiasi delle query o dei valori, come illustrato nell'immagine successiva.

Dopo alcune altre trasformazioni, è possibile notare che è stato raggiunto l'output desiderato e applicato la logica per tale trasformazione da una funzione personalizzata.