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.
È possibile creare manualmente una funzione personalizzata di Power Query usando il linguaggio di formula M di Power Query o l'interfaccia utente di Power Query offre funzionalità per velocizzare, semplificare e migliorare il processo di creazione e gestione di una funzione personalizzata.
Prima di tutto verranno illustrati i passaggi di base per la creazione di una funzione personalizzata con codice nell'interfaccia utente, quindi ci concentreremo sull'uso dell'interfaccia per trasformare azioni complesse in una funzione riutilizzabile.
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 altri articoli nella documentazione di Power Query per altre informazioni sulle trasformazioni specifiche a cui si fa riferimento in questo articolo.
Creare una funzione personalizzata dal codice nell'interfaccia utente
Nota
I passaggi seguenti possono essere seguiti in Power BI Desktop o usando l'esperienza di Power Query disponibile in Excel per Windows.
- Usare l'esperienza del connettore per connettersi ai dati dove sono memorizzati. Dopo aver selezionato i dati, selezionare il pulsante Trasforma dati o Modifica. Verrà visualizzata l'esperienza di Power Query.
- Fare clic con il pulsante destro del mouse su un punto vuoto nel riquadro query a sinistra.
- Selezionare Query Vuota.
- Nella nuova finestra di query vuota, selezionare il menu Home, quindi l'Editor avanzato.
- Sostituire il modello con la funzione personalizzata. Per esempio:
let HelloWorld = () => ("Hello World") in HelloWorld - Selezionare Fine.
Per altre informazioni sullo sviluppo di funzioni personalizzate con il linguaggio di formula M di Power Query, vedere questo articolo: Informazioni sulle funzioni M di Power Query. Nelle sezioni seguenti sono disponibili esercitazioni che descrivono come usare l'interfaccia utente di Power Query per sviluppare funzioni personalizzate senza scrivere codice e istruzioni su come richiamare la funzione personalizzata nella query.
Esercitazione sulla creazione di una funzione personalizzata da una tabella di riferimento
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, vedere Cartelle. 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, utilizzare il connettore Cartelle per andare 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 di vostra scelta 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 risulta essere il file Aprile 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. Rinomina questa query in file di esempio.
Creare un nuovo parametro con il nome Parametro File e tipo Binario. 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 della finestra di dialogo Parametri nella lista a discesa Tipo quando si ha una query che viene valutata come un 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 Parametro file dal riquadro query. Selezionare l'opzione riferimento .
Rinominare la query appena creata da parametro file (2) in Trasforma file di esempio.
Fare clic con il pulsante destro del mouse su questo nuovo file di esempio di trasformazione della query e selezionare l'opzione Crea funzione.
Questa operazione crea efficacemente una nuova funzione che si collega alla query del file di esempio di trasformazione . Tutte le modifiche apportate al file Transform Sample query vengono replicate automaticamente nella funzione personalizzata. Durante la creazione di questa nuova funzione, usare file Transform 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 nel file di esempio di trasformazione della query.
- La query di esempio del file Transform, comunemente nota come query di esempio .
- La funzione appena creata, in questo caso file Transform.
Applicazione di trasformazioni a una query di esempio
Dopo aver creato la nuova funzione, selezionare la query con il nome Trasformare File di esempio. Questa query è ora collegata al file di trasformazione e alla funzione, pertanto tutte le modifiche apportate a questa query si riflettono nella funzione. Questa connessione è ciò che viene definito come il concetto di una 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 superiori. 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 campione di trasformazione sono:
Rimuovere le prime quattro righe: questa azione elimina le righe considerate parte della sezione di intestazione del file.
Nota
Per altre informazioni su come rimuovere righe o filtrare una tabella in base alla posizione della riga, passare a Filtrare per posizione di riga.
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, Per impostazione predefinita, Power Query aggiunge automaticamente un nuovo tipo modificato passaggio che rileva automaticamente i tipi di dati per ogni colonna. La query del file di esempio di trasformazione somiglia all'immagine successiva.
Nota
Per altre informazioni su come alzare di livello e abbassare di livello le intestazioni, passare a Alzare di livello o abbassare di livello le intestazioni di colonna.
Cautela
La funzione del file Transform si basa sui passaggi eseguiti nella query del file di esempio Transform. Tuttavia, se si tenta di modificare manualmente il codice per la funzione 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 dei file dalla cartella (file CSV in questo esempio). Nella scheda Aggiungi Colonna nel nastro, seleziona Invoca Funzione Personalizzata dal gruppo Generale. Nella finestra Invoke Custom Function (Richiama funzione personalizzata), immettere Output Tabella come Nuovo nome colonna. Selezionare il nome della funzione, Trasforma file, dall'elenco a discesa delle 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 include valori table nelle relative celle, come illustrato nell'immagine successiva. Per semplicità, rimuovere tutte le colonne da questa tabella ad eccezione di Name e Tabella di output.
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 desiderata, è 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 data, 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 di Combinare i file, ma fatto manualmente.
È consigliabile leggere anche l'articolo sulla panoramica Combinare i file 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
Immagina che ci sia un nuovo requisito oltre a quello che hai già costruito. Il nuovo requisito richiede che prima di combinare i file, filtrare 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 il valore corrente , inserire il valore Panama.
Con questo nuovo parametro, selezionare la query file di trasformazione di esempio e filtrare il campo Paese utilizzando il valore del parametro Mercato.
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 del file Transform , che ora richiede due parametri in base ai due parametri usati dal file di esempio Transform .
Tuttavia, il file CSV query ha un segno di avviso accanto. Ora che la funzione è stata aggiornata, sono necessari due parametri. Pertanto, il passaggio in cui si richiama la funzione porta a valori di errore, poiché solo uno degli argomenti è stato passato alla funzione di trasformazione file durante il passaggio di Invocazione Funzione Personalizzata .
Per correggere gli errori, fare doppio clic su funzione personalizzata richiamata nei passaggi applicati 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 solo le righe in cui Country sia uguale a Panama vengano visualizzate nel risultato 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, si tratta del 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:
- Origine = PTY
- destinazione = 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 ulteriori informazioni sul linguaggio di formula M di Power Query, vai a 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 selezionando 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.
