Procedure consigliate quando si usa Power Query

Questo articolo contiene alcuni suggerimenti e consigli per sfruttare al meglio l'esperienza di data wrangling in Power Query.

Scegliere il connettore corretto

Power Query offre un numero elevato di connettori dati. Questi connettori variano da origini dati come i file TXT, CSV ed Excel, ai database come Microsoft SQL Server e i servizi SaaS più diffusi, ad esempio Microsoft Dynamics 365 e Salesforce. Se l'origine dati non è elencata nella finestra Recupera dati , è sempre possibile usare il connettore ODBC o OLEDB per connettersi all'origine dati.

L'uso del connettore migliore per l'attività offre un'esperienza e prestazioni ottimali. Ad esempio, l'uso del connettore SQL Server anziché del connettore ODBC quando ci si connette a un database di SQL Server non solo offre un'esperienza di recupero dei dati migliore, ma il connettore SQL Server offre anche funzionalità che possono migliorare l'esperienza e le prestazioni, ad esempio la riduzione delle query. Per saperne di più sulla riduzione delle query, consultare Panoramica della valutazione delle query e riduzione delle query in Power Query.

Ogni connettore dati segue un'esperienza standard, come illustrato in Recupero di dati. Questa esperienza standardizzata ha una fase denominata Anteprima dati. In questa fase viene fornita una finestra intuitiva per selezionare i dati che si desidera ottenere dall'origine dati, se il connettore lo consente, e per visualizzare una semplice anteprima dei dati. È anche possibile selezionare più set di dati dall'origine dati tramite la finestra Navigatore.

Annotazioni

Per visualizzare l'elenco completo dei connettori disponibili in Power Query, passare a Connettori in Power Query.

Filtra in anticipo

È sempre consigliabile filtrare i dati nelle prime fasi della query o il prima possibile. Alcuni connettori sfruttano i filtri tramite riduzione delle query, come descritto in Panoramica della valutazione delle query e riduzione delle query in Power Query. È anche consigliabile filtrare i dati che non sono rilevanti per il caso. Questo filtro consente di concentrarsi meglio sull'attività visualizzando solo i dati rilevanti nella sezione anteprima dei dati.

È possibile usare il menu filtro automatico che visualizza un elenco distinto dei valori trovati nella colonna per selezionare i valori da mantenere o filtrare. È anche possibile usare la barra di ricerca per trovare i valori nella colonna.

È anche possibile sfruttare i vantaggi dei filtri specifici del tipo, ad esempio Nel precedente per una colonna date, datetime o anche date timezone.

Screenshot di un filtro di tipo specifico di esempio per una colonna della data con l'opzione precedente evidenziata.

Questi filtri specifici del tipo consentono di creare un filtro dinamico che recupera sempre i dati presenti nel numero x precedente di secondi, minuti, ore, giorni, settimane, mesi, trimestri o anni.

Annotazioni

Per altre informazioni sul filtro dei dati in base ai valori di una colonna, passare a Filtra per valori.

Esegui le operazioni costose per ultime

Alcune operazioni richiedono la lettura dell'intera origine dati per restituire alcun risultato e quindi l'anteprima nell'editor di Power Query è lenta. Ad esempio, se si esegue un ordinamento, è possibile che le prime righe ordinate si trovino alla fine dei dati di origine. Pertanto, per restituire i risultati, l'operazione di ordinamento deve prima leggere tutte le righe.

Altre operazioni , ad esempio i filtri, non devono leggere tutti i dati prima di restituire risultati. Operano invece sui dati in modalità "streaming". I dati scorrono e i risultati vengono restituiti nel processo. Nell'editor di Power Query tali operazioni devono solo leggere abbastanza dati di origine per popolare l'anteprima.

Quando possibile, eseguire prima tali operazioni di streaming ed eseguire per ultime le operazioni più costose. L'esecuzione di operazioni in questo ordine consente di ridurre al minimo la quantità di tempo atteso per il rendering dell'anteprima ogni volta che si aggiunge un nuovo passaggio alla query.

Lavorare temporaneamente su un sottoinsieme di dati

Se l'aggiunta di nuovi passaggi alla query nell'editor di Power Query è lenta, è consigliabile prima eseguire un'operazione "Mantieni prime righe" e limitare il numero di righe in uso. Dopo aver aggiunto tutti i passaggi necessari, rimuovere il passaggio "Mantieni prime righe".

Usare i tipi di dati corretti

Alcune funzionalità di Power Query sono contestuali al tipo di dati della colonna selezionata. Ad esempio, quando si seleziona una colonna data, le opzioni disponibili nel gruppo di colonne Data e ora nel menu Aggiungi colonna sono utilizzabili. Tuttavia, se la colonna non dispone di un set di tipi di dati, queste opzioni vengono disattivate.

