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 dati molto migliore, ma il connettore SQL Server offre anche funzionalità che possono migliorare l'esperienza e le prestazioni, ad esempio la riduzione delle query. Per altre informazioni sulla riduzione delle query, vedere 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 una semplice anteprima dei dati di tali dati. È anche possibile selezionare più set di dati dall'origine dati tramite la finestra Strumento di navigazione , come illustrato nell'immagine seguente.

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. In questo modo è possibile 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.

Questi filtri specifici del tipo consentono di creare un filtro dinamico che recupererà sempre i dati presenti nel numero x precedente di secondi, minuti, ore, giorni, settimane, mesi, trimestri o anni, come illustrato nell'immagine seguente.

Eseguire operazioni costose per ultima

Alcune operazioni richiedono la lettura dell'origine dati completa per restituire i risultati e quindi saranno lente nell'anteprima nella editor di Power Query. 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 una modalità di "streaming". I dati "flussi" per e i risultati vengono restituiti lungo la strada. Nella editor di Power Query, tali operazioni devono solo leggere abbastanza dei dati di origine per popolare l'anteprima.

Quando possibile, eseguire prima tali operazioni di streaming ed eseguire altre operazioni più costose. Ciò consentirà 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 subset di dati

Se l'aggiunta di nuovi passaggi alla query nella 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, saranno disponibili le opzioni disponibili nel gruppo di colonne Data e ora nel menu Aggiungi colonna . Tuttavia, se la colonna non dispone di un set di tipi di dati, queste opzioni verranno 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 saranno disponibili.

È fondamentale usare sempre i tipi di dati corretti per le colonne. Quando si utilizzano origini dati strutturate, ad esempio i database, le informazioni sul tipo di dati verranno portate dallo schema della tabella trovato 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. Altre informazioni su questa funzionalità e su come possono essere utili nei tipi di dati.

Esplorare i 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 la 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 consentono di preparare i 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.

Adottare un approccio modulare

È completamente 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 nel 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 visualizzata una finestra di dialogo per assegnare un nome alla nuova query. In questo modo la query verrà suddivisa in modo efficace in due query. Una query avrà tutte le query prima dell'unione. L'altra query avrà un passaggio iniziale che farà riferimento alla nuova query e al resto dei passaggi della query originale della tabella Merge with Prices verso il basso.

È anche possibile sfruttare l'uso di riferimenti alle query nel modo desiderato. Ma è consigliabile mantenere le query a un livello che non sembra scoraggiante a prima vista con così tanti passaggi.

Crea gruppi

Un ottimo modo per mantenere organizzato 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 trascinamento della selezione.

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

Query di correzione future

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.

È consigliabile definire l'ambito della query come eseguire le operazioni da eseguire e le operazioni da tenere in considerazione in termini di struttura, layout, nomi di colonne, tipi di dati e qualsiasi altro componente considerato 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.

  Nota

  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 .

  Nota

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

• Se la query dispone di un numero di colonne dinamico ed è necessario annullare ilpivot solo un subset delle colonne, è possibile usare la funzionalità unpivot solo per le colonne selezionate.

  Nota

  Per altre informazioni sulle opzioni per annullare il pivot delle colonne, passare a Unpivot columns .To learn more about the options to unpivot columns, go to 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.

  Nota

  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 parametri di riferimento come argomenti della funzione personalizzata.

  

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ù passaggi o query.

• Semplifica e semplifica la creazione di funzioni personalizzate.

È 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 verranno aggiornate.

Creare funzioni riutilizzabili

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.

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, di creare una query con diversi codici come stringa di testo e di voler 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:

• Origin = PTY
• Destination = 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 una qualsiasi delle query o dei valori, come illustrato nell'immagine seguente.

Dopo alcune altre trasformazioni, è possibile notare che è stato raggiunto l'output desiderato e si è usata la logica per una trasformazione di questo tipo da una funzione personalizzata.