Si verifica una situazione simile per i filtri specifici del tipo, poiché sono specifici di determinati tipi di dati. Se nella colonna non è definito il tipo di dati corretto, questi filtri specifici del tipo non sono disponibili.

È fondamentale usare sempre i tipi di dati corretti per le colonne. Quando si lavora con origini dati strutturate, ad esempio i database, le informazioni sul tipo di dati vengono portate dallo schema della tabella presente nel database. Tuttavia, per le origini dati non strutturate, ad esempio i file TXT e CSV, è importante impostare i tipi di dati corretti per le colonne provenienti da tale origine dati. Per impostazione predefinita, Power Query offre un rilevamento automatico dei tipi di dati per le origini dati non strutturate. Puoi leggere di più su questa funzionalità e su come può esserti utile nei tipi di dati.

Annotazioni

Per altre informazioni sull'importanza dei tipi di dati e su come usarli, vedere Tipi di dati.

Esplora i tuoi dati

Prima di iniziare a preparare i dati e aggiungere nuovi passaggi di trasformazione, è consigliabile abilitare gli strumenti di profilatura dei dati di Power Query per individuare facilmente le informazioni sui dati.

Questi strumenti di profilatura dei dati consentono di comprendere meglio i dati. Gli strumenti offrono visualizzazioni di piccole dimensioni che mostrano informazioni per ogni colonna, ad esempio:

• Qualità colonna: fornisce un piccolo grafico a barre e tre indicatori con una rappresentazione del numero di valori nella colonna che rientrano nelle categorie di valori validi, di errore o vuoti.
• Distribuzione delle colonne: fornisce un set di oggetti visivi sotto i nomi delle colonne che mostrano la frequenza e la distribuzione dei valori in ognuna delle colonne.
• Profilo colonna: offre una visualizzazione più approfondita della colonna e delle statistiche associate.

È anche possibile interagire con queste funzionalità, che consente di preparare i dati.

Annotazioni

Per altre informazioni sugli strumenti di profilatura dei dati, vedere Strumenti di profilatura dei dati.

Documentare il lavoro

È consigliabile documentare le query rinominando o aggiungendo una descrizione ai passaggi, alle query o ai gruppi in base alle esigenze.

Anche se Power Query crea automaticamente un nome di passaggio nel riquadro passaggi applicati, è anche possibile rinominare i passaggi o aggiungere una descrizione a uno qualsiasi di essi.

Annotazioni

Per altre informazioni su tutte le funzionalità e i componenti disponibili disponibili all'interno del riquadro dei passaggi applicati, vedere Uso dell'elenco Passaggi applicati.

Adottare un approccio modulare

È possibile creare una singola query contenente tutte le trasformazioni e i calcoli necessari. Tuttavia, se la query contiene un numero elevato di passaggi, potrebbe essere consigliabile suddividere la query in più query, in cui una query fa riferimento alla successiva. L'obiettivo di questo approccio è semplificare e disaccoppiare le fasi di trasformazione in parti più piccole in modo che siano più facili da comprendere.

Si supponga, ad esempio, di avere una query con i nove passaggi illustrati nell'immagine seguente.

È possibile suddividere questa query in due al passaggio Unione con la tabella Prezzi. In questo modo è più semplice comprendere i passaggi applicati alla query di vendita prima dell'unione. Per eseguire questa operazione, fare clic con il pulsante destro del mouse sul passaggio Unione con la tabella Prezzi e selezionare l'opzione Estrai precedente .

Verrà quindi richiesto di assegnare un nome alla nuova query con una finestra di dialogo. Questo passaggio suddivide in modo efficace la query in due query. C'è una query che include tutte le query prima della fusione. L'altra query inizia con un passaggio che fa riferimento alla tua nuova query e prosegue con il resto dei passaggi che avevi nella tua query originale, a partire dal passaggio Merge with Prices in poi.

È anche possibile usare il riferimento alle query come meglio credi. Ma è consigliabile mantenere le tue query a un livello che non appaia scoraggiante a prima vista a causa dei numerosi passaggi.

Annotazioni

Per saperne di più sul riferimento alle query, vedere Informazioni sul riquadro query.

Crea gruppi

Un ottimo modo per organizzare il lavoro consiste nell'usare i gruppi nel riquadro query.

L'unico scopo dei gruppi è quello di mantenere il lavoro organizzato fungendo da cartelle per le query. È possibile creare gruppi all'interno dei gruppi in caso di necessità. Lo spostamento di query tra gruppi è semplice quanto il trascina e rilascia.

Provare a assegnare ai gruppi un nome significativo che abbia senso per te e il tuo caso.

Annotazioni

Per altre informazioni su tutte le funzionalità e i componenti disponibili disponibili all'interno del riquadro query, vedere Informazioni sul riquadro query.

Query a prova di futuro

Assicurarsi di creare una query che non presenti problemi durante un aggiornamento futuro è una priorità assoluta. In Power Query sono disponibili diverse funzionalità che rendono la query resiliente alle modifiche e può essere aggiornata anche quando alcuni componenti dell'origine dati cambiano.

È una buona prassi definire l'ambito della query specificando cosa dovrebbe fare e a cosa dovrebbe tenere conto in termini di struttura, layout, nomi delle colonne, tipi di dati e qualsiasi altro componente che si ritiene pertinente per l'ambito.

Di seguito sono riportati alcuni esempi di trasformazioni che consentono di rendere resiliente la query alle modifiche:

• Se la query ha un numero dinamico di righe con dati, ma un numero fisso di righe che fungono da piè di pagina da rimuovere, è possibile usare la funzionalità Rimuovi righe inferiori .

  Annotazioni

  Per altre informazioni su come filtrare i dati in base alla posizione della riga, vedere Filtrare una tabella in base alla posizione della riga.

• Se la query include un numero di colonne dinamico, ma è sufficiente selezionare colonne specifiche dal set di dati, è possibile usare la funzionalità Scegli colonne .

  Annotazioni

  Per altre informazioni sulla scelta o la rimozione di colonne, vedere Scegliere o rimuovere colonne.

• Se la query ha un numero di colonne dinamico e hai bisogno di effettuare l'unpivot solo su un sottoinsieme delle colonne, è possibile usare la funzionalità unpivot solo per le colonne selezionate.

  Annotazioni

  Per ulteriori informazioni sulle opzioni di annullamento del pivot delle colonne, vai a Unpivot columns.

• Se la query ha un passaggio che modifica il tipo di dati di una colonna, ma alcune celle generano errori perché i valori non sono conformi al tipo di dati desiderato, è possibile rimuovere le righe che hanno restituito valori di errore.

  Annotazioni

  Per altre informazioni sull'uso e sulla gestione degli errori, vedere Gestione degli errori.

Utilizzare i parametri

La creazione di query dinamiche e flessibili è una procedura consigliata. I parametri in Power Query consentono di rendere le query più dinamiche e flessibili. Un parametro funge da modo per archiviare e gestire facilmente un valore che può essere riutilizzato in molti modi diversi. Ma è più comunemente usato in due scenari:

• Argomento passaggio: è possibile usare un parametro come argomento di più trasformazioni guidate dall'interfaccia utente.

  

• Argomento Funzione personalizzata: È possibile creare una nuova funzione da una query e fare riferimento ai parametri come argomenti della funzione personalizzata.

  Screenshot del menu di scelta rapida delle Query con l'opzione Crea funzione evidenziata e la finestra di dialogo Crea funzione.

I principali vantaggi della creazione e dell'uso dei parametri sono i seguenti:

• Visualizzazione centralizzata di tutti i parametri tramite la finestra Gestisci parametri .

  

• Riutilizzabilità del parametro in più fasi o query.

• Rende la creazione di funzioni personalizzate semplice e immediata.

È anche possibile usare i parametri in alcuni degli argomenti dei connettori dati. Ad esempio, è possibile creare un parametro per il nome del server durante la connessione al database di SQL Server. È quindi possibile usare tale parametro all'interno della finestra di dialogo del database di SQL Server.

Se si modifica il percorso del server, è sufficiente aggiornare il parametro per il nome del server e le query vengono aggiornate.

Annotazioni

Per altre informazioni sulla creazione e l'uso dei parametri, vedere Uso dei parametri.

Creare funzioni riutilizzabili

È possibile che si trovi in una situazione in cui è necessario applicare lo stesso set di trasformazioni a query o valori diversi. In questo caso, potrebbe essere utile creare una funzione personalizzata di Power Query che può essere riutilizzata quante volte necessario. 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.

Si supponga, ad esempio, di avere più query o valori che richiedono lo stesso set di trasformazioni. È possibile creare una funzione personalizzata che in un secondo momento potrebbe 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.

Le funzioni personalizzate di Power Query possono essere create da query e parametri esistenti. Si supponga, ad esempio, che una query con diversi codici come stringa di testo e si voglia creare una funzione che decodifica tali valori.

Si inizia con un parametro con un valore che funge da esempio.

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

È 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 qualsiasi delle tue query o valori.

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

Annotazioni

Per altre informazioni su come creare e usare funzioni personalizzate in Power Query, vedere l'articolo Funzioni personalizzate